March 10, 2014 at 4:50 am
I want the count of orders of a particular table on weekly basis i.e if date given to me is 10\03\2014 then my output should be count of orders from date 10\03\2014 to 09\03\2014(one week) then count of orders from 2\03\2014 to 08\03\2014(another week) and then from 24\02\2014 to 01\03\2014(another week).....
March 10, 2014 at 5:06 am
That is fascinating.
Is there also a question?
Hint: read the links How to post forum questions[/url] and Need an answer? No, you need a question.
ps: just to shove you in the right direction: group by week (use the DATEPART function to find the week) and count the orders.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 10, 2014 at 7:21 am
vinaya_2690 (3/10/2014)
I want the count of orders of a particular table on weekly basis i.e if date given to me is 10\03\2014 then my output should be count of orders from date 10\03\2014 to 09\03\2014(one week) then count of orders from 2\03\2014 to 08\03\2014(another week) and then from 24\02\2014 to 01\03\2014(another week).....
Also, hard to tell what your weeks are from the dates given above. Since this is an international site you may want to post dates in the yyyy-mm-dd format and show start date first followed be end date.
From the information given, there really isn't much we can do but guess at what you are actually trying to accomplish.
March 10, 2014 at 9:41 am
Koen Verbeeck (3/10/2014)
That is fascinating.Is there also a question?
Hint: read the links How to post forum questions[/url] and Need an answer? No, you need a question.
ps: just to shove you in the right direction: group by week (use the DATEPART function to find the week) and count the orders.
Instead of DATEPART, I would suggest a date calculation to get the first (or last) day of the week to group by it as it would be more descriptive.
March 10, 2014 at 1:50 pm
Luis Cazares (3/10/2014)
Koen Verbeeck (3/10/2014)
That is fascinating.Is there also a question?
Hint: read the links How to post forum questions[/url] and Need an answer? No, you need a question.
ps: just to shove you in the right direction: group by week (use the DATEPART function to find the week) and count the orders.
Instead of DATEPART, I would suggest a date calculation to get the first (or last) day of the week to group by it as it would be more descriptive.
Possibly. I'll think about it when I see some sample data and desired output 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 11, 2014 at 6:14 pm
Hello,
Please find an example sql below. Hope it helps.
-- Drop table if it already exists
IF (OBJECT_ID('tempdb..#tmpOrders') IS NOT NULL)
BEGIN
DROP TABLE #tmpOrders ;
END ;
-- Create temp table to hold orders
CREATE TABLE #tmpOrders
(
OrderNo INT IDENTITY (1, 1) NOT NULL
, OrderQuantity INT NOT NULL
, OrderDate DATE NOT NULL
) ;
-- Insert orders
INSERT INTO #tmpOrders
SELECT o.OrderQuantity
, o.OrderDate
FROM (
VALUES ('100', '2014-03-01')
, ('200', '2014-03-10')
, ('200', '2014-03-09')
, ('300', '2014-03-08')
, ('300', '2014-03-02')
, ('300', '2014-03-07')
, ('400', '2013-03-10')
, ('500', '2013-03-10')
, ('700', '2013-02-10')
, ('800', '2013-01-10')
) o(OrderQuantity, OrderDate)
ORDER BY o.OrderDate ;
-- Select orders
-- SELECT * FROM #tmpOrders ;
-- Set 'Sunday' as the first day of the week (SELECT @@DATEFIRST ;)
SET DATEFIRST 7 ;
-- Select the Windowed data
SELECT *
FROM #tmpOrders o
CROSS APPLY (
SELECT CAST(YEAR(o.OrderDate) AS CHAR(4)) + CAST(DATEPART(WEEK, o.OrderDate) AS VARCHAR(2))
) xWindow(WindowingValue) ;
-- Select the order counts
SELECT Window = xwindow.WindowingValue
, OrderCount = COUNT(*)
FROM #tmpOrders o
CROSS APPLY (
SELECT CAST(YEAR(o.OrderDate) AS CHAR(4)) + CAST(DATEPART(WEEK, o.OrderDate) AS VARCHAR(2))
) xWindow(WindowingValue)
GROUP BY xwindow.WindowingValue ;
-- Cleanup
IF (OBJECT_ID('tempdb..#tmpOrders') IS NOT NULL)
BEGIN
DROP TABLE #tmpOrders ;
END ;
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply