none
SQL Query help

    Question

  • 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

    Smash126

    Tuesday, November 19, 2013 10:16 AM

All replies

  • Could you please provide DDL/DML along with your desired output for some dummy data.

    This would help us to help you better.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, November 19, 2013 10:35 AM
  • It's very tough to provide these details for me lots of tables are connected. The cash received should match when combine two queries in to one

    Smash126

    Tuesday, November 19, 2013 10:49 AM
  • SELECT C.COMPANY_SK,
    CUST.CUSTOMER_DESC,
    CUST.CUSTOMER_SHORT_DESC,
    SUM((CASE WHEN TRANSTYPE IN ('SLINV','SLCRD') THEN SL.BASEVALUE ELSE 0 END) * CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END) AS  OUTSTANDINGAMOUNTFORBILLING,
    SUM((CASE WHEN TRANSTYPE ='SLCSH' THEN SL.BASEVALUE ELSE 0 END) * CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END)* CASE WHEN TRANSTYPE IN ('SLCSH') THEN -1 ELSE 1 END) 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 IN ('SLINV','SLCRD','SLCSH') AND
    SL.PERIODID>=@PeriodStart AND SL.PERIODID<=@PeriodEnd AND C.COMPANY_SK IN (@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)*-1) DESC


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    • Edited by Visakh16MVP Tuesday, November 19, 2013 2:04 PM
    Tuesday, November 19, 2013 2:03 PM