Exclude Holiday date records from Working Day rec? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Exclude Holiday date records from Working Day rec?

Author

Topic

Maverick_
Posting Yak Master

107 Posts

Posted - 2010-03-03 : 06:48:13

Hi all!

This question is related to a post I made yesterday: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=140703

I have this query that lists customer enquiries made, logged_date is to be used to see when the enquiry was recorded.

I want the SQL to show list of enquiries made just on working days (or to put it another way, exclude enquiries logged on public holidays).

There is a Holidays table which contains: nonworkingday_id, nonworking_date.

There is a Enquiry status log Table which that holds the log details: enquiry_status_log.enquiry_log_number
enquiry_status_log.logged_date
enquiry_status_log.login_name

How do I get the code to kind of compare what is in the Holiday table, against Enquiry table and where it finds a match betweeen logged date and nonworking_date, it should exlude that result? (i.e. enquiry logged on Christmas Day 25/12/2010, New Year day 01/01/2011) etc.

This is the code I have created so far (please kindly excuse the format :) this is how they prefer it here)
---------------------------------------------------
SELECT
customer_enquiry.enquiry_number,
customer_enquiry.enquiry_desc,
customer_enquiry.service_code,
customer_enquiry.subject_code,
type_of_service.service_code,
type_of_service.service_name,
enquiry_subject.subject_code,
enquiry_subject.subject_name,
enquiry_status_log.enquiry_log_number,
enquiry_status_log.logged_date,
enquiry_status_log.login_name,
DATENAME(dw, GETDATE())
FROM
Og_officer,
customer_enquiry,
enquiry_status_log,
enquiry_subject,
type_of_service
WHERE
NOT exists (SELECT nonworking_date FROM nonworking_day) AND
type_of_service.service_code = enquiry_subject.service_code AND
Og_officer.officer_code = enquiry_subject.officer_code AND
enquiry_subject.service_code = customer_enquiry.service_code AND
enquiry_subject.subject_code = customer_enquiry.subject_code AND
customer_enquiry.enquiry_number = enquiry_status_log.enquiry_number AND
customer_enquiry.enquiry_number = enquiry_status_log.enquiry_number AND
customer_enquiry.enquiry_log_number = enquiry_status_log.enquiry_log_number AND
enquiry_status_log.logged_date > dateadd(dd,-3,getdate()) AND
customer_enquiry.service_code LIKE ('S%') AND
DATENAME(dw, GETDATE()) = 'Monday'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 09:10:49

thats what I've done in other post using

LEFT JOIN Holiday h
ON h.[date]= c.Date

and including condition

h.[date] IS NULL

to take only non matching records (non holidays)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Maverick_
Posting Yak Master

107 Posts

Posted - 2010-03-04 : 04:46:22

Thank you Visakh!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 08:24:53

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources