count on date - SQL Server Forums

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

count on date

Author

Topic

vasu4us
Posting Yak Master

102 Posts

Posted - 2007-01-19 : 11:07:53

iam new to SQL i mostly do select on DB but now iam asked to do this...need Help
I have a task to calculate the cost of a lead depending on conditions
Condition: The leads form PhoneLeads channel (of many channels)
First 30 records per day, type A will cost 20$. From 31st record its 50% off
First 30 records per day, type B will cost 10$. From the 31st record 50% off
Its straight and simple if the count is less than 30 on a day but how to change the cost from the 31st lead….How do I do this as an update to the table. Initially the cost is null for all

Sample output data:
Channel Type date cost
PhoneLeads A 2007-01-17 00:00:00 20
PhoneLeads B 2007-01-17 00:00:00 10
PhoneLeads B 2007-01-17 00:00:00 10
PhoneLeads B 2007-01-17 00:00:00 10
PhoneLeads A 2007-01-17 00:00:00 20
… .. … ..
… .. … ..
After the 30th lead on the same day the cost should be 50% discounted
PhoneLeads A 2007-01-17 00:00:00 10
PhoneLeads A 2007-01-17 00:00:00 10
PhoneLeads B 2007-01-17 00:00:00 5
PhoneLeads A 2007-01-17 00:00:00 10
PhoneLeads A 2007-01-17 00:00:00 10
PhoneLeads B 2007-01-17 00:00:00 5
PhoneLeads B 2007-01-17 00:00:00 5

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 12:25:08

You would have to do something like this.

DECLARE	@CurrDate DATETIME,
	@MaxDate DATETIME

SELECT	@CurrDate = MIN(LeadDate),
	@MaxDate = MAX(LeadDate)
FROM	LeadTable

SET ROWCOUNT 0

UPDATE	LeadTable
SET	Cost = NULL

SET ROWCOUNT 30

WHILE @CurrDate <= @MaxDate
	BEGIN
		UPDATE	LeadTable
		SET	Cost = 20
		WHERE	Channel = 'A'
			AND LeadDate = @CurrDate

		UPDATE	LeadTable
		SET	Cost = 10
		WHERE	Channel = 'B'
			AND LeadDate = @CurrDate

		SELECT	@CurrDate = MIN(LeadDate)
		FROM	LeadTable
		WHERE	LeadDate > @CurrDate
	END

SET ROWCOUNT 0

UPDATE	LeadTable
SET	Cost = 10
WHERE	Channel = 'A'
	AND Cost IS NULL

UPDATE	LeadTable
SET	Cost = 5
WHERE	Channel = 'B'
	AND Cost IS NULL

Peter Larsson
Helsingborg, Sweden

vasu4us
Posting Yak Master

102 Posts

Posted - 2007-01-19 : 14:28:31

Thanks Peter I understand it all but one little clarification,
is SET ROWCOUNT 30 used to run the while condetion 30 times?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 14:48:36

No. It limits every SELECT, UPDATE, DELETE to a maximum of thirty affected records.
When the limit has been reached, no more records are SELECTed, UPDATEd or DELETEd in that query.

Peter Larsson
Helsingborg, Sweden

vasu4us
Posting Yak Master

102 Posts

Posted - 2007-01-19 : 16:01:29

THIS IS REALLY GREAT PETER FOR THE LAST 2 DAYS I WAS RUNNING BETWEEN SELECT, CASE, UPDATE AND SUBQUEARIED THANK YOU THIS GIVES ME ALL NEW WAY OF THINKING.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 16:20:37

Great! Good luck.

Do you also understand the two last UPDATEs?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources