Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

count the order on weekly basis Expand / Collapse
Author
Message
Posted Monday, March 10, 2014 4:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 6:35 AM
Points: 1, Visits: 3
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).....
Post #1549171
Posted Monday, March 10, 2014 5:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 13,520, Visits: 11,313
That is fascinating.
Is there also a question?

Hint: read the links How to post forum questions 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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1549176
Posted Monday, March 10, 2014 7:21 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 20,799, Visits: 32,717
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1549216
Posted Monday, March 10, 2014 9:41 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 3,908, Visits: 8,863
Koen Verbeeck (3/10/2014)
That is fascinating.
Is there also a question?

Hint: read the links How to post forum questions 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.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1549314
Posted Monday, March 10, 2014 1:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 13,520, Visits: 11,313
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 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




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1549468
Posted Tuesday, March 11, 2014 6:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 27, 2014 5:43 PM
Points: 4, Visits: 83
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

Post #1549989
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse