Running total , missing date , NULL in amount, exception handling

Question

Running total , missing date , NULL in amount, exception handling

karthik M

One Orange Chip

Points: 29139
More actions
December 24, 2009 at 12:52 pm

#213929

Hi All,
I read 'Solving running total problem' recently. I tried to apply it in a procedure. I have implemented/rewritten the query (50% only) with 'quirky update' method. But still some requirements are in open position.
I would like to use the same DDL (which Jeff posted)
http://www.sqlservercentral.com/articles/T-SQL/68467/
so please take the same DDL code.
I would like to add two more column in the table called 'inceptiondate' & 'initalamount'
My requirements:
1) If any date is misssing in the sequence then i don't want to calculate 'running total' after that row. But i can do the calculation before the date missing row. so i need to check datediff(mm,currentmonth,previousmonth) should be always 1.
2) if i give any date as input then i need to calculate running total after that date.
2.1) if the given date is greater than 'inceptiondate' (for any account) then i have to take the 'initalamount' otherwise we have to take 'Amount'.
3) if the amount is NULL for any account then no need to calculate running total for that particular account only.
Inputs are welcome!
incase if any account's 'inceptiondate' is
karthik

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

karthik M One Orange Chip Points: 29139 More actions · Answer 1

I saw huge performance improvement after implementing 'quirky method'. Yes, The execution time was reduced from 45 minutes to 30 seconds( Thanks a lot to Jeff)

But i need to implement the validations which i mentioned above. 🙁

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 2

karthik M

One Orange Chip

Points: 29139

December 26, 2009 at 7:40 am

#1096522

Any inputs?

karthik

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

karthikeyan-444867 (12/26/2009)
Any inputs?

Not a one, Karthik... at least not the kind you're expecting. You've simply asked people to do too much of your work, your requirements are quite unclear and, in some cases, seem contradictory.

Considering that you're adding two columns and have requirements as to what to do with those two columns, how about you provide the complete DDL and the table population code for people to use? After all... you're the one who needs the help.

Inputs are welcome!

Ok... here's a friendly input. Help us help you... instead of sending folks off to my article to get the DDL and the data generation and then having them add two columns and populate those two new columns to test YOUR requirements, do your own leg work. The reason why people haven't answered is simply because they don't want to take all that time to do YOUR work. Come up with and post viable DDL that meets all of your requirements, post a data generator that will exercise all of your requirements, post requirements that we don't have to guess the meaning of, post examples of "before'n'after" data to amplify what the requirements mean, and take 10 minutes to practice with the IFCode's on this forum to properly format the data and any code you may post so we don't have to work so bloody hard to read your posts.

You're a Senior Software Engineer... I know you can do it. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 4

CREATE TABLE Returns_t

(Asset_ID INT

,[Date] DATETIME NULL

,[Return] FLOAT NULL

,InceptionDate DATETIME NULL

,InitialNAV FLOAT NULL

,NAV FLOAT NULL

)

insert into Returns_t

select 112 ,'30/apr/2009', 0.0122,'30/apr/2008',100,null

union all

select 112, '31/may/2009', 0.0213,'30/apr/2008',100,null

union all

select 112 ,'30/jun/2009', 0.0310,'30/apr/2008',100,null

union all

select 112, '31/jul/2009', 0.1103,'30/apr/2008',100,null

union all

select 112, '31/aug/2009', 0.1113,'30/apr/2008',100,null

union all

select 112, '30/sep/2009', 0.2213,'30/apr/2008',100,null

union all

select 112, '31/oct/2009', 0.5213,'30/apr/2008',100,null

union all

select 112, '30/nov/2009', 0.8213,'30/apr/2008',100,null

union all

select 112, '31/dec/2009', 0.6213,'30/apr/2008',100,null

union all

select 12, '30/apr/2009', 1.0122,'01/apr/2009',100,null

union all

select 12, '31/may/2009', 1.0213,'01/apr/2009',100,null

union all

select 12, '30/jun/2009' ,1.0310,'01/apr/2009',100,null

union all

select 12, '31/jul/2009' ,1.1103,'01/apr/2009',100,null

union all

select 12, '31/aug/2009' ,1.1113,'01/apr/2009',100,null

union all

select 12, '30/sep/2009' ,1.2213,'01/apr/2009',100,null

union all

select 12, '31/oct/2009' ,1.5213,'01/apr/2009',100,null

union all

select 12, '30/nov/2009' ,1.8213,'01/apr/2009',100,null

union all

select 12, '31/dec/2009' ,1.6213,'01/apr/2009',100,null

create clustered index id1 on Returns_t (Asset_ID,[Date])

Declare @NAV float

Declare @prev_assetid int

update Returns_t

set @NAV = NAV = case when Asset_ID = @prev_assetid

then @NAV * (1 + [Return])

else initialNAV * (1 + [Return])

end

from Returns_t

WITH (TABLOCKX) OPTION (MAXDOP 1)

My requirement is :

1) If any date is misssing in the sequence then i don't want to calculate 'running total' after that row. But i can do the calculation before the missing row.

2) if i give any date as input then i need to calculate running total after that date.

3) if the return is NULL for any asset_id then no need to calculate running total for that particular account only.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 5

since the data's are confidential , i posted some dummy data's. Actually this table has more than 200,000 records. So i just moved all the records into a temp table and then i created the clustered index.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 6

Requirement #1:

delete from Returns_t

where Date = '31/oct/2009'

and asset_id = 12

if i delete the above row then the expected output is

122009-04-30 00:00:00.0001.01222009-04-01 00:00:00.000100201.22

122009-05-31 00:00:00.0001.02132009-04-01 00:00:00.000100202.13

122009-06-30 00:00:00.0001.0312009-04-01 00:00:00.000100203.1

122009-07-31 00:00:00.0001.11032009-04-01 00:00:00.000100211.03

122009-08-31 00:00:00.0001.11132009-04-01 00:00:00.000100211.13

122009-09-30 00:00:00.0001.22132009-04-01 00:00:00.000100222.13

122009-11-30 00:00:00.0001.82132009-04-01 00:00:00.000100NULL

122009-12-31 00:00:00.0001.62132009-04-01 00:00:00.000100NULL

1122009-04-30 00:00:00.0000.01222008-04-30 00:00:00.000100101.22

1122009-05-31 00:00:00.0000.02132008-04-30 00:00:00.000100102.13

1122009-06-30 00:00:00.0000.0312008-04-30 00:00:00.000100103.1

1122009-07-31 00:00:00.0000.11032008-04-30 00:00:00.000100111.03

1122009-08-31 00:00:00.0000.11132008-04-30 00:00:00.000100111.13

1122009-09-30 00:00:00.0000.22132008-04-30 00:00:00.000100122.13

1122009-10-31 00:00:00.0000.52132008-04-30 00:00:00.000100152.13

1122009-11-30 00:00:00.0000.82132008-04-30 00:00:00.000100182.13

1122009-12-31 00:00:00.0000.62132008-04-30 00:00:00.000100162.13

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 7

karthik M

One Orange Chip

Points: 29139

December 30, 2009 at 7:07 am

#1097412

Any inputs?

karthik

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

karthikeyan-444867 (12/28/2009)
My requirement is :
1) If any date is misssing in the sequence then i don't want to calculate 'running total' after that row. But i can do the calculation before the missing row.
2) if i give any date as input then i need to calculate running total after that date.
3) if the return is NULL for any asset_id then no need to calculate running total for that particular account only.

This is probably why people haven't responded... they get tired of trying to drag the requirements out of you, Karthik. Whith that in mind, WHICH DATE? You have two date columns.

Also, you've already posted that you've moved the data to a temp table so you can apply the correct clustered index and, although I've not been able to get to it, you're code looks to be in the correct vein... you should continue to try to resolve this yourself. It's just not that hard for a Sr. Software Engineer. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 9

Declare @NAV float

Declare @prev_assetid int

declare @prev_date datetime

SET ANSI_NULLS ON

update Returns_t

set @NAV = NAV = case when (Asset_ID = @prev_assetid) then case when datediff(month,@prev_date,Date) <> 1

then null * (1 + [Return])

else @NAV * (1 + [Return])

end

else initialNAV * (1 + [Return])

end,

@prev_date = Date

from Returns_t

WITH (TABLOCKX) OPTION (MAXDOP 1)

select * from returns_t

I implemented the above logic in my actual code. It works fine for me. But it is not working for my sample table. i am little bit confused here. Because i implemented the same logic in my actual procedure.

WHICH DATE? You have two date columns.

column name is 'Date'.

Requirement #2 and #3 is resolved.

you should continue to try to resolve this yourself. It's just not that hard for a Sr. Software Engineer.

Yes. I resolved all my requirements. The procedure is running 35 seconds now. This 'Quirky Update' part is taking 5-7 seconds only. I am working on to reduce the execution time nearly 10 seconds.

Now my doubt is why the same logic is not working for my sample table.

karthik

Mike01 SSChampion Points: 11395 More actions · Answer 10

I ran your code and it's not working the way you think it is. When I ran it without modifications, it just added the NAV to the return. No running totals. I removed your case when code and replaced it with hard coded values to validate. I also put it in a transaction so I can roll it back for testing

begin tran

Declare @NAV float

Declare @prev_assetid int

SET ANSI_NULLS ON

update Returns_t

set @NAV = NAV =

case when (Asset_ID = @prev_assetid) then

case when datediff(month,@prev_date,Date) <> 1

then 1

else 2

end

else 3

end,

@prev_date = Date

from Returns_t

WITH (TABLOCKX) OPTION (MAXDOP 1)

select * from returns_t

rollback

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Mike01 SSChampion Points: 11395 More actions · Answer 11

I was able to get the results I think you were looking for. It had to do with the order in which the variables were set. Try this. I put a rollback in so I could retest. I also only added the return rates together. You can modify it as you see fit.

begin tran

Declare @NAV float

Declare @prev_assetid int, @prev_date datetime , @next_date datetime

set @nav = 0

SET ANSI_NULLS ON

update Returns_t

set @next_Date = @prev_date, @prev_date = Date,

@NAV = NAV = case when @prev_Assetid = Asset_ID then

case when DateDiff(m,@next_date,date) <> 1

then null

else

@nav + [Return]

end

else

[Return]

end,

@prev_assetid = Asset_id

from Returns_t

WITH (TABLOCKX) OPTION (MAXDOP 1)

select * from returns_t

rollback

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

karthik M One Orange Chip Points: 29139 More actions · Answer 12

Mike,

Thanks for your reply.

Actually i did a small mistake in the code. Thats why it gave wrong output. Yes, I missed to include the below line.

Declare @NAV float

Declare @prev_assetid int

declare @prev_date datetime

SET ANSI_NULLS ON

update Returns_t

set @NAV = NAV = case when (Asset_ID = @prev_assetid) then case when datediff(month,@prev_date,Date) <> 1

then null * (1 + [Return])

else @NAV * (1 + [Return])

end

else initialNAV * (1 + [Return])

end,

@prev_date = Date,

@prev_assetid = Asset_ID -- Missed it in my previous code

from Returns_t

WITH (TABLOCKX) OPTION (MAXDOP 1)

select * from returns_t

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 13

After including that line, it is running fine now.

Jeff,

I really learned some good things from your article. Thanks for my sql teacher.

you should continue to try to resolve this yourself. It's just not that hard for a Sr. Software Engineer.

karthik

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 14

karthikeyan-444867 (12/31/2009)
After including that line, it is running fine now.
Jeff,
I really learned some good things from your article. Thanks for my sql teacher.
you should continue to try to resolve this yourself. It's just not that hard for a Sr. Software Engineer.

I knew you had it in you. Karthik. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)