Can I simplify this using CTE's? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can I simplify this using CTE's?

Author

Topic

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-07-18 : 14:19:17

I've inherited the following and the third party software I'm using doesn't recognise temporary tables although it seems to be fine with CTE's.

I have no experience with CTE's so down't even know if what I'm asking is do-able.

Any advice gratefully accepted


DECLARE @FutureReleases AS TABLE (TRANSACTION_MONTH  CHAR(6)
                                 ,BrCID VARCHAR (10)
                                 ,Fnid VARCHAR (2)
                                 ,shid VARCHAR (3)
                                 ,INTEREST MONEY
                                 ,INTEREST_EARNED MONEY
                                 ,NET_INTEREST_OUTSTANDING MONEY)           
                                    
INSERT INTO @FutureReleases

SELECT CONVERT(CHAR(6),TH.EFFECTIVE_DATE,112) AS TRANSACTION_MONTH
,RIGHT('000' + CAST(A.BROKER_COMPANY_ID AS VARCHAR), 3) + '_' + 
 RIGHT('000' + CAST([INSURANCE_TYPE_ID] AS VARCHAR), 3) AS BrCID
,RIGHT('0000' + CAST(A.FINANCIER_ID AS VARCHAR), 2) AS Fnid
,RIGHT('0000' + CAST(A.SCHEME_ID AS VARCHAR), 3) AS shid
,ISNULL(SUM(CASE WHEN TH.TRANSACTION_TYPE_ID IN (1, 2, 3, 5) THEN ISNULL(TH.INTEREST_VALUE, 0) END), 0) AS INTEREST
,ISNULL(SUM(CASE WHEN TH.TRANSACTION_TYPE_ID IN (4, 9, 10, 20, 21, 24) THEN ISNULL(INTEREST_VALUE, 0)  END), 0) AS INTEREST_EARNED
,ISNULL(SUM(CASE WHEN TH.TRANSACTION_TYPE_ID IN (1, 2, 3, 5) THEN ISNULL(TH.INTEREST_VALUE, 0) END), 0)
-
ISNULL(SUM(CASE WHEN TH.TRANSACTION_TYPE_ID IN (4, 9, 10, 20, 21, 24) THEN ISNULL(INTEREST_VALUE, 0)  END), 0)AS NET_INTEREST_OUTSTANDING
FROM TRANSACTION_HEADER AS TH
JOIN AGREEMENTS AS A ON A.AGREEMENT_ID = TH.AGREEMENT_ID
WHERE TRANSACTION_TYPE_ID IN (1, 2, 3, 4, 5, 9, 10, 20, 21, 24)
AND CHARGE_NUMBER IS NULL
GROUP BY CONVERT(CHAR(6),TH.EFFECTIVE_DATE,112), BROKER_COMPANY_ID, INSURANCE_TYPE_ID, FINANCIER_ID, SCHEME_ID

DECLARE @YearMonth AS TABLE (TRANSACTION_MONTH CHAR(6))    

INSERT INTO @YearMonth
SELECT DISTINCT TRANSACTION_MONTH
FROM @FutureReleases

DECLARE @Cumulative AS TABLE (TRANSACTION_MONTH  CHAR(6)
                                 ,BrCID VARCHAR (10)
                                 ,Fnid VARCHAR (2)
                                 ,shid VARCHAR (3))    
                                    
INSERT INTO @Cumulative
SELECT DISTINCT YM.TRANSACTION_MONTH
,BrCID
,Fnid
,shid
FROM @YearMonth AS YM
CROSS JOIN @FutureReleases AS FR

SELECT
TRANSACTION_MONTH
,BrCID
,Fnid
,shid
,(SELECT ISNULL(SUM(ISNULL(NET_INTEREST_OUTSTANDING, 0)), 0) FROM @FutureReleases AS F 
WHERE F.BrCID = C.BrCID 
AND F.Fnid = C.Fnid 
AND F.shid = C.shid
AND F.TRANSACTION_MONTH <= C.TRANSACTION_MONTH) AS INTEREST_FOR_FUTURE_RELEASE
FROM
@Cumulative AS C
WHERE
(SELECT ISNULL(SUM(ISNULL(NET_INTEREST_OUTSTANDING, 0)), 0) FROM @FutureReleases AS F 
WHERE F.BrCID = C.BrCID 
AND F.Fnid = C.Fnid 
AND F.shid = C.shid
AND F.TRANSACTION_MONTH <= C.TRANSACTION_MONTH) <> 0

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-18 : 14:39:13

>> the third party software I'm using doesn't recognise temporary tables
Try this
put a
set nocount on
at the top of the code
after that add
if 1=0 -- return resultset with output format
select TRANSACTION_MONTH = space(6) ,
BrCID = space(10) ,
....

Could be that the front end is trying to get the resultset format before the call.
This will use the first select it finds - hence the select that is never executed
The
set nocount on
is to stop it getting upset by the closed resultset for the rowcount.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-18 : 14:42:42

alternatively using ctes

;with FutureReleases as
(
SELECT CONVERT(CHAR(6),TH.EFFECTIVE_DATE,112) AS TRANSACTION_MONTH
,RIGHT('000' + CAST(A.BROKER_COMPANY_ID AS VARCHAR), 3) + '_' +
RIGHT('000' + CAST([INSURANCE_TYPE_ID] AS VARCHAR), 3) AS BrCID
,RIGHT('0000' + CAST(A.FINANCIER_ID AS VARCHAR), 2) AS Fnid
,RIGHT('0000' + CAST(A.SCHEME_ID AS VARCHAR), 3) AS shid
,ISNULL(SUM(CASE WHEN TH.TRANSACTION_TYPE_ID IN (1, 2, 3, 5) THEN ISNULL(TH.INTEREST_VALUE, 0) END), 0) AS INTEREST
,ISNULL(SUM(CASE WHEN TH.TRANSACTION_TYPE_ID IN (4, 9, 10, 20, 21, 24) THEN ISNULL(INTEREST_VALUE, 0) END), 0) AS INTEREST_EARNED
,ISNULL(SUM(CASE WHEN TH.TRANSACTION_TYPE_ID IN (1, 2, 3, 5) THEN ISNULL(TH.INTEREST_VALUE, 0) END), 0)
-
ISNULL(SUM(CASE WHEN TH.TRANSACTION_TYPE_ID IN (4, 9, 10, 20, 21, 24) THEN ISNULL(INTEREST_VALUE, 0) END), 0)AS NET_INTEREST_OUTSTANDING
FROM TRANSACTION_HEADER AS TH
JOIN AGREEMENTS AS A ON A.AGREEMENT_ID = TH.AGREEMENT_ID
WHERE TRANSACTION_TYPE_ID IN (1, 2, 3, 4, 5, 9, 10, 20, 21, 24)
AND CHARGE_NUMBER IS NULL
GROUP BY CONVERT(CHAR(6),TH.EFFECTIVE_DATE,112), BROKER_COMPANY_ID, INSURANCE_TYPE_ID, FINANCIER_ID, SCHEME_ID
) ,
YearMonth as
(
SELECT DISTINCT TRANSACTION_MONTH
FROM FutureReleases
) ,
Cumulative as
(
SELECT DISTINCT YM.TRANSACTION_MONTH
,BrCID
,Fnid
,shid
FROM YearMonth AS YM
CROSS JOIN FutureReleases AS FR
)
SELECT
TRANSACTION_MONTH
,BrCID
,Fnid
,shid
,(SELECT ISNULL(SUM(ISNULL(NET_INTEREST_OUTSTANDING, 0)), 0) FROM FutureReleases AS F
WHERE F.BrCID = C.BrCID
AND F.Fnid = C.Fnid
AND F.shid = C.shid
AND F.TRANSACTION_MONTH <= C.TRANSACTION_MONTH) AS INTEREST_FOR_FUTURE_RELEASE
FROM
Cumulative AS C
WHERE
(SELECT ISNULL(SUM(ISNULL(NET_INTEREST_OUTSTANDING, 0)), 0) FROM FutureReleases AS F
WHERE F.BrCID = C.BrCID
AND F.Fnid = C.Fnid
AND F.shid = C.shid
AND F.TRANSACTION_MONTH <= C.TRANSACTION_MONTH) <> 0

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-07-19 : 00:39:49

Thank you Nigel, I will try that out later.

Much appreciated

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-07-19 : 13:28:24

Are CTEs inefficient?

My code as adapted by Nigel was still running in Management Studio when I left the office earlier (2hrs since start) - the original code took approximately 37 minutes to complete.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 13:29:51

quote:
Originally posted by Rasta Pickles

Are CTEs inefficient?

My code as adapted by Nigel was still running in Management Studio when I left the office earlier (2hrs since start) - the original code took approximately 37 minutes to complete.

depends on how you've written them and also on level of recursion. Did you analyse and see whats causing bottleneck in execution plan?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources