Last day of the month type question 1

[HestonJames]

Hello Guys,

I have a scheduled script that runs every day and I want to pull a list of customers who should be billed on this day of the month.

Now, I've mocked up a little data to show you how I want this to work.

DECLARE @Customers TABLE (
	Customer_ID int IDENTITY(1,1) NOT NULL,
	FirstBillingDate datetime
);

Insert Into	@Customers (
						FirstBillingDate
)
Values (
		'20090720'
);

Insert Into	@Customers (
						FirstBillingDate
)
Values (
		'20090531'
);

Select * From @Customers

Now, what I want to do is pass in a date and have it return a list of customers for which the day of the month passed in matches their first billing date.

So for instance I pass in '20091120' and it'll return the first customer as their first billing date was the 20th, you see?

Now, the difficulty comes with people like the second customer in my example, whereby their first billing date is on a day which doesn't exist in every month, such as the 29th 30th and the 30st. In these instances I want these customers to be returned on the last day of the month, so if I passed in a date like '20100228' then it'll return all those records who's first billing date was for the 29th 30th and 31st to ensure they get billed very month.

Does that make sense? how would you go about coding that logic?

Thanks in advance chaps,

Heston

 

[bborissov]

[COLOR=blue]DECLARE[/color] @tesDate [COLOR=blue]AS[/color] [COLOR=#FF00FF]datetime[/color]
[COLOR=blue]SET[/color] @tesDate = [COLOR=red]'20090207'[/color]

[COLOR=blue]DECLARE[/color] @IsLastDay [COLOR=blue]bit[/color]
[COLOR=blue]SET[/color] @IsLastDay = [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]DAY[/color](@tesDate) = [COLOR=#FF00FF]DAY[/color]([COLOR=#FF00FF]DATEADD[/color](dd,-[COLOR=#FF00FF]DAY[/color](@tesDate),[COLOR=#FF00FF]DATEADD[/color](mm,1,@tesDate))) [COLOR=blue]THEN[/color] 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]


[COLOR=blue]SELECT[/color] ....
[COLOR=blue]FROM[/color] YourTable 
[COLOR=blue]WHERE[/color] [COLOR=#FF00FF]DAY[/color](payDate) = [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] @IsLastDay = 1
                               [COLOR=blue]THEN[/color] [COLOR=#FF00FF]DAY[/color]([COLOR=#FF00FF]DATEADD[/color](dd,-[COLOR=#FF00FF]DAY[/color](payDate),[COLOR=#FF00FF]DATEADD[/color](mm,1,payDate))
                          [COLOR=blue]ELSE[/color] [COLOR=#FF00FF]DAY[/color](@tesDate) [COLOR=blue]END[/color]

Where @tesDate is the variable you passed

NOT TESTED!!!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[RiverGuy]

DECLARE @Customers TABLE (
    Customer_ID int IDENTITY(1,1) NOT NULL,
    FirstBillingDate datetime
);

Insert Into    @Customers (
                        FirstBillingDate
)
Values (
        '20090720'
);

Insert Into    @Customers (
                        FirstBillingDate
)
Values (
        '20090531'
);

[b]
DECLARE @TheDate DATETIME
SELECT @TheDate = '20090731'
--SELECT @TheDate = '20091120'
[/b]

Select * From @Customers
[b]
WHERE (DAY(@TheDate) = DAY(FirstBillingDate) AND MONTH(DATEADD(day, 1, @TheDate)) = MONTH(@TheDate))
OR (
MONTH(DATEADD(day, 1, FirstBillingDate)) <> MONTH(FirstBillingDate)
AND 
MONTH(DATEADD(day, 1, @TheDate)) <> MONTH(@TheDate)
)
[/b]

 

[HestonJames]

Hey Guys,

Thanks a great deal for this, really excellent stuff. RiverGuy, your code seems to work sweet, I've tested plenty and it returns exactly as I would expect.

Thank you!

Heston