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)
 Use one nested query 3 running totals

Author  Topic 

madlo
Starting Member

41 Posts

Posted - 2015-04-22 : 03:31:45
I have the following query


SELECT a.item,a.docdate, SUM(a.value),SUM(a.qty),SUM(a.total),
(SELECT SUM(b.value)
FROM RunTotalTestData b
WHERE b.docdate <= a.docdate and b.item=a.item) as rtvalue,
(SELECT SUM(c.qty)
FROM RunTotalTestData c
WHERE c.docdate <= a.docdate and c.item=a.item) as rtqty,
(SELECT SUM(d.total)
FROM RunTotalTestData c
WHERE d.docdate <= d.docdate and d.item=a.item) as rttotal,
FROM RunTotalTestData a
GROUP BY a.item,a.docdate


Is there a way to write the 3 subueries as one query? It doesn't look very efficient doing similar thing 3 times

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-04-22 : 05:46:24
Looks like you are trying to query the same table so in effect different result sets, if you are you can join the subs something like this:


SELECT a.item,
a.docdate,
SUM(a.value),
SUM(a.qty),
SUM(a.total)
FROM RunTotalTestData a
INNER JOIN (Select SUM(b.value) rtvalue
FROM RunTotalTestData) b
ON b.item=a.item AND b.docdate <= a.docdate
INNER JOIN (SELECT SUM(c.qty) rtqty, SUM(c.total) rttotal
FROM RunTotalTestData) c
ON c.item=a.item AND c.docdate <= a.docdate
GROUP BY a.item,a.docdate
Go to Top of Page
   

- Advertisement -