none
Three selects that need to populate one table

    Question

  • select count(distinct a.txtprogram) as countOfActivePlans, c.txtLevel1
    from tblOfferings a, tblClaims b, tblRetailers c
    where a.ysnActive=1 and c.intRetailer=b.intRetailer and c.intRetailer=a.intRetailer
    group by c.txtLevel1
    order by c.txtLevel1

    select count(distinct a.txtProgram) as plansWithActivity,   c.txtLevel1
        from tblClaims a, tblOfferings b, tblRetailers c
        where a.intOffering=b.intOffering and c.intRetailer=a.intRetailer and   c.intRetailer=b.intRetailer
        group by c.txtlevel1
        order by c.txtLevel1

    Approved/Denied by Area
        select count(a.dblTransaction) as invoicesSubmitted, sum(curPaid) as   sumOfcurPaid,  b.txtLevel1,
        case when a.intTransCode=0 then 'Denied' when a.intTransCode=1 then   'Approved' else 'Reissued' end as txtLabel
        from tblClaims a, tblRetailers b, tbLTransCode c
        where a.intRetailer=b.intRetailer and a.intTransCode=c.intTransCode
        group by b.txtLevel1, case when a.intTransCode=0 then 'Denied' when   a.intTransCode=1 then 'Approved' else 'Reissued' end

    order by b.txtLevel1 asc

    The table is below.  The first select populates countofActive

    the second select populates planswithactivity

    the thrid populates approved, denied, and sumofcurpaid

    How can i make this one insert that populates the table?

    Any help would be greatly appreciated.  Thank you

    Area countOfActivePlans plansWithActivity approved denied sumOfCurPaid
    CE 237 230 89898 11438 1238152.858
    NE 189 178 54159 5667 1665176.757
    SE 177 165 58675 5873 1406241.758
    SW 118 111 32450 6934 1329101.503
    WE 152 145 40266 3473 529956.6875


    Chaunnah Railey

    Friday, March 08, 2013 2:43 PM

Answers

  • Do you have a common column from the 3 SELECT statements where you can join and retreive -

    USE AdventureWorks;
    ;WITH cte1 AS
    (
    SELECT COUNT(SafetyStockLevel) AS c1,ProductID
    FROM AdventureWorks.Production.Product 
    GROUP BY ProductID
    ),cte2 AS
    (
    SELECT SUM(ListPrice) AS c2,ProductID
    FROM AdventureWorks.Production.Product
    GROUP BY ProductID
    ),cte3 AS
    (SELECT SUM(StandardCost) AS c3,ProductID
    FROM AdventureWorks.Production.Product
    GROUP BY ProductID
    )
    SELECT a.ProductID,a.c1,b.c2,c.c3
    FROM cte1 a
    	INNER JOIN cte2 b
    		ON a.ProductID = b.ProductID
    	INNER JOIN cte3 c
    		ON b.ProductID = c.ProductID


    Narsimha


    Friday, March 08, 2013 3:12 PM
  • If i understood this correctly, try like this one below,

    insert into CommonTable (Area,countOfActivePlans,plansWithActivity,approved,denied,	sumOfCurPaid)
    select a.lvl1,a.countOfActivePlans,b.plansWithActivity,c.Approved,c.Denied,c.sumOfcurPaid from 
    (
    select count(distinct a.txtprogram) as countOfActivePlans, c.txtLevel1 lvl1
    from tblOfferings a, tblClaims b, tblRetailers c
    where a.ysnActive=1 and c.intRetailer=b.intRetailer and c.intRetailer=a.intRetailer
    group by c.txtLevel1
    )a
    join 
    (
    select count(distinct a.txtProgram) as plansWithActivity,   c.txtLevel1 lvl1
    from tblClaims a, tblOfferings b, tblRetailers c
    where a.intOffering=b.intOffering and c.intRetailer=a.intRetailer and   c.intRetailer=b.intRetailer
    group by c.txtlevel1
    )b
    on a.lvl1 = b.lvl
    join
    (
    select count(a.dblTransaction) as invoicesSubmitted, sum(curPaid) as   sumOfcurPaid,  b.txtLevel1 lvl1,
    sum(case when a.intTransCode=0 then 1 end) as Denied ,
    sum(case when a.intTransCode=1 then 1 end) as Approved,
    sum(Case when a.intTransCode<> and a.intTransCode<>1 then 1 end )AS Reissued
    from tblClaims a, tblRetailers b, tbLTransCode c
    where a.intRetailer=b.intRetailer and a.intTransCode=c.intTransCode
    group by b.txtLevel1
    )c
    on b.lvl1 = c.lvl1
    
    


    Regards, Dineshkumar
    Please "Mark as Answer" if my post answers your question and "Vote as Helpful" if it helps you

    Friday, March 08, 2013 3:51 PM

All replies

  • This pattern inserts values into different columns using subselect queries for each value. You could try that

    insert table_1 ([column1], [column2]) 
    VALUES 
    ((select [acolumn] from table_2 where ...),(select [acolumn] from table_3 where ...))

    Friday, March 08, 2013 3:08 PM
  • Do you have a common column from the 3 SELECT statements where you can join and retreive -

    USE AdventureWorks;
    ;WITH cte1 AS
    (
    SELECT COUNT(SafetyStockLevel) AS c1,ProductID
    FROM AdventureWorks.Production.Product 
    GROUP BY ProductID
    ),cte2 AS
    (
    SELECT SUM(ListPrice) AS c2,ProductID
    FROM AdventureWorks.Production.Product
    GROUP BY ProductID
    ),cte3 AS
    (SELECT SUM(StandardCost) AS c3,ProductID
    FROM AdventureWorks.Production.Product
    GROUP BY ProductID
    )
    SELECT a.ProductID,a.c1,b.c2,c.c3
    FROM cte1 a
    	INNER JOIN cte2 b
    		ON a.ProductID = b.ProductID
    	INNER JOIN cte3 c
    		ON b.ProductID = c.ProductID


    Narsimha


    Friday, March 08, 2013 3:12 PM
  • yes txtlevel1 is common column they are grouped by

    Chaunnah Railey

    Friday, March 08, 2013 3:14 PM
  • If i understood this correctly, try like this one below,

    insert into CommonTable (Area,countOfActivePlans,plansWithActivity,approved,denied,	sumOfCurPaid)
    select a.lvl1,a.countOfActivePlans,b.plansWithActivity,c.Approved,c.Denied,c.sumOfcurPaid from 
    (
    select count(distinct a.txtprogram) as countOfActivePlans, c.txtLevel1 lvl1
    from tblOfferings a, tblClaims b, tblRetailers c
    where a.ysnActive=1 and c.intRetailer=b.intRetailer and c.intRetailer=a.intRetailer
    group by c.txtLevel1
    )a
    join 
    (
    select count(distinct a.txtProgram) as plansWithActivity,   c.txtLevel1 lvl1
    from tblClaims a, tblOfferings b, tblRetailers c
    where a.intOffering=b.intOffering and c.intRetailer=a.intRetailer and   c.intRetailer=b.intRetailer
    group by c.txtlevel1
    )b
    on a.lvl1 = b.lvl
    join
    (
    select count(a.dblTransaction) as invoicesSubmitted, sum(curPaid) as   sumOfcurPaid,  b.txtLevel1 lvl1,
    sum(case when a.intTransCode=0 then 1 end) as Denied ,
    sum(case when a.intTransCode=1 then 1 end) as Approved,
    sum(Case when a.intTransCode<> and a.intTransCode<>1 then 1 end )AS Reissued
    from tblClaims a, tblRetailers b, tbLTransCode c
    where a.intRetailer=b.intRetailer and a.intTransCode=c.intTransCode
    group by b.txtLevel1
    )c
    on b.lvl1 = c.lvl1
    
    


    Regards, Dineshkumar
    Please "Mark as Answer" if my post answers your question and "Vote as Helpful" if it helps you

    Friday, March 08, 2013 3:51 PM