February 13, 2007 at 4:32 pm
I've written a SQL query which sums ledger transactions by month. The query is as follows:
CREATE PROCEDURE SubTrialBalanceByFinancialYearSp
@StartDate datetime,
@EndDatedatetime,
@StartAcctAcctType,
@EndAcctAcctType
AS
SELECT ledger.acct,
ledger.acct_unit1,
chart.description,
CASE
WHEN MONTH(ledger.trans_date) < 7 THEN MONTH(ledger.trans_date) + 6
WHEN MONTH(ledger.trans_date) > 6 THEN MONTH(ledger.trans_date) - 6
ELSE 13
END AS FiscalMonth,
SUM(ledger.dom_amount) AS Amount
FROM ledger INNER JOIN
chart ON ledger.acct = chart.acct
WHERE
ledger.trans_date >= @StartDate AND
ledger.trans_date <= @EndDate AND
ledger.acct >= @StartAcct AND
ledger.acct <= @EndAcct
GROUP BY ledger.acct,
ledger.acct_unit1,
chart.description,
CASE
WHEN MONTH(ledger.trans_date) < 7 THEN MONTH(ledger.trans_date) + 6
WHEN MONTH(ledger.trans_date) > 6 THEN MONTH(ledger.trans_date) - 6
ELSE 13
END
ORDER BY ledger.acct,
CASE
WHEN MONTH(ledger.trans_date) < 7 THEN MONTH(ledger.trans_date) + 6
WHEN MONTH(ledger.trans_date) > 6 THEN MONTH(ledger.trans_date) - 6
ELSE 13
END
As you can see, I have repeated the same CASE statement three times. I would like to define this only once. How can I do that?
Thanks,
Sam
February 13, 2007 at 6:01 pm
As all select statements return a relation and anywhere that a table, which is a relation that has physical existance, can be referenced in a SQL statement, can be substituted by that select, which must be named
This is easier than is sounds and an example is easier to understand..
SELECT acct
, acct_unit1
, description
, FiscalMonth
, SUM(dom_amount) AS Amount
FROM ( -- start of nested relation
SELECT ledger.acct
, ledger.acct_unit1
, chart.description
, CASE WHEN MONTH(ledger.trans_date) < 7 THEN MONTH(ledger.trans_date) + 6
WHEN MONTH(ledger.trans_date) > 6 THEN MONTH(ledger.trans_date) - 6
ELSE 13
END AS FiscalMonth
, ledger.dom_amount
FROM ledger
JOIN chart
ON ledger.acct = chart.acct
WHERE ledger.trans_date between @StartDate AND @EndDate
AND ledger.acct between @StartAcct and @EndAcct
) -- end of nested relation
as NeededLedger -- named of the nested relation
GROUP BY acct
, acct_unit1
, description
, FiscalMonth
ORDER BY acct
, FiscalMonth
SQL = Scarcely Qualifies as a Language
February 13, 2007 at 6:24 pm
Thanks very much, that's perfect.
February 13, 2007 at 6:26 pm
Out of curiousity, is there a performance impact in doing this?
February 14, 2007 at 9:10 am
Just for grins... the following two items are functionally equivelent...
CASE
WHEN MONTH(ledger.trans_date) < 7 THEN MONTH(ledger.trans_date) + 6
WHEN MONTH(ledger.trans_date) > 6 THEN MONTH(ledger.trans_date) - 6
ELSE 13
END AS FiscalMonth,
(MONTH(ledger.trans_date)+5)%12+1 AS FiscalMonth,
Also, it is not necessary to repeat the formula in the Order By... you can use the assigned column alias...
ORDER BY FiscalMonth
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2007 at 9:22 am
As a side bar... if your trans_date has time in it... don't use BETWEEN because it will ignore most of the end date. For that same reason, you shouldn't use the >= <= method, either. You should use the following to include all times of the end date...
ledger.trans_date >= @StartDate AND
ledger.trans_date < @EndDate+1
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply