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 |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2008-01-10 : 12:30:37
|
| I have a table called imagesiddate (datetime field)siteimagenamenow i want create a stored procedure that will return a recordset for each site -- each row will show - those images date 4 weeks ago, 3 weeks ago, 2 weeks ago, 1 week ago, then today divided by hour 8, 9,10,11 ,12 and so on and then the total todaycan someone help me with where to start?i'm sure I can do this in one stored procedure but i'm not sure how.. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-10 : 12:37:10
|
| can you post desired o/p format? |
 |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2008-01-10 : 13:14:57
|
| collumnsthe individual times are hours todaysite 4 weeks ago 3 weeks ago 2 weeks ago 1 week ago , 8,9 10,11, all for the day |
|
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2008-01-10 : 13:54:04
|
| for each of these columns you want just the count of images right?you can use case statement with group by clause |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2008-01-10 : 15:10:00
|
| yes but by week and then the hour to todays date |
|
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2008-01-10 : 16:37:45
|
| as I told you, you'll have to use case statement...something likeselect site, sum(case when date between date1 and date2 then 1 else 0 end) as between_date1_and_date2,sum(case when date between date3 and date4 then 1 else 0 end) as between_date3_and_date4, .... from imagesgroup by site |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2008-01-11 : 01:54:04
|
| sum(case when date between date1 and date2 then 1 else 0 end) as between_date1_and_date2isn't it the count of images? why sum? - how do i get the could of images between these days? |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2008-01-11 : 02:22:44
|
| thanks i actually see this does work though i'm not sure why sum and not count.date between '2008-01-10 8:00' and '2008-01-10 9:00' how can i do the above but taking get date and changing the time?do i have to convert to a varchar and then back to a date? |
|
|
|
|
|
|
|
|
stored procedure help