CODEWITHSUNDEEP
sqlsql-server-2008
Jkterina
Jkterina

Reputation: 3

SQL Server query : how can I SUM one SELECT to another?

I have a problem with my SQL Server query, and I hope you will help me

The main select must output date and sum of the weight

How can I sum one select to another? Data is taking from one table Tbreport, and condition is - to sum weight of the concrete date and previous date (concrete date minus 1 day)

For example: 

CONCRETE DATE  WEIGHT 
  Jan 1         100                 
  Jan 2         150         
  Jan 3         210         

PREVIOUS DATE   WEIGHT 
  Jan 1          100 
  Jan 2          250 (Jan 1 + Jan 2)
  Jan 3          460 (Jan 1 + Jan 2 + Jan 3)

In real table I have format in seconds. For ex: 1358892000 seconds is 2013 Jan 23 0:00:00 and 1358978400 is 2013 Jan 23 23:59:00. And every time is own weight

Query:

SELECT CONVERT(varchar, DATEADD(s, TBreport.date, 25568), 102)  AS DATE,
    SUM(TBreport.weight) 
+ 
(
SELECT SUM(TBreport.weight) AS WEIGHT 
FROM         TBreport INNER JOIN TBway ON TBreport.id_way = TBway.id 
WHERE     (SUBSTRING(TBway.name, 5, 8) LIKE 'to warehouse')  
AND ... ???
GROUP BY CONVERT(varchar, DATEADD(s, TBreport.date, 25568), 102)  
)  
FROM         TBreport INNER JOIN TBway ON TBreport.id_way = TBway.id 
WHERE     (SUBSTRING(TBway.name, 5, 8) LIKE 'to warehouse')  
GROUP BY CONVERT(varchar, DATEADD(s, TBreport.date, 25568), 102)

Upvotes: 0

Views: 518

Answers (1)

lc.
lc.

Reputation: 116468

I'm assuming date is unique in TBreport

SELECT this.date, SUM(upToThis.weight)
FROM TBreport this
INNER JOIN TBreport upToThis ON upToThis.date <= this.date
GROUP BY this.date

Upvotes: 1

Related Questions