help with CASE / Cross tab report

Question

Post reply

help with CASE / Cross tab report

joe the sql dude

Newbie

Points: 4
More actions
January 9, 2006 at 9:14 am

#92547

I'm trying to get a cross tab report with two columns, '2005' & '2006' each grouped with month, day & hour showing the number of transactions. The SQL I have is
declare @lyr int
declare @lmonth int
declare @yr int
declare @mth int
set @lyr = datepart(yy, getdate()) -1
set @yr = datepart(yy, getdate())
set @mth = datepart(month, getdate())
select datepart(mm, dateadded) as 'Month', datepart(day, dateadded) as 'Day', datepart(hour, dateadded) as 'Hour', count(dateadded) as 'Count',
CASE datepart(yy, dateadded)
WHEN '06' THEN (select datepart(year, dateadded) as '2005')
WHEN '05' THEN (select datepart(year, dateadded) as '2006')
END
from orders
where (datepart(yy, dateadded) = @yr and datepart(mm, dateadded) = @mth) or (datepart(yy, dateadded) = @lyr and datepart(mm, dateadded) = @mth)
group by datepart(yy, dateadded), datepart(mm, dateadded), datepart(day, dateadded), datepart(hour, dateadded)

Desired output is:
2005

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Peter Schott SSCrazy Eights Points: 9675 More actions · Answer 1

I don't think what you're trying to do will work. You'll probably have to split out your COUNT into a CASE statement:

CASE WHEN Year = '2005'

THEN COUNT(2005)

ELSE 0

END as '2005'

CASE WHEN Year = '2006'

THEN Count(2006)

END as '2006'

Perhaps even doing something to get two derived tables (select count(*) from orders where Year = @lyr) as 'Data2005'

JOIN (select count(*) from orders where Year = @yr) as 'Data2006'

etc

-Pete

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 2

DECLARE @orders TABLE

(

OrderID INT,

dateadded DATETIME,

OrderDet VARCHAR(100)

)

INSERT @orders

SELECT 1, '01/01/2005', 'Det1' UNION

SELECT 2, '02/01/2005', 'Det2' UNION

SELECT 3, '03/01/2005', 'Det3' UNION

SELECT 4, '04/01/2005', 'Det4' UNION

SELECT 5, '07/01/2005', 'Det5' UNION

SELECT 6, '08/01/2005', 'Det6' UNION

SELECT 7, '01/05/2005', 'Det7' UNION

SELECT 8, '01/08/2005', 'Det8' UNION

SELECT 9, '01/09/2005', 'Det9' UNION

SELECT 10, '01/01/2006', 'Det10' UNION

SELECT 11, '01/01/2006', 'Det11' UNION

SELECT 12, '01/01/2006', 'Det12' UNION

SELECT 13, '01/01/2006', 'Det13' UNION

SELECT 14, '01/01/2006', 'Det14' UNION

SELECT 15, '01/01/2006', 'Det15' UNION

SELECT 16, '01/01/2006', 'Det16' UNION

SELECT 17, '01/01/2006', 'Det17' UNION

SELECT 18, '01/01/2006', 'Det18' UNION

SELECT 19, '01/01/2006', 'Det19'

declare @lyr int

declare @lmonth int

declare @yr int

declare @mth int

set @lyr = datepart(yy, getdate()) -1

set @yr = datepart(yy, getdate())

set @mth = datepart(month, getdate())

select datepart(mm, dateadded) as 'Month', datepart(day, dateadded) as 'Day', datepart(hour, dateadded) as 'Hour', count(dateadded) as 'Count',

Year2005 = SUM(CASE WHEN datepart(yy, dateadded) = 2005 THEN 1 ELSE 0 END),

Year2006 = SUM(CASE WHEN datepart(yy, dateadded) = 2006 THEN 1 ELSE 0 END)

from @orders

where ((datepart(yy, dateadded) = @yr and datepart(mm, dateadded) = @mth)) or ((datepart(yy, dateadded) = @lyr and datepart(mm, dateadded) = @mth))

group by datepart(yy, dateadded), datepart(mm, dateadded), datepart(day, dateadded), datepart(hour, dateadded)

Regards,
gova

noeld SSC Guru Points: 96590 More actions · Answer 3

Just to complement abit more govinn solution. Make sure "dateadded" does not contains NULLs and change

datepart(yy, dateadded) for year ( dateadded )

datepart(mm, dateadded) for month ( dateadded )

datepart(day, date added) for day ( dateadded )

Cheers,

* Noel

joe the sql dude Newbie Points: 4 More actions · Answer 4

close, but the results I'm getting are:

Month Day Hour Count Year2005 Year2006

----------- ----------- ----------- ----------- ----------- -----------

1 1 0 5 5 0

1 1 1 6 6 0

1 1 2 3 3 0

1 1 5 3 3 0

1 1 6 2 2 0

....... (and way down in the report I get the 2006 data)

1 1 0 17 0 17

1 1 1 40 0 40

1 1 2 59 0 59

1 1 5 78 0 78

What I'd like to see is:

count(2005) count(2006)

1 1 0 5 17

1 1 1 6 40

I want a side-by-side comparison of the two years for the count (broken down by month/day/hour).

Is this possible ?

R. Brush SSCommitted Points: 1669 More actions · Answer 5

SELECT MONTH([dateadded]) AS MonthOfYear

, DAY([dateadded]) as DayOfMonth

, datepart(hour, [dateadded]) as HourOfDay

, SUM(CASE WHEN YEAR([dateadded])='2005' THEN 1 ELSE 0 END) as Count2005

, SUM(CASE WHEN YEAR([dateadded])='2006' THEN 1 ELSE 0 END) as Count2006

FROM [orders]

WHERE YEAR([dateadded]) IN('2005', '2006')

GROUP BY MONTH([dateadded])

, DAY([dateadded])

, DATEPART(hour, [dateadded])

ORDER BY MONTH([dateadded])

, DAY([dateadded])

, DATEPART(hour, [dateadded])

noeld SSC Guru Points: 96590 More actions · Answer 6

Joe,

If you include the "DATE" in the grouping you always going to have the separation because a day can only belong to one and only one year

R. Brush sugestion will give you exactly what you need

Cheers,

* Noel

joe the sql dude Newbie Points: 4 More actions · Answer 7

Yes, that works great! Thanks for the help everyone !!