Combining the Result from two different Queries

Question

Post reply

Combining the Result from two different Queries

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

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

Ant-Green SSC Guru Points: 113566 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: 113566 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.