April 1, 2005 at 8:02 am
Can I produce following report using T-SQL:
(colum headers)
LocationName9:00 am - 10:00 am10:00 am - 11:00 am11:00 am - 12:00 pm12:00 pm - 1:00 pm
(column values)
Location AJohn Doe4202020
Location BJane Doe20202010
Location BJohn Smith44420
When it is stored in the following way...
(colum headers)
Location NameStartDateEndDateStatus
(colum values)
Location AJohn Doe1/21/05 9:00 AM1/21/05 10:00 AM4
Location AJohn Doe1/21/05 10:00 AM1/21/05 11:00 AM20
Location AJohn Doe1/21/05 11:00 AM1/21/05 12:00 AM20
Location AJohn Doe1/21/05 12:00 PM1/21/05 1:00 PM20
Location BJane Doe1/21/05 9:00 AM1/21/05 10:00 AM20
Location BJane Doe1/21/05 10:00 AM1/21/05 11:00 AM20
Location BJane Doe1/21/05 11:00 AM1/21/05 12:00 AM20
Location BJane Doe1/21/05 12:00 PM1/21/05 1:00 PM10
Location BJohn Smith1/21/05 9:00 AM1/21/05 10:00 AM4
Location BJohn Smith1/21/05 10:00 AM1/21/05 11:00 AM4
Location BJohn Smith1/21/05 11:00 AM1/21/05 12:00 AM4
Location BJohn Smith1/21/05 12:00 PM1/21/05 1:00 PM10
Note that the output disregards the date portion of the value and only displays the time in header rows.
Also if I wanted the headers to be static and show every hour of the day from 9:00 am to 9:00 pm (ignoring whatever values the table may have for start date and end date; except to determine which column the respective value lie).
Again would I be able to get this using T-SQL... ThanX 4 d help
April 4, 2005 at 8:00 am
This was removed by the editor as SPAM
April 4, 2005 at 3:46 pm
This should get you started. You'll have to clean it up a little though...
SELECT
Location,
Name,
MAX(CASE StartDate when '9:00am' then Status ELSE NULL END) AS [9:00 am - 10:00 am],
MAX(CASE StartDate when '10:00am' then Status ELSE NULL END) AS [10:00 am - 11:00 am]
MAX(CASE StartDate when '11:00am' then Status ELSE NULL END) AS [11:00 am - 12:00 pm]
MAX(CASE StartDate when '12:00pm' then Status ELSE NULL END) AS [12:00 am - 1:00 pm]
FROM Table1
GROUP BY Location, Name
If you want to disassemble it, comment out the group by and get rid of the MAX(). That will output your whole table with a bunch of nulls. work the case statements until you get your time periods working. you may want to use BETWEEN or something. The group by and max is to get rid of the nulls. Many people use sum instead of max to do similar time series reporting.
Yukon will have new syntax to make this easier, but this works with any ANSI database.
Ray Metz
Redmond, WA
April 7, 2005 at 9:25 am
I got quite the same. I also included year and day of year in the group by clause to work on more thant one day.
select ct.Location, ct.Name, datepart(year, startdate) as theYear, datepart(dayofyear, startdate) theDay,
max((case datepart(hour, startdate) when 9 then 1 else 0 end)*status) as h9,
max((case datepart(hour, startdate) when 10 then 1 else 0 end)*status) as h10,
max((case datepart(hour, startdate) when 11 then 1 else 0 end)*status) as h11,
max((case datepart(hour, startdate) when 12 then 1 else 0 end)*status) as h12
from crosstab ct
group by ct.location, ct.name, datepart(year, startdate), datepart(dayofyear, startdate)
order by theYear, theDay
Patrick Duflot
April 9, 2005 at 12:56 pm
Thanx guys... greatly appreciated your response. I came up with the same solution the day after I sent the post. At least now i know i wasn't far off
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply