January 25, 2012 at 4:18 pm
I am having trouble getting this update correct:
DECLARE
@TopNumINT,
@TopNum2INT,
@AcctNumDECIMAL(10,0),
@TranDateDECIMAL(8,0),
@TotalAmtDECIMAL(11,2)
UPDATE T
SET @AcctNum = T.AcctNum,
@TranDate = T.TranDate,
@TopNum = ((DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)
+ ABS(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)))/2),
@TopNum2 = @TopNum - (SELECT TOP (@TopNum) ISNULL(COUNT(*),0)
FROM MonthEnd M
WHERE M.AcctNum = @AcctNum
AND M.Year*100 + M.Month < RIGHT(LEFT(@TranDate,4),2)*100 + LEFT(RIGHT(@TranDate,4),2)),
@TotalAmt = TotalAmt = (SELECT ISNULL(SUM(AmtUse),0)
FROM (SELECT TOP (@TopNum)
AmtUse
FROM (SELECT M.AmtUse, M.Year, M.Month
FROM MonthEnd M
WHERE M.AcctNum = @AcctNum
AND M.Year*100 + M.Month < RIGHT(LEFT(@TranDate,4),2)*100 + LEFT(RIGHT(@TranDate,4),2)
UNION ALL
SELECT M.AmtUse, M.Year, M.Month
FROM MonthEnd M
CROSS JOIN (SELECT TOP (@TopNum2)
*
FROM sysobjects) D
WHERE M.AcctNum = @AcctNum
AND M.Year*100 + M.Month = (SELECT MIN(E.Year*100 + E.Month) FROM MonthEnd E WHERE E.AcctNum = @AcctNum)
) P
ORDER BY P.Year DESC, P.Month DESC) A)
FROM TranTable T
My first 4 variables do not have a value when the subqueries run. Now, I think I understand why that happens: since I am not correlating the subqueries, they run first.
But I have tried writing this correlated as well and get a message stating the T. whatever is unknown.
I assume this is because the subqueries are nested inside subqueries.
If sample data is needed, I will put some together.
My hope is someone can take a one look, see the error of my ways and set me straight.
Thanks
January 25, 2012 at 4:31 pm
Some DDL and sample data would help. 🙂
EDIT: And what are your expected results?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 25, 2012 at 7:09 pm
DougG (1/25/2012)
I am having trouble getting this update correct:
Whoa! This is a stop-and-start-again situation I am afraid. The code looks like it is based on some attempt at a 'quirky update' running total, but it breaks all the rules and looks to have completely gotten away from you (meant in the nicest way). Sample data and a *clear* description of what you need to achieve here please. Between us we should be able to come up with a good solution.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 25, 2012 at 7:19 pm
SQL Kiwi (1/25/2012)
DougG (1/25/2012)
I am having trouble getting this update correct:Whoa! This is a stop-and-start-again situation I am afraid. The code looks like it is based on some attempt at a 'quirky update' running total, but it breaks all the rules and looks to have completely gotten away from you (meant in the nicest way). Sample data and a *clear* description of what you need to achieve here please. Between us we should be able to come up with a good solution.
Yeah, that's what I meant Paul. 🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 25, 2012 at 7:25 pm
Jason Selburg (1/25/2012)
Yeah, that's what I meant Paul. 🙂
:laugh: By the way, it's not often code produces a 'whoa!' reaction from me.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 25, 2012 at 10:07 pm
SQL Kiwi (1/25/2012)
Jason Selburg (1/25/2012)
Yeah, that's what I meant Paul. 🙂:laugh: By the way, it's not often code produces a 'whoa!' reaction from me.
It wasn't a 'whoa' that came out of me when I saw the code. 😛 Like you said, it breaks nearly every QU rule.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 26, 2012 at 6:29 am
I was taking the approach that if the OP had to type out the requirements, that it might help them see the error in their ways. :-/
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 26, 2012 at 6:38 am
Okay, I'm not sure, but I think you guys didn't like that query :hehe:
I was a little rushed last night and just hoped one of you would see what I was trying to do.
I am working on a new and (hopefully) improved post.
January 26, 2012 at 9:59 am
Alright. As Jason suspected, I just needed to spend some time putting together all the required data for you guys to work your magic
and the solution would appear.
I have a working update query now - at least with test data. I will run against my project data later today.
Here is the code, if you care to take a look. Please feel free to rip it apart:
IF OBJECT_ID('tempdb..#TmpMonthEnd','U') IS NOT NULL DROP TABLE #TmpMonthEnd;
GO
CREATE TABLE #TmpMonthEnd
(
YearINT,
MonthINT,
AcctNumINT,
AmtUseDECIMAL(11,2)
)
INSERT INTO #TmpMonthEnd (Year, Month, AcctNum, AmtUse)
SELECT 10,11,1,2186.96 UNION ALL
SELECT 10,12,1,2186.96 UNION ALL
SELECT 11,1,1,2186.96 UNION ALL
SELECT 11,2,1,2186.96 UNION ALL
SELECT 11,3,1,2186.96 UNION ALL
SELECT 11,4,1,2186.96 UNION ALL
SELECT 11,5,1,2186.96 UNION ALL
SELECT 11,6,1,2186.96 UNION ALL
SELECT 11,7,1,2186.96 UNION ALL
SELECT 11,8,1,2186.96 UNION ALL
SELECT 11,9,1,2186.96 UNION ALL
SELECT 11,10,1,2186.96 UNION ALL
SELECT 11,11,1,2186.96 UNION ALL
SELECT 11,12,1,2186.96 UNION ALL
SELECT 10,11,2,1821.75 UNION ALL
SELECT 10,12,2,1821.75 UNION ALL
SELECT 11,1,2,1774.95 UNION ALL
SELECT 11,2,2,1767.02 UNION ALL
SELECT 11,3,2,1767.02 UNION ALL
SELECT 11,4,2,1767.02 UNION ALL
SELECT 11,5,2,1763.85 UNION ALL
SELECT 11,6,2,1767.02 UNION ALL
SELECT 11,7,2,1767.02 UNION ALL
SELECT 11,8,2,1767.02 UNION ALL
SELECT 11,9,2,1767.02 UNION ALL
SELECT 11,10,2,1767.02 UNION ALL
SELECT 11,11,2,1767.02 UNION ALL
SELECT 11,12,2,1767.02 UNION ALL
SELECT 7,1,3,137.56 UNION ALL
SELECT 7,2,3,137.56 UNION ALL
SELECT 7,3,3,137.56 UNION ALL
SELECT 7,4,3,137.56 UNION ALL
SELECT 7,5,3,137.56 UNION ALL
SELECT 7,6,3,137.56 UNION ALL
SELECT 7,7,3,137.56 UNION ALL
SELECT 7,8,3,137.56 UNION ALL
SELECT 7,9,3,137.56 UNION ALL
SELECT 7,10,3,137.56 UNION ALL
SELECT 7,11,3,137.56 UNION ALL
SELECT 7,12,3,137.56 UNION ALL
SELECT 8,1,3,137.56 UNION ALL
SELECT 8,2,3,137.56 UNION ALL
SELECT 8,3,3,137.56 UNION ALL
SELECT 8,4,3,137.56 UNION ALL
SELECT 8,5,3,137.56 UNION ALL
SELECT 8,6,3,137.56 UNION ALL
SELECT 8,7,3,137.56 UNION ALL
SELECT 8,8,3,137.56 UNION ALL
SELECT 8,9,3,137.56 UNION ALL
SELECT 8,10,3,137.56 UNION ALL
SELECT 8,11,3,137.56 UNION ALL
SELECT 8,12,3,137.56 UNION ALL
SELECT 9,1,3,137.56 UNION ALL
SELECT 9,2,3,137.56 UNION ALL
SELECT 9,3,3,137.56 UNION ALL
SELECT 9,4,3,137.56 UNION ALL
SELECT 9,5,3,137.56 UNION ALL
SELECT 9,6,3,137.56 UNION ALL
SELECT 9,7,3,137.56 UNION ALL
SELECT 9,8,3,137.56 UNION ALL
SELECT 9,9,3,137.56 UNION ALL
SELECT 9,10,3,137.56 UNION ALL
SELECT 9,11,3,137.56 UNION ALL
SELECT 9,12,3,137.56 UNION ALL
SELECT 10,1,3,137.56 UNION ALL
SELECT 10,2,3,137.56 UNION ALL
SELECT 10,3,3,137.56 UNION ALL
SELECT 10,4,3,137.56 UNION ALL
SELECT 10,5,3,137.56 UNION ALL
SELECT 10,6,3,137.56 UNION ALL
SELECT 10,7,3,137.56 UNION ALL
SELECT 10,8,3,137.56 UNION ALL
SELECT 10,9,3,137.56 UNION ALL
SELECT 10,10,3,137.56 UNION ALL
SELECT 10,11,3,86.90 UNION ALL
SELECT 10,12,3,86.90 UNION ALL
SELECT 11,1,3,86.90 UNION ALL
SELECT 11,2,3,86.90 UNION ALL
SELECT 11,3,3,86.90 UNION ALL
SELECT 11,4,3,86.90 UNION ALL
SELECT 11,5,3,86.90 UNION ALL
SELECT 11,6,3,86.90 UNION ALL
SELECT 11,7,3,86.90 UNION ALL
SELECT 11,8,3,86.90 UNION ALL
SELECT 11,9,3,86.90 UNION ALL
SELECT 11,10,3,86.90 UNION ALL
SELECT 11,11,3,86.90 UNION ALL
SELECT 11,12,3,86.90
IF OBJECT_ID('tempdb..#TmpSettings','U') IS NOT NULL DROP TABLE #TmpSettings;
GO
CREATE TABLE #TmpSettings
(
AcctNumINT,
CalcMethodVARCHAR(10),
CIRCHAR(1)
)
INSERT INTO #TmpSettings
SELECT 1, 'Part', 'N' UNION ALL
SELECT 2, 'Part', 'N' UNION ALL
SELECT 3, 'Part', 'N'
IF OBJECT_ID('tempdb..#TmpTrans','U') IS NOT NULL DROP TABLE #TmpTrans;
GO
CREATE TABLE #TmpTrans
(
AcctNumINT,
TranSeqINT,
TranDateDECIMAL(8,0), --YYYYMMDD
NextDateDECIMAL(8,0), --YYYYMMDD
CurrentDateDATE,
PartialAmtDECIMAL(11,2),
CIRPartialAmtDECIMAL(11,2)
)
INSERT INTO #TmpTrans (AcctNum, TranSeq, TranDate, NextDate, CurrentDate, PartialAmt, CIRPartialAmt)
SELECT 1, 38, 20101116, 20100601, '2010-11-01', 275.34, 0.00 UNION ALL
SELECT 1, 41, 20101216, 20100601, '2010-12-01', 275.34, 0.00 UNION ALL
SELECT 1, 42, 20110117, 20100601, '2011-01-01', 275.34, 0.00 UNION ALL
SELECT 2, 18, 20110117, 20101201, '2011-01-01', 873.44, 0.00 UNION ALL
SELECT 2, 27, 20110216, 20110201, '2011-02-01', 50.17, 0.00 UNION ALL
SELECT 2, 38, 20110316, 20110301, '2011-02-01', 1866.53, 0.00 UNION ALL
SELECT 3, 389, 20080618, 20080608, '2008-06-08', 126.67, 0.00 UNION ALL
SELECT 3, 395, 20080718, 20080608, '2008-07-08', 150.54, 0.00 UNION ALL
SELECT 3, 401, 20080818, 20080808, '2008-08-08', 114.41, 0.00
SELECT *
FROM #TmpTrans
ORDER BY AcctNum, TranSeq
-- Test query to show the what is needed
-- MonthsDiff = months between NextDate and CurrentDate
-- MonthsReturned = Numbers of MonthEnd rows returned. This number should equal MonthsDiff.
-- TotalAmtUse = Sum of AmtUse from MonthEnd
-- NewPartAmt = PartialAmt - TotalAmtUse
SELECT AcctNum, TranSeq, PartialAmt,
(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)
+ ABS(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)))/2 MonthsDiff,
(SELECT ISNULL(COUNT(AmtUse),0)
FROM (SELECT TOP ((DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)
+ ABS(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)))/2)
AmtUse
FROM #TmpMonthEnd M
WHERE M.AcctNum = T.AcctNum
AND M.Year*100 + M.Month < RIGHT(LEFT(T.TranDate,4),2)*100 + LEFT(RIGHT(T.TranDate,4),2)
ORDER BY M.Year, M.Month DESC) A) MonthsReturned,
(SELECT ISNULL(SUM(AmtUse),0)
FROM (SELECT TOP ((DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)
+ ABS(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)))/2)
AmtUse
FROM #TmpMonthEnd M
WHERE M.AcctNum = T.AcctNum
AND M.Year*100 + M.Month < RIGHT(LEFT(T.TranDate,4),2)*100 + LEFT(RIGHT(T.TranDate,4),2)
ORDER BY M.Year, M.Month DESC) A) TotalAmtUse,
ISNULL(PartialAmt,0) - (SELECT ISNULL(SUM(AmtUse),0)
FROM (SELECT TOP ((DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)
+ ABS(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)))/2)
AmtUse
FROM #TmpMonthEnd M
WHERE M.AcctNum = T.AcctNum
AND M.Year*100 + M.Month < RIGHT(LEFT(T.TranDate,4),2)*100 + LEFT(RIGHT(T.TranDate,4),2)
ORDER BY M.Year, M.Month DESC) A) NewPartAmt
FROM #TmpTrans T
--Updated test query to correct the months returned. (and subsequently the TotalAmtUse and NewPartAmt)
-- MonthsDiff = months between NextDate and CurrentDate
-- MonthsReturned = Numbers of MonthEnd rows returned. This number now equals MonthsDiff.
-- TotalAmtUse = Sum of AmtUse from MonthEnd
-- NewPartAmt = PartialAmt - TotalAmtUse
SELECT AcctNum, TranSeq, PartialAmt,
(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)
+ ABS(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)))/2 MonthsDiff,
(SELECT ISNULL(COUNT(AmtUse),0)
FROM (SELECT TOP ((DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)
+ ABS(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)))/2)
AmtUse
FROM (SELECT M.AmtUse, M.Year, M.Month
FROM #TmpMonthEnd M
WHERE M.AcctNum = T.AcctNum
AND M.Year*100 + M.Month < RIGHT(LEFT(T.TranDate,4),2)*100 + LEFT(RIGHT(T.TranDate,4),2)
UNION ALL
SELECT M.AmtUse, M.Year, M.Month
FROM #TmpMonthEnd M
CROSS JOIN (SELECT TOP (((DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)
+ ABS(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)))/2)
- (SELECT ISNULL(COUNT(*),0)
FROM (SELECT TOP (((DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)
+ ABS(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)))/2))
*
FROM #TmpMonthEnd M
WHERE M.AcctNum = T.AcctNum
AND M.Year*100 + M.Month < RIGHT(LEFT(T.TranDate,4),2)*100 + LEFT(RIGHT(T.TranDate,4),2)) B))
*
FROM sysobjects) D
WHERE M.AcctNum = T.AcctNum
AND M.Year*100 + M.Month = (SELECT MIN(E.Year*100 + E.Month) FROM #TmpMonthEnd E WHERE E.AcctNum = T.AcctNum)
) P
ORDER BY P.Year DESC, P.Month DESC) A) MonthsReturned,
(SELECT ISNULL(SUM(AmtUse),0)
FROM (SELECT TOP ((DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)
+ ABS(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)))/2)
AmtUse
FROM (SELECT M.AmtUse, M.Year, M.Month
FROM #TmpMonthEnd M
WHERE M.AcctNum = T.AcctNum
AND M.Year*100 + M.Month < RIGHT(LEFT(T.TranDate,4),2)*100 + LEFT(RIGHT(T.TranDate,4),2)
UNION ALL
SELECT M.AmtUse, M.Year, M.Month
FROM #TmpMonthEnd M
CROSS JOIN (SELECT TOP (((DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)
+ ABS(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)))/2)
- (SELECT ISNULL(COUNT(*),0)
FROM (SELECT TOP (((DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)
+ ABS(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)))/2))
*
FROM #TmpMonthEnd M
WHERE M.AcctNum = T.AcctNum
AND M.Year*100 + M.Month < RIGHT(LEFT(T.TranDate,4),2)*100 + LEFT(RIGHT(T.TranDate,4),2)) B))
*
FROM sysobjects) D
WHERE M.AcctNum = T.AcctNum
AND M.Year*100 + M.Month = (SELECT MIN(E.Year*100 + E.Month) FROM #TmpMonthEnd E WHERE E.AcctNum = T.AcctNum)
) P
ORDER BY P.Year DESC, P.Month DESC) A) TotalAmtUse,
ISNULL(PartialAmt,0) - (SELECT ISNULL(SUM(AmtUse),0)
FROM (SELECT TOP ((DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)
+ ABS(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)))/2)
AmtUse
FROM (SELECT M.AmtUse, M.Year, M.Month
FROM #TmpMonthEnd M
WHERE M.AcctNum = T.AcctNum
AND M.Year*100 + M.Month < RIGHT(LEFT(T.TranDate,4),2)*100 + LEFT(RIGHT(T.TranDate,4),2)
UNION ALL
SELECT M.AmtUse, M.Year, M.Month
FROM #TmpMonthEnd M
CROSS JOIN (SELECT TOP (((DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)
+ ABS(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)))/2)
- (SELECT ISNULL(COUNT(*),0)
FROM (SELECT TOP (((DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)
+ ABS(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)))/2))
*
FROM #TmpMonthEnd M
WHERE M.AcctNum = T.AcctNum
AND M.Year*100 + M.Month < RIGHT(LEFT(T.TranDate,4),2)*100 + LEFT(RIGHT(T.TranDate,4),2)) B))
*
FROM sysobjects) D
WHERE M.AcctNum = T.AcctNum
AND M.Year*100 + M.Month = (SELECT MIN(E.Year*100 + E.Month) FROM #TmpMonthEnd E WHERE E.AcctNum = T.AcctNum)
) P
ORDER BY P.Year DESC, P.Month DESC) A) NewPartAmt
FROM #TmpTrans T
SELECT *
FROM #TmpTrans
ORDER BY AcctNum, TranSeq
--Here is the update query. It now works as expected.
--Prior to modifying it would work for accounts where the required number of prior months rows existed.
--Now it uses the first month end record to fill in the required number of rows.
--This is how I first attempted to write it, but kept getting an error like T.AcctNum is invalid
DECLARE
@CalcPartAmtDECIMAL(11,2),
@PartialAmtDECIMAL(11,2)
UPDATE T
SET @CalcPartAmt = ISNULL(PartialAmt,0) - (SELECT ISNULL(SUM(AmtUse),0)
FROM (SELECT TOP ((DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)
+ ABS(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)))/2)
AmtUse
FROM (SELECT M.AmtUse, M.Year, M.Month
FROM #TmpMonthEnd M
WHERE M.AcctNum = T.AcctNum
AND M.Year*100 + M.Month < RIGHT(LEFT(T.TranDate,4),2)*100 + LEFT(RIGHT(T.TranDate,4),2)
UNION ALL
SELECT M.AmtUse, M.Year, M.Month
FROM #TmpMonthEnd M
CROSS JOIN (SELECT TOP (((DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)
+ ABS(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)))/2)
- (SELECT ISNULL(COUNT(*),0)
FROM (SELECT TOP (((DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)
+ ABS(DATEDIFF(M,CONVERT(DATE, CONVERT(VARCHAR(8),T.NextDate), 112), T.CurrentDate)))/2))
*
FROM #TmpMonthEnd M
WHERE M.AcctNum = T.AcctNum
AND M.Year*100 + M.Month < RIGHT(LEFT(T.TranDate,4),2)*100 + LEFT(RIGHT(T.TranDate,4),2)) B))
*
FROM sysobjects) D
WHERE M.AcctNum = T.AcctNum
AND M.Year*100 + M.Month = (SELECT MIN(E.Year*100 + E.Month) FROM #TmpMonthEnd E WHERE E.AcctNum = T.AcctNum)
) P
ORDER BY P.Year DESC, P.Month DESC) A),
@CalcPartAmt = (@CalcPartAmt + ABS(@CalcPartAmt))/2,
@PartialAmt = PartialAmt = CASE WHEN S.CalcMethod = 'Full'
THEN 0
ELSE CASE WHEN S.CIR = 'P'
THEN CASE WHEN ISNULL(T.CIRPartialAmt,0) > @CalcPartAmt
THEN ISNULL(T.CIRPartialAmt,0)
ELSE @CalcPartAmt
END
ELSE @CalcPartAmt
END
END
FROM #TmpTrans T, #TmpSettings S
WHERE T.AcctNum = S.AcctNum
SELECT *
FROM #TmpTrans
ORDER BY AcctNum, TranSeq
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply