Date coding logic - 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

Date coding logic

Author

Topic

wsilage
Yak Posting Veteran

82 Posts

Posted - 2013-05-03 : 08:19:38

I need to create some logic with dates.

I am creating a case statement, but I don't know if I am getting the criteria right.

I need to say when the due date is less than 75 days from current date, then "Initial Credentialing"

The next one is....

When Future dare is between current date and >75 day from Due Date then "Recredentialing"

another one...

Current Date is >75 fays of Due Date and >105 from Future date then "Recredential Rush"

Future date is > than Current date then "Remove"

So this is what I have so far in my case statement, not sure if it is correct or not. Any help for the above statemets would be awesome !

Case
When [Future Date] = '' or [Future Date] = NULL or [Future Date] is null Then 'Initial Credential'
WHEN [Due Date] <=DATEADD(dd,75,GETDATE()) Then 'Initial Credential'
Else 'XX'
End as [Provider Type New],

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-03 : 09:09:26

You don't need/should not have the "[Future Date] = '' or [Future Date] = NULL " (unless you have changed ANSI_NULLS setting, which you should not). So the query would be like this:


        CASE WHEN [Future Date] IS NULL THEN 'Initial Credential'
             WHEN [Due Date] <= DATEADD(dd, 75, GETDATE()) THEN 'Initial Credential'
             ELSE 'XX'
        END AS [Provider Type New]

Whether that is logically right or not - I am not able to say from your description. In English, what is saying is that if FUTURE DATE column has a null value, or if Due Date column has a value that is less than 75 days from now, select 'Initial Credential' else select 'XX'.

The comparison you have also would take into account time. If you want to remove time part, modify the second WHEN expression to

WHEN due_date < DATEADD(d,75,CAST(GETDATE() AS DATE) THEN 'Initial Credential'

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-03 : 09:18:25

May be this?

SELECT
CASE
WHEN DATEDIFF(dd,@currentDate,@DueDate) <75 then 'Initial Credentialing'
WHEN @futureDate Between @CurrentDate and DATEADD(dd,75,@dueDate) THEN 'Recredentialing'
WHEN @currentDate>DATEADD(dd,75,@dueDate) AND @currentDate > DATEADD(dd,75,@futureDate) then 'Recredential Rush'
WHEN @futureDate > @CurrentDate THEN 'Remove'
END

If not, at least will give you an idea as how to do it... and would give you a ground to play in.

still after that If you face any problem, come up with sample data (in consumable format - meaning in the form of Insert statemnts) and desired ouput in light of that sample data.

Cheers
MIK

wsilage
Yak Posting Veteran

82 Posts

Posted - 2013-05-03 : 09:34:40

Thanks for all of your help! I will take a look.

wsilage
Yak Posting Veteran

82 Posts

Posted - 2013-05-03 : 09:53:25

the only thing I changed was the declare to say...

DECLARE @CurrentDate AS Datetime = getdate()
DECLARE @DueDate AS Datetime ='5/15/2013'
DECLARE @futureDate Datetime='6/30/2013'

I can add the getdate() to the due date right?

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-03 : 10:08:42

You can not add two dates, but you can add number of days or months or years to a date or
subtract number of days or months or years from a date:

For example if you want to add 75 days to a date you do this:

[CODE]
DATEADD(dd,75,@dueDate)-- Add 75 days to @duedate
DATEADD(dd,75,getdate())-- Add 75 days to todays date
[/CODE]

To get the number of days between two dates you can do this:
[CODE]
DATEDIFF(dd,@currentDate,@DueDate) -- gives you days between @currentdate and @duedate
[/CODE]

wsilage
Yak Posting Veteran

82 Posts

Posted - 2013-05-03 : 10:31:56

Okay this does pull what I want, but I think it is overlapping my data.

DECLARE @CurrentDate AS Datetime = getdate()
DECLARE @DueDate AS Datetime ='5/15/2013'
DECLARE @futureDate Datetime='6/30/2013'

Select Distinct

[Provider Type],

CASE
When [Future Date] = '' or [Future Date] = NULL or [Future Date] is null Then 'Initial Credential'
WHEN DATEDIFF(dd,@currentDate,@DueDate) <75 then 'Initial Credentialing'
WHEN (@futureDate Between @CurrentDate and DATEADD(dd,75,@dueDate)) THEN 'Recredentialing'
WHEN @currentDate>DATEADD(dd,75,@dueDate) AND @currentDate > DATEADD(dd,75,@futureDate) then 'Recredential Rush'
WHEN (@futureDate > @CurrentDate) THEN 'Remove'
----When [Due Date] < DATEADD(d,75,CAST(GETDATE() AS DATE) THEN 'Initial Credential'
Else 'XX'

End as [Provider Type New],

[Future Date],[Due Date],

[Last Credential Date],
[Date Required to be Credentialed],
[Month to Begin Re-credentialing (future date)],
datename(month,[Month to Mail App for Initial Credential]) + ' ' + cast(year([Month to Mail App for Initial Credential]) as char(4)) as [Month to Mail App for Initial Credential],
[Date Back to DHS]

From Unique_Provider_Final

Here are the results that I am getting.

PCS Provider Type New Future Date Due Date
00070329 Initial Credentialing 2011-12-08 00:00:00.000 2012-06-08 00:00:00.000
00080169 Initial Credentialing 2013-06-20 00:00:00.000 2013-12-20 00:00:00.000
00133768 Initial Credentialing 2015-03-24 00:00:00.000 2015-09-24 00:00:00.000

PCS 00072329 should be Initial Credentialing
PCS 00080169 should be Recredentialing
PCS 00133768 should be Removed

Now if I do the case statements Separately they work, but If I keep them like I do above, it is just putting everything as Initial Credentialing. I guess back to square one again :(

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-03 : 10:46:29

:) yes it ought to give you always "initial credentialing".... since those are just variables and are using static values provided in the declare @date date = ....

You need to replace the variables (@) with respective columns of the table. I just used them as an example as to show how you can implement your logic.

Cheers
MIK

wsilage
Yak Posting Veteran

82 Posts

Posted - 2013-05-03 : 11:06:25

then how do I get the current date?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-03 : 11:11:22

GetDate()

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources