• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 595
  • Last Modified:

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

SQL 2008 :-

Sequence of the Posting : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26826746.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

0
chokka
Asked:
chokka
  • 6
  • 4
  • 3
2 Solutions
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Try this:  Essentially uses a temporary table as a bridge.

-- 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
into #temp
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
insert into #temp
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 [Generic Code], NDC, [Drug Name], sum(Qty) as Qty
from #temp
group by
[Generic Code], NDC, [Drug Name]

drop table #temp
0
 
SharathData EngineerCommented:
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.
0
 
chokkaAuthor Commented:
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
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
SharathData EngineerCommented:
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

0
 
chokkaAuthor Commented:
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
0
 
SharathData EngineerCommented:
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

0
 
chokkaAuthor Commented:
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
0
 
SharathData EngineerCommented:
corrected.
;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 p3.[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

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
was there something wrong with my query?
0
 
chokkaAuthor Commented:
Sharath123 - ID: 34917881 ; is not working.

0
 
chokkaAuthor Commented:
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.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
chokka:  I still believe this solution works: 34917258
0
 
chokkaAuthor Commented:
ged325,  your query output was varying ( Total Number of records affected ). I felt hard to track row by row.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now