Use one nested query 3 running totals

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources