SQL Query help

  • Smash125

    Hall of Fame

    Points: 3529

    1. First Query Gives me Outstanding Amount for Billing. Here Transaction type is ('SLINV','SLCRD')

    SELECT C.COMPANY_SK,

    CUST.CUSTOMER_DESC,

    CUST.CUSTOMER_SHORT_DESC,

    SUM((SL.BASEVALUE)*CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END) OUTSTANDINGAMOUNTFORBILLING

    FROM

    VW_FACT_SLTRANS SL

    INNER JOIN

    VW_DIM_COMPANY C

    ON SL.COMPANY_SK=C.COMPANY_SK

    INNER JOIN DIVISIONMAPPING D

    ON C.COMPANYID=D.COMPANYID

    INNER JOIN

    VW_DIM_CUSTOMER CUST

    ON SL.CUSTOMER_SK=CUST.CUSTOMER_SK AND SL.COMPANY_SK=CUST.COMPANY_SK

    LEFT OUTER JOIN VW_FACT_CURRENCY CURR

    ON SL.COMPANY_SK=CURR.COMPANY_SK

    AND CURR.CURRENCY IN ('GBP','STER')

    WHERE D.ACTIVE IN ('YES','NO') AND SL.POSTED=1 AND TRANSTYPE IN ('SLINV','SLCRD') AND

    SL.PERIODID<='201302' and SL.PERIODID>='201203' AND C.COMPANY_SK IN (SELECT COMPANY_SK FROM DIM_COMPANY)

    GROUP BY

    C.COMPANY_SK,

    CUST.CUSTOMER_DESC,

    CUST.CUSTOMER_SHORT_DESC

    ORDER BY SUM((SL.BASEVALUE)*CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END) DESC

    2. Second Query Gives me OutStandingAmount for Cash Received

    here trans type is 'SLCSH'

    SELECT

    CUST.CUSTOMER_DESC,

    (SUM((SL.BASEVALUE)*CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END)*-1) OUTSTANDINGAMOUNTCASHRECEIVED

    FROM

    VW_FACT_SLTRANS SL

    INNER JOIN

    VW_DIM_COMPANY C

    ON SL.COMPANY_SK=C.COMPANY_SK

    INNER JOIN DIVISIONMAPPING D

    ON C.COMPANYID=D.COMPANYID

    INNER JOIN

    VW_DIM_CUSTOMER CUST

    ON SL.CUSTOMER_SK=CUST.CUSTOMER_SK AND SL.COMPANY_SK=CUST.COMPANY_SK

    LEFT OUTER JOIN VW_FACT_CURRENCY CURR

    ON SL.COMPANY_SK=CURR.COMPANY_SK

    AND CURR.CURRENCY IN ('GBP','STER')

    WHERE D.ACTIVE IN ('YES','NO') AND SL.POSTED=1 AND TRANSTYPE='SLCSH' AND

    SL.PERIODID>=@PeriodStart AND SL.PERIODID<=@PeriodEnd AND C.COMPANY_SK IN (@Company)

    GROUP BY

    CUST.CUSTOMER_DESC,

    CUST.CUSTOMER_SHORT_DESC

    ORDER BY (SUM((SL.BASEVALUE)*CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END)*-1) DESC

    Both the queries are same. only difference is transtype

    what i want is result should be some like

    Company_SK, Company_Short_Desc, Company_Desc,OUTSTANDINGAMOUNTFORBILLING,OUTSTANDINGAMOUNTCASHRECEIVED

    with altering the data could you please help me on this

  • HanShi

    SSC-Dedicated

    Points: 33358

    Put both SELECT queries as sub-queries together in a wrapping query, joining them on the company and customer. In the wrapping query you can select the totals from each sub-query.

    SELECT first_select.Company_SK

    , first_select.Company_Short_Desc

    , first_select.Company_Desc

    , first_select.OUTSTANDINGAMOUNTFORBILLING

    , second_select.OUTSTANDINGAMOUNTCASHRECEIVED

    FROM (

    SELECT C.COMPANY_SK

    , CUST.CUSTOMER_DESC

    , CUST.CUSTOMER_SHORT_DESC

    , SUM((SL.BASEVALUE) * CASE

    WHEN CURR.RATE IS NULL

    THEN 1

    ELSE CURR.RATE

    END) OUTSTANDINGAMOUNTFORBILLING

    FROM VW_FACT_SLTRANS SL

    INNER JOIN VW_DIM_COMPANY C ON SL.COMPANY_SK = C.COMPANY_SK

    INNER JOIN DIVISIONMAPPING D ON C.COMPANYID = D.COMPANYID

    INNER JOIN VW_DIM_CUSTOMER CUST ON SL.CUSTOMER_SK = CUST.CUSTOMER_SK

    AND SL.COMPANY_SK = CUST.COMPANY_SK

    LEFT JOIN VW_FACT_CURRENCY CURR ON SL.COMPANY_SK = CURR.COMPANY_SK

    AND CURR.CURRENCY IN (

    'GBP'

    , 'STER'

    )

    WHERE D.ACTIVE IN (

    'YES'

    , 'NO'

    )

    AND SL.POSTED = 1

    AND TRANSTYPE IN (

    'SLINV'

    , 'SLCRD'

    )

    AND SL.PERIODID <= '201302'

    AND SL.PERIODID >= '201203'

    AND C.COMPANY_SK IN (

    SELECT COMPANY_SK

    FROM DIM_COMPANY

    )

    GROUP BY C.COMPANY_SK

    , CUST.CUSTOMER_DESC

    , CUST.CUSTOMER_SHORT_DESC

    ) first_select

    INNER JOIN (

    SELECT C.COMPANY_SK

    , CUST.CUSTOMER_SHORT_DESC

    , (

    SUM((SL.BASEVALUE) * CASE

    WHEN CURR.RATE IS NULL

    THEN 1

    ELSE CURR.RATE

    END) * - 1

    ) OUTSTANDINGAMOUNTCASHRECEIVED

    FROM VW_FACT_SLTRANS SL

    INNER JOIN VW_DIM_COMPANY C ON SL.COMPANY_SK = C.COMPANY_SK

    INNER JOIN DIVISIONMAPPING D ON C.COMPANYID = D.COMPANYID

    INNER JOIN VW_DIM_CUSTOMER CUST ON SL.CUSTOMER_SK = CUST.CUSTOMER_SK

    AND SL.COMPANY_SK = CUST.COMPANY_SK

    LEFT JOIN VW_FACT_CURRENCY CURR ON SL.COMPANY_SK = CURR.COMPANY_SK

    AND CURR.CURRENCY IN (

    'GBP'

    , 'STER'

    )

    WHERE D.ACTIVE IN (

    'YES'

    , 'NO'

    )

    AND SL.POSTED = 1

    AND TRANSTYPE = 'SLCSH'

    AND SL.PERIODID >= @PeriodStart

    AND SL.PERIODID <= @PeriodEnd

    AND C.COMPANY_SK IN (@Company)

    GROUP BY CUST.CUSTOMER_DESC

    , CUST.CUSTOMER_SHORT_DESC

    ) second_select

    ON first_select.Company_SK = second_select.Company_SK

    AND first_select.Company_Short_Desc = second_select.Company_Short_Desc

    ORDER BY first_select.OUTSTANDINGAMOUNTFORBILLING DESC

    , second_select.OUTSTANDINGAMOUNTCASHRECEIVE DESC

    You can probably optimize (in terms of performance) the above query by moving one or more table JOINs and part of the WHERE clause from the inner subqueries to the outer query, but the code above is the fastest way (in terms of coding, not performance) to rebuild your query.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Smash125

    Hall of Fame

    Points: 3529

    Thanks a lot. It worked for me

  • Smash125

    Hall of Fame

    Points: 3529

    I have another requirement

    i need to embed another data set to the existing what i you have proposed

    SELECT COMPANY_SK,CUSTOMER_DESC,CUSTOMER_SHORT_DESC,OUTSTANDINGAMOUNTFORBILLING,OUTSTANDINGAMOUNTCASHRECEIVED,

    OUTSTANDINGAMOUNTDEBT FROM (

    SELECT first_select.Company_SK

    , first_select.CUSTOMER_SHORT_DESC

    , first_select.CUSTOMER_DESC

    , first_select.OUTSTANDINGAMOUNTFORBILLING

    , second_select.OUTSTANDINGAMOUNTCASHRECEIVED

    ,third_select.OUTSTANDINGAMOUNTDEBT

    FROM (

    SELECT C.COMPANY_SK

    , CUST.CUSTOMER_DESC

    , CUST.CUSTOMER_SHORT_DESC

    , SUM((SL.BASEVALUE) * CASE

    WHEN CURR.RATE IS NULL

    THEN 1

    ELSE CURR.RATE

    END) OUTSTANDINGAMOUNTFORBILLING

    FROM VW_FACT_SLTRANS SL

    INNER JOIN VW_DIM_COMPANY C ON SL.COMPANY_SK = C.COMPANY_SK

    INNER JOIN DIVISIONMAPPING D ON C.COMPANYID = D.COMPANYID

    INNER JOIN VW_DIM_CUSTOMER CUST ON SL.CUSTOMER_SK = CUST.CUSTOMER_SK

    AND SL.COMPANY_SK = CUST.COMPANY_SK

    LEFT JOIN VW_FACT_CURRENCY CURR ON SL.COMPANY_SK = CURR.COMPANY_SK

    AND CURR.CURRENCY IN (

    'GBP'

    , 'STER'

    )

    WHERE D.ACTIVE IN (

    'YES'

    , 'NO'

    )

    AND SL.POSTED = 1

    AND TRANSTYPE IN (

    'SLINV'

    , 'SLCRD'

    )

    AND SL.PERIODID <= '201302'

    AND SL.PERIODID >= '201203'

    AND C.COMPANY_SK IN (

    SELECT COMPANY_SK

    FROM DIM_COMPANY

    )

    GROUP BY C.COMPANY_SK

    , CUST.CUSTOMER_DESC

    , CUST.CUSTOMER_SHORT_DESC

    ) first_select

    INNER JOIN (

    SELECT C.COMPANY_SK

    , CUST.CUSTOMER_SHORT_DESC

    , (

    SUM((SL.BASEVALUE) * CASE

    WHEN CURR.RATE IS NULL

    THEN 1

    ELSE CURR.RATE

    END) * - 1

    ) OUTSTANDINGAMOUNTCASHRECEIVED

    FROM VW_FACT_SLTRANS SL

    INNER JOIN VW_DIM_COMPANY C ON SL.COMPANY_SK = C.COMPANY_SK

    INNER JOIN DIVISIONMAPPING D ON C.COMPANYID = D.COMPANYID

    INNER JOIN VW_DIM_CUSTOMER CUST ON SL.CUSTOMER_SK = CUST.CUSTOMER_SK

    AND SL.COMPANY_SK = CUST.COMPANY_SK

    LEFT JOIN VW_FACT_CURRENCY CURR ON SL.COMPANY_SK = CURR.COMPANY_SK

    AND CURR.CURRENCY IN (

    'GBP'

    , 'STER'

    )

    WHERE D.ACTIVE IN (

    'YES'

    , 'NO'

    )

    AND SL.POSTED = 1

    AND TRANSTYPE = 'SLCSH'

    AND SL.PERIODID >= '201203'

    AND SL.PERIODID <= '201302'

    AND C.COMPANY_SK IN (SELECT COMPANY_SK FROM DIM_COMPANY)

    GROUP BY C.COMPANY_SK,CUST.CUSTOMER_DESC

    , CUST.CUSTOMER_SHORT_DESC

    ) second_select

    ON first_select.Company_SK = second_select.Company_SK

    AND first_select.CUSTOMER_SHORT_DESC = second_select.CUSTOMER_SHORT_DESC

    INNER JOIN

    (

    SELECT c.COMPANY_SK,

    SUP.SUPPLIER_DESC,

    SUP.SUPPLIER_SHORT_DESC,

    SUM((PL.BASEVALUE-PL.MTCHBASE)*CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END ) OUTSTANDINGAMOUNTDEBT

    FROM

    VW_FACT_PLTRANS PL

    INNER JOIN

    VW_DIM_COMPANY C

    ON PL.COMPANY_SK=C.COMPANY_SK

    INNER JOIN DIVISIONMAPPING D

    ON C.COMPANYID=D.COMPANYID

    INNER JOIN

    VW_DIM_SUPPLIER SUP

    ON PL.SUPPLIER_SK=SUP.SUPPLIER_SK AND PL.COMPANY_SK=SUP.COMPANY_SK

    LEFT OUTER JOIN VW_FACT_CURRENCY CURR

    ON PL.COMPANY_SK=CURR.COMPANY_SK AND

    CURR.CURRENCY IN ('GBP','STER')

    WHERE D.ACTIVE IN ('YES','NO') AND

    PL.POSTED=1 AND

    PL.STATUS NOT IN ('M','C')

    AND PL.PERIODID<='201302'

    AND C.COMPANY_SK IN (SELECT COMPANY_SK FROM DIM_COMPANY)

    GROUP BY c.COMPANY_SK,

    SUP.SUPPLIER_DESC,

    SUP.SUPPLIER_SHORT_DESC

    ) third_select

    ON third_select.COMPANY_SK = second_select.Company_SK ) AS D

    WHERE OUTSTANDINGAMOUNTDEBT<>0

    ORDER BY COMPANY_SK ASC,CUSTOMER_DESC ASC,

    OUTSTANDINGAMOUNTFORBILLING DESC

    ,OUTSTANDINGAMOUNTCASHRECEIVED DESC

    ,OUTSTANDINGAMOUNTDEBT DESC

    Have written query. Created another sub query saying third_select. That sub query as below

    The new data set is it gives debt info

    SELECT

    SUP.SUPPLIER_DESC,

    SUP.SUPPLIER_SHORT_DESC,

    SUM((PL.BASEVALUE-PL.MTCHBASE)*CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END ) OUTSTANDINGAMOUNT

    FROM

    VW_FACT_PLTRANS PL

    INNER JOIN

    VW_DIM_COMPANY C

    ON PL.COMPANY_SK=C.COMPANY_SK

    INNER JOIN DIVISIONMAPPING D

    ON C.COMPANYID=D.COMPANYID

    INNER JOIN

    VW_DIM_SUPPLIER SUP

    ON PL.SUPPLIER_SK=SUP.SUPPLIER_SK AND PL.COMPANY_SK=SUP.COMPANY_SK

    LEFT OUTER JOIN VW_FACT_CURRENCY CURR

    ON PL.COMPANY_SK=CURR.COMPANY_SK AND

    CURR.CURRENCY IN ('GBP','STER')

    WHERE D.ACTIVE IN ('YES','NO') AND

    PL.POSTED=1 AND

    PL.STATUS NOT IN ('M','C')

    AND PL.PERIODID<='201302'

    AND C.COMPANY_SK IN (SELECT COMPANY_SK FROM DIM_COMPANY)

    GROUP BY

    SUP.SUPPLIER_DESC,

    SUP.SUPPLIER_SHORT_DESC

    ORDER BY SUM((PL.BASEVALUE-PL.MTCHBASE)*CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END) DESC

    Please correct me if any thing wrond

  • HanShi

    SSC-Dedicated

    Points: 33358

    The code gets a bit uggly to read but on first glance you only need to remove the " ) AS D " after the JOIN statement of the third select to get the query running.

    ON third_select.COMPANY_SK = second_select.Company_SK ) AS D

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply