Group by expiration date

Is there a way to get this down to one select statement and group it as such
in Reporting services?
select custnmbr,contnbr,enddate,Cast(total as Money) from svc00600
where enddate >=getdate() and enddate <=getdate()+30
order by enddate asc
select custnmbr,contnbr,enddate,Cast(total as Money) from svc00600
where enddate >=getdate()+30 and enddate <=getdate()+60
order by enddate asc
select custnmbr,contnbr,enddate,Cast(total as Money) from svc00600
where enddate >=getdate()+60 and enddate <=getdate()+120
order by enddate ascYou could try either of the options below. Then group on the new
psuedo-field DaysOut
select custnmbr,contnbr,enddate,Cast(total as Money), '30' as DaysOut
from svc00600
where enddate >=getdate() and enddate <=getdate()+30
UNION
select custnmbr,contnbr,enddate,Cast(total as Money), '60' as DaysOut
from svc00600
where enddate >=getdate()+30 and enddate <=getdate()+60
UNION
select custnmbr,contnbr,enddate,Cast(total as Money), '120' as DaysOut
from svc00600
where enddate >=getdate()+60 and enddate <=getdate()+120
order by enddate asc
SELECT custnmbr, contnbr, enddate, CAST(total AS MONEY),
'DAYSOUT' = CASE
WHEN enddate >= getdate() AND enddate <= DATEADD(DD,30,getdate())
THEN '30'
WHEN enddate >= DATEADD(DD,30,getdate()) AND enddate <=DATEADD(DD,60,getdate()) THEN '60'
WHEN enddate >= DATEADD(DD,60,getdate()) AND enddate <=DATEADD(DD,120,getdate()) THEN '120'
END
FROM svc00600
WHERE enddate >=getdate() AND enddate <= DATEADD(DD,120,getdate())
ORDER BY enddate ASC|||Awesome, thanks.
"Ches" wrote:
> You could try either of the options below. Then group on the new
> psuedo-field DaysOut
>
> select custnmbr,contnbr,enddate,Cast(total as Money), '30' as DaysOut
> from svc00600
> where enddate >=getdate() and enddate <=getdate()+30
> UNION
> select custnmbr,contnbr,enddate,Cast(total as Money), '60' as DaysOut
> from svc00600
> where enddate >=getdate()+30 and enddate <=getdate()+60
> UNION
> select custnmbr,contnbr,enddate,Cast(total as Money), '120' as DaysOut
> from svc00600
> where enddate >=getdate()+60 and enddate <=getdate()+120
> order by enddate asc
>
> SELECT custnmbr, contnbr, enddate, CAST(total AS MONEY),
> 'DAYSOUT' = CASE
> WHEN enddate >= getdate() AND enddate <= DATEADD(DD,30,getdate())
> THEN '30'
> WHEN enddate >= DATEADD(DD,30,getdate()) AND enddate <=> DATEADD(DD,60,getdate()) THEN '60'
> WHEN enddate >= DATEADD(DD,60,getdate()) AND enddate <=> DATEADD(DD,120,getdate()) THEN '120'
> END
> FROM svc00600
> WHERE enddate >=getdate() AND enddate <= DATEADD(DD,120,getdate())
> ORDER BY enddate ASC
>|||Be Careful--this query will possibly produce wrong results. If you do
<=getdate()+30 in the first group and >=getdate()+30 in the second, you will
potentially have a customer show up in both results. Your query should look
like:
select custnmbr,contnbr,enddate,Cast(total as Money), '30' as DaysOut
from svc00600
where enddate >=getdate() and enddate <=getdate()+30
UNION
select custnmbr,contnbr,enddate,Cast(total as Money), '60' as DaysOut
from svc00600
where enddate >getdate()+30 and enddate <=getdate()+60
UNION
select custnmbr,contnbr,enddate,Cast(total as Money), '120' as DaysOut
from svc00600
where enddate >getdate()+60 and enddate <=getdate()+120
order by enddate asc
Even still, this query will list account with less than 30, between 31 than
60 and between 61 and 120--not necessarily the accounts that are 30 days
overdue, 60 days over or 120 days over as I would assume you are interested
in.
Brian
"Jeff Metcalf" <JeffMetcalf@.discussions.microsoft.com> wrote in message
news:BBC3F268-B818-40FB-9D58-BDD8A2199F6E@.microsoft.com...
> Awesome, thanks.
> "Ches" wrote:
>> You could try either of the options below. Then group on the new
>> psuedo-field DaysOut
>>
>> select custnmbr,contnbr,enddate,Cast(total as Money), '30' as DaysOut
>> from svc00600
>> where enddate >=getdate() and enddate <=getdate()+30
>> UNION
>> select custnmbr,contnbr,enddate,Cast(total as Money), '60' as DaysOut
>> from svc00600
>> where enddate >=getdate()+30 and enddate <=getdate()+60
>> UNION
>> select custnmbr,contnbr,enddate,Cast(total as Money), '120' as DaysOut
>> from svc00600
>> where enddate >=getdate()+60 and enddate <=getdate()+120
>> order by enddate asc
>>
>> SELECT custnmbr, contnbr, enddate, CAST(total AS MONEY),
>> 'DAYSOUT' = CASE
>> WHEN enddate >= getdate() AND enddate <= DATEADD(DD,30,getdate())
>> THEN '30'
>> WHEN enddate >= DATEADD(DD,30,getdate()) AND enddate <=>> DATEADD(DD,60,getdate()) THEN '60'
>> WHEN enddate >= DATEADD(DD,60,getdate()) AND enddate <=>> DATEADD(DD,120,getdate()) THEN '120'
>> END
>> FROM svc00600
>> WHERE enddate >=getdate() AND enddate <= DATEADD(DD,120,getdate())
>> ORDER BY enddate ASC
>>|||Hey,
Querying GreatPlains data, huh?
You can avoid the union query with the following:
select custnmbr,contnbr,enddate datediff(dd, enddate, getdate()),
aging30 = case when datediff(dd, enddate, getdate())<=30 then Cast(total as
money) else 0 end ,
aging60 = case when datediff(dd, enddate, getdate())Between 31 and 60 then
cast(total as money) else 0 end
from SVC00600 order by enddate desc
and if you really wanted you could even sum and group reight in this query
Have you thought about using a matrix report and doing the grouping on the
report?
HS
"goodman" <goodman93@.hotmail.com> wrote in message
news:OcbuGbj3FHA.2144@.TK2MSFTNGP09.phx.gbl...
: Be Careful--this query will possibly produce wrong results. If you do
: <=getdate()+30 in the first group and >=getdate()+30 in the second, you
will
: potentially have a customer show up in both results. Your query should
look
: like:
:
: select custnmbr,contnbr,enddate,Cast(total as Money), '30' as DaysOut
: from svc00600
: where enddate >=getdate() and enddate <=getdate()+30
: UNION
: select custnmbr,contnbr,enddate,Cast(total as Money), '60' as DaysOut
: from svc00600
: where enddate >getdate()+30 and enddate <=getdate()+60
: UNION
: select custnmbr,contnbr,enddate,Cast(total as Money), '120' as DaysOut
: from svc00600
: where enddate >getdate()+60 and enddate <=getdate()+120
: order by enddate asc
:
: Even still, this query will list account with less than 30, between 31
than
: 60 and between 61 and 120--not necessarily the accounts that are 30 days
: overdue, 60 days over or 120 days over as I would assume you are
interested
: in.
:
: Brian
:
:
: "Jeff Metcalf" <JeffMetcalf@.discussions.microsoft.com> wrote in message
: news:BBC3F268-B818-40FB-9D58-BDD8A2199F6E@.microsoft.com...
: > Awesome, thanks.
: >
: > "Ches" wrote:
: >
: >> You could try either of the options below. Then group on the new
: >> psuedo-field DaysOut
: >>
: >>
: >> select custnmbr,contnbr,enddate,Cast(total as Money), '30' as DaysOut
: >> from svc00600
: >> where enddate >=getdate() and enddate <=getdate()+30
: >> UNION
: >> select custnmbr,contnbr,enddate,Cast(total as Money), '60' as DaysOut
: >> from svc00600
: >> where enddate >=getdate()+30 and enddate <=getdate()+60
: >> UNION
: >> select custnmbr,contnbr,enddate,Cast(total as Money), '120' as DaysOut
: >> from svc00600
: >> where enddate >=getdate()+60 and enddate <=getdate()+120
: >> order by enddate asc
: >>
: >>
: >>
: >> SELECT custnmbr, contnbr, enddate, CAST(total AS MONEY),
: >> 'DAYSOUT' = CASE
: >> WHEN enddate >= getdate() AND enddate <= DATEADD(DD,30,getdate())
: >> THEN '30'
: >> WHEN enddate >= DATEADD(DD,30,getdate()) AND enddate <=: >> DATEADD(DD,60,getdate()) THEN '60'
: >> WHEN enddate >= DATEADD(DD,60,getdate()) AND enddate <=: >> DATEADD(DD,120,getdate()) THEN '120'
: >> END
: >> FROM svc00600
: >> WHERE enddate >=getdate() AND enddate <= DATEADD(DD,120,getdate())
: >> ORDER BY enddate ASC
: >>
: >>
:
: