Solved

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

Posted on 2011-02-17
13
562 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
  • 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 40

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
 
LVL 40

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 40

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 40

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now