Getting rows by lengths of time

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Getting rows by lengths of time

Author

Topic

aquinasnmore
Starting Member

3 Posts

Posted - 2009-09-25 : 12:41:03

Is there a way to get results in rows by specific lengths of time?

For example, I would like to get a count of products sold by week or month or year over a period of time.

The results would look like:
1/1/09-1/7/09 20
1/7/09-1/14/09 15

etc

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-25 : 13:00:57

Here is an example for groups by week:


;with [yourTableWithDates] (dt)
as
(--table of dates
       select getdate()-33
       union all
       select dateadd(day, 1, dt)
       from [yourTableWithDates]
       where datediff(day, getdate(), dt) < 1
)

select dateadd(week, datediff(week, 0, dt), 0) [from_date]
       ,dateadd(week, datediff(week, 0, dt), 0) + 6 [to_date]
       ,count(*) [day_count_of_group]
from [yourTableWithDates]
group by dateadd(week, datediff(week, 0, dt), 0)


EDIT:
--output
from_date               to_date                 day_count_of_group
----------------------- ----------------------- ------------------
2009-08-24 00:00:00.000 2009-08-30 00:00:00.000 7
2009-08-31 00:00:00.000 2009-09-06 00:00:00.000 7
2009-09-07 00:00:00.000 2009-09-13 00:00:00.000 7
2009-09-14 00:00:00.000 2009-09-20 00:00:00.000 7
2009-09-21 00:00:00.000 2009-09-27 00:00:00.000 7

Be One with the Optimizer
TG

aquinasnmore
Starting Member

3 Posts

Posted - 2009-09-27 : 00:09:08

Thanks! That worked exactly as I needed it to.

aquinasnmore
Starting Member

3 Posts

Posted - 2009-09-29 : 13:11:54

One other issue:

How do I fill in zero values for time periods without any sales so that every time has a value even if it is zero? Right now I get results only for time periods with sales.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 13:50:57

for how long you want this group by week info for?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-29 : 13:51:59

Well, you need start with either a calender table or a table of numbers or a function that returns a table of dates or numbers, then LEFT OUTER JOIN to your table with the dates. Change the group by to use the numbers and aggregate from your table with the sparse data. Here is an example using the same example as before but including a table from MASTER database which happens to contain numbers.


declare @start datetime
       ,@end datetime

select @start = getdate() - 33
       ,@end = getdate() + 14

;with [yourTableWithDates] (dt)
as
(--table of dates
       select getdate()-33
       union all
       select dateadd(day, 1, dt)
       from [yourTableWithDates]
       where datediff(day, getdate(), dt) < 1
)

select dateadd(week, n.number, @start) [from_date]
       ,dateadd(week, n.number, @start) + 6 [end_date]
       ,count(t.dt) [day_count_of_group]
from master..spt_values n
left outer join [yourTableWithDates] t
       on t.dt >= dateadd(week, n.number, @start)
       and t.dt < dateadd(week, n.number, @start) + 7
where  n.type = 'P'
and n.number < datediff(week, @start, @end)
group by dateadd(week, n.number, @start)

OUTPUT:
from_date               end_date                day_count_of_group
----------------------- ----------------------- ------------------
2009-08-27 13:48:08.440 2009-09-02 13:48:08.440 7
2009-09-03 13:48:08.440 2009-09-09 13:48:08.440 7
2009-09-10 13:48:08.440 2009-09-16 13:48:08.440 7
2009-09-17 13:48:08.440 2009-09-23 13:48:08.440 7
2009-09-24 13:48:08.440 2009-09-30 13:48:08.440 7 
2009-10-01 13:48:08.440 2009-10-07 13:48:08.440 0
2009-10-08 13:48:08.440 2009-10-14 13:48:08.440 0

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources