update table on date and other condetion

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

update table on date and other condetion

Author

Topic

vasu4us
Posting Yak Master

102 Posts

Posted - 2007-01-15 : 16:47:38

I have a table with date,cost,Ltype columns
I have to update the cost based on Ltype and count of rows in a singel day.
the business rule is like this
1.LtypeA =$90 LtypeB=$30
2.50% discount($45 & $15) after 30th row in a singel day for each row over 30

i have data for 2006 year and updated every day with new rows ie
i have to run a update for the historic data and run it everyday for the newonce.

any help appriciated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-15 : 17:18:00

insert into a table variable the top 30 rows.
update original table with left join to table variable. if match, do one thing, of no match, do another thing.

Peter Larsson
Helsingborg, Sweden

vasu4us
Posting Yak Master

102 Posts

Posted - 2007-01-15 : 17:35:08

Thanks Peter tell me if i have got u wrong
you want me to take the top 30 rows of each day into a temptable
and then update the temp table with the actual cost and the actual table with a 50% discounted cost and then join them.

I was trying to count the number of roes in a day then
if <= 30 cost =90/30(depending on type)
else
????????????this is where iam breaking

Guys be patient I have no clue how iam going to do this...thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-15 : 17:37:07

Or, you cansimply give us some sample data, and your expected output based on the provided sample data.

Peter Larsson
Helsingborg, Sweden

vasu4us
Posting Yak Master

102 Posts

Posted - 2007-01-16 : 09:13:59

Hi Peter, here is the sample data with the required output in the lead cost(leadcost is null in the source)

Date Ltype Cost
1/1/2007 a 90
1/1/2007 b 30
1/1/2007 a 90
. . .
. . .
. . .
22 rows for that day so cost is 90/30 depending upon type

Date Ltype Cost
1/2/2007 a 90
1/2/2007 b 30
1/2/2007 a 90
. . .
. . .
. . .
after the 30th row
1/2/2007 a 45
1/2/2007 b 15
1/2/2007 a 45
1/2/2007 a 45
1/2/2007 a 45
1/2/2007 b 15

we get a discount of 50% after the receipt of 30th customer call per day

vasu4us
Posting Yak Master

102 Posts

Posted - 2007-01-16 : 13:17:33

can some one help me on this. let me know if its not clear.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources