Solved

SQL Query around dates

Posted on 2014-12-18
8
183 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 167 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 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 167 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 166 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 69

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

717 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