asked on

SQL - Sum of the Column Value - Agg function - By Combining two different Queries

SQL 2008 :-

Sequence of the Posting : https://www.experts-exchange.com/questions/26826746/SQL-Sum-of-the-Column-Value-Agg-function.html

I have attached SQL Scripts which two SQL Queries. I am trying to combine these two scripts as a Single StoredProcedure.

The Stored Procedure result has to be SUM ( Qty ) based on its Generic Code. The DrugName / NDC has to be selected from the Drug which has Maximum Qty.

---
Generic Code NDC Drug Name Qty
4521 00378156001 PHENYTOIN SOD EXT 100MG CAP 30
4521 64679072002 PHENYTOIN SOD EXT 100 MG CA 450

So, Expected Output should be :-

Generic Code NDC Drug Name Qty
4521 64679072002 PHENYTOIN SOD EXT 100 MG CA 480

-- SQL Query 1:-

with p as (
select    
   TR.[Generic Code],  
   TR.NDC,     
   TR.[Drug Name],
   Sum(TR.[Dispensed Qty]) as Qty  
from  TransactionReport TR      
Group By TR.[Generic Code], TR.NDC,TR.[Drug Name] 
)
select x.[Generic Code], x.NDC, x.[Drug Name], y.Qty from 
(
select p.*, row_number() over (partition by [Generic Code] order by Qty desc) rn from p
) x 
left join 
(
select [Generic Code], sum(Qty) Qty from p group by [Generic Code]
) y 
on x.[Generic Code]=y.[Generic Code]
where rn=1;


-- SQL Query 2

select Dr.GenericCode as [Generic Code],Br.NDC as [NDC],Dr.Name as [Drug Name],Br.Qty as [Qty] from BalanceReport Br
Left Join Drug Dr
on 
stuff(Dr.DrugNDCNbr,case when Dr.DrugNDCType in (50, 56) then 1 when Dr.DrugNDCType in (51, 57) then 6    when Dr.DrugNDCType = 52 then 10     end, 0, '0') = Br.NDC

Open in new window

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sharath S

Is the output posted in your questions, result of first query or second query? When you want to combine two queries, post the result of two queries and your expected result.

chokka

ASKER

ASSUME :-

Result of First Query is

Generic Code NDC Drug Name Qty
4521 00378156001 PHENYTOIN SOD EXT 100MG CAP 30

Result of Second Query is

Generic Code NDC Drug Name Qty
4521 64679072002 PHENYTOIN SOD EXT 100 MG CA 450

So, Expected Result is

Generic Code NDC Drug Name Qty
4521 64679072002 PHENYTOIN SOD EXT 100 MG CA 480

Sharath S

check this.

WITH p 
     AS (  SELECT TR.[Generic Code], 
                  TR.NDC, 
                  TR.[Drug Name], 
                  SUM(TR.[Dispensed Qty]) AS Qty 
             FROM TransactionReport TR 
         GROUP BY TR.[Generic Code], 
                  TR.NDC, 
                  TR.[Drug Name]), 
     p1 
     AS (SELECT x.[Generic Code], 
                x.NDC, 
                x.[Drug Name], 
                y.Qty 
           FROM (SELECT p.*, 
                        ROW_NUMBER() 
                          OVER(PARTITION BY [Generic Code] ORDER BY Qty DESC) rn 
                   FROM p) x 
                LEFT JOIN (  SELECT [Generic Code], 
                                    SUM(Qty) Qty 
                               FROM p 
                           GROUP BY [Generic Code]) y 
                  ON x.[Generic Code] = y.[Generic Code] 
          WHERE rn = 1), 
     p2 
     AS (  SELECT [Generic Code], 
                  SUM(Qty) Qty 
             FROM p1 
         GROUP BY [Generic Code]), 
     p3 
     AS (-- SQL Query 2 
        SELECT Dr.GenericCode AS [Generic Code], 
               Br.NDC         AS [NDC], 
               Dr.Name        AS [Drug Name], 
               Br.Qty         AS [Qty] 
           FROM BalanceReport Br 
                LEFT JOIN Drug Dr 
                  ON STUFF(Dr.DrugNDCNbr,CASE 
                                           WHEN Dr.DrugNDCType IN (50,56) THEN 1 
                                           WHEN Dr.DrugNDCType IN (51,57) THEN 6 
                                           WHEN Dr.DrugNDCType = 52 THEN 10 
                                         END,0,'0') = Br.NDC) 
SELECT p2.[Generic Code], 
       NDC, 
       [Drug  Name], 
       p2.Qty + p3.Qty Qty 
  FROM p2 
       JOIN p3 
         ON p2.[Generic Code] = p3.[Generic Code]

Open in new window

chokka

ASKER

Sharath :- The Second Query has bunch of NULL Values in GenericCode,DrugName Columns which are ignored on your script.

select Dr.GenericCode as [Generic Code],Br.NDC as [NDC],Dr.Name as [Drug Name],Br.Qty as [Qty] from BalanceReport Br
Left Join Drug Dr
on
stuff(Dr.DrugNDCNbr,case when Dr.DrugNDCType in (50, 56) then 1 when Dr.DrugNDCType in (51, 57) then 6 when Dr.DrugNDCType = 52 then 10 end, 0, '0') = Br.NDC

Sharath S

ok. check this.

;WITH p 
     AS (  SELECT TR.[Generic Code], 
                  TR.NDC, 
                  TR.[Drug Name], 
                  SUM(TR.[Dispensed Qty]) AS Qty 
             FROM TransactionReport TR 
         GROUP BY TR.[Generic Code], 
                  TR.NDC, 
                  TR.[Drug Name]), 
     p1 
     AS (SELECT x.[Generic Code], 
                x.NDC, 
                x.[Drug Name], 
                y.Qty 
           FROM (SELECT p.*, 
                        ROW_NUMBER() 
                          OVER(PARTITION BY [Generic Code] ORDER BY Qty DESC) rn 
                   FROM p) x 
                LEFT JOIN (  SELECT [Generic Code], 
                                    SUM(Qty) Qty 
                               FROM p 
                           GROUP BY [Generic Code]) y 
                  ON x.[Generic Code] = y.[Generic Code] 
          WHERE rn = 1), 
     p2 
     AS (  SELECT [Generic Code], 
                  SUM(Qty) Qty 
             FROM p1 
         GROUP BY [Generic Code]), 
     p3 
     AS (-- SQL Query 2 
        SELECT Dr.GenericCode AS [Generic Code], 
               Br.NDC         AS [NDC], 
               Dr.Name        AS [Drug Name], 
               Br.Qty         AS [Qty] 
           FROM BalanceReport Br 
                LEFT JOIN Drug Dr 
                  ON STUFF(Dr.DrugNDCNbr,CASE 
                                           WHEN Dr.DrugNDCType IN (50,56) THEN 1 
                                           WHEN Dr.DrugNDCType IN (51,57) THEN 6 
                                           WHEN Dr.DrugNDCType = 52 THEN 10 
                                         END,0,'0') = Br.NDC) 
SELECT p2.[Generic Code], 
       NDC, 
       [Drug  Name], 
       ISNULL(p2.Qty,0) + ISNULL(p3.Qty,0) Qty 
  FROM p3 
       LEFT JOIN p2
         ON p2.[Generic Code] = p3.[Generic Code]

Open in new window

chokka

ASKER

Sharath, We are pretty close.

There is a small mistake.

For these list

Generic Code NDC Drug Name Qty
      60258017101      MAGNESIUM OXIDE 400MG TAB      30
      00603533831      PREDNISONE 10MG TABLET       11
      00054430125      FUROSEMIDE TAB 80MG 100.000      30
      00603578021      SULFAMETHOXAZOLE-TMP SS TAB      79
      00093403001      INDOMETHACIN 50 MG CAPSULE       40
      00093745601      GLIPIZIDE-METFORMIN 2.5-500      60

They have Generic Code, but it is not displaying in the output from the query

SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kyle Abrahams, PMP

was there something wrong with my query?

chokka

ASKER

Sharath123 - ID: 34917881 ; is not working.

chokka

ASKER

I did small trick to get my output.

1) First set of query , i kept in a View

2) second set of quert, i kept in a view

3) i created a view for ( merging these two view by keeping UNION ALL ) keyword.

4) Now i have one View - To which i need to do summation. I did it.

Kyle Abrahams, PMP

chokka: I still believe this solution works: 34917258

chokka

ASKER

ged325, your query output was varying ( Total Number of records affected ). I felt hard to track row by row.