looking for a better query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

looking for a better query

Author

Topic

yumyum113
Starting Member

31 Posts

Posted - 2007-05-02 : 18:49:14

Hi Guys,

Hope you could again help me with this one. I was wondering if there is another way to do a query that I have written.

Here is the sample data

declare @monthly_days_complete table
(
Rep_type varchar(100),
dtc int,
total int)

insert into @monthly_days_complete
select 'COBRA Report',2, 1 union all
select 'QMCSO',4,1 union all
select 'Hard Pend',3, 18 union all
select 'Hard Pend',2, 1 union all
select 'Hard Pend',7, 2 union all
select 'Hard Pend',13, 2 union all
select 'Hard Pend',26, 3 union all
select 'ID Card Error Report',0,3 union all
select 'ID Card Error Report',2,2 union all
select 'QMCSO',2,1 union all
select 'QMCSO',35,1 union all
select 'QMCSO',6,1 union all
select 'QMCSO',4,1

Here is the output that I want..

rep_type aging Total trans
------------------------- -------------------- -----------
COBRA Report Day 2 1
Hard Pend Day 15-28 3
Hard Pend Day 2 1
Hard Pend Day 3 18
Hard Pend Day 7-14 4
ID Card Error Report Day 0 3
ID Card Error Report Day 2 2
QMCSO Day 2 1
QMCSO Day 29+ 1
QMCSO Day 4 2
QMCSO Day 6 1

Here is how I do it...

declare @temptable table
(
Rep_type varchar(25),
dtc int,
Aging varchar(20),
TotalTrans int
)

insert into @temptable
select rep_type,dtc,null,sum(total)
from @monthly_days_complete
group by rep_type,dtc

update @temptable
set aging =
case
when dtc=0 then 'Day 0'
when dtc=1 then 'Day 1'
when dtc=2 then 'Day 2'
when dtc=3 then 'Day 3'
when dtc=4 then 'Day 4'
when dtc=5 then 'Day 5'
when dtc=6 then 'Day 6'
when dtc>=7 and dtc <=14 then 'Day 7-14'
when dtc>=15 and dtc <=28 then 'Day 15-28'
when dtc>28 then 'Day 29+'
end

select rep_type,aging,sum(totaltrans)
from @temptable
group by rep_type,aging
order by rep_type

QUESTION: Could the output that I'am looking for be done with one query without using a temporary table?

Thanks in advance for taking time.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-02 : 19:16:08

[code]SELECT
rep_type,
case
when dtc=0 then 'Day 0'
when dtc=1 then 'Day 1'
when dtc=2 then 'Day 2'
when dtc=3 then 'Day 3'
when dtc=4 then 'Day 4'
when dtc=5 then 'Day 5'
when dtc=6 then 'Day 6'
when dtc>=7 and dtc <=14 then 'Day 7-14'
when dtc>=15 and dtc <=28 then 'Day 15-28'
when dtc>28 then 'Day 29+'
END AS aging,
sum(total)
FROM
@monthly_days_complete
group by
rep_type,
case
when dtc=0 then 'Day 0'
when dtc=1 then 'Day 1'
when dtc=2 then 'Day 2'
when dtc=3 then 'Day 3'
when dtc=4 then 'Day 4'
when dtc=5 then 'Day 5'
when dtc=6 then 'Day 6'
when dtc>=7 and dtc <=14 then 'Day 7-14'
when dtc>=15 and dtc <=28 then 'Day 15-28'
when dtc>28 then 'Day 29+'
END
ORDER BY
rep_type[/code]