SQL calculating two different things from same data

[baitmaster]

Hi, I'm trying to use SQL within VBA to calculate several related things at once, but I'm doing something wrong

I have 4 columns I'm interested in: ID, Description, Value and Date (see code for exact labels). I want to return a dataset that contains
- unique ID
- unique Description
- sum of all values up to end of this month
- sum of all values up to end of last month

I think I need to create two sets of calculations and then join the results

My current attempt has been to do this but use UNION on the two sets of results, which I can see is wrong. Pretty sure I need to be Joining the results but just getting syntax errors. I've split into 2 separate subqueries for clarity

Function getRsReport1Sum(lAcct As Long, lDate As Long, lDatePrior As Long) As ADODB.Recordset


' create SQL string
Dim strTbl1 As String, strTbl2 As String, strSql As String


strTbl1 = "SELECT " & _
         "[data$].[Proj Desc], [data$].[Proj ID], SUM([data$].[Monetary AMT ]) AS CurrMonth " & _
         "FROM [data$] " & _
         "WHERE [data$].[Account ] = " & lAcct & " " & _
         "AND [data$].[Journal Date ] <= " & lDate & " " & _
         "GROUP BY [data$].[Proj ID], [data$].[Proj Desc] "
         
strTbl2 = "SELECT " & _
         "[data$].[Proj Desc], [data$].[Proj ID], SUM([data$].[Monetary AMT ]) AS PriorMonth " & _
         "FROM [data$] " & _
         "WHERE [data$].[Account ] = " & lAcct & " " & _
         "AND [data$].[Journal Date ] <= " & lDatePrior & " " & _
         "GROUP BY [data$].[Proj ID], [data$].[Proj Desc] "
         
strSql = "SELECT * FROM (" & _
         strTbl1 & _
         "UNION " & _
         strTbl2 & _
         ");"

The results I'm getting are in 3 columns so clearly the values are being merged, which is not what I want - I'm trying to keep two separate columns

The final SQL string I'm getting is
SELECT *
FROM (

SELECT [data$].[Proj Desc], [data$].[Proj ID], SUM([data$].[Monetary AMT ]) AS CurrMonth
FROM [data$]
WHERE [data$].[Account ] = 120009 And [data$].[Journal Date ] <= 43585
GROUP BY [data$].[Proj ID], [data$].[Proj Desc]

UNION

SELECT [data$].[Proj Desc], [data$].[Proj ID], SUM([data$].[Monetary AMT ]) AS PriorMonth
FROM [data$]
WHERE [data$].[Account ] = 120009 And [data$].[Journal Date ] <= 43555
GROUP BY [data$].[Proj ID], [data$].[Proj Desc]

);

 

[baitmaster]

Have tried to JOIN sub-tables but still getting no results

SELECT tbl1.[Proj Desc], tbl1.[Proj ID], SUM(CurrMonth), SUM(PriorMonth) FROM 
(
	(
	SELECT [data$].[Proj Desc], [data$].[Proj ID], [data$].[Monetary AMT ] AS CurrMonth 
	FROM [data$] 
	WHERE [data$].[Account ] = 120000 
	AND [data$].[Journal Date ] <= 43585
	) AS tbl1 
	LEFT JOIN 
		(
		SELECT [data$].[Proj Desc], [data$].[Proj ID], [data$].[Monetary AMT ] AS PriorMonth 
		FROM [data$] 
		WHERE [data$].[Account ] = 120000 
		AND [data$].[Journal Date ] <= 43555
		)  AS tbl2
	ON tbl1.[Proj ID] = tbl2.[Proj ID]
) 
GROUP BY tbl1.[Proj ID], tbl1.[Proj Desc];

 

[Joe4]

How about something like this:

SELECT tbl1.*, tbl2.PriorMonth
FROM
[COLOR=#0000ff](SELECT [data$].[Proj Desc], [data$].[Proj ID], SUM([data$].[Monetary AMT ]) AS CurrMonth
FROM [data$]
WHERE [data$].[Account ] = 120009 And [data$].[Journal Date ] <= 43585
GROUP BY [data$].[Proj ID], [data$].[Proj Desc]) as tbl1[/COLOR]
INNER JOIN
[COLOR=#ff0000](SELECT [data$].[Proj Desc], [data$].[Proj ID], SUM([data$].[Monetary AMT ]) AS PriorMonth
FROM [data$]
WHERE [data$].[Account ] = 120009 And [data$].[Journal Date ] <= 43555
GROUP BY [data$].[Proj ID], [data$].[Proj Desc]) as tbl2[/COLOR][COLOR=#333333]
ON tbl1.[Proj ID] = tbl2.[Proj ID]
[/COLOR]

 

[baitmaster]

Thanks Joe4, that put me on the right track. I wasn't helped by using example values that were returning null data - but your corrections helped me spot that too

 

[Joe4]

You are welcome.

 

[baitmaster]

Follow-up question, I'm looking to extend this theory to calculate all months in one go, but seem to be having trouble joining multiple tables of calculations. I'm generating the SQL using a loop that involves dates, and have created the following

SELECT 	tbl1.[Proj Desc], 
	tbl1.[Proj ID], 
	ROUND(IIF(ISNULL(tbl43555.d43555), 0, tbl43555.d43555), 2), 
	ROUND(IIF(ISNULL(tbl43585.d43585), 0, tbl43585.d43585), 2), 
	ROUND(IIF(ISNULL(tbl43616.d43616), 0, tbl43616.d43616), 2), 
	ROUND(IIF(ISNULL(tbl43646.d43646), 0, tbl43646.d43646), 2), 
	ROUND(IIF(ISNULL(tbl43677.d43677), 0, tbl43677.d43677), 2), 
	ROUND(IIF(ISNULL(tbl43708.d43708), 0, tbl43708.d43708), 2), 
	ROUND(IIF(ISNULL(tbl43738.d43738), 0, tbl43738.d43738), 2), 
	ROUND(IIF(ISNULL(tbl43769.d43769), 0, tbl43769.d43769), 2), 
	ROUND(IIF(ISNULL(tbl43799.d43799), 0, tbl43799.d43799), 2), 
	ROUND(IIF(ISNULL(tbl43830.d43830), 0, tbl43830.d43830), 2), 
	ROUND(IIF(ISNULL(tbl43861.d43861), 0, tbl43861.d43861), 2), 
	ROUND(IIF(ISNULL(tbl43890.d43890), 0, tbl43890.d43890), 2), 
	ROUND(IIF(ISNULL(tbl43921.d43921), 0, tbl43921.d43921), 2) 


FROM 
	(
	SELECT 
		[data$].[Proj Desc], 
		[data$].[Proj ID] 
	FROM 
		[data$] 
	WHERE 
		[data$].[Account ] = 120009
	) 
	As tbl1 
	
[COLOR=#0000ff]LEFT Join [/COLOR]
[COLOR=#0000ff]	([/COLOR]
[COLOR=#0000ff]	SELECT [/COLOR]
[COLOR=#0000ff]		[data$].[Proj Desc], [/COLOR]
[COLOR=#0000ff]		[data$].[Proj ID], [/COLOR]
[COLOR=#0000ff]		SUM([data$].[Monetary AMT ]) AS d43555[/COLOR]
[COLOR=#0000ff]	FROM [/COLOR]
[COLOR=#0000ff]		[data$][/COLOR]
[COLOR=#0000ff]	WHERE [/COLOR]
[COLOR=#0000ff]		[data$].[Account ] = 120009 [/COLOR]
[COLOR=#0000ff]	AND [/COLOR]
[COLOR=#0000ff]		[data$].[Journal Date ] <= 43555 [/COLOR]
[COLOR=#0000ff]	GROUP BY [/COLOR]
[COLOR=#0000ff]		[data$].[Proj ID], [/COLOR]
[COLOR=#0000ff]		[data$].[Proj Desc][/COLOR]
[COLOR=#0000ff]	) [/COLOR]
[COLOR=#0000ff]	as tbl43555 [/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]ON [/COLOR]
[COLOR=#0000ff]	tbl1.[Proj ID] = tbl43555.[Proj ID] [/COLOR]
	
[COLOR=#ff0000]LEFT Join [/COLOR]
[COLOR=#ff0000]	([/COLOR]
[COLOR=#ff0000]	SELECT [/COLOR]
[COLOR=#ff0000]		[data$].[Proj Desc], [/COLOR]
[COLOR=#ff0000]		[data$].[Proj ID], [/COLOR]
[COLOR=#ff0000]		SUM([data$].[Monetary AMT ]) AS d43585 [/COLOR]
[COLOR=#ff0000]	FROM [/COLOR]
[COLOR=#ff0000]		[data$] [/COLOR]
[COLOR=#ff0000]	WHERE [/COLOR]
[COLOR=#ff0000]		[data$].[Account ] = 120009 [/COLOR]
[COLOR=#ff0000]	AND [/COLOR]
[COLOR=#ff0000]		[data$].[Journal Date ] <= 43585 [/COLOR]
[COLOR=#ff0000]	GROUP BY [/COLOR]
[COLOR=#ff0000]		[data$].[Proj ID], [/COLOR]
[COLOR=#ff0000]		[data$].[Proj Desc][/COLOR]
[COLOR=#ff0000]	) [/COLOR]
[COLOR=#ff0000]	as tbl43585 [/COLOR]
[COLOR=#ff0000]
[/COLOR]
[COLOR=#ff0000]ON [/COLOR]
[COLOR=#ff0000]	tbl1.[Proj ID] = tbl43585.[Proj ID][/COLOR] 

... continues repeating the LEFT JOIN section to end, including ";"

I'm getting a syntax error (missing operation) in query expression 'tbl1.[Proj ID] = ........... WHERE [data$].[Account] = 120009'

Is there a different rule for joining multiple tables? Do I need to reconsider how brackets are being used (if even needed at all)? Or have I created a text string that's too long to use..?

 

[Joe4]

Things can get kind of tricky if not every id has an entry for every month. What I typically do is create a query that just returns each distinct ID once, and use that as my "main" object, and then do Left Outer joins from that to all the other 12 minthly totals queries.

What I would recommend doing is starting with this "main" query, and just one month and test it out. Once that works, and in the second, etc, until you get to 12,

 

[baitmaster]

Sadly, that's what I've done

With just the black and blue sections this works. Add in the red or more then it doesn't (noting that I also trimmed the ROUND sections)

I've deliberately started with just Project details since this will be a complete set, and then left joined everything to this table. My hunch is that I'm not quite doing this right, so my order of priority is e.g. joining subtable 2 to subtable 1, not to the full table - this was my thinking behind whether I can use brackets to resolve it - but I don't know if that's even a valid approach in SQL

Plus I question if that's even the problem noting the error appears to be syntax-related

 

[Joe4]

Yes, you will want to join all 12 monthly subqueries to the main ID query (not to each other).

Do you have Microsoft Access?

If so, try setting up a simple example in there like this, and see if you can get it to work.
If you can, then switch your query to SQL View and inspect the code and see how it needs to be structured.

 

[baitmaster]

Again sadly not, I'm working on client systems and they only have Excel. If needed I can do this in other ways but I'm interested in understanding how far I can go with ADO instead, as this would potentially integrate well with some other work I'm doing for them, and be more robust in the long term. Looks like I'm gonna have to do it the old-fashioned way...

For the record, this works

SELECT 
	tbl1.[Proj Desc], 
	tbl1.[Proj ID], 
	ROUND(IIF(ISNULL(tbl43555.d43555), 0, tbl43555.d43555), 2) 


FROM 
	(
	SELECT 
		[data$].[Proj Desc], 
		[data$].[Proj ID] 
	FROM 
		[data$] 
	WHERE 
		[data$].[Account ] = 120009
	)
	As tbl1 


LEFT Join 
	(
	SELECT 
		[data$].[Proj ID], 
		SUM([data$].[Monetary AMT ]) AS d43555 
	FROM 
		[data$] 
	WHERE 
		[data$].[Account ] = 120009 
	AND 
		[data$].[Journal Date ] <= 43555 
	GROUP BY 
		[data$].[Proj ID]
	) 
	as tbl43555 
ON 
	tbl1.[Proj ID] = tbl43555.[Proj ID] 
;

but this doesn't

SELECT 
	tbl1.[Proj Desc], 
	tbl1.[Proj ID], 
	ROUND(IIF(ISNULL(tbl43555.d43555), 0, tbl43555.d43555), 2), 
	ROUND(IIF(ISNULL(tbl43585.d43585), 0, tbl43585.d43585), 2) 
FROM 
	(
	SELECT 
		[data$].[Proj Desc], 
		[data$].[Proj ID] 
	FROM 
		[data$] 
	WHERE 
		[data$].[Account ] = 120009
		)
		As tbl1 
	
LEFT Join 
	(
	SELECT 
		[data$].[Proj ID], 
		SUM([data$].[Monetary AMT ]) AS d43555 
	FROM 
		[data$] 
	WHERE 
		[data$].[Account ] = 120009 
	AND 
		[data$].[Journal Date ] <= 43555 
	GROUP BY 
		[data$].[Proj ID]
	)
	as tbl43555
	
ON 
	tbl1.[Proj ID] = tbl43555.[Proj ID] 
	
LEFT Join 
	(
	SELECT 
		[data$].[Proj ID], 
		SUM([data$].[Monetary AMT ]) AS d43585 
	FROM 
		[data$] 
	WHERE 
		[data$].[Account ] = 120009 
	AND 
		[data$].[Journal Date ] <= 43585 
	GROUP BY 
		[data$].[Proj ID]
	) 
	as tbl43585 


ON 
	tbl1.[Proj ID] = tbl43585.[Proj ID]
;