SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 3 consecutive days with highest total
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

squenter
Starting Member

USA
3 Posts

Posted - 11/04/2013 :  18:17:42  Show Profile  Reply with Quote
Hi,

I am trying to write a t-sql 2008 query that scans a year of data and returns three consecutive days with the highest summed total value. The raw data is hourly, but I have rolled it up to daily for the sample data. I have been working on this for a few days, and would appreciate help. Here is a data sample. Thanks

dptNO saleDAY prodMONTH QUANTITY
50763 02/01/12 201302 309527
50763 02/02/12 201302 308888
50763 02/03/12 201302 303204
50763 02/04/12 201302 303203
50763 02/05/12 201302 300604
50763 02/05/12 201302 300127
50763 02/07/12 201302 299101
50763 02/08/12 201302 298966
50763 02/09/12 201302 296425
50763 02/10/12 201302 294471
50763 02/11/12 201302 294161
50763 02/12/12 201302 294159
50763 02/13/12 201302 293028
50763 02/14/12 201302 292034
50763 02/15/12 201302 291794
50763 02/16/12 201302 288485
50763 02/17/12 201302 288485
50763 02/18/12 201302 288485
50763 02/05/12 201302 287777
50763 02/20/12 201302 287732
50763 02/21/12 201302 287365
50763 02/22/12 201302 286684
50763 02/23/12 201302 286363
50763 02/24/12 201302 285396
50763 02/25/12 201302 284575
50763 02/26/12 201302 284575
50763 02/27/12 201302 284575
50763 02/28/12 201302 284187

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/04/2013 :  18:47:42  Show Profile  Reply with Quote
I think this works:
-- Sample Data
DECLARE @Foo TABLE (dptNO INT, saleDAY DATE, prodMONTH INT, QUANTITY INT)
INSERT @Foo VALUES
(50763, '02/01/12', 201302,309527),
(50763, '02/02/12', 201302,308888),
(50763, '02/03/12', 201302,303204),
(50763, '02/04/12', 201302,303203),
(50763, '02/05/12', 201302,300604),
(50763, '02/05/12', 201302,300127),
(50763, '02/07/12', 201302,299101),
(50763, '02/08/12', 201302,298966),
(50763, '02/09/12', 201302,296425),
(50763, '02/10/12', 201302,294471),
(50763, '02/11/12', 201302,294161),
(50763, '02/12/12', 201302,294159),
(50763, '02/13/12', 201302,293028),
(50763, '02/14/12', 201302,292034),
(50763, '02/15/12', 201302,291794),
(50763, '02/16/12', 201302,288485),
(50763, '02/17/12', 201302,288485),
(50763, '02/18/12', 201302,288485),
(50763, '02/05/12', 201302,287777),
(50763, '02/20/12', 201302,287732),
(50763, '02/21/12', 201302,287365),
(50763, '02/22/12', 201302,286684),
(50763, '02/23/12', 201302,286363),
(50763, '02/24/12', 201302,285396),
(50763, '02/25/12', 201302,284575),
(50763, '02/26/12', 201302,284575),
(50763, '02/27/12', 201302,284575),
(50763, '02/28/12', 201302,284187)


-- Query
;WITH Cte
AS
(
	SELECT
		saleDAY,
		SUM(QUANTITY) AS QUANTITY,
		ROW_NUMBER() OVER (ORDER BY SaleDay) AS RowNum
	FROM 
		@Foo
	GROUP BY
		SaleDay

)
SELECT
	A.saleDAY,
	A.Quantity + B.Quantity + C.Quantity
FROM
	Cte AS A
INNER JOIN
	Cte AS B
	ON A.RowNum = B.RowNum + 1
INNER JOIN
	Cte AS C
	ON A.RowNum = C.RowNum + 2
ORDER BY
	A.Quantity + B.Quantity + C.Quantity DESC
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/05/2013 :  00:37:38  Show Profile  Reply with Quote
Can you post what should be expected output? ARe you looking at just three days data which have highest Quantity value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

squenter
Starting Member

USA
3 Posts

Posted - 11/05/2013 :  14:54:08  Show Profile  Reply with Quote
The output will be broken down by day by department by individual item value. Something like:


8/1/2013			8/2/2013			8/3/2013	
Dept. 50763	540	Dept. 50763	213	Dept. 50763	260
Item1	72		Item1	58		Item1	69
Item2	81		Item2	67		Item2	79
Item3	55		Item3	40		Item3	50
Item4	62		Item4	48		Item4	62
							
5/15/2013		5/16/2013		5/17/2013	
Dept. 50764	514	Dept. 50764	450	Dept. 50764	430
Item1	186		Item1	175		Item1	159
Item2	104		Item2	92		Item2	114
Item3	113		Item3	101		Item3	70
Item4	60		Item4	48		Item4	64
Item5	46		Item5	34		Item5	21
Item6	5		Item6	0		Item6	2


The software package we use has a report tool, and that tool doesn't work with CTE's, so I am re-writing the CTE as a derived table. I will let you know my progress

Edited by - squenter on 11/05/2013 14:57:04
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
374 Posts

Posted - 11/05/2013 :  16:48:25  Show Profile  Reply with Quote
I don't see item# in the sample data.
Go to Top of Page

squenter
Starting Member

USA
3 Posts

Posted - 11/05/2013 :  17:25:13  Show Profile  Reply with Quote
When I rolled up the hourly data, I took the total for the day. The original requirement was to capture the highest daily department total over three consecutive days, and return the dates, the department number, the three day total as well as each day's total. The Item detail level was added this morning. What I need to know is how to find the highest three day total over a year. I thank Lamprey for providing his approach.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000