?
Solved

SQL Query around dates

Posted on 2014-12-18
8
Medium Priority
?
190 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
ID: 40507339
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 668 total points
ID: 40507352
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 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 668 total points
ID: 40507362
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 recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 664 total points
ID: 40507364
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
 

Author Comment

by:dmanisit
ID: 40507382
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 70

Expert Comment

by:Scott Pletcher
ID: 40507404
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
ID: 40508149
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
ID: 40508154
Thank you
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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
Suggested Courses

864 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