Solved

SQL Query around dates

Posted on 2014-12-18
8
173 Views
Last Modified: 2014-12-18
So here is my Dilemma. I wrote a SQL Query ( I am fairly new to writing SQL) , The below query is pulling data for 2 days in advance. I am sending this data off to a company that Automates calling.  I need to pull data for 2 business days in Advance. HOWEVER, what the hell do I do when it becomes Thursday and Friday? On Thursday, I need to Pull Monday (4 days ) and on Friday I need to pull Tuesdays (4 days in advance) Then on Monday, I need to go back to 2 days in advance to pull Wednesdays. (Code in next post)
0
Comment
Question by:dmanisit
8 Comments
 

Author Comment

by:dmanisit
Comment Utility
Select C.First_Name, C.Last_Name, A.Phone, cast(datepart(mm,APP.Appointment_DateTime) as varchar(2))+'/'+cast(datepart(dd,APP.Appointment_DateTime) as varchar(2))+'/'+ cast(datepart(yyyy,APP.Appointment_DateTime) as varchar(4)) as Appointment_Date, 
case when len(cast(datepart(hour, APP.Appointment_DateTime) as varchar(2)))=1 then '0' + cast(datepart(hour, APP.Appointment_DateTime) as varchar(2)) else cast(datepart(hour, APP.Appointment_DateTime) as varchar(2)) end + ':'+ 
case when len(cast(datepart(minute, APP.Appointment_DateTime) as varchar(2)))=1 then '0' + cast(datepart(minute, APP.Appointment_DateTime) as varchar(2)) else cast(datepart(minute, APP.Appointment_DateTime) as varchar(2)) end + ':'+
case when len(cast(datepart(second, APP.Appointment_DateTime) as varchar(2)))=1 then '0' + cast(datepart(second, APP.Appointment_DateTime) as varchar(2)) else cast(datepart(second, APP.Appointment_DateTime) as varchar(2)) end as Appointment_Time, 
 P.Patient_Number AS Client_Account_Number, 
 APP.Resource_ID AS Provider_Number, APP.Appointment_Type_ID AS Procedure_Number, SL.Scheduling_Location_ID AS Location_Number, APP.Comments AS Appointment_Notes,
'"' + R.Description + '"' AS Provider_Name, '' AS Procedure_Name, SL.Description AS Location_Name, '' AS Message_Name, C.Cell_Phone, '' AS Send_SMS, LOC.Phone AS Caller_ID_Number,
C.Email_Address, '' AS SEND_EMAIL, A.Street1 AS Client_Address, A.City AS Client_City, A.State AS Client_State, A.Zip_Code AS Client_ZipCode, C.Work_Phone AS Alternate_Client_Phone,
'' AS Client_Info_Notes, '' AS Never_Call, PI.Field_Value AS Language, '' AS Send_Fax, '' AS Volume_Level
FROM PMdb01.Ntier_JSA.PM.Contacts C
INNER JOIN PMdb01.Ntier_JSA.PM.Patients P WITH (NOLOCK) ON P.Contact_ID = C.Contact_ID 
 INNER JOIN PMdb01.Ntier_JSA.PM.Addresses A WITH (NOLOCK) ON A.Address_ID = C.Address_ID
INNER JOIN PMdb01.Ntier_JSA.PM.Appointments APP WITH (NOLOCK) ON APP.Patient_ID = P.Patient_ID
INNER JOIN PMdb01.Ntier_JSA.PM.Patient_Info PI WITH (NOLOCK) ON PI.Patient_ID = P.Patient_ID
INNER JOIN PMdb01.Ntier_JSA.PM.Resources R WITH (NOLOCK) ON R.Practitioner_ID = P.PCP_Practitioner_ID
INNER JOIN PMdb01.Ntier_JSA.PM.Scheduling_Locations SL WITH (NOLOCK) ON SL.Scheduling_Location_ID = APP.Scheduling_Location_ID
INNER JOIN PMdb01.Ntier_JSA.PM.Locations LOC WITH (NOLOCK) ON LOC.Location_ID = SL.Location_ID
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, APP.Appointment_DateTime)) = DATEADD(dd, 2, DATEDIFF(dd, 0, GETDATE())) AND APP.Status = 'S'
--WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, APP.Appointment_DateTime)) = DATEADD(dd, 0, DATEDIFF(dd, 3, GETDATE())) AND APP.Status = 'S'
--WHERE APP.Appointment_DateTime between GETDATE() and DateAdd(d,5,GetDate()) AND APP.Status = 'S'
AND PI.Field_Value IN ('English', 'Spanish', 'French', 'Portuguese', 'Chinese', 'Vietnamese', 'Greek', 'Russian', 'Other')

Open in new window

0
 
LVL 31

Accepted Solution

by:
James Murrell earned 167 total points
Comment Utility
you could use the WEEKDAY Function: this returns a number from 1 to 7 identifying the day of the week of a date. By default the day ranges from 1 (Sunday) to 7 (Saturday).

that should help
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 167 total points
Comment Utility
Try this solution:
Select C.First_Name, C.Last_Name, A.Phone
	, cast(datepart(mm,APP.Appointment_DateTime) as varchar(2))+'/'+cast(datepart(dd,APP.Appointment_DateTime) as varchar(2))+'/'+ cast(datepart(yyyy,APP.Appointment_DateTime) as varchar(4)) as Appointment_Date, 
	case 
		when len(cast(datepart(hour, APP.Appointment_DateTime) as varchar(2)))=1 then '0' + cast(datepart(hour, APP.Appointment_DateTime) as varchar(2)) 
		else cast(datepart(hour, APP.Appointment_DateTime) as varchar(2)) 
	end + ':'+ 
	case 
		when len(cast(datepart(minute, APP.Appointment_DateTime) as varchar(2)))=1 then '0' + cast(datepart(minute, APP.Appointment_DateTime) as varchar(2)) 
		else cast(datepart(minute, APP.Appointment_DateTime) as varchar(2))
	end + ':'+
	case 
		when len(cast(datepart(second, APP.Appointment_DateTime) as varchar(2)))=1 then '0' + cast(datepart(second, APP.Appointment_DateTime) as varchar(2)) 
		else cast(datepart(second, APP.Appointment_DateTime) as varchar(2)) 
	end as Appointment_Time, 
	P.Patient_Number AS Client_Account_Number, APP.Resource_ID AS Provider_Number, APP.Appointment_Type_ID AS Procedure_Number, SL.Scheduling_Location_ID AS Location_Number, APP.Comments AS Appointment_Notes,
	'"' + R.Description + '"' AS Provider_Name, '' AS Procedure_Name, SL.Description AS Location_Name, '' AS Message_Name, C.Cell_Phone, '' AS Send_SMS, LOC.Phone AS Caller_ID_Number,
	C.Email_Address, '' AS SEND_EMAIL, A.Street1 AS Client_Address, A.City AS Client_City, A.State AS Client_State, A.Zip_Code AS Client_ZipCode, C.Work_Phone AS Alternate_Client_Phone,
	'' AS Client_Info_Notes, '' AS Never_Call, PI.Field_Value AS Language, '' AS Send_Fax, '' AS Volume_Level
FROM PMdb01.Ntier_JSA.PM.Contacts C
	INNER JOIN PMdb01.Ntier_JSA.PM.Patients P WITH (NOLOCK) ON P.Contact_ID = C.Contact_ID 
	INNER JOIN PMdb01.Ntier_JSA.PM.Addresses A WITH (NOLOCK) ON A.Address_ID = C.Address_ID
	INNER JOIN PMdb01.Ntier_JSA.PM.Appointments APP WITH (NOLOCK) ON APP.Patient_ID = P.Patient_ID
	INNER JOIN PMdb01.Ntier_JSA.PM.Patient_Info PI WITH (NOLOCK) ON PI.Patient_ID = P.Patient_ID
	INNER JOIN PMdb01.Ntier_JSA.PM.Resources R WITH (NOLOCK) ON R.Practitioner_ID = P.PCP_Practitioner_ID
	INNER JOIN PMdb01.Ntier_JSA.PM.Scheduling_Locations SL WITH (NOLOCK) ON SL.Scheduling_Location_ID = APP.Scheduling_Location_ID
	INNER JOIN PMdb01.Ntier_JSA.PM.Locations LOC WITH (NOLOCK) ON LOC.Location_ID = SL.Location_ID
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, APP.Appointment_DateTime)) = DATEADD(dd, CASE DATEPART(WEEKDAY,GETDATE()) WHEN 1 THEN 3 WHEN 7 THEN 4 ELSE 2 END, DATEDIFF(dd, 0, GETDATE())) AND APP.Status = 'S'
	AND PI.Field_Value IN ('English', 'Spanish', 'French', 'Portuguese', 'Chinese', 'Vietnamese', 'Greek', 'Russian', 'Other')

Open in new window

0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 166 total points
Comment Utility
This is a classic case for Building a custom Calendar Table, so you can write T-SQL that includes custom business day logic.   The link is to an aritlce I wrote that has all the T-SQL needed to pull that off.

The Weekday function can be used if your logic is always Monday through Friday.  If not, i.e. adding to your requirements is if the next Monday is Memorial Day, then Friday would mean the next Tuesday and Wednesday.   Once that enters the mix you'll need to 'roll your own', as SQL Server isn't configured to handle every country/state/businesses' custom holidays.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:dmanisit
Comment Utility
Thank you everyone for commenting. Probably out of everything in SQL, I dont really get the date stuff. With that said. Vitor, your query is not returning any values. Today being Thursday I would need for it to pull Monday.

Jim, I like your idea, I am reading your article.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
A non-work days table is enough.  You then just get the next two calendar days that are not in the table.
0
 

Author Comment

by:dmanisit
Comment Utility
I want to thank everyone for the comments. All great ideas and I liked the article. I elected to go a different route.

Declare @dow int

select  @dow =  (select
					case 
						when datename(dw, getdate()) = 'MONDAY' THEN 2
						when datename(dw, getdate()) = 'TUESDAY' THEN 2
						when datename(dw, getdate()) = 'WEDNESDAY' THEN 2
						when datename(dw, getdate()) = 'THURSDAY' THEN  4
						when datename(dw, getdate()) = 'FRIDAY' THEN 4
					END
					)
	
	--select * from televox_out
Delete FROM dbo.televox_out
    -- Insert statements for procedure here
insert into dbo.Televox_Out    
Select Distinct C.First_Name, C.Last_Name, A.Phone, cast(datepart(mm,APP.Appointment_DateTime) as varchar(2))+'/'+cast(datepart(dd,APP.Appointment_DateTime) as varchar(2))+'/'+ cast(datepart(yyyy,APP.Appointment_DateTime) as varchar(4)) as Appointment_Date, 
case when len(cast(datepart(hour, APP.Appointment_DateTime) as varchar(2)))=1 then '0' + cast(datepart(hour, APP.Appointment_DateTime) as varchar(2)) else cast(datepart(hour, APP.Appointment_DateTime) as varchar(2)) end + ':'+ 
case when len(cast(datepart(minute, APP.Appointment_DateTime) as varchar(2)))=1 then '0' + cast(datepart(minute, APP.Appointment_DateTime) as varchar(2)) else cast(datepart(minute, APP.Appointment_DateTime) as varchar(2)) end + ':'+
case when len(cast(datepart(second, APP.Appointment_DateTime) as varchar(2)))=1 then '0' + cast(datepart(second, APP.Appointment_DateTime) as varchar(2)) else cast(datepart(second, APP.Appointment_DateTime) as varchar(2)) end as Appointment_Time, 
 P.Patient_Number AS Client_Account_Number, 
 APP.Resource_ID AS Provider_Number, APP.Appointment_Type_ID AS Procedure_Number, SL.Scheduling_Location_ID AS Location_Number, APP.Comments AS Appointment_Notes,
'"' + R.Description + '"' AS Provider_Name, '' AS Procedure_Name, SL.Description AS Location_Name, '' AS Message_Name, C.Cell_Phone, '' AS Send_SMS, LOC.Phone AS Caller_ID_Number,
C.Email_Address, '' AS SEND_EMAIL, A.Street1 AS Client_Address, A.City AS Client_City, A.State AS Client_State, A.Zip_Code AS Client_ZipCode, C.Work_Phone AS Alternate_Client_Phone,
'' AS Client_Info_Notes, '' AS Never_Call, PI.Field_Value AS Language, '' AS Send_Fax, '' AS Volume_Level
FROM PMdb01.Ntier_JSA.PM.Contacts C
INNER JOIN PMdb01.Ntier_JSA.PM.Patients P WITH (NOLOCK) ON P.Contact_ID = C.Contact_ID 
 INNER JOIN PMdb01.Ntier_JSA.PM.Addresses A WITH (NOLOCK) ON A.Address_ID = C.Address_ID
INNER JOIN PMdb01.Ntier_JSA.PM.Appointments APP WITH (NOLOCK) ON APP.Patient_ID = P.Patient_ID
INNER JOIN PMdb01.Ntier_JSA.PM.Patient_Info PI WITH (NOLOCK) ON PI.Patient_ID = P.Patient_ID
INNER JOIN PMdb01.Ntier_JSA.PM.Resources R WITH (NOLOCK) ON R.resource_id = app.resource_id
INNER JOIN PMdb01.Ntier_JSA.PM.Scheduling_Locations SL WITH (NOLOCK) ON SL.Scheduling_Location_ID = APP.Scheduling_Location_ID
INNER JOIN PMdb01.Ntier_JSA.PM.Locations LOC WITH (NOLOCK) ON LOC.Location_ID = SL.Location_ID
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, APP.Appointment_DateTime)) = DATEADD(dd, @dow, DATEDIFF(dd, 0, GETDATE())) AND APP.Status = 'S'
--WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, APP.Appointment_DateTime)) = DATEADD(dd, 0, DATEDIFF(dd, 3, GETDATE())) AND APP.Status = 'S'
--WHERE APP.Appointment_DateTime between GETDATE() and DateAdd(d,5,GetDate()) AND APP.Status = 'S'
AND PI.Field_Value IN ('English', 'Spanish', 'French', 'Portuguese', 'Chinese', 'Vietnamese', 'Greek', 'Russian', 'Other')

Open in new window

0
 

Author Closing Comment

by:dmanisit
Comment Utility
Thank you
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now