Report Based On Multiple SQL Queries

[iRadiate]

You might want to add another column to each part of the union all query, in this case I would add 'QaOutputWater' as 'fromtable' to the first query, and 'QaOutputPlastic' as 'fromtable' to the second query.
This obviously gives you a way to determine where it's from.

I think this is the right track. Unfortunately, MS SQL doesn't let me return table name.

[iRadiate]

Okay, had to enter the table name as a text field. This does work ..

Union All.jpg

I should be able to combine all the queries into one this way. That should work for using it in a report.

THANK YOU everyone for the great suggestions, this was a very interesting challenge.

[madtowng]

sorry i wasn’t more specific about how to add those columns but glad to see you figured it out.

[Russell Craycraft]

This may work for you better? I'm probably just shooting from the hip at this point though.

SELECT COALESCE(w.MachineId, p.MachineId, m.MachineId, me.MachineId, bm.MachineId, img.MachineId, mm.MachineId, pr.MachineId, vm.MachineId) AS MachineId, COALESCE(w.MachineDisplayName, p.MachineDisplayName, m.MachineDisplayName, me.MachineDisplayName, bm.MachineDisplayName, img.MachineDisplayName, mm.MachineDisplayName, pr.MachineDisplayName, vm.MachineDisplayName) AS MachineDisplayName, w.QaId AS OutputWaterQaId, w.Day AS OutputWaterDay, w.ElapsedDays AS OutputWaterElapsedDays, w.PrimaryPhys AS OutputWaterPrimaryPhys, w.BackupPhys AS OutputWaterBackupPhys, w.Approved AS OutputWaterApproved, p.QaId AS OutputPlasticQaId, p.Day AS OutputPlasticDay, p.ElapsedDays AS OutputPlasticElapsedDays, p.PrimaryPhys AS OutputPlasticPrimaryPhys, p.BackupPhys AS OutputPlasticBackupPhys, p.Approved AS OutputPlasticApproved, -- Repeat this pattern for the remaining tables (m, me, bm, img, mm, pr, vm) FROM (SELECT MAX(OutputWaterQaId) AS QaId, MachineDisplayName, MachineId, Day, ABS(DATEDIFF(DAY, GETDATE(), Day)) AS ElapsedDays, PrimaryPhys, BackupPhys, Approved FROM QaOutputWater GROUP BY MachineId) w LEFT JOIN (SELECT MAX(OutputPlasticQaId) AS QaId, MachineDisplayName, MachineId, Day, ABS(DATEDIFF(DAY, GETDATE(), Day)), PrimaryPhys, BackupPhys, Approved FROM QaOutputPlastic GROUP BY MachineId) p ON w.MachineId = p.MachineId LEFT JOIN (SELECT MAX(WeeklyMlcQaId) AS QaId, MachineDisplayName, MachineId, Day, ABS(DATEDIFF(DAY, GETDATE(), Day)), PrimaryPhys, BackupPhys, Approved FROM QaWeeklyMlc GROUP BY MachineId) m ON w.MachineId = m.MachineId LEFT JOIN (SELECT MAX(WeeklyMechQaId) AS QaId, MachineDisplayName, MachineId, Day, ABS(DATEDIFF(DAY, GETDATE(), Day)), PrimaryPhys, BackupPhys, Approved FROM QaWeeklyMechanical GROUP BY MachineId) me ON w.MachineId = me.MachineId LEFT JOIN (SELECT MAX(BiWeeklyMechQaId) AS QaId, MachineDisplayName, MachineId, Day, ABS(DATEDIFF(DAY, GETDATE(), Day)), PrimaryPhys, BackupPhys, Approved FROM QaBiWeeklyMechanical GROUP BY MachineId) bm ON w.MachineId = bm.MachineId LEFT JOIN (SELECT MAX(MonthlyImgQaId) AS QaId, MachineDisplayName, MachineId, Day, ABS(DATEDIFF(DAY, GETDATE(), Day)), PrimaryPhys, BackupPhys, Approved FROM QaMonthlyImaging GROUP BY MachineId) img ON w.MachineId = img.MachineId LEFT JOIN (SELECT MAX(MonthlyMechQaId) AS QaId, MachineDisplayName, MachineId, Day, ABS(DATEDIFF(DAY, GETDATE(), Day)), PrimaryPhys, BackupPhys, Approved vbnetCopy code
FROM QaMonthlyMechanical GROUP BY MachineId) mm ON w.MachineId = mm.MachineId
LEFT JOIN (SELECT MAX(MonthlyProfilerQaId) AS QaId, MachineDisplayName, MachineId, Day, ABS(DATEDIFF(DAY, GETDATE(), Day)), PrimaryPhys, BackupPhys, Approved FROM QaMonthlyProfiler GROUP BY MachineId) pr ON w.MachineId = pr.MachineId LEFT JOIN (SELECT MAX(MonthlyVMAT2QaId) AS QaId, MachineDisplayName, MachineId, Day, ABS(DATEDIFF(DAY, GETDATE(), Day)), PrimaryPhys, BackupPhys, Approved FROM QaMonthlyVMAT2 GROUP BY MachineId) vm ON w.MachineId = vm.MachineId ORDER BY MachineId;

[Russell Craycraft]

My bad. I didn't see that there were already 2 pages to this post. I'm glad the other seemed to work for you. What I sent last may or may not work. It's hard to work in a database you can't see or test lol.

[iRadiate]

So here is the final working report Query ...

SELECT OutputWaterQaId,        
'Water Output' AS Tablename,
MachineDisplayName AS OutputWaterMachineDisplayName,
MachineId AS OutputWaterMachineId,
Day AS OutputWaterDay,
Abs(DATEDIFF(day, GETDATE(), Day)) AS OutputWaterElapsedDays,
PrimaryPhys AS OutputWaterPrimaryPhys,
BackupPhys AS OutputWaterBackupPhys,
Approved AS OutputWaterApproved
FROM QaOutputWater
WHERE OutputWaterQaId IN (SELECT MAX(OutputWaterQaId) FROM QaOutputWater GROUP BY MachineId)

UNION ALL

SELECT OutputPlasticQaId,
'Energy Ratio' AS Tablename,
MachineDisplayName AS OutputPlasticMachineDisplayName,
MachineId AS OutputPlasticMachineId,
Day AS OutputPlasticDay,
Abs(DATEDIFF(day, GETDATE(), Day)) AS OutputPlasticElapsedDays,
PrimaryPhys AS OutputPlasticPrimaryPhys,
BackupPhys AS OutputPlasticBackupPhys,
Approved AS OutputPlasticApproved
FROM QaOutputPlastic
WHERE OutputPlasticQaId IN (SELECT MAX(OutputPlasticQaId) FROM QaOutputPlastic GROUP BY MachineId)

UNION ALL

SELECT     WeeklyMlcQaId,
'Weekly MLC' as Tablename,
MachineDisplayName AS WeeklyMLCMachineDisplayName,
MachineId AS WeeklyMLCMachineId,
Day AS WeeklyMLCDay,
Abs(DATEDIFF(day, GetDate(), Day)) AS WeeklyMLCElapsedDays,
PrimaryPhys AS WeeklyMLCPrimaryPhys,
BackupPhys AS WeeklyMLCBackupPhys,
Approved AS WeeklyMLCApproved
FROM QaWeeklyMlc
WHERE WeeklyMlcQaId IN (SELECT MAX(WeeklyMlcQaId) FROM QaWeeklyMlc GROUP BY MachineId)

UNION ALL

SELECT     WeeklyMechQaId,
'Weekly Mechanical' AS Tablename,
MachineDisplayName AS WeeklyMechMachineDisplayName,
MachineId AS WeeklyMechMachineId,
Day AS WeeklyMechDay,
Abs(DATEDIFF(day,  GetDate() , Day) )  AS WeeklyMechElapsedDays,
PrimaryPhys AS WeeklyMechPrimaryPhys,
BackupPhys AS WeeklyMechBackupPhys,
Approved AS WeeklyMechApproved    
FROM QaWeeklyMechanical
WHERE WeeklyMechQaId IN (SELECT MAX(WeeklyMechQaId) FROM QaWeeklyMechanical GROUP BY MachineId)

UNION ALL

SELECT  BiWeeklyMechQaId,
'Bi-Weekly Mechanical' AS Tablename,
MachineDisplayName AS BiWeeklyMechMachineDisplayName,
MachineId AS BiWeeklyMechMachineId,
Day AS BiWeeklyMechDay,
Abs(DATEDIFF(day, GetDate(), Day)) AS BiWeeklyMechElapsedDays,
PrimaryPhys AS BiWeeklyMechPrimaryPhys,
BackupPhys AS BiWeeklyMechBackupPhys,
Approved AS BiWeeklyMechApproved
FROM QaBiWeeklyMechanical
WHERE BiWeeklyMechQaId IN (SELECT MAX(BiWeeklyMechQaId) FROM QaBiWeeklyMechanical GROUP BY MachineId)  

UNION ALL

SELECT     MonthlyImgQaId,
'Monthly Imaging' AS Tablename,
MachineDisplayName AS MonthlyImgMachineDisplayName,
MachineId AS MonthlyImgMachineId,
Day AS MonthlyImgDay,
Abs(DATEDIFF(day,  GetDate() , Day) )  AS MonthlyImgElapsedDays,
PrimaryPhys AS MonthlyImgPrimaryPhys,
BackupPhys AS MonthlyImgBackupPhys,
Approved AS MonthlyImgApproved
FROM QaMonthlyImaging
WHERE MonthlyImgQaId IN (SELECT MAX(MonthlyImgQaId) FROM QaMonthlyImaging GROUP BY MachineId)  

UNION ALL

SELECT     MonthlyMechQaId,
'Monthly Mechanical' AS Tablename,
MachineDisplayName AS MonthlyMechMachineDisplayName,
MachineId AS MonthlyMechMachineId,
Day AS MonthlyMechDay,
Abs( DATEDIFF(day,  GetDate() , Day) )  AS MonthlyMechElapsedDays,
PrimaryPhys AS MonthlyMechPrimaryPhys,
BackupPhys AS MonthlyMechBackupPhys,
Approved AS MonthlyMechApproved
FROM QaMonthlyMechanical
WHERE MonthlyMechQaId IN (SELECT MAX(MonthlyMechQaId) FROM QaMonthlyMechanical GROUP BY MachineId)

UNION ALL

SELECT MonthlyProfilerQaId,
'Monthly Profiler' AS Tablename,
MachineDisplayName AS MonthlyProfilerMachineDisplayName,
MachineId AS MonthlyProfilerMachineId,
Day AS MonthlyProfilerDay,
Abs(DATEDIFF(day, GetDate(), Day)) AS MonthlyProfilerElapsedDays,
PrimaryPhys AS MonthlyProfilerPrimaryPhys,
BackupPhys AS MonthlyProfilerBackupPhys,
Approved AS MonthlyProfilerApproved
FROM QaMonthlyProfiler
WHERE MonthlyProfilerQaId IN (SELECT MAX(MonthlyProfilerQaId) FROM QaMonthlyProfiler GROUP BY MachineId)  

UNION ALL

SELECT MonthlyVMAT2QaId,
'Monthly VMAT' AS Tablename,
MachineDisplayName AS MonthlyVMAT2MachineDisplayName,
MachineId AS MonthlyVMAT2MachineId,
Day AS MonthlyVMAT2Day,
Abs(DATEDIFF(day, GetDate(), Day)) AS MonthlyVMAT2ElapsedDays,
PrimaryPhys AS MonthlyVMAT2PrimaryPhys,
BackupPhys AS MonthlyVMAT2BackupPhys,
Approved AS MonthlyVMAT2Approved
FROM QaMonthlyVMAT2
WHERE MonthlyVMAT2QaId IN (SELECT MAX(MonthlyVMAT2QaId) FROM QaMonthlyVMAT2 GROUP BY MachineId)

ORDER BY TableName, OutputWaterMachineId

​

Which gives me the summary report I need ...

Final Report.jpg

I just need to pretty up the report a bit.
And for some reason the final line with ORDER BY TableName, OutputWaterMachineId works in SSMS but Alpha complains about the syntax (whether or not I use Portable or Native SQL and despite the Alpha Preview showing the proper sorted results .. weird).

Thanks again everyone for you help and suggestions with this.

[Russell Craycraft]

I'm glad you got it working.

Now just get ChatGPT lol. The free version was worth so much in time savings, that I got the paid subscription last week. (I know I keep pushing people towards it, but it really is that great). I didn't have time to build the query yesterday, but I had time to use your originals and make it build it for you.

[iRadiate]

I just created an account

[Russell Craycraft]

Congrats!
You need to boss it around though, and be specific, but you'll get the hang of it.

Try make this JavaScript better "insert code after"

Or Fix this ms sql query ...
But put in ms sql or in MySQL so it's consistent.

[iRadiate]

It just solved my issue with my ORDER BY issue.
I created a stored procedure for the report.

Does this mean I can't claim that I am the sole developer of my application now?

[CharlesParker]

You'll always be the "soul" developer

[pcsystems]

...also didn't see there are two pages to this post and the issue has already been resolved...

Lately, I've started setting up all of my Alpha reports to use a "custom data source", rather than a specific SQL query. As a result, I can use Xbasic code to access every element of the "e" object that gets passed in and control the exact data the report should print. And that way, all of the SQL queries have been done by the viewboxes, lists, etc before the user chooses the report they want to produce. At print time, I simply send a customized comma-delimited string of data along to the report and it works quite well. Not sure if that approach would work for you in this case, but if you just want your report to include the contents of a single viewbox, no matter how the data got there, it could be an option.