Grouping orders on the friday of the week

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)

Grouping orders on the friday of the week

Author

Topic

GordonCopestake
Starting Member

8 Posts

Posted - 2012-03-19 : 08:39:39

Hi All,
I have a table SalesOrders with :
OrderID
ItemID
Qty
DueDate

and I would like to produce a summary report of the orders that groups my orders by week. I have used the below code from an example to add a "FridayDate" to the table to group against but this seems to jump to the NEAREST Friday, not the following Friday. e.g. if an order is on for the 19th March the FridayDate is being shown as 16th March instead of 23rd March.

Could someone point out where I have gone wrong please?

select *,
DATEPART(wk, SalesOrders.DueDate) AS WkNo ,
dateadd(week, datediff(day,'20120106',DueDate) / 7, '20120106') AS FridayDate
from SalesOrders

where I've used 20120106 as a known Friday.

Thanks in advance

Gordon

GordonCopestake
Starting Member

8 Posts

Posted - 2012-03-19 : 08:54:09

Ah! I have fixed my own problem by using:

CAST(DATEADD(d, 6 - DATEPART(dw,DueDate), DueDate) AS date) AS FridayDate

much nicer code too

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-19 : 09:00:22

cool...thanks for sharing

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources