Formatting SQL Results Question

[JAPixley]

I've got a question which is probably pretty basic. I have a database which has 35-36 day's worth of data. I am trying to format the output of a query so that it can easily be cut and pasted into a spreadsheet. I'm guessing that I probably need a cube or some such, but cannot get it to work. The query below counts occurrences of a greeting message being played for an IVR:
select datepart(dw, cs.stat_start_date) as [Day],
substring(cs.stat_start_date_time,1,8) as [Date],
count(*) as [Count]
from call_statistics as cs inner join
statistics_events as se on
cs.stat_call_id = se.ehst_call_id and
cs.stat_process_id = se.ehst_process_id and
cs.stat_script_name = se.ehst_script_name
where se.ehst_script_name = 'TMI'
and se.ehst_code = 101 ---> Greeting Message Played
group by substring(cs.stat_start_date_time,1,8),
datepart(dw, cs.stat_start_date)
order by datepart(dw, cs.stat_start_date),
substring(cs.stat_start_date_time,1,8)

I am using the datepart function to group the data because I ultimately want to format the data as shown below:
Sun Mon Tue Wed Thu Fri Sat W/E
-- -- 30 27 25 20 10 20061202
17 52 33 26 22 21 11 20061209
19 55 34 28 23 22 12 20061216
20 69 41 29 24 20 11 20061223
15 18 60 41 30 23 13 20061230
19 26 57 -- -- -- -- 20070106

Is there some way to get SQL 2000 to do this for me or will the last step have to remain manual? Thank you in advance for your help.

 

[gmmastros]

Here's a 'nudge' in the right direction. There may be flaws with this query, but it should get you started. Hopefully it helps.

Select  Max(Case When DateName(Weekday, TheDate) = 'Sunday'
                 Then TheCount
                 End) As SundayCount,
        Max(Case When DateName(Weekday, TheDate) = 'Monday'
                 Then TheCount
                 End) As MondayCount,
        Max(Case When DateName(Weekday, TheDate) = 'Tuesday'
                 Then TheCount
                 End) As TuesdayCount,
        Max(Case When DateName(Weekday, TheDate) = 'Wednesday'
                 Then TheCount
                 End) As WednesdayCount,
        Max(Case When DateName(Weekday, TheDate) = 'Thursday'
                 Then TheCount
                 End) As ThursdayCount,
        Max(Case When DateName(Weekday, TheDate) = 'Friday'
                 Then TheCount
                 End) As FridayCount,
        Max(Case When DateName(Weekday, TheDate) = 'Saturday'
                 Then TheCount
                 End) As SaturdayCount,
        DateAdd(Week, DateDiff(Week, 0, TheDate), 0) As WeekEnding
From    (
        select  DateAdd(Day, DateDiff(Day, 0, cs.stat_start_date_time), 0) As TheDate,
                count(*) as [TheCount]
        from  call_statistics as cs inner join
          statistics_events as se on
            cs.stat_call_id = se.ehst_call_id and
            cs.stat_process_id = se.ehst_process_id and
            cs.stat_script_name = se.ehst_script_name
        where  se.ehst_script_name = 'TMI'
          and se.ehst_code = 101   ---> Greeting Message Played
        group  by DateAdd(Day, DateDiff(Day, 0, cs.stat_start_date_time), 0)
        ) As DailyCounts
Group By DateAdd(Week, DateDiff(Week, 0, TheDate), 0)
Order By DateAdd(Week, DateDiff(Week, 0, TheDate), 0) As WeekEnding

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JAPixley]

George,

Thank you so much! This is almost exactly what I was looking for, with just enough nit-picking left for me to figure out how this works in the process.

-Alex