SUM Multiple Days Based on same value

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)

SUM Multiple Days Based on same value

Author

Topic

MKz71
Starting Member

30 Posts

Posted - 2012-01-04 : 07:07:50

OK... The following is an example table (not real data):

ID dtTimeStamp Column1 Column2 Column3
--- ----------- ------- ------- -------

1 '2011-12-23 08:23:15.399' A 1 100
2 '2011-12-23 10:14:21.419' A 1 50
3 '2011-12-23 16:04:01.256' A 2 75
4 '2011-12-24 02:27:19.201' A 2 25
5 '2011-12-24 06:17:46.221' A 1 125
6 '2011-12-24 19:11:32.003' B 1 100
7 '2011-12-25 14:52:37.109' A 2 50

My SQL Query:

SELECT MIN(DATEADD(dd, 0, DATEDIFF(dd, 0, dtTimeStamp))) AS "DAY", Column1, Column2, SUM(Column3) AS "TOTAL LENGTH"
FROM Table1
WHERE dtTimeStamp >= '2011-10-14 00:00:00.000'
GROUP BY day(dtTimeStamp), Column1, Column2
ORDER BY "DAY" DESC

OUTPUT:

DAY Column1 Column2 TOTAL LENGTH
--- ------- ------- -------------
'2011-12-23 00:00:00.000' A 1 150
'2011-12-23 00:00:00.000' A 2 75
'2011-12-24 00:00:00.000' A 2 25
'2011-12-24 00:00:00.000' A 1 125
'2011-12-24 00:00:00.000' B 1 100
'2011-12-25 00:00:00.000' A 2 50

My Question:

If you look closely at the input from above, there are two days where Column1 = A and Column2 = 2 back to back. Is there any way to change my SQL Query to SUM Column3 whenever Column1 and Column2 are consecutive. The scenario is, Column1 and Column2 is the product sequence that is currently being run on an assembly line. A product can be run accross multiple days, and I would like to get the SUM total of Column3 for the entire run of that product. Keep in mind the product1 can run for multiple days then change to a different product and then change back to product1. If there is a break in product1 being run then when the sum should stop when product2 starts. I hope this makes sense to someone, as I am terrible at explaining situations.

Arumugam
Starting Member

11 Posts

Posted - 2012-01-04 : 08:22:42

If you get only maximum of 2 rows Consecutively, the below query will do that.

Select B.ID, A.Column1,A.Column2,A.Column3 AS AColumn3,B.Column3 AS BColumn3, A.Column3+B.Column3 As Total From
(Select ID - 1 as PreviousID,Column1,Column2,Column3 from Table1) A JOIN
(Select * from Table1) B ON A.PreviousID = B.ID AND A.Column1 = B.Column1 And A.Column2 = B.Column2

Regards,
Arumugam K

MKz71
Starting Member

30 Posts

Posted - 2012-01-04 : 08:46:05

Unfortunately, I could get 3 days or 4 (or even more). Each day constitutes a new row based on the fact that I am grouping by the day first. My example was all free write and made up, so it doesn't reflect a live db result.

Lets say that the line runs the same product for 5 days and then switches to a new product. If I were to change the "WHERE" part of my above query to reflect just this 6 day period, my query would give me 6 rows as a result. Each day would have a row with the sum of what I identified originally as Column3. This narrows 300,000 records down to around 100, which is great, but a day doesn't constitute a new run for that product, the run continued for 5 days. So what I would like to kick back is the MIN(DAY)= the first day they started running that product, Column1 & Column 2 (the product identifiers), and the SUM(Column3). So with the example I gave a couple sentences ago, instead of returning 6 rows (one for each day) it would actually return 2 rows (given the line stopped before midnight on the fifth day and didn't resume production with the new product until after midnight on the sixth day). 1 row showing the production total of that product run of 5 days, and 1 row showing the production total of the single day of production on the sixth day. Again, everything I am stating here is just giving numbers to help explain. The number of days is in now way static, it always changes and can change multiple times a day if needed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-04 : 12:49:16

[code]
CREATE TABLE #Tbl_test
(
ID int,
dtTimeStamp datetime,
Column1 char(1),
Column2 int,
Column3 int
)
INSERT #Tbl_test
SELECT 1 ,'2011-12-23 08:23:15.399', 'A', 1, 100 UNION ALL
SELECT 2, '2011-12-23 10:14:21.419', 'A', 1, 50 UNION ALL
SELECT 3, '2011-12-23 16:04:01.256', 'A', 2, 75 UNION ALL
SELECT 4 ,'2011-12-24 02:27:19.201', 'A', 2, 25 UNION ALL
SELECT 5, '2011-12-24 06:17:46.221', 'A', 1, 125 UNION ALL
SELECT 6, '2011-12-24 19:11:32.003', 'B', 1, 100 UNION ALL
SELECT 7, '2011-12-25 14:52:37.109', 'A', 2, 50

;With CTE
AS
(
SELECT t.ID,
t.dtTimeStamp,
t.Column1,
t.Column2,
t.Column3,
t.dtTimeStamp AS DispDate
FROM #Tbl_test t
WHERE ID=1

UNION ALL

SELECT t.ID,
t.dtTimeStamp,
t.Column1,
t.Column2,
t.Column3,
CASE WHEN c.Column1 = t.Column1 AND c.Column2 = t.Column2 THEN c.dtTimeStamp ELSE t.dtTimeStamp END
FROM CTE c
INNER JOIN #Tbl_test t
ON t.ID = c.ID + 1
)

SELECT DispDate,
Column1,
Column2,
SUM(Column3) AS Column3
FROM CTE
GROUP BY DispDate,
Column1,
Column2
OPTION (MAXRECURSION 0)

output
-------------------------------------------------
DispDate Column1 Column2 Column3
2011-12-23 08:23:15.400 A 1 150
2011-12-23 16:04:01.257 A 2 100
2011-12-24 06:17:46.220 A 1 125
2011-12-24 19:11:32.003 B 1 100
2011-12-25 14:52:37.110 A 2 50

[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-04 : 12:50:43

Only thing above is i've assumed ID values are continuos as per your sample data So if its not same in actual case you need to generate a id value sequence using ROW_NUMBER() function and then use it in above suggestion

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

Arumugam
Starting Member

11 Posts

Posted - 2012-01-05 : 08:29:36

Please check the below query and let us know, if it satisfies ur requirement.

CREATE TABLE #Tbl_test
(
ID int,
dtTimeStamp datetime,
Column1 char(1),
Column2 int,
Column3 int
)
INSERT #Tbl_test
SELECT 1 ,'2011-12-23 08:23:15.399', 'A', 1, 100 UNION ALL
SELECT 2, '2011-12-23 10:14:21.419', 'A', 1, 50 UNION ALL
SELECT 3, '2011-12-23 16:04:01.256', 'A', 2, 75 UNION ALL
SELECT 4 ,'2011-12-24 02:27:19.201', 'A', 2, 25 UNION ALL
SELECT 5, '2011-12-24 06:17:46.221', 'A', 1, 125 UNION ALL
SELECT 6, '2011-12-24 19:11:32.003', 'B', 1, 100 UNION ALL
SELECT 7, '2011-12-25 14:52:37.109', 'A', 2, 50

--Taking only consecutive records and inserting into a temp table
Select B.ID, B.dtTimeStamp,A.Column1,A.Column2,A.Column3 AS AColumn3,B.Column3 AS BColumn3 INTO #Temp From
(Select ID - 1 as PreviousID,Column1,Column2,Column3 from #Tbl_test) A JOIN
(Select * from #Tbl_test) B ON A.PreviousID = B.ID AND A.Column1 = B.Column1 And A.Column2 = B.Column2

--Setting min(dtTimeStamp) for the consecutive records
Update #Temp Set dtTimeStamp = Null, BColumn3 = 0 where ID - 1 IN (Select ID From #Temp)
update #Temp set dtTimeStamp = (select top 1 dtTimeStamp from #Temp as T2 where T2.ID < T.ID and T2.dtTimeStamp is not null Order By T2.ID Desc) from #Temp as T where T.dtTimeStamp is null

--Group by based on TimeStamp, Column1 and Column2
Select dtTimeStamp, Column1, Column2, SUM(AColumn3+BColumn3) from #Temp Group by dtTimeStamp, Column1,Column2

--Drop Temp table used
DROP Table #Temp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources