June 5, 2009 at 2:42 pm
Begineer wants to know if simple way to transform 'hardcoded'
SQL with Case statement to some sort of loop
The existing SQL is
SELECT MBI, ACT_ID, TITLE, BTG, NO_MNTHS, START_DT,
CASE
WHEN (NO_MNTHS + 1) >= 1
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_1,
CASE
WHEN (NO_MNTHS + 1) >= 2
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_2,
CASE
WHEN (NO_MNTHS + 1) >= 3
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_3,
....
etc. this goes on month repeating up until 18
....
FROM tbEXPEN
WHERE NO_MNTHS is not NULL
June 5, 2009 at 6:37 pm
If you're just trying to compress the code, you may want to consider a user-defined function that takes NO_Mnths, BTG and the integer month number as input and returns the MNTH_x. I don't know that it would be worth it, though, considering how simple each case is. As far as making a loop, you really don't want to do that as you'd end up processing each of the eighteen output fields separately for each input row of tbEXPEN. Performance would be affected something horribly.
June 5, 2009 at 6:50 pm
I'd also want to see the table DDL, sample data (in a readily consummable format for a cut, paste, and execute in SSMS or QA), expected results based on the sample data, and the original code.
June 5, 2009 at 8:03 pm
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table
CREATE TABLE #mytable
(MBI_NUM VARCHAR(8),
ACT_ID VARCHAR(15),
TITLE VARCHAR(15),
BTG MONEY,
NO_MNTHS INT,
START_DT DATETIME )
--===== Insert the test data into the test table
INSERT INTO #mytable (MBI_NUM, ACT_ID, TITLE, BTG, NO_MNTHS, START_DT)
SELECT 'ABC','A1234','TEST DATA',5000,5,'Oct 17 2007 12:00AM' UNION ALL
SELECT 'DEF','B1234','TEST DATA',1000,4,'Oct 17 2007 12:00AM' UNION ALL
SELECT 'GHI','C1234','TEST DATA',1000,3,'Oct 17 2007 12:00AM' UNION ALL
SELECT 'JKL','D1234','TEST DATA',0,2,'Oct 17 2007 12:00AM' UNION ALL
SELECT 'MNO','E1234','TEST DATA',-5000,NULL,'Oct 17 2007 12:00AM'
-- Existing code to create expense profile
SELECT MBI_NUM, ACT_ID, TITLE, BTG, NO_MNTHS, START_DT,
CASE
WHEN (NO_MNTHS + 1) >= 1
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_1,
CASE
WHEN (NO_MNTHS + 1) >= 2
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_2,
CASE
WHEN (NO_MNTHS + 1) >= 3
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_3,
CASE
WHEN (NO_MNTHS + 1) >= 4
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_4,
CASE
WHEN (NO_MNTHS + 1) >= 5
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_5,
CASE
WHEN (NO_MNTHS + 1) >= 6
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_6,
CASE
WHEN (NO_MNTHS + 1) >= 7
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_7,
CASE
WHEN (NO_MNTHS + 1) >= 8
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_8,
CASE
WHEN (NO_MNTHS + 1) >= 9
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_9,
CASE
WHEN (NO_MNTHS + 1) >= 10
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_10,
CASE
WHEN (NO_MNTHS + 1) >= 11
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_11,
CASE
WHEN (NO_MNTHS + 1) >= 12
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_12,
CASE
WHEN (NO_MNTHS + 1) >= 13
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_13,
CASE
WHEN (NO_MNTHS + 1) >= 14
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_14,
CASE
WHEN (NO_MNTHS + 1) >= 15
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_15,
CASE
WHEN (NO_MNTHS + 1) >= 16
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_16,
CASE
WHEN (NO_MNTHS + 1) >= 17
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_17,
CASE
WHEN (NO_MNTHS + 1) >= 18
THEN BTG/(NO_MNTHS + 1 )
ELSE 0
END AS MNTH_18
FROM #MYTABLE
WHERE NO_MNTHS IS NOT NULL
RESULTS OF EXISTING QUERY
ABCA1234TEST DATA5000.000052007-10-17 00:00:00.000833.3333833.3333833.3333833.3333833.3333833.3333.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000
DEFB1234TEST DATA1000.000042007-10-17 00:00:00.000200.0000200.0000200.0000200.0000200.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000
GHIC1234TEST DATA1000.000032007-10-17 00:00:00.000250.0000250.0000250.0000250.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000
JKLD1234TEST DATA.000022007-10-17 00:00:00.000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply