Solved

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

Posted on 2011-02-17
13
577 Views
Last Modified: 2012-05-11
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
Comment
Question by:chokka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
13 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 250 total points
ID: 34917258
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
 
LVL 41

Expert Comment

by:Sharath
ID: 34917494
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
 

Author Comment

by:chokka
ID: 34917523
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 41

Expert Comment

by:Sharath
ID: 34917634
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
 

Author Comment

by:chokka
ID: 34917680
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
 
LVL 41

Expert Comment

by:Sharath
ID: 34917706
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
 

Author Comment

by:chokka
ID: 34917821
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
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 250 total points
ID: 34917881
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 34917922
was there something wrong with my query?
0
 

Author Comment

by:chokka
ID: 34919329
Sharath123 - ID: 34917881 ; is not working.

0
 

Author Comment

by:chokka
ID: 34919506
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 34919519
chokka:  I still believe this solution works: 34917258
0
 

Author Closing Comment

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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question