Find the sum of profit since last transaction(same day or a previous day) at the end of a day in T-SQL_问答_开发者

I had asked a question in April and @Mikael helped me solve it. The requirement has since changed and I am having trouble figuring it out how to build the query. The table structure is

SID Sdate                  Profit   Units 
1   7/26/2010 9:15:00 AM    -37.5   -1      
1   7/26/2010 12:00:00 PM   -125    -1      
1   7/26/2010 12:45:01 PM   -12.5   -1      
1   7/26/2010 12:45:02 PM   0       0       
1   7/26/2010 12:45:03 PM   -75     1 开发者_运维问答      
1   7/26/2010 2:00:01 PM    -12.5   1       
1   7/26/2010 2:00:02 PM    0       0       
1   7/26/2010 2:00:03 PM    -125    -1      
1   7/26/2010 2:15:00 PM    -50     -1  
1   7/27/2010 9:15:00 AM    25      -1      
1   7/27/2010 12:00:00 PM   196     -1      
1   7/27/2010 2:15:00 PM    -12.5   -1 
1   7/28/2010 9:15:00 AM    425     -1      
1   7/28/2010 12:00:00 PM   -125    -1      
1   7/28/2010 2:15:00 PM    -12.5   -1 
1   7/29/2010 9:15:00 AM    -37.5   -1      
1   7/29/2010 12:00:00 PM   -90     -1      
1   7/29/2010 12:45:01 PM   -12.5   -1      
1   7/29/2010 12:45:02 PM   0       0       
1   7/29/2010 12:45:03 PM   -75     1       
1   7/29/2010 2:15:00 PM    23      1

The calculation is explained as :

SID Sdate                  Profit   Units   End-of-day-Profit   Comments
1   7/26/2010 9:15:00 AM    -37.5   -1      
1   7/26/2010 12:00:00 PM   -125    -1      
1   7/26/2010 12:45:01 PM   -12.5   -1      
1   7/26/2010 12:45:02 PM   0       0       
1   7/26/2010 12:45:03 PM   -75     1       
1   7/26/2010 2:00:01 PM    -12.5   1       
1   7/26/2010 2:00:02 PM    0       0       
1   7/26/2010 2:00:03 PM    -125    -1      
1   7/26/2010 2:15:00 PM    -50     -1  -175    SUM of profit(row 9 to row 11) going back to 7/26/2010  2:00:02 PM
1   7/27/2010 9:15:00 AM    25      -1      
1   7/27/2010 12:00:00 PM   196     -1      
1   7/27/2010 2:15:00 PM    -12.5   -1  33.5    SUM of profit ( row 9 to row 14) going back to 7/26/2010  2:00:02 PM
1   7/28/2010 9:15:00 AM    425     -1      
1   7/28/2010 12:00:00 PM   -125    -1      
1   7/28/2010 2:15:00 PM    -12.5   -1  321     SUM of profit(row9 to row 17) going back to 7/26/2010  2:00:02 PM
1   7/29/2010 9:15:00 AM    -37.5   -1      
1   7/29/2010 12:00:00 PM   -90     -1      
1   7/29/2010 12:45:01 PM   -12.5   -1      
1   7/29/2010 12:45:02 PM   0       0       
1   7/29/2010 12:45:03 PM   -75     1       
1   7/29/2010 2:15:00 PM    23      1   -52     SUM of profit(row 22 to row 23) going back to 7/29/2010  12:45:02 PM

The result would be

SID Sdate       Profit   Units
1   7/26/2010   -175    -1
1   7/27/2010   33.5    -1
1   7/28/2010   321     -1
1   7/29/2010   -52     1

The sum of net profit column is taken at 2:15PM everyday for every SID. I included one SID for simplicity.The idea is to look back either current day or previous days for the row that has profit=0 and units=0. Once that row is found, then sum all profit values.

I appreciate any help on this.Thanks.

I don't have access to a database to test it. But populate a table variable @t and try this.

declare @t table (SID int, Sdate datetime, profit decimal(9,1), units int)

<populate it>

I had guess you want a list for each SID. I misunderstood that. I like to change my answer to this.

select SID, Sdata, (select sum(Profit) from @t where Sdate between s.start and s.Sdate and S.SID = SID) profit, Units from 
(select SID, Sdata, (select max(Sdata) from @t where profit = 0 and unit = 0 and Sdata < t.Sdate and SID = t.SID) start from @t t 
where DATEPART(MINUTE, t.Sdata) =15 AND DATEPART(HOUR, t.Sdata) = 14 AND DATEPART(SECOND, t.Sdata) = 0 ) s
ORDER BY SID, Sdate

DDL & sample data:

CREATE TABLE atable (
  SID int,
  Sdate datetime,
  Profit money,
  Units int
);
INSERT INTO atable (SID, Sdate, Profit, Units)
SELECT 1, '7/26/2010 9:15:00 AM' , -37.5, -1   UNION ALL    
SELECT 1, '7/26/2010 12:00:00 PM', -125 , -1   UNION ALL    
SELECT 1, '7/26/2010 12:45:01 PM', -12.5, -1   UNION ALL    
SELECT 1, '7/26/2010 12:45:02 PM', 0    ,  0   UNION ALL     
SELECT 1, '7/26/2010 12:45:03 PM', -75  ,  1   UNION ALL     
SELECT 1, '7/26/2010 2:00:01 PM' , -12.5,  1   UNION ALL     
SELECT 1, '7/26/2010 2:00:02 PM' , 0    ,  0   UNION ALL     
SELECT 1, '7/26/2010 2:00:03 PM' , -125 , -1   UNION ALL    
SELECT 1, '7/26/2010 2:15:00 PM' , -50  , -1   UNION ALL
SELECT 1, '7/27/2010 9:15:00 AM' , 25   , -1   UNION ALL    
SELECT 1, '7/27/2010 12:00:00 PM', 196  , -1   UNION ALL    
SELECT 1, '7/27/2010 2:15:00 PM' , -12.5, -1   UNION ALL
SELECT 1, '7/28/2010 9:15:00 AM' , 425  , -1   UNION ALL    
SELECT 1, '7/28/2010 12:00:00 PM', -125 , -1   UNION ALL    
SELECT 1, '7/28/2010 2:15:00 PM' , -12.5, -1   UNION ALL
SELECT 1, '7/29/2010 9:15:00 AM' , -37.5, -1   UNION ALL    
SELECT 1, '7/29/2010 12:00:00 PM', -90  , -1   UNION ALL    
SELECT 1, '7/29/2010 12:45:01 PM', -12.5, -1   UNION ALL    
SELECT 1, '7/29/2010 12:45:02 PM', 0    ,  0   UNION ALL    
SELECT 1, '7/29/2010 12:45:03 PM', -75  ,  1   UNION ALL    
SELECT 1, '7/29/2010 2:15:00 PM' , 23   ,  1;

The query:

WITH ranges AS (
  SELECT
    t.SID,
    t.Sdate,
    t.Units,
    SdateStart = MAX(t0.Sdate)
  FROM atable t
    INNER JOIN atable t0
       ON t0.SID = t.SID AND t0.Sdate < t.Sdate
      AND t0.Profit = 0 AND t0.Units = 0
  WHERE DATEPART(HH, t.Sdate) = 14
    AND DATEPART(MI, t.Sdate) = 15
  GROUP BY
    t.SID,
    t.Sdate,
    t.Units
)
SElECT
  r.SID,
  r.Sdate,
  Profit = SUM(t.Profit),
  r.Units
FROM ranges r
  INNER JOIN atable t ON t.SID = r.SID
    AND t.Sdate BETWEEN r.SdateStart AND r.Sdate
GROUP BY
  r.SID,
  r.Sdate,
  r.Units;

The output:

SID         Sdate                   Profit                Units
----------- ----------------------- --------------------- -----------
1           2010-07-26 14:15:00.000 -175.00               -1
1           2010-07-27 14:15:00.000 33.50                 -1
1           2010-07-28 14:15:00.000 321.00                -1
1           2010-07-29 14:15:00.000 -52.00                1