Solved

SQL Query around dates

Posted on 2014-12-18
8
179 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 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 48

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 65

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

839 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