-
Create a Function in 2005
I have this script in a procedure. I would like to create a function
to perform this operation. How do I do it, the subselect is making it kinda
difficult ?
TRUNCATE TABLE tbDeviate
INSERT tbDeviate
(
LoanRef
,Aug05Amt
,Sep05Amt
,Oct05Amt
,Nov05Amt
,Dec05Amt
,Jan06Amt
,Feb06Amt
,Mar06Amt
,Apr06Amt
,May06Amt
,Jun06Amt
,Jul06Amt
,Aug06Amt
,Sep06Amt
,Oct06Amt
,Nov06Amt
,Dec06Amt
,Jan07Amt
,Feb07Amt
,Mar07Amt
,Apr07Amt
,May07Amt
,Jun07Amt
,Jul07Amt
)
SELECT a.LoanRef
,SUM(a.Aug05Amt) AS Aug05Amt
,SUM(a.Sep05Amt) AS Sep05Amt
,SUM(a.Oct05Amt) AS Oct05Amt
,SUM(a.Nov05Amt) AS Nov05Amt
,SUM(a.Dec05Amt) AS Dec05Amt
,SUM(a.Jan06Amt) AS Jan06Amt
,SUM(a.Feb06Amt) AS Feb06Amt
,SUM(a.Mar06Amt) AS Mar06Amt
,SUM(a.Apr06Amt) AS Apr06Amt
,SUM(a.May06Amt) AS May06Amt
,SUM(a.Jun06Amt) AS Jun06Amt
,SUM(a.Jul06Amt) AS Jul06Amt
,SUM(a.Aug06Amt) AS Aug06Amt
,SUM(a.Sep06Amt) AS Sep06Amt
,SUM(a.Oct06Amt) AS Oct06Amt
,SUM(a.Nov06Amt) AS Nov06Amt
,SUM(a.Dec06Amt) AS Dec06Amt
,SUM(a.Jan07Amt) AS Jan07Amt
,SUM(a.Feb07Amt) AS Feb07Amt
,SUM(a.Mar07Amt) AS Mar07Amt
,SUM(a.Apr07Amt) AS Apr07Amt
,SUM(a.May07Amt) AS May07Amt
,SUM(a.Jun07Amt) AS Jun07Amt
,SUM(a.Jul07Amt) AS Jul07Amt
FROM
--Select transactions in RFSD not in TRFSE
((SELECT
LoanRefid
,SUM(CASE WHEN ((MONTH(Transactiondate) = 10) AND (YEAR(Transactiondate) = 2005)) THEN CRAmount ELSE 0 END) AS [Oct05Amt]
,SUM(CASE WHEN ((MONTH(Transactiondate) = 11) AND (YEAR(Transactiondate) = 2005)) THEN CRAmount ELSE 0 END) AS [Nov05Amt]
,SUM(CASE WHEN ((MONTH(Transactiondate) = 12) AND (YEAR(Transactiondate) = 2005)) THEN CRAmount ELSE 0 END) AS [Dec05Amt]
,SUM(CASE WHEN ((MONTH(Transactiondate) = 01) AND (YEAR(Transactiondate) = 2006)) THEN CRAmount ELSE 0 END) AS [Jan06Amt]
,SUM(CASE WHEN ((MONTH(Transactiondate) = 02) AND (YEAR(Transactiondate) = 2006)) THEN CRAmount ELSE 0 END) AS [Feb06Amt]
,SUM(CASE WHEN ((MONTH(Transactiondate) = 03) AND (YEAR(Transactiondate) = 2006)) THEN CRAmount ELSE 0 END) AS [Mar06Amt]
,SUM(CASE WHEN ((MONTH(Transactiondate) = 04) AND (YEAR(Transactiondate) = 2006)) THEN CRAmount ELSE 0 END) AS [Apr06Amt]
,SUM(CASE WHEN ((MONTH(Transactiondate) = 05) AND (YEAR(Transactiondate) = 2006)) THEN CRAmount ELSE 0 END) AS [May06Amt]
,SUM(CASE WHEN ((MONTH(Transactiondate) = 06) AND (YEAR(Transactiondate) = 2006)) THEN CRAmount ELSE 0 END) AS [Jun06Amt]
,SUM(CASE WHEN ((MONTH(Transactiondate) = 07) AND (YEAR(Transactiondate) = 2006)) THEN CRAmount ELSE 0 END) AS [Jul06Amt]
,SUM(CASE WHEN ((MONTH(Transactiondate) = 08) AND (YEAR(Transactiondate) = 2006)) THEN CRAmount ELSE 0 END) AS [Aug06Amt]
,SUM(CASE WHEN ((MONTH(Transactiondate) = 09) AND (YEAR(Transactiondate) = 2006)) THEN CRAmount ELSE 0 END) AS [Sep06Amt]
,SUM(CASE WHEN ((MONTH(Transactiondate) = 10) AND (YEAR(Transactiondate) = 2006)) THEN CRAmount ELSE 0 END) AS Oct06Amt
,SUM(CASE WHEN ((MONTH(Transactiondate) = 11) AND (YEAR(Transactiondate) = 2006)) THEN CRAmount ELSE 0 END) AS Nov06Amt
,SUM(CASE WHEN ((MONTH(Transactiondate) = 12) AND (YEAR(Transactiondate) = 2006)) THEN CRAmount ELSE 0 END) AS Dec06Amt
,SUM(CASE WHEN ((MONTH(Transactiondate) = 01) AND (YEAR(Transactiondate) = 2007)) THEN CRAmount ELSE 0 END) AS Jan07Amt
,SUM(CASE WHEN ((MONTH(Transactiondate) = 02) AND (YEAR(Transactiondate) = 2007)) THEN CRAmount ELSE 0 END) AS Feb07Amt
,SUM(CASE WHEN ((MONTH(Transactiondate) = 03) AND (YEAR(Transactiondate) = 2007)) THEN CRAmount ELSE 0 END) AS Mar07Amt
,SUM(CASE WHEN ((MONTH(Transactiondate) = 04) AND (YEAR(Transactiondate) = 2007)) THEN CRAmount ELSE 0 END) AS Apr07Amt
,SUM(CASE WHEN ((MONTH(Transactiondate) = 05) AND (YEAR(Transactiondate) = 2007)) THEN CRAmount ELSE 0 END) AS May07Amt
,SUM(CASE WHEN ((MONTH(Transactiondate) = 06) AND (YEAR(Transactiondate) = 2007)) THEN CRAmount ELSE 0 END) AS Jun07Amt
,SUM(CASE WHEN ((MONTH(Transactiondate) = 07) AND (YEAR(Transactiondate) = 2007)) THEN CRAmount ELSE 0 END) AS Jun07Amt
FROM
tbTransactionfact
WHERE Bcashind='Y'
GROUP BY Loanrefid
)
UNION ALL
--Select the Exactus transactions in the deviate format
(SELECT * FROM tbDeviate
WHERE Loanref IN (SELECT Loanrefid FROM vwtbLoan))) a
GROUP BY a.Loanref
-
Why convert to UDF? Need call it as UDF in sql statement?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|