computed column from date range

Question

Post reply

computed column from date range

srathna77

SSCommitted

Points: 1982
More actions
September 9, 2009 at 2:59 pm

#138695

Following query Provides summary of accounts receivable by ship to state for a particular date range. Used by Financial, Operations, etc. departments to track outstanding accounts receivable balances.
I Need to add some columns like
Outstanding Balance column logic:INVOICE_FACT_OES.ACCOUNT_RECEIVABLE_FACT.AMOUNT_TOTAL_REMAINING
Current: AMOUNT_TOTAL_REMAINING – summed up for today’s date through 30 days back
31-60 DaysAMOUNT_TOTAL_REMAINING – summed up for the 31-60 day time period
61-90 DaysAMOUNT_TOTAL_REMAINING – summed up for the 61-90 day time period
91-120 DaysAMOUNT_TOTAL_REMAINING – summed up for the 91-120 day time period
121-150 DaysAMOUNT_TOTAL_REMAINING – summed up for the 121-150 day time period
151-180 DaysAMOUNT_TOTAL_REMAINING – summed up for the 151-180 day time period
181-99999 DaysAMOUNT_TOTAL_REMAINING – summed up for the 181 day and beyond time period
Query I wrote :
SELECT
CS.STATE_ABBREV as State,
CA.ACCOUNT_NUMBER as Account#,
CA.CUSTOMER_ACCOUNT_NAME OrgName,
CS.CUST_SITE_USE_NAME CustName,
CS.EMAIL_ADDRESS ContactEmail,
CS.PHONE_NUMBER ContactPhone,
AR.APPLY_DATE,
AR.STATUS,
AR.AMOUNT_TOTAL_REMAINING "Outstanding Balance",
(select SUM(AMOUNT_TOTAL_REMAINING) from ACCOUNTS_RECEIVABLE_FACT
where
APPLY_DATE between GETDATE()-30 and GETDATE()
group by APPLY_DATE)as "Current",
(select SUM(AMOUNT_TOTAL_REMAINING) from ACCOUNTS_RECEIVABLE_FACT
where
APPLY_DATE between GETDATE()-60 and GETDATE()-30
group by APPLY_DATE)as "31-60 Days",
(select SUM(AMOUNT_TOTAL_REMAINING) from ACCOUNTS_RECEIVABLE_FACT
where
APPLY_DATE between GETDATE()-90 and GETDATE()-60
group by APPLY_DATE)as "61-90 Days",
(select SUM(AMOUNT_TOTAL_REMAINING) from ACCOUNTS_RECEIVABLE_FACT
where
APPLY_DATE between GETDATE()-120 and GETDATE()-90
group by APPLY_DATE)as "121-90 Days",
(select SUM(AMOUNT_TOTAL_REMAINING) from ACCOUNTS_RECEIVABLE_FACT
where
APPLY_DATE between GETDATE()-150 and GETDATE()-120
group by APPLY_DATE)as "151-120 Days",
(select SUM(AMOUNT_TOTAL_REMAINING) from ACCOUNTS_RECEIVABLE_FACT
where
APPLY_DATE between GETDATE()-180 and GETDATE()-150
group by APPLY_DATE)as "180-150 Days"
FROM
CUSTOMER_SITE_USE_DIMENSION CS
INNER JOIN
CUSTOMER_ACCOUNT_DIMENSION CA
ON CS.CUSTOMER_ACCOUNT_ID = CA.CUSTOMER_ACCOUNT_ID
INNER JOIN
ACCOUNTS_RECEIVABLE_FACT AR
ON CS.CUSTOMER_SITE_USE_ID = AR.CUSTOMER_SITE_USE_ID
INNER JOIN
TIME_DIMENSION TD ON AR.APPLY_DATE = TD.STANDARD_DATE
AND AR.INCLUDE_IN_BALANCE_IND in ('Y')
AND AR.TRANSACTION_TYPE IN ('CM','INV')

Viewing 3 posts - 1 through 2 (of 2 total)

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

My guess is that writing your query to use correlated subqueries in that fashion is going to be obnoxiously slow.

You might consider restructuring that query to be something like this:

SELECT

CS.STATE_ABBREV as State,

CA.ACCOUNT_NUMBER as Account#,

CA.CUSTOMER_ACCOUNT_NAME OrgName,

CS.CUST_SITE_USE_NAME CustName,

CS.EMAIL_ADDRESS ContactEmail,

CS.PHONE_NUMBER ContactPhone,

AR.APPLY_DATE,

AR.STATUS,

AR.AMOUNT_TOTAL_REMAINING [Outstanding Balance],

ARP.[Current],

ARP.[31-60 Days],

ARP.[61-90 Days],

ARP.[91-120 Days],

ARP.[121-150 Days],

ARP.[151-180 Days]

FROM CUSTOMER_SITE_USE_DIMENSION CS

INNER JOIN CUSTOMER_ACCOUNT_DIMENSION CA ON CS.CUSTOMER_ACCOUNT_ID = CA.CUSTOMER_ACCOUNT_ID

INNER JOIN TIME_DIMENSION TD ON AR.APPLY_DATE = TD.STANDARD_DATE

AND AR.INCLUDE_IN_BALANCE_IND in ('Y')

AND AR.TRANSACTION_TYPE IN ('CM','INV')

INNER JOIN (

SELECT CS.CUSTOMER_SITE_USE_ID,

SUM(CASE WHEN APPLY_DATE BETWEEN GETDATE()-30 AND GETDATE() THEN AMOUNT_TOTAL_REMAINING ELSE 0 END) [Current],

SUM(CASE WHEN APPLY_DATE BETWEEN GETDATE()-60 AND GETDATE()-30 THEN AMOUNT_TOTAL_REMAINING ELSE 0 END) [31-60 Days],

SUM(CASE WHEN APPLY_DATE BETWEEN GETDATE()-90 AND GETDATE()-60 THEN AMOUNT_TOTAL_REMAINING ELSE 0 END) [61-90 Days],

SUM(CASE WHEN APPLY_DATE BETWEEN GETDATE()-120 AND GETDATE()-90 THEN AMOUNT_TOTAL_REMAINING ELSE 0 END) [91-120 Days],

SUM(CASE WHEN APPLY_DATE BETWEEN GETDATE()-150 AND GETDATE()-120 THEN AMOUNT_TOTAL_REMAINING ELSE 0 END) [121-150 Days],

SUM(CASE WHEN APPLY_DATE BETWEEN GETDATE()-180 AND GETDATE()-150 THEN AMOUNT_TOTAL_REMAINING ELSE 0 END) [151-180 Days]

FROM CUSTOMER_SITE_USE_DIMENSION CS

INNER JOIN ACCOUNTS_RECEIVABLE_FACT ON CS.CUSTOMER_SITE_USE_ID = AR.CUSTOMER_SITE_USE_ID

INNER JOIN TIME_DIMENSION TD ON AR.APPLY_DATE = TD.STANDARD_DATE

AND AR.INCLUDE_IN_BALANCE_IND in ('Y')

AND AR.TRANSACTION_TYPE IN ('CM','INV')

GROUP BY CS.CUSTOMER_SITE_USE_ID

) ARP

ON CS.CUSTOMER_SITE_USE_ID = ARP.CUSTOMER_SITE_USE_ID

This being 2005, you can do much the same thing with a CTE and possibly increase the readability of that some.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 2

Seth is spot on... here's an article that explains the performance advantage of the method he used...

http://www.sqlservercentral.com/articles/T-SQL/63681/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)