November 7, 2008 at 9:08 am
Hi,
I put in example data and the UPDATE STATEMENT, and for the life of me I can't figure out why my results are this:
(2 row(s) affected)
ID_NUMBER PAYMENT_DATE AMOUNT RECCNT AVG_PMT_FREQ
--------------------------------------- ----------------------- ---------------------- ----------- ------------
100000110011 2013-01-01 00:00:00.000 726342.88 1 0
100000110011 2014-01-01 00:00:00.000 1073941.8 1 41273
(2 row(s) affected)
it should be 365,
Ironically when I have more data, the first 3 rows are 0 and then everything works beautiful, but that isn't what I want... obviously...
It seems that when this update is executed the flow is from the bottom up, that is why I placed my variables where i did...
thanks,
John
IF OBJECT_ID('tempdb..##PS') IS NOT NULL
DROP TABLE ##PS
-- TEMP
CREATE TABLE ##PS
(
[ID_NUMBER]DECIMAL(25, 0) NOT NULL, [PAYMENT_DATE]DATETIME,
[AMOUNT]FLOAT,
[RECCNT]INT,
[AVG_PMT_FREQ]INT
)
INSERT INTO ##PS ([ID_NUMBER], [PAYMENT_DATE], [AMOUNT])
VALUES(100000110011, '2013-01-01 00:00:00.000', 726342.88)
INSERT INTO ##PS ([ID_NUMBER], [PAYMENT_DATE], [AMOUNT])
VALUES(100000110011, '2014-01-01 00:00:00.000', 1073941.8)
CREATE CLUSTERED INDEX IX_SP_ID_PD
ON ##PS ([ID_NUMBER], [PAYMENT_DATE])
DECLARE @PrevAcctIDAS DECIMAL(25,0)
DECLARE @PRIORDATEAS DATETIME
DECLARE @CURRDATEAS DATETIME
DECLARE @PrevGrpCntAS INT--Running count resets when account changes
SET@PrevGrpCnt= 0
UPDATE ##PS
SET
[RECCNT]=1 -- [C].[CNT]
--CASE
--WHEN [P].[ID_NUMBER] = @PrevAcctID
--THEN (CASE WHEN @PrevGrpCnt = 1 THEN 0 ELSE @CURRDATE END)
--ELSE 0
--END
-- DO CALC OF DATA DIFF
,
@PRIORDATE=@CURRDATE
,
[AVG_PMT_FREQ]=CASE
WHEN [P].[ID_NUMBER] = @PrevAcctID THEN
CASE
WHEN DATEDIFF(DAY, @PRIORDATE, 0) <> 0 AND @PRIORDATE IS NOT NULL
THEN ABS(DATEDIFF(DAY, @PRIORDATE, @CURRDATE))
ELSE 0
END
ELSE 0
END
,
@CURRDATE=CASE
WHEN [P].[ID_NUMBER] = @PrevAcctID
THEN CASE WHEN @PrevGrpCnt = 1 THEN 0 ELSE [P].[PAYMENT_DATE] END
ELSE [P].[PAYMENT_DATE]
END
,
@PrevGrpCnt=CASE
WHEN [P].[ID_NUMBER] = @PrevAcctID THEN @PrevGrpCnt + 1
ELSE 1 -- Restarts count at "1"
END
,
@PrevAcctID=[P].[ID_NUMBER]
FROM ##PS [P]
WITH (INDEX(IX_SP_ID_PD),TABLOCKX)
WHERE [ID_NUMBER] = 100000110011
SELECT * --[ID_NUMBER], COUNT([ID_NUMBER]) --[PAYMENT_DATE], [AMOUNT]
FROM ##PS
WITH (INDEX(IX_SP_ID_PD),TABLOCKX)
WHERE [ID_NUMBER] = 100000110011
November 7, 2008 at 9:46 am
Looks like it has to do with the order in which your items are listed in the UPDATE statement. Please check out the following code based on what you posted with some modifications.
-- TEMP
CREATE TABLE #PS
(
[ID_NUMBER] DECIMAL(25, 0) NOT NULL, [PAYMENT_DATE] DATETIME,
[AMOUNT] FLOAT,
[RECCNT] INT,
[AVG_PMT_FREQ] INT
)
INSERT INTO #PS ([ID_NUMBER], [PAYMENT_DATE], [AMOUNT])
VALUES(100000110011, '2013-01-01 00:00:00.000', 726342.88)
INSERT INTO #PS ([ID_NUMBER], [PAYMENT_DATE], [AMOUNT])
VALUES(100000110011, '2014-01-01 00:00:00.000', 1073941.8)
CREATE CLUSTERED INDEX IX_SP_ID_PD
ON #PS ([ID_NUMBER], [PAYMENT_DATE])
DECLARE @PrevAcctID AS DECIMAL(25,0)
DECLARE @PRIORDATE AS DATETIME
DECLARE @CURRDATE AS DATETIME
DECLARE @PrevGrpCnt AS INT --Running count resets when account changes
SET @PrevGrpCnt = 0
UPDATE #PS SET
[RECCNT] = 1 -- [C].[CNT]
-- CASE WHEN [P].[ID_NUMBER] = @PrevAcctID
-- THEN CASE WHEN @PrevGrpCnt = 1
-- THEN 0
-- ELSE cast(@CURRDATE as int)
-- END
-- ELSE 0
-- END
-- DO CALC OF DATA DIFF
,@PRIORDATE = @CURRDATE
,@CURRDATE = CASE WHEN [P].[ID_NUMBER] = @PrevAcctID
THEN CASE WHEN @PrevGrpCnt = 0
THEN 0
ELSE [P].[PAYMENT_DATE]
END
ELSE [P].[PAYMENT_DATE]
END
,[AVG_PMT_FREQ] = CASE WHEN [P].[ID_NUMBER] = @PrevAcctID
THEN CASE WHEN DATEDIFF(DAY, 0, @PRIORDATE) <> 0
-- AND @PRIORDATE IS NOT NULL
THEN ABS(DATEDIFF(DAY, @PRIORDATE, @CURRDATE))
ELSE 0
END
ELSE 0
END
,@PrevAcctID = [P].[ID_NUMBER]
,@PrevGrpCnt = CASE WHEN [P].[ID_NUMBER] = @PrevAcctID
THEN @PrevGrpCnt + 1
ELSE 0 -- Restarts count at "1"
END
FROM
#PS [P] WITH (INDEX(IX_SP_ID_PD),TABLOCKX)
WHERE
[ID_NUMBER] = 100000110011
SELECT * --[ID_NUMBER], COUNT([ID_NUMBER]) --[PAYMENT_DATE], [AMOUNT]
FROM #PS
WITH (INDEX(IX_SP_ID_PD),TABLOCKX)
WHERE [ID_NUMBER] = 100000110011
DROP TABLE #PS
November 7, 2008 at 9:53 am
You are so smart...
Trying to figure out the order in which to lay things out is tough for me... I get those vulcon mind melts...
Thanks so much,
John
November 7, 2008 at 10:03 am
Remember ... (Name the movie and the character!)
November 7, 2008 at 10:07 am
Are you talking about the vulcon mind melt?
oh, 1 more thing too please; at each change in the ID_NUMBER, the code isn't setting the first row to 0, it's taking the last ID_NUMBER DATE and doing DATEDIFF with the new ID_NUMBER DATE...???
November 7, 2008 at 10:16 am
Got it... I added
,@PRIORDATE = CASE WHEN [P].[ID_NUMBER] = @PrevAcctID THEN @CURRDATE ELSE 0 END
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply