Query Help: Counts & Sums

microsoft.public.sqlserver.programming

8 Dec 2005 7:50 PM

JDP@Work

How do I get the individual awrv sums for each region_group, I'm real close but
no cigar....

I've used coalesce as each record will only have one install_date based on the
sale stage of 50, 75 or 90

Each record has the total sum(awrv) not the specific region_group in my ddl &
qry below.

TIA

JeffP....

set nocount on
go
create table #fiscal_month (fisc_MoName varchar(15) ,fisc_Year int ,fisc_MoStart
datetime ,fisc_MoEnd datetime)
insert #fiscal_month
select 'November' ,'2005' ,'10/29/05' ,'11/25/05'
insert #fiscal_month
select 'December','2005','11/26/05','12/30/05'
insert #fiscal_month
select 'January','2006','12/31/05','1/27/06'
insert #fiscal_month
select 'Febuary','2006','1/28/06','2/24/06'

create table #estimator (id int ,region_group varchar(20) ,awrv int
,install_date_50 datetime ,install_date_75 datetime ,install_date_90 datetime)
insert #estimator
select 1 ,'C' ,125 ,null ,'12/15/2005' ,null
insert #estimator
select 2 ,'C' ,45 ,null ,'01/06/2006' ,null
insert #estimator
select 3 ,'W' ,500 ,'01/03/2006' ,null ,null
insert #estimator
select 4 ,'W' ,100 ,null ,'02/04/2005' ,null
insert #estimator
select 5 ,'S' ,320 ,'12/28/2005' ,null ,null
insert #estimator
select 6 ,'S' ,75 ,null ,'01/13/2006' ,null
insert #estimator
select 7 ,'E' ,90 ,'12/28/2005' ,null ,null
insert #estimator
select 8 ,'E' ,215 ,null ,'12/21/2005' ,null
insert #estimator
select 9 ,'C' ,35 ,null ,'02/02/2005' ,null

select Region = case
when cast(region_group as varchar(1)) = 'C' then 'Central'
when cast(region_group as varchar(1)) = 'E' then 'Eastern'
when cast(region_group as varchar(1)) = 'S' then 'Southern'
when cast(region_group as varchar(1)) = 'W' then 'Western'
else 'Other' end
,count(region_group)[Counts]
,Install_2 = (select sum(awrv) from #estimator where
coalesce(install_date_90,install_date_75,install_date_50) between
   (select fisc_moStart from #fiscal_month where fisc_moname in(select case when
datepart(dd,getdate()) <= 15 then datename(m,getdate())
           when datepart(dd,getdate()) > 15 then datename(m,getdate()+30) end))
   and (select fisc_moEnd from #fiscal_month where fisc_moname in(select case
when datepart(dd,getdate()) <= 15 then datename(m,getdate())
           when datepart(dd,getdate()) > 15 then datename(m,getdate()+30)
end)))
,Install_2 = (select sum(awrv) from #estimator where
coalesce(install_date_90,install_date_75,install_date_50) between
   (select fisc_moStart from #fiscal_month where fisc_moname in(select case when
datepart(dd,getdate()) <= 15 then datename(m,dateadd(m,1,getdate()))
           when datepart(dd,getdate()) > 15 then
datename(m,dateadd(m,1,getdate())+30) end))
   and (select fisc_moEnd from #fiscal_month where fisc_moname in(select case
when datepart(dd,getdate()) <= 15 then datename(m,dateadd(m,1,getdate()))
           when datepart(dd,getdate()) > 15 then
datename(m,dateadd(m,1,getdate())+30) end)))
, Install_3 = (select sum(awrv) from #estimator where
coalesce(install_date_90,install_date_75,install_date_50) between
   (select fisc_moStart from #fiscal_month where fisc_moname in(select case when
datepart(dd,getdate()) <= 15 then datename(m,dateadd(m,2,getdate()))
           when datepart(dd,getdate()) > 15 then
datename(m,dateadd(m,2,getdate())+30) end))
   and (select fisc_moEnd from #fiscal_month where fisc_moname in(select case
when datepart(dd,getdate()) <= 15 then datename(m,dateadd(m,1,getdate()))
           when datepart(dd,getdate()) > 15 then
datename(m,dateadd(m,2,getdate())+30) end)))
from #estimator
group by cast(region_group as varchar(1))

drop table #fiscal_month
drop table #estimator

8 Dec 2005 9:37 PM

Erland Sommarskog

JDP@Work (JPGMTNoSpam@sbcglobal.net) writes:

> How do I get the individual awrv sums for each region_group, I'm real
> close but no cigar....
>
> I've used coalesce as each record will only have one install_date based
> on the sale stage of 50, 75 or 90
>
> Each record has the total sum(awrv) not the specific region_group in my
> ddl & qry below.

Thanks for the tables and the sample data. But there is one thing
missing: what is the expected results from this? I noticed that when I
ran this, that Install_3 only gave NULL values, but I was not able to
say whether this was expected.

When reformatting, I did spot one thing that looked wrong:

> , Install_3 = (select sum(awrv) from #estimator where
>...
> when datepart(dd,getdate()) <= 15 then datename(m,dateadd(m,1,getdate()))

Should that not be a 2 in the dateadd()?

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

8 Dec 2005 10:04 PM

JDP@Work

Yes that should be a 2

The expected data should be a total for each region, not the same total for all
regions.

My qry is ignoring the region and just totalling the install_n where that is
this month ,next and the next if it's on or before the 15th, after the 15th of
the month it's next month etc....

Still stumped....

JeffP......

Show quote

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns9726E62D7CD5CYazorman@127.0.0.1...
> JDP@Work (JPGMTNoSpam@sbcglobal.net) writes:
> > How do I get the individual awrv sums for each region_group, I'm real
> > close but no cigar....
> >
> > I've used coalesce as each record will only have one install_date based
> > on the sale stage of 50, 75 or 90
> >
> > Each record has the total sum(awrv) not the specific region_group in my
> > ddl & qry below.
>
> Thanks for the tables and the sample data. But there is one thing
> missing: what is the expected results from this? I noticed that when I
> ran this, that Install_3 only gave NULL values, but I was not able to
> say whether this was expected.
>
> When reformatting, I did spot one thing that looked wrong:
>
> > , Install_3 = (select sum(awrv) from #estimator where
> >...
> > when datepart(dd,getdate()) <= 15 then datename(m,dateadd(m,1,getdate()))
>
> Should that not be a 2 in the dateadd()?
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

10 Dec 2005 8:08 PM

Erland Sommarskog

JDP@Work (JPGMTNoSpam@sbcglobal.net) writes:

> Yes that should be a 2
>
> The expected data should be a total for each region, not the same total
> for all regions.

And those totals are? Sorry, for being nosy, but without knowing the
right numbers, it's difficult to say if a modified query returns the
correctl result or not. You may know how to compute the totals, but I
don't.

So here is a rewritten query, which may be completely useless:

select Region = case
when cast(region_group as varchar(1)) = 'C' then 'Central'
when cast(region_group as varchar(1)) = 'E' then 'Eastern'
when cast(region_group as varchar(1)) = 'S' then 'Southern'
when cast(region_group as varchar(1)) = 'W' then 'Western'
else 'Other' end,
count(region_group) [Counts], sum(e.awrv)
from (SELECT n = 0 UNION ALL SELECT 1 UNION ALL SELECT 2) AS n
join #estimator e ON coalesce(e.install_date_90, e.install_date_75,
                                 e.install_date_50) between
        (select fisc_MoStart
         from   #fiscal_month
         where fisc_MoName in
           (select case when datepart(dd,getdate()) <= 15 then
                             datename(m,dateadd(m, n.n, getdate()))
                        when datepart(dd,getdate()) > 15 then
                             datename(m,dateadd(m, n.n, getdate())+30)
                   end))           and
        (select fisc_MoEnd
         from   #fiscal_month
         where fisc_MoName in
            (select case when datepart(dd,getdate()) <= 15 then
                              datename(m, dateadd(m, n.n, getdate()))
                         when datepart(dd,getdate()) > 15 then
                             datename(m,dateadd(m, n.n, getdate())+30)
                    end))
group by cast(region_group as varchar(1)), n.n

8 Dec 2005 10:29 PM

Trey Walpole

You're getting the full totals because your sum subqueries aren't
correlating to the region in the outer table, just to the dates.

you can get better performance without those subqueries, though:
[and why the cast to varchar(1)? seems like overhead from the example...]
with these indexes, it showed a 4:1 improvement over the correctly
correlated original

create index nameind on #fiscal_month (fisc_moname)
create index dateind on #fiscal_month (fisc_mostart, fisc_moend)
create index regionind on #estimator (region_group)

e.g.
select case cast(region_group as varchar(1))
   when 'C' then 'Central'
   when 'E' then 'Eastern'
   when 'S' then 'Southern'
   when 'W' then 'Western'
else 'Other' end as Region
,count(region_group) as [Counts]
,sum(case when fm1.fisc_moName is not null then awrv end) as Install_1
,sum(case when fm2.fisc_moName is not null then awrv end) as Install_2
,sum(case when fm3.fisc_moName is not null then awrv end) as Install_3
from #estimator e
   left join #fiscal_month fm1
       on coalesce(e.install_date_90, e.install_date_75,
e.install_date_50) between fm1.fisc_moStart and fm1.fisc_moEnd
    and fm1.fisc_moName = datename(m, getdate()+ case when
datepart(dd,getdate())<=15 then 0 else 30 end )
   left join #fiscal_month fm2
       on coalesce(e.install_date_90, e.install_date_75,
e.install_date_50) between fm2.fisc_moStart and fm2.fisc_moEnd
    and fm2.fisc_moName = datename(m, dateadd(m,1,getdate())+ case when
datepart(dd,getdate())<=15 then 0 else 30 end )
   left join #fiscal_month fm3
       on coalesce(e.install_date_90, e.install_date_75,
e.install_date_50) between fm3.fisc_moStart and fm3.fisc_moEnd
    and fm3.fisc_moName = datename(m, dateadd(m,2,getdate())+ case when
datepart(dd,getdate())<=15 then 0 else 30 end )
group by cast(region_group as varchar(1))

JDP@Work wrote:
Show quote

> How do I get the individual awrv sums for each region_group, I'm real close but
> no cigar....
>
> I've used coalesce as each record will only have one install_date based on the
> sale stage of 50, 75 or 90
>
> Each record has the total sum(awrv) not the specific region_group in my ddl &
> qry below.
>
> TIA
>
> JeffP....
>
>
> set nocount on
> go
> create table #fiscal_month (fisc_MoName varchar(15) ,fisc_Year int ,fisc_MoStart
> datetime ,fisc_MoEnd datetime)
> insert #fiscal_month
> select 'November' ,'2005' ,'10/29/05' ,'11/25/05'
> insert #fiscal_month
> select 'December','2005','11/26/05','12/30/05'
> insert #fiscal_month
> select 'January','2006','12/31/05','1/27/06'
> insert #fiscal_month
> select 'Febuary','2006','1/28/06','2/24/06'
>
> create table #estimator (id int ,region_group varchar(20) ,awrv int
> ,install_date_50 datetime ,install_date_75 datetime ,install_date_90 datetime)
> insert #estimator
> select 1 ,'C' ,125 ,null ,'12/15/2005' ,null
> insert #estimator
> select 2 ,'C' ,45 ,null ,'01/06/2006' ,null
> insert #estimator
> select 3 ,'W' ,500 ,'01/03/2006' ,null ,null
> insert #estimator
> select 4 ,'W' ,100 ,null ,'02/04/2005' ,null
> insert #estimator
> select 5 ,'S' ,320 ,'12/28/2005' ,null ,null
> insert #estimator
> select 6 ,'S' ,75 ,null ,'01/13/2006' ,null
> insert #estimator
> select 7 ,'E' ,90 ,'12/28/2005' ,null ,null
> insert #estimator
> select 8 ,'E' ,215 ,null ,'12/21/2005' ,null
> insert #estimator
> select 9 ,'C' ,35 ,null ,'02/02/2005' ,null
>
> select Region = case
> when cast(region_group as varchar(1)) = 'C' then 'Central'
> when cast(region_group as varchar(1)) = 'E' then 'Eastern'
> when cast(region_group as varchar(1)) = 'S' then 'Southern'
> when cast(region_group as varchar(1)) = 'W' then 'Western'
> else 'Other' end
> ,count(region_group)[Counts]
> ,Install_2 = (select sum(awrv) from #estimator where
> coalesce(install_date_90,install_date_75,install_date_50) between
>    (select fisc_moStart from #fiscal_month where fisc_moname in(select case when
> datepart(dd,getdate()) <= 15 then datename(m,getdate())
>            when datepart(dd,getdate()) > 15 then datename(m,getdate()+30) end))
>    and (select fisc_moEnd from #fiscal_month where fisc_moname in(select case
> when datepart(dd,getdate()) <= 15 then datename(m,getdate())
>            when datepart(dd,getdate()) > 15 then datename(m,getdate()+30)
> end)))
> ,Install_2 = (select sum(awrv) from #estimator where
> coalesce(install_date_90,install_date_75,install_date_50) between
>    (select fisc_moStart from #fiscal_month where fisc_moname in(select case when
> datepart(dd,getdate()) <= 15 then datename(m,dateadd(m,1,getdate()))
>            when datepart(dd,getdate()) > 15 then
> datename(m,dateadd(m,1,getdate())+30) end))
>    and (select fisc_moEnd from #fiscal_month where fisc_moname in(select case
> when datepart(dd,getdate()) <= 15 then datename(m,dateadd(m,1,getdate()))
>            when datepart(dd,getdate()) > 15 then
> datename(m,dateadd(m,1,getdate())+30) end)))
> , Install_3 = (select sum(awrv) from #estimator where
> coalesce(install_date_90,install_date_75,install_date_50) between
>    (select fisc_moStart from #fiscal_month where fisc_moname in(select case when
> datepart(dd,getdate()) <= 15 then datename(m,dateadd(m,2,getdate()))
>            when datepart(dd,getdate()) > 15 then
> datename(m,dateadd(m,2,getdate())+30) end))
>    and (select fisc_moEnd from #fiscal_month where fisc_moname in(select case
> when datepart(dd,getdate()) <= 15 then datename(m,dateadd(m,1,getdate()))
>            when datepart(dd,getdate()) > 15 then
> datename(m,dateadd(m,2,getdate())+30) end)))
> from #estimator
> group by cast(region_group as varchar(1))
>
> drop table #fiscal_month
> drop table #estimator
>
>

9 Dec 2005 12:12 PM

Microsoft_Public

Trey, Thanks you are a real life saver....

I was wrestling with viruses at a client for two days & trying to keep the
asp.Net programmer working and also fielding calls from clients..... to make
matters worse this project is 5 weeks behind with the first work being done
by me this week, I just got it last Sunday (doc's have a 10/28/05 date)!

Anyway, I will add indexes when I can focus on them.

I'm using dateadd, I'm assuming that it will be the same day one month later
rather than exactly 30 days, No I didn't test this.... :-)

I added isnull so that the results look pretty.

The reason that I use cast is performance above substring or left, as the
region_group looks like... E-OH,PA,WV,NY

I have 6 result sets the first two follow, the first just returns the 3
month names so that the asp.Net datagrid has them.

e.g.
select Install_1 = fm1.fisc_moName
,Install_2 = fm2.fisc_moname
,Install_3 = fm3.fisc_moname
from fiscal_month fm1
join fiscal_month fm2
on fm2.fisc_moname in(select fisc_moName = case when
datepart(dd,getdate()) <= 15 then datename(m,dateadd(m,1,getdate()))
   when datepart(dd,getdate()) > 15 then datename(m,dateadd(m,2,getdate()))
end)
join fiscal_month fm3
on fm3.fisc_moname in(select fisc_moName = case when
datepart(dd,getdate()) <= 15 then datename(m,dateadd(m,2,getdate()))
   when datepart(dd,getdate()) > 15 then datename(m,dateadd(m,3,getdate()))
end)
where fm1.fisc_moname in(select fisc_moName = case when
datepart(dd,getdate()) <= 15 then datename(m,getdate())
   when datepart(dd,getdate()) > 15 then datename(m,dateadd(m,1,getdate()))
end)

select case cast(region_group as varchar(1))
    when 'C' then 'Central'
    when 'E' then 'Eastern'
    when 'S' then 'Southern'
    when 'W' then 'Western'
   else 'Other' end as Region
   ,count(region_group) as [Counts]
   ,isnull(sum(case when fm1.fisc_moName is not null then
coalesce(install_amount_90,install_amount_75,install_amount_50) end),0)
Install_1
   ,isnull(sum(case when fm2.fisc_moName is not null then
coalesce(install_amount_90,install_amount_75,install_amount_50) end),0)
Install_2
   ,isnull(sum(case when fm3.fisc_moName is not null then
coalesce(install_amount_90,install_amount_75,install_amount_50) end),o)
Install_3
from estimator e
    left join fiscal_month fm1
        on coalesce(e.install_date_90,e.install_date_75,e.install_date_50)
between fm1.fisc_moStart and fm1.fisc_moEnd
and fm1.fisc_moName = case when datepart(dd,getdate()) <= 15 then
datename(m,getdate())
   when datepart(dd,getdate()) > 15 then datename(m,dateadd(m,1,getdate()))
end
    left join fiscal_month fm2
        on coalesce(e.install_date_90,e.install_date_75,e.install_date_50)
between fm2.fisc_moStart and fm2.fisc_moEnd
and fm2.fisc_moName = case when datepart(dd,getdate()) <= 15 then
datename(m,dateadd(m,1,getdate()))
   when datepart(dd,getdate()) > 15 then datename(m,dateadd(m,2,getdate()))
end
    left join fiscal_month fm3
        on coalesce(e.install_date_90,e.install_date_75,e.install_date_50)
between fm3.fisc_moStart and fm3.fisc_moEnd
and fm3.fisc_moName = case when datepart(dd,getdate()) <= 15 then
datename(m,dateadd(m,2,getdate()))
   when datepart(dd,getdate()) > 15 then datename(m,dateadd(m,3,getdate()))
end
group by cast(region_group as varchar(1))

Show quote

"Trey Walpole" <treypole@newsgroups.nospam> wrote in message
news:OsR%23jZE$FHA.3568@TK2MSFTNGP09.phx.gbl...
> You're getting the full totals because your sum subqueries aren't
> correlating to the region in the outer table, just to the dates.
>
> you can get better performance without those subqueries, though:
> [and why the cast to varchar(1)? seems like overhead from the example...]
> with these indexes, it showed a 4:1 improvement over the correctly
> correlated original
>
> create index nameind on #fiscal_month (fisc_moname)
> create index dateind on #fiscal_month (fisc_mostart, fisc_moend)
> create index regionind on #estimator (region_group)
>
>
> e.g.
> select case cast(region_group as varchar(1))
>   when 'C' then 'Central'
>   when 'E' then 'Eastern'
>   when 'S' then 'Southern'
>   when 'W' then 'Western'
> else 'Other' end as Region
> ,count(region_group) as [Counts]
> ,sum(case when fm1.fisc_moName is not null then awrv end) as Install_1
> ,sum(case when fm2.fisc_moName is not null then awrv end) as Install_2
> ,sum(case when fm3.fisc_moName is not null then awrv end) as Install_3
> from #estimator e
>   left join #fiscal_month fm1
>       on coalesce(e.install_date_90, e.install_date_75, e.install_date_50)
> between fm1.fisc_moStart and fm1.fisc_moEnd
>    and fm1.fisc_moName = datename(m, getdate()+ case when
> datepart(dd,getdate())<=15 then 0 else 30 end )
>   left join #fiscal_month fm2
>       on coalesce(e.install_date_90, e.install_date_75, e.install_date_50)
> between fm2.fisc_moStart and fm2.fisc_moEnd
>    and fm2.fisc_moName = datename(m, dateadd(m,1,getdate())+ case when
> datepart(dd,getdate())<=15 then 0 else 30 end )
>   left join #fiscal_month fm3
>       on coalesce(e.install_date_90, e.install_date_75, e.install_date_50)
> between fm3.fisc_moStart and fm3.fisc_moEnd
>    and fm3.fisc_moName = datename(m, dateadd(m,2,getdate())+ case when
> datepart(dd,getdate())<=15 then 0 else 30 end )
> group by cast(region_group as varchar(1))
>
> JDP@Work wrote:
>> How do I get the individual awrv sums for each region_group, I'm real
>> close but
>> no cigar....
>>
>> I've used coalesce as each record will only have one install_date based
>> on the
>> sale stage of 50, 75 or 90
>>
>> Each record has the total sum(awrv) not the specific region_group in my
>> ddl &
>> qry below.
>>
>> TIA
>>
>> JeffP....
>>
>>
>> set nocount on
>> go
>> create table #fiscal_month (fisc_MoName varchar(15) ,fisc_Year int
>> ,fisc_MoStart
>> datetime ,fisc_MoEnd datetime)
>> insert #fiscal_month
>> select 'November' ,'2005' ,'10/29/05' ,'11/25/05'
>> insert #fiscal_month
>> select 'December','2005','11/26/05','12/30/05'
>> insert #fiscal_month
>> select 'January','2006','12/31/05','1/27/06'
>> insert #fiscal_month
>> select 'Febuary','2006','1/28/06','2/24/06'
>>
>> create table #estimator (id int ,region_group varchar(20) ,awrv int
>> ,install_date_50 datetime ,install_date_75 datetime ,install_date_90
>> datetime)
>> insert #estimator
>> select 1 ,'C' ,125 ,null ,'12/15/2005' ,null
>> insert #estimator
>> select 2 ,'C' ,45 ,null ,'01/06/2006' ,null
>> insert #estimator
>> select 3 ,'W' ,500 ,'01/03/2006' ,null ,null
>> insert #estimator
>> select 4 ,'W' ,100 ,null ,'02/04/2005' ,null
>> insert #estimator
>> select 5 ,'S' ,320 ,'12/28/2005' ,null ,null
>> insert #estimator
>> select 6 ,'S' ,75 ,null ,'01/13/2006' ,null
>> insert #estimator
>> select 7 ,'E' ,90 ,'12/28/2005' ,null ,null
>> insert #estimator
>> select 8 ,'E' ,215 ,null ,'12/21/2005' ,null
>> insert #estimator
>> select 9 ,'C' ,35 ,null ,'02/02/2005' ,null
>>
>> select Region = case
>> when cast(region_group as varchar(1)) = 'C' then 'Central'
>> when cast(region_group as varchar(1)) = 'E' then 'Eastern'
>> when cast(region_group as varchar(1)) = 'S' then 'Southern'
>> when cast(region_group as varchar(1)) = 'W' then 'Western'
>> else 'Other' end
>> ,count(region_group)[Counts]
>> ,Install_2 = (select sum(awrv) from #estimator where
>> coalesce(install_date_90,install_date_75,install_date_50) between
>>    (select fisc_moStart from #fiscal_month where fisc_moname in(select
>> case when
>> datepart(dd,getdate()) <= 15 then datename(m,getdate())
>>            when datepart(dd,getdate()) > 15 then datename(m,getdate()+30)
>> end))
>>    and (select fisc_moEnd from #fiscal_month where fisc_moname in(select
>> case
>> when datepart(dd,getdate()) <= 15 then datename(m,getdate())
>>            when datepart(dd,getdate()) > 15 then datename(m,getdate()+30)
>> end)))
>> ,Install_2 = (select sum(awrv) from #estimator where
>> coalesce(install_date_90,install_date_75,install_date_50) between
>>    (select fisc_moStart from #fiscal_month where fisc_moname in(select
>> case when
>> datepart(dd,getdate()) <= 15 then datename(m,dateadd(m,1,getdate()))
>>            when datepart(dd,getdate()) > 15 then
>> datename(m,dateadd(m,1,getdate())+30) end))
>>    and (select fisc_moEnd from #fiscal_month where fisc_moname in(select
>> case
>> when datepart(dd,getdate()) <= 15 then datename(m,dateadd(m,1,getdate()))
>>            when datepart(dd,getdate()) > 15 then
>> datename(m,dateadd(m,1,getdate())+30) end)))
>> , Install_3 = (select sum(awrv) from #estimator where
>> coalesce(install_date_90,install_date_75,install_date_50) between
>>    (select fisc_moStart from #fiscal_month where fisc_moname in(select
>> case when
>> datepart(dd,getdate()) <= 15 then datename(m,dateadd(m,2,getdate()))
>>            when datepart(dd,getdate()) > 15 then
>> datename(m,dateadd(m,2,getdate())+30) end))
>>    and (select fisc_moEnd from #fiscal_month where fisc_moname in(select
>> case
>> when datepart(dd,getdate()) <= 15 then datename(m,dateadd(m,1,getdate()))
>>            when datepart(dd,getdate()) > 15 then
>> datename(m,dateadd(m,2,getdate())+30) end)))
>> from #estimator
>> group by cast(region_group as varchar(1))
>>
>> drop table #fiscal_month
>> drop table #estimator
>>