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
 Date coding logic
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wsilage
Yak Posting Veteran

USA
76 Posts

Posted - 05/03/2013 :  08:19:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 05/03/2013 :  09:09:26  Show Profile  Reply with Quote
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'
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 05/03/2013 :  09:18:25  Show Profile  Reply with Quote
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
Go to Top of Page

wsilage
Yak Posting Veteran

USA
76 Posts

Posted - 05/03/2013 :  09:34:40  Show Profile  Reply with Quote
Thanks for all of your help! I will take a look.
Go to Top of Page

wsilage
Yak Posting Veteran

USA
76 Posts

Posted - 05/03/2013 :  09:53:25  Show Profile  Reply with Quote
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?
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/03/2013 :  10:08:42  Show Profile  Reply with Quote
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:


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


To get the number of days between two dates you can do this:

DATEDIFF(dd,@currentDate,@DueDate) -- gives you days between @currentdate and @duedate

Go to Top of Page

wsilage
Yak Posting Veteran

USA
76 Posts

Posted - 05/03/2013 :  10:31:56  Show Profile  Reply with Quote
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 :(


Edited by - wsilage on 05/03/2013 10:33:03
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 05/03/2013 :  10:46:29  Show Profile  Reply with Quote
:) 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
Go to Top of Page

wsilage
Yak Posting Veteran

USA
76 Posts

Posted - 05/03/2013 :  11:06:25  Show Profile  Reply with Quote
then how do I get the current date?
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 05/03/2013 :  11:11:22  Show Profile  Reply with Quote
GetDate()


Cheers
MIK
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.11 seconds. Powered By: Snitz Forums 2000