Query Problem - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query Problem

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-10-25 : 05:45:31

Hello everyone,
Please help me in this scenario.

Table Structure and sample data
[CODE]
CREATE Table MobileCustomers( CustomerID Int, Usage Money, UsageDate DateTime )

INSERT INTO MobileCustomers
SELECT '1','8','10/20/10' UNION ALL
SELECT '1','4','10/21/10' UNION ALL
SELECT '1','2','10/22/10' UNION ALL
SELECT '1','1','10/23/10' UNION ALL
SELECT '1','12','10/24/10' UNION ALL
SELECT '1','11','10/25/10' UNION ALL
SELECT '2','45','10/20/10' UNION ALL
SELECT '2','34','10/21/10' UNION ALL
SELECT '2','23','10/22/10' UNION ALL
SELECT '2','20','10/23/10' UNION ALL
SELECT '2','17','10/24/10' UNION ALL
SELECT '2','18','10/25/10' UNION ALL
SELECT '3','12','10/20/10' UNION ALL
SELECT '3','23','10/21/10' UNION ALL
SELECT '3','14','10/22/10' UNION ALL
SELECT '3','12','10/23/10' UNION ALL
SELECT '3','11','10/24/10' UNION ALL
SELECT '3','3','10/25/10' UNION ALL
SELECT '4','12','10/20/10' UNION ALL
SELECT '4','3','10/21/10' UNION ALL
SELECT '4','45','10/22/10' UNION ALL
SELECT '4','56','10/23/10' UNION ALL
SELECT '4','23','10/24/10' UNION ALL
SELECT '4','3','10/25/10'

SELECT * FROM MobileCustomers
[/CODE]

What I need

I have to select customers who have total usage minimum 16 Rs from last 3 days they are being selected for some offer.

But condition is that if customer's usage is reach upto 16 today only then he will be select for the offer.
and if customer's usage is not upto 16 and usage of yesterday is more than 16
it means he is already selected for the offer so he will not be selected for today.
even his total usage is more than 16 in last three days.

Please wait for expected output.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-10-25 : 07:11:15

Expected Output -


CustomerID	TotalUsage	OfferDate
1		24		2010-10-25 00:00:00.000
2		18		2010-10-25 00:00:00.000
3		26		2010-10-25 00:00:00.000

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Sachin.Nand

2937 Posts

Posted - 2010-10-25 : 09:00:27

Why should CustomerId 2 get selected when his totalusage on yesterday is more than 16?

PBUH

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-10-25 : 09:22:40

Because today his total usage is more than 16 so he is elligible for the offer.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources