November 19, 2013 at 3:07 am
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
November 19, 2013 at 4:53 am
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.
November 19, 2013 at 5:37 am
Thanks a lot. It worked for me
November 20, 2013 at 3:16 am
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
November 20, 2013 at 8:32 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply