CODEWITHSUNDEEP
sqlsql-server
Mustafa
Mustafa

Reputation: 825

SQL Hourly Cumulative Sum of Quantities

The table below stores processed product quantities(processedquantity) in specified operations(operid). DefaultTable What I want to acquire from this table is to have hourly cumulative sum of quantities in any time necessary.

I was tried two different approaches to achieve what I want.

1st approach with loop

Declare @hour tinyint;
SET @hour=8;
Declare @date datetime;
SET @date = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()));
while (@hour<=DATEPART(HOUR, GETDATE()))
Begin
       select lineid, sectionid, operid, sum(processedquantity) as 'CumulativeSum', DATEADD(hour,@hour,@date) as 'UntilTime' from PROCESSBUNDLE
             where PROCESSBUNDLE.PROCESSEND>=@date
       and OPERID in (SELECT OPERID FROM [VTRR].[dbo].[MODELOPER] where MONITOR='1')
       and SECTIONID=40 
       and PROCESSEND<=DATEADD(hour,@hour,@date)
       group by LINEID, SECTIONID, OPERID
       order by OPERID asc;
       SET @hour=@hour+1;
end

The query loop above can calculate what I want but unfortunately all results are in different queries. So the results will look like this:

1stapproachwithloop

2nd approach with Group By

Declare @hour tinyint;
SET @hour=DATEPART(HOUR, GETDATE())+1;
Declare @date datetime;
SET @date = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()));

select lineid, sectionid, operid, sum(processedquantity) as 'Adet', DATEPART(HOUR, PROCESSEND)+1 as 'UntilHour' from PROCESSBUNDLE
       where PROCESSEND>=@date
       and OPERID in (SELECT OPERID FROM [VTRR].[dbo].[MODELOPER] where MONITOR='1')
       and SECTIONID=40 
       and PROCESSEND<=DATEADD(hour,@hour,@date)
       group by LINEID, SECTIONID, OPERID, DATEPART(HOUR, PROCESSEND)
       order by OPERID, 'UntilHour' asc;

This query can not give me cumulative results but gives me the appearance I want because of the hourly "Group By" Here are the results:

2ndapproachwithgroupby

So is it possible to have cumulative results in one resultset? Here is what I want to achieve:

Result

Upvotes: 1

Views: 1890

Answers (2)

AntDC
AntDC

Reputation: 1917

This does what you want............

CREATE TABLE #PROCESSBUNDLE (
    BUNDLEID varchar(50) not null, 
    LINEID varchar(50) not null,
    SECTIONID varchar(50) not null,
    OPERID varchar(50) not null,
    PROCESSBEGIN datetime not null,
    PROCESSEND datetime not null,
    PROCESSEDQUANTITY int not null );

GO

INSERT INTO #PROCESSBUNDLE
    ([BUNDLEID], [LINEID], [SECTIONID], [OPERID], [PROCESSBEGIN], [PROCESSEND], [PROCESSEDQUANTITY])
VALUES
    ('2016031460', '01', '40', '0080004', '2016-Oct-18 14:40:41.000', '2016-Oct-18 14:46:17.193', 20),
    ('2016031360', '01', '40', '3020001', '2016-Oct-18 08:02:04.603', '2016-Oct-18 08:08:47.420', 15),
    ('2016031368', '01', '40', '3020001', '2016-Oct-18 09:10:39.860', '2016-Oct-18 09:15:38.170', 12),
    ('2016031432', '01', '40', '3020001', '2016-Oct-18 09:50:54.743', '2016-Oct-18 10:05:11.560', 13),
    ('2016031437', '01', '40', '3020001', '2016-Oct-18 11:09:55.570', '2016-Oct-18 11:15:27.733', 20),
    ('2016031450', '01', '40', '3020001', '2016-Oct-18 12:00:59.473', '2016-Oct-18 12:10:30.467', 15),
    ('2016031540', '01', '40', '3020001', '2016-Oct-18 14:35:13.067', '2016-Oct-18 14:42:45.613', 14),
    ('2016031323', '01', '40', '3120010', '2016-Oct-18 08:18:05.723', '2016-Oct-18 08:22:13.333', 20),
    ('2016031333', '01', '40', '3120010', '2016-Oct-18 08:56:33.393', '2016-Oct-18 09:03:56.020', 20),
    ('2016031341', '01', '40', '3120010', '2016-Oct-18 09:35:36.240', '2016-Oct-18 09:40:17.470', 17),
    ('2016031346', '01', '40', '3120010', '2016-Oct-18 10:37:38.190', '2016-Oct-18 10:43:16.990', 17),
    ('2016031356', '01', '40', '3120010', '2016-Oct-18 11:29:47.540', '2016-Oct-18 11:34:47.130', 20),
    ('2016031368', '01', '40', '3120010', '2016-Oct-18 12:13:15.887', '2016-Oct-18 12:16:10.800', 12),
    ('2016031434', '01', '40', '3120010', '2016-Oct-18 13:24:22.120', '2016-Oct-18 13:27:46.367', 20),
    ('2016031444', '01', '40', '3120010', '2016-Oct-18 14:05:44.710', '2016-Oct-18 14:12:36.430', 20),
    ('2016029572', '01', '40', '3190000', '2016-Oct-18 07:54:58.873', '2016-Oct-18 08:01:37.667', 17),
    ('2016031285', '01', '40', '3140000', '2016-Oct-18 07:59:18.137', '2016-Oct-18 08:21:45.207', 17),
    ('2016031287', '01', '40', '3190000', '2016-Oct-18 09:56:59.367', '2016-Oct-18 10:08:59.743', 17),
    ('2016031315', '01', '40', '3190000', '2016-Oct-18 08:34:50.027', '2016-Oct-18 08:42:57.040', 13),
    ('2016031324', '01', '40', '3190000', '2016-Oct-18 09:07:19.597', '2016-Oct-18 09:14:57.113', 20),
    ('2016031330', '01', '40', '3140000', '2016-Oct-18 07:30:15.730', '2016-Oct-18 07:41:43.390', 15),
    ('2016031338', '01', '40', '3190000', '2016-Oct-18 11:08:30.757', '2016-Oct-18 11:15:43.453', 17),
    ('2016031342', '01', '40', '3140000', '2016-Oct-18 09:02:01.737', '2016-Oct-18 09:26:24.780', 16),
    ('2016031346', '01', '40', '3190000', '2016-Oct-18 11:52:23.667', '2016-Oct-18 11:58:22.227', 17),
    ('2016031350', '01', '40', '3140000', '2016-Oct-18 09:57:29.077', '2016-Oct-18 10:39:06.273', 20),
    ('2016031356', '01', '40', '3190000', '2016-Oct-18 13:26:02.440', '2016-Oct-18 13:30:53.807', 20),
    ('2016031360', '01', '40', '3140000', '2016-Oct-18 11:08:58.843', '2016-Oct-18 11:30:53.213', 15),
    ('2016031365', '01', '40', '3140000', '2016-Oct-18 11:30:53.213', '2016-Oct-18 12:00:02.970', 20),
    ('2016031438', '01', '40', '3140000', '2016-Oct-18 12:08:46.970', '2016-Oct-18 12:35:02.767', 20),
    ('2016031444', '01', '40', '3140000', '2016-Oct-18 13:36:11.650', '2016-Oct-18 14:04:19.220', 20),
    ('2016031559', '01', '40', '3140000', '2016-Oct-18 14:48:08.700', '2016-Oct-18 14:53:47.587', 20),
    ('2016029572', '01', '40', '3170010', '2016-Oct-18 07:29:35.693', '2016-Oct-18 07:49:48.240', 17),
    ('2016029582', '01', '40', '3240000', '2016-Oct-18 07:53:46.757', '2016-Oct-18 07:54:46.723', 14),
    ('2016031164', '01', '40', '3260000', '2016-Oct-18 07:46:28.670', '2016-Oct-18 07:54:32.370', 20),
    ('2016031167', '01', '40', '3250002', '2016-Oct-18 08:00:18.847', '2016-Oct-18 08:08:33.143', 13),
    ('2016031172', '01', '40', '3260000', '2016-Oct-18 09:13:13.433', '2016-Oct-18 09:17:35.810', 13),
    ('2016031173', '01', '40', '3260000', '2016-Oct-18 08:45:57.543', '2016-Oct-18 08:46:04.777', 17),
    ('2016031287', '01', '40', '3240000', '2016-Oct-18 12:06:09.583', '2016-Oct-18 12:12:50.987', 17)


SELECT 
  lineid, sectionid, operid, UntilHour, Adet, SUM(Adet) 
  OVER(PARTITION BY lineid, sectionid, operid 
  ORDER BY lineid, sectionid, operid, UntilHour ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS RunningTotal
FROM 
(
  SELECT SUB_Q.lineid, SUB_Q.sectionid, SUB_Q.operid, SUB_Q.UntilHour, SUM(SUB_Q.processedquantity) as Adet
  FROM
  (
    select lineid, sectionid, operid, DATEPART(HOUR, PROCESSEND)+1 as UntilHour, processedquantity
    from #PROCESSBUNDLE
    where 1=1
    --and OPERID in (SELECT OPERID FROM [VTRR].[dbo].[MODELOPER] where MONITOR='1')
    and SECTIONID=40 
    --and PROCESSEND<=DATEADD(hour,@hour,@date)
  ) SUB_Q
  GROUP BY SUB_Q.lineid, SUB_Q.sectionid, SUB_Q.operid, SUB_Q.UntilHour
)TOTALS_Q
ORDER BY lineid, sectionid, operid, UntilHour

Upvotes: 4

Nebi
Nebi

Reputation: 326

Maybe Window-Function like SUM() OVER(PARTITION BY ) might help you, depending on your SQL-Server version.

Given that Processend is from datatype datetime and not a varchar:

 select lineid, sectionid, operid, sum(processedquantity) OVER(PARTITION BY CONVERT(NVARCHAR(13), Processend, 20)) as 'Adet'from PROCESSBUNDLE
   where OPERID in (SELECT OPERID FROM [VTRR].[dbo].[MODELOPER] where MONITOR='1')
   and SECTIONID=40 
   order by OPERID, Processend;

You will get the SUM of processedquantity in every row that applies for the Partition By grouping. Here I kicked out the group by condition, so you will see all rows. I'm not sure if adding the group by will work. It is untested. I guess it doesn't because of Processend has to be in the grouping as well and that would falsify the result. If you want a grouped resut you can do the query above in a subselect and then do the grouping.

For that example Data would have been great.

Tell me if that helped you.

Upvotes: 1

Related Questions