• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

SQL Query around dates

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
dmanisit
Asked:
dmanisit
3 Solutions
 
dmanisitAuthor Commented:
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
 
James MurrellProduct SpecialistCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
dmanisitAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
A non-work days table is enough.  You then just get the next two calendar days that are not in the table.
0
 
dmanisitAuthor Commented:
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
 
dmanisitAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now