SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 update table on date and other condetion
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vasu4us
Posting Yak Master

102 Posts

Posted - 01/15/2007 :  16:47:38  Show Profile  Reply with Quote
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

Sweden
30190 Posts

Posted - 01/15/2007 :  17:18:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

vasu4us
Posting Yak Master

102 Posts

Posted - 01/15/2007 :  17:35:08  Show Profile  Reply with Quote
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

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30190 Posts

Posted - 01/15/2007 :  17:37:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Or, you cansimply give us some sample data, and your expected output based on the provided sample data.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

vasu4us
Posting Yak Master

102 Posts

Posted - 01/16/2007 :  09:13:59  Show Profile  Reply with Quote
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

Edited by - vasu4us on 01/16/2007 09:15:42
Go to Top of Page

vasu4us
Posting Yak Master

102 Posts

Posted - 01/16/2007 :  13:17:33  Show Profile  Reply with Quote
can some one help me on this. let me know if its not clear.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000