SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


count the order on weekly basis


count the order on weekly basis

Author
Message
vinaya_2690
vinaya_2690
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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).....
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27773 Visits: 13268
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40248 Visits: 38567
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.

Cool
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)
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16850 Visits: 19122
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27773 Visits: 13268
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 :-D


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

MCSE Business Intelligence - Microsoft Data Platform MVP
Aditya Daruka
Aditya Daruka
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 175
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 Wink
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search