help with CASE / Cross tab report

• joe the sql dude

  Newbie

  Points: 4

  More actions

  January 9, 2006 at 9:14 am

  #92548

  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)

• R. Brush

  SSCommitted

  Points: 1669

  More actions

  January 10, 2006 at 12:29 pm

  #613906

  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])