CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Billydan
Billydan

Reputation: 485

select one/sevral more row for each group

I have a set of supply operation organised like this:

op_type  |  time_stamp  |  product | in  | out
----------------------------------------------------
01       |  08:00:00    | p1       | 50  | 0  
02       |  08:01:00    | p1       | 0   | 10  
02       |  08:02:00    | p1       | 0   | 35  
03       |  08:03:00    | p1       | 0   | 5  
01       |  08:04:00    | p1       | 60  | 0  
02       |  08:09:00    | p1       | 0   | 15  
01       |  08:10:00    | p1       | 30  | 0  
02       |  08:11:00    | p1       | 0   | 20  
01       |  08:00:00    | p2       | 100 | 0  
02       |  08:01:00    | p2       | 0   | 20  
02       |  08:02:00    | p2       | 0   | 45  
03       |  08:03:00    | p2       | 0   | 15  
01       |  08:03:10    | p2       | 60  | 0  
01       |  08:04:00    | p2       | 5   | 0  
02       |  08:09:00    | p2       | 0   | 30  
01       |  08:10:00    | p2       | 30  | 0  
02       |  08:11:00    | p2       | 0   | 10

What I want is to select the list witch the SUM(in) group by product will cover the SUM(out) group by product starting from a given time EX: for time_stamp>'08:05:00' I have :

SUM(out) for p1=35 so the sum and SUM(out) for p2=40

so the list I want to get would be

op_type  |  time_stamp  |  product | in  | out
----------------------------------------------------
01       |  08:04:00    | p1       | 60  | 0  
02       |  08:09:00    | p1       | 0   | 15  
01       |  08:10:00    | p1       | 30  | 0  
02       |  08:11:00    | p1       | 0   | 20 
01       |  08:03:10    | p2       | 60  | 0  
01       |  08:04:00    | p2       | 5   | 0  
02       |  08:09:00    | p2       | 0   | 30  
01       |  08:10:00    | p2       | 30  | 0  
02       |  08:11:00    | p2       | 0   | 10

what i did is that i added tow column to the result witch one has the sum of out and the other has the cumulative sum of in

 SELECT B.*,C.sum_out  FROM (SELECT A.*,SUM(in) OVER (PARTITION BY product ORDER BY time_stamp desc) AS sum_in FROM table A) B
 LEFT OUTER JOIN 
 SELECT C.* FROM (SELECT product,SUM(out) AS sum_out from table GROUP BY product WHERE time_stamp>'08:05:00') C
 ON B.product=C.product

so I get :

op_type  |  time_stamp  |  product | in  | out  | sum_in  | sum_out
-------------------------------------------------------------------------
01       |  08:00:00    | p1       | 50  | 0    | 140     | 35
02       |  08:01:00    | p1       | 0   | 10   | 90      | 35
02       |  08:02:00    | p1       | 0   | 35   | 90      | 35
03       |  08:03:00    | p1       | 0   | 5    | 90      | 35
01       |  08:04:00    | p1       | 60  | 0    | 90      | 35
02       |  08:09:00    | p1       | 0   | 15   | 30      | 35
01       |  08:10:00    | p1       | 30  | 0    | 30      | 35
02       |  08:11:00    | p1       | 0   | 20   | 0       | 35
01       |  08:00:00    | p2       | 100 | 0    | 195     | 40
02       |  08:01:00    | p2       | 0   | 20   | 95      | 40
02       |  08:02:00    | p2       | 0   | 45   | 95      | 40 
03       |  08:03:00    | p2       | 0   | 15   | 95      | 40 
01       |  08:03:10    | p2       | 60  | 0    | 95      | 40 
01       |  08:04:00    | p2       | 5   | 0    | 35      | 40
02       |  08:09:00    | p2       | 0   | 30   | 30      | 40
01       |  08:10:00    | p2       | 30  | 0    | 30      | 40
02       |  08:11:00    | p2       | 0   | 10   | 0       | 40

If I add the clause WHERE B.sum_in<=C.sum_out i would be able to get 

op_type  |  time_stamp  |  product | in  | out  | sum_in  | sum_out
-------------------------------------------------------------------------
02       |  08:09:00    | p1       | 0   | 15   | 30      | 35
01       |  08:10:00    | p1       | 30  | 0    | 30      | 35
02       |  08:11:00    | p1       | 0   | 20   | 0       | 35
01       |  08:04:00    | p2       | 5   | 0    | 35      | 40
02       |  08:09:00    | p2       | 0   | 30   | 30      | 40
01       |  08:10:00    | p2       | 30  | 0    | 30      | 40
02       |  08:11:00    | p2       | 0   | 10   | 0       | 40

So I will need one more row for each product.

Any idea how to perform this?? PS: I'm using SQL SERVER 2012.

Upvotes: 0

Views: 67

Answers (2)

Juan Piaggio
Juan Piaggio

Reputation: 190

I drafted a possible solution, you can use subqueries to get all the accumulated transactions 

            Select time_stamp, 
        (Select Sum(SQ.InEntry - SQ.OutEntry)  from Table_1 AS SQ where T.time_stamp > SQ.time_stamp AND T.product = SQ.product group by product) +
            InEntry - OutEntry 
            , T.InEntry
            , T.OutEntry
            , product 
            from Table_1 T
            order by product,time_stamp

To generate random data I used this statement 

            declare @FromDate date = '2016-01-01'
            declare @ToDate date = '2016-12-31'
             declare @product varchar(50)
             set @product = ( select top 1  A.Prod from (Select 'A' AS Prod union all select 'B' AS Prod union all select 'C' AS Prod) AS A order by newid())

            INSERT INTO [dbo].[Table_1]
                       ([time_stamp]
                       ,[product]
                       ,[InEntry]
                       ,[OutEntry])
            Select
            dateadd(day, 
                           rand(checksum(newid()))*(1+datediff(day, @FromDate, @ToDate)), 
                           @FromDate)
            ,@product
            ,0
            ,ABS(Checksum(NewID()) % 100)


            INSERT INTO [dbo].[Table_1]
                       ([time_stamp]
                       ,[product]
                       ,[InEntry]
                       ,[OutEntry])
            Select
            dateadd(day, 
                           rand(checksum(newid()))*(1+datediff(day, @FromDate, @ToDate)), 
                           @FromDate)
            ,@product
            ,@product ,ABS(Checksum(NewID()) % 100)
            ,0
            GO 40

Upvotes: 2

AntDC
AntDC

Reputation: 1917

You don't seem to be grouping by product? Anyway this may help, you can do your normal aggregates using SUM & GROUP BY grouping on what ever with the appropriate WHERE clause on the time.

But to get what you ask...

SUM(in) group by product will cover the SUM(out)

....you can use HAVING.....

Eg 

SELECT Field1, Field2, SUM(IN), SUM(OUT)
FROM Table
WHERE Your_where_clause
GROUP BY Field1, Field2
HAVING SUM(IN) >= SUM(OUT)

Upvotes: 0

Related Questions