Combining the Result from two different Queries

Question

Post reply

Combining the Result from two different Queries

kd_um

SSC-Addicted

Points: 460
More actions
October 3, 2012 at 5:16 am

#262494

Hi experts,
Have been dwindling on this query for some time and need your help now. I have two separate queries with different tables all together. Both these queries have one Union All each, so each of the two query has 2 sub queries. Now I want the result of these two queries into one select statement/ or one final result.
The result I want is:
Total Sales £ Total Uninvoiced £
--------------------------------------
10000 500
The 2 queries are:
Query 1:
======
SELECT
Sum(TotalSales) as 'Total Sales £' FROM (Select (T0.[DocTotal]-T0.[VatSum]) as 'TotalSales' FROM OINV T0 WHERE T0.[DocDate] = GETDATE()
union all
Select -(T0.[DocTotal]-T0.[VatSum]) as 'TotalSales' FROM ORIN T0 WHERE T0.[DocDate] = GETDATE()) TotalSales,
Query 2:
=======
SELECT
Sum(Total) as 'Total Uninvoiced £' From (Select sum(T1.LineTotal) as Total FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode WHERE T1.[OpenQty]>0
UNION ALL
Select sum(T1.LineTotal) as Total FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode WHERE T1.[OpenQty]>0 and T1.[BaseRef] > 0) Total
Please help.
Thanks in advance.
Kanu

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 112965 More actions · Answer 1

Nest the two statements as derived subqueries and add a joining criteria, then join the two together like below

SELECT

T1.[Total Sales £],

T2.[Total Uninvoiced £]

FROM

(

SELECT

1 AS ID,

Sum(TotalSales) as 'Total Sales £' FROM (Select (T0.[DocTotal]-T0.[VatSum]) as 'TotalSales' FROM OINV T0 WHERE T0.[DocDate] = GETDATE()

union all

Select -(T0.[DocTotal]-T0.[VatSum]) as 'TotalSales' FROM ORIN T0 WHERE T0.[DocDate] = GETDATE()) TotalSales,

) AS T1

INNER JOIN

(

SELECT

1 AS ID,

Sum(Total) as 'Total Uninvoiced £' From (Select sum(T1.LineTotal) as Total FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode WHERE T1.[OpenQty]>0

UNION ALL

Select sum(T1.LineTotal) as Total FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode WHERE T1.[OpenQty]>0 and T1.[BaseRef] > 0) Total

) AS T2

ON T1.ID = T2.ID

kd_um SSC-Addicted Points: 460 More actions · Answer 2

Hi Anthony,

Thanks for the solution. The query is giving some syntax error:

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ')'. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'AS'. 3). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'Service Contracts' (OCTR) (s) could not be prepared.

Any idea sir?

Thanks & Regards,

Kanu

kd_um SSC-Addicted Points: 460 More actions · Answer 3

Found out the problem. My fault.

Thank you very much for the solution. It is working as I wanted.

Regards,

Kanu

Ant-Green SSC Guru Points: 112965 More actions · Answer 4

There is a rouge comma in the SQL which you supplied on the first post at the end of the TotalSales word, should be able to remove that and it work as intended.