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.
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 toWHEN 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.CheersMIK |
|
|
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 orsubtract 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 @duedateDATEADD(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_FinalHere are the results that I am getting.PCS Provider Type New Future Date Due Date00070329 Initial Credentialing 2011-12-08 00:00:00.000 2012-06-08 00:00:00.00000080169 Initial Credentialing 2013-06-20 00:00:00.000 2013-12-20 00:00:00.00000133768 Initial Credentialing 2015-03-24 00:00:00.000 2015-09-24 00:00:00.000PCS 00072329 should be Initial CredentialingPCS 00080169 should be RecredentialingPCS 00133768 should be RemovedNow 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.CheersMIK |
|
|
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()CheersMIK |
|
|
|
|
|
|
|