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.
| Author |
Topic |
|
srivatsahg
Yak Posting Veteran
71 Posts |
 Posted - 2009-07-08 : 05:34:32
|
Hello I need some help in creating queries for the nonexisting data using pivots.My current query shows the number of events that have occured over the time in a day.The events and the eventdetails are read from the database before being printed on the crosstabi.e something like this. Note : there are no events between 3:00-3:59,5:00-5:59;6:00-6:59,7:00-7:59 (not present in the database)----------------------------------------------------- 01:00 02:00 04:00 08:00 10:00----------------------------------------Event X 1 1 4 1 3 Event Y 3 3 2 2 1----------------------------------------Total 4 4 6 3 4---------------------------------------- So far so good...Now i have to enhance my query to include the event details which have not happened i.e to include the time details where no events have happened 01:00 02:00 03:00 04:00 05:00 6:00 07:00 8:00 9:00 10:00----------------------------------------------------------------------Event X 1 1 0 4 0 4 0 1 0 3Event Y 3 3 0 2 0 2 0 2 0 1----------------------------------------------------------------------Total 4 4 0 6 0 6 0 3 0 4---------------------------------------------------------------------- I have fell short of ideas this time around to implement such a thingAny help in this direction is deeply appreciatedRegardsSrivatsa |
|
|
waterduck
Aged Yak Warrior
982 Posts |
|
|
srivatsahg
Yak Posting Veteran
71 Posts |
Posted - 2009-07-08 : 08:32:19
|
Thanks for your reply,I know to use the PIVOT operator, but i do not know how to include the non existing data as columns as i have mentioned in my post.RegardsSrivatsaquote:
Originally posted by waterduck
hi, you can use pivot to help you...but you also can post up yours sample data, require output...http://msdn.microsoft.com/en-us/library/ms177410.aspx
|
 |
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2009-07-08 : 09:20:06
|
| may be outer join with a table which has all the hours |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-08 : 11:49:04
|
quote:
Originally posted by srivatsahg
Hello I need some help in creating queries for the nonexisting data using pivots.My current query shows the number of events that have occured over the time in a day.The events and the eventdetails are read from the database before being printed on the crosstabi.e something like this. Note : there are no events between 3:00-3:59,5:00-5:59;6:00-6:59,7:00-7:59 (not present in the database)----------------------------------------------------- 01:00 02:00 04:00 08:00 10:00----------------------------------------Event X 1 1 4 1 3 Event Y 3 3 2 2 1----------------------------------------Total 4 4 6 3 4---------------------------------------- So far so good...Now i have to enhance my query to include the event details which have not happened i.e to include the time details where no events have happened 01:00 02:00 03:00 04:00 05:00 6:00 07:00 8:00 9:00 10:00----------------------------------------------------------------------Event X 1 1 0 4 0 4 0 1 0 3Event Y 3 3 0 2 0 2 0 2 0 1----------------------------------------------------------------------Total 4 4 0 6 0 6 0 3 0 4---------------------------------------------------------------------- I have fell short of ideas this time around to implement such a thingAny help in this direction is deeply appreciatedRegardsSrivatsa
Some thing likeSELECT Event,SUM(CASE WHEN DATEPART(hh,yourdatefield) = 1 THEN 1 ELSE 0 END) AS [01:00], SUM(CASE WHEN DATEPART(hh,yourdatefield) = 2 THEN 1 ELSE 0 END) AS [02:00],SUM(CASE WHEN DATEPART(hh,yourdatefield) = 3 THEN 1 ELSE 0 END) AS [03:00],...SUM(CASE WHEN DATEPART(hh,yourdatefield) = 23 THEN 1 ELSE 0 END) AS [23:00]FROM YourTableGROUP BY EventWITH ROLLUP |
|
|
|
|
|
|
|
|
Help needed SQL Query