CODEWITHSUNDEEP
sqlsql-server
RoverRoll
RoverRoll

Reputation: 135

Two query in one main query

I got two queries where i need to make a single query , only one conditions are different among them .

    SELECT DATEADD(DD,7-CHOOSE(DATEPART(dw, TM.PostDate), 2,3,4,5,6,7,1),TM.PostDate) AS [WeekEndDate],
LocationGroupname,
SUM(ISNULL(ChargeAmount,0)) AS CA 
 from [TransactionMasterReport]  as tm where tm.PostDate between '01/01/2018' and '02/03/2018'   
AND MODALITY IN ('DRUGS','E & M CODES','SPP & LASH')
**and transactiontype  IN ('Charges' ,'Voided Charges')**
 and locationgroupname like '%ABC%'

    group by DATEADD(DD,7-CHOOSE(DATEPART(dw, TM.PostDate), 2,3,4,5,6,7,1),TM.PostDate),LocationGroupname

    order by [WeekEndDate]


    SELECT DATEADD(DD,7-CHOOSE(DATEPART(dw, TM.PostDate), 2,3,4,5,6,7,1),TM.PostDate) AS [WeekEndDate],
LocationGroupname,
SUM(ISNULL(PaymentAmount,0))  AS PA 
from [TransactionMasterReport]  as tm 
where tm.PostDate between '01/01/2018' and '02/03/2018'   
 AND MODALITY IN ('DRUGS','E & M','SPP & LASH')
**and [TransactionType] in ('Payments','Adjustments')** 
and locationgroupname like '%ABC%'

 group by DATEADD(DD,7-CHOOSE(DATEPART(dw, TM.PostDate), 2,3,4,5,6,7,1),TM.PostDate),LocationGroupname

    order by [WeekEndDate]

THE output must be like this:Output

Upvotes: 0

Views: 60

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you can join your both query 

with cte1 as
(
SELECT DATEADD(DD,7-CHOOSE(DATEPART(dw, TM.PostDate), 2,3,4,5,6,7,1),TM.PostDate) AS [WeekEndDate],
LocationGroupname,
SUM(ISNULL(ChargeAmount,0)) AS CA 
 from [TransactionMasterReport]  as tm where tm.PostDate between '01/01/2018' and '02/03/2018'   
AND MODALITY IN ('DRUGS','E & M CODES','SPP & LASH')
**and transactiontype  IN ('Charges' ,'Voided Charges')**
 and locationgroupname like '%ABC%'

    group by DATEADD(DD,7-CHOOSE(DATEPART(dw, TM.PostDate), 2,3,4,5,6,7,1),TM.PostDate),LocationGroupname

    order by [WeekEndDate]
), cte2 as(


    SELECT DATEADD(DD,7-CHOOSE(DATEPART(dw, TM.PostDate), 2,3,4,5,6,7,1),TM.PostDate) AS [WeekEndDate],
LocationGroupname,
SUM(ISNULL(PaymentAmount,0))  AS PA 
from [TransactionMasterReport]  as tm 
where tm.PostDate between '01/01/2018' and '02/03/2018'   
 AND MODALITY IN ('DRUGS','E & M','SPP & LASH')
**and [TransactionType] in ('Payments','Adjustments')** 
and locationgroupname like '%ABC%'

 group by DATEADD(DD,7-CHOOSE(DATEPART(dw, TM.PostDate), 2,3,4,5,6,7,1),TM.PostDate),LocationGroupname

    order by [WeekEndDate]
) select cte1.* ,cte2.PA from cte1 join cte2 on cte1.LocationGroupname=cte2.LocationGroupname and cte1.WeekEndDate=cte2.WeekEndDate

Upvotes: -1

Venkataraman R
Venkataraman R

Reputation: 12959

Join two IN clauses into a single IN clause and apply CASE logic in the calculation of SUM, so that you are calculating paymentamount and chargeamount separately. 

SELECT DATEADD(DD,7-CHOOSE(DATEPART(dw, TM.PostDate), 2,3,4,5,6,7,1),TM.PostDate) AS [WeekEndDate],
LocationGroupname,
SUM(CASE WHEN transactiontype IN (transactiontype  IN ('Charges' ,'Voided Charges') THEN ISNULL(ChargeAmount,0) ELSE 0 END) AS CA, 
  SUM(CASE WHEN transactiontype IN (transactiontype  IN ('Payments','Adjustments') THEN iSNULL(PaymentAmount,0) ELSE 0 END) AS PA
 from [TransactionMasterReport]  as tm where tm.PostDate between '01/01/2018' and '02/03/2018'   
AND MODALITY IN ('DRUGS','E & M CODES','SPP & LASH')
and transactiontype  IN ('Charges' ,'Voided Charges', 'Payments','Adjustments')
 and locationgroupname like '%ABC%'

    group by DATEADD(DD,7-CHOOSE(DATEPART(dw, TM.PostDate), 2,3,4,5,6,7,1),TM.PostDate),LocationGroupname

    order by [WeekEndDate]

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

Use conditional aggregation: As you are querying from same table:

 SELECT DATEADD(DD,7-CHOOSE(DATEPART(dw, TM.PostDate), 2,3,4,5,6,7,1),TM.PostDate) AS [WeekEndDate],
LocationGroupname,
SUM(case when transactiontype  IN ('Charges' ,'Voided Charges') then ChargeAmount else 0 end) AS CA,
SUM(case when transactiontype  IN ('Payments','Adjustments') then PaymentAmount else 0 end) AS PA 
 from [TransactionMasterReport]  as tm where tm.PostDate between '01/01/2018' and '02/03/2018'   
AND MODALITY IN ('DRUGS','E & M CODES','SPP & LASH')
and locationgroupname like '%ABC%'
group by DATEADD(DD,7-CHOOSE(DATEPART(dw, TM.PostDate), 2,3,4,5,6,7,1),TM.PostDate),LocationGroupname
order by [WeekEndDate]

Upvotes: 3

Related Questions