List the Order from last 10 day up to today

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 2005 Forums

Transact-SQL (2005)

List the Order from last 10 day up to today

Author

Topic

hisouka
Starting Member

28 Posts

Posted - 2009-08-25 : 03:48:03

HI I want to list the count of order from the last 10 Working Days,

Ex:
------D10---D9--D8--D7--D6--D5--D4--D3--D2--D1---dateToday
Order--5----6---10--5---10--11--2---9---7---2---8/25/2009

so the column D1 is 8/24/2009, D2 is 8/23/2009 so on and so fort.

so it list last 10 working Days(Monday to Friday Only)

How can i make query of that?

Table is tblOrder with:
OrderID, OrderDate

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-25 : 03:52:31

have you tried using the PIVOT operator ?

KH
[spoiler]Time is always against us[/spoiler]

hisouka
Starting Member

28 Posts

Posted - 2009-08-25 : 04:03:45

how about the Monday to Friday only? how can i do that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-25 : 04:16:00

[code]
declare @data table
(
OrderID int identity,
OrderDate datetime
)

insert into @data (OrderDate)
select '2009-08-11' union all
select '2009-08-12' union all
select '2009-08-12' union all
select '2009-08-13' union all
select '2009-08-13' union all
select '2009-08-13' union all
select '2009-08-14' union all
select '2009-08-14' union all
select '2009-08-15' union all
select '2009-08-15' union all
select '2009-08-16' union all
select '2009-08-16' union all
select '2009-08-17' union all
select '2009-08-18' union all
select '2009-08-19' union all
select '2009-08-20' union all
select '2009-08-21' union all
select '2009-08-22' union all
select '2009-08-23' union all
select '2009-08-23' union all
select '2009-08-24' union all
select '2009-08-24' union all
select '2009-08-25'

; with data (OrderID, OrderDate, work_days)
as
(
select OrderID,
OrderDate,
work_days = dense_rank() over (order by OrderDate desc)
from @data
where OrderDate >= dateadd(day, datediff(day, 0, getdate()), -14)
and datename (weekday, OrderDate) not in ('Saturday', 'Sunday')
)
select *
from (
select OrderID, work_days
from data
) d
pivot
(
count(OrderID)
for work_days in ([1], [2], [3], [4], [5], [6], [7],