Need help on SQL query

I need help to apply below formula/logic for column [LT] using tsql statement.

DB column details :
[LT]
[Submitted Date]

Logic: Count the work days from the submitted date to Today's date and then exclude the public holidays from the count.

Public holidays are:

New Years Day…...….….Jan. 1

Memorial Day………......May 26

Independence Day…….. July 4

Labor Day……..……..….Sept. 1

Thanksgiving …...…..Nov. 27-28

Christmas….………...Dec. 25-26

Please advise how to achieve above requirement using tsql statment.
sqldba2013Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
May I suggest you read this previous question

The best method (IMO) is to use a table e.g.
Date Fun, Part One:  Build your own SQL calendar table to perform complex date expressions

Once you have a relevant table you can count the number of working days between any 2 dates.
Tony303Commented:
Hi,

Here is an example for you. However, Pete above is completely correct a Calendar Table is an essential item in any database.
--Set Date as a Variable
Declare @DateCheck Date
SET @DateCheck = '2013-12-01' --<<<JUST CHANGE YOUR START DATE HERE

--CREATE A TEMP TABLE
CREATE TABLE #TempTable (TheDate Date, WorkDay Int, Holiday Int)

--POPULATE THE TEMP TABLE WITH DATES, WORKDAYS AND HOLIDAYS
Begin
While @DateCheck <= GetDate()
 BEGIN
 INSERT INTO #TempTable (TheDate, WorkDay, Holiday) 
 VALUES (@DateCheck, 
		CASE WHEN DATEPART(DW,@DateCheck) in (1,7) THEN 0 ELSE 1 END,
		CASE WHEN DatePart(Day,@DateCheck) = 1	AND DatePart(Month,@DateCheck) = 1	THEN 1
			WHEN DatePart(Day,@DateCheck) = 26	AND DatePart(Month,@DateCheck) = 5	THEN 1
			WHEN DatePart(Day,@DateCheck) = 4	AND DatePart(Month,@DateCheck) = 7	THEN 1
			WHEN DatePart(Day,@DateCheck) = 1	AND DatePart(Month,@DateCheck) = 10 THEN 1
			WHEN DatePart(Day,@DateCheck) = 27	AND DatePart(Month,@DateCheck) = 11 THEN 1
			WHEN DatePart(Day,@DateCheck) = 28	AND DatePart(Month,@DateCheck) = 11 THEN 1
			WHEN DatePart(Day,@DateCheck) = 25	AND DatePart(Month,@DateCheck) = 12 THEN 1
			WHEN DatePart(Day,@DateCheck) = 26	AND DatePart(Month,@DateCheck) = 12 THEN 1
		ELSE 0 END
		)
 
 SET @DateCheck = DATEADD(D,1,@DateCheck)
  END
END 

--SHOW ALL DATA IN TEMP TABLE
SELECT *
FROM #TempTable

--HERE IS YOUR ANSWER
SELECT SUM(Workday) 
FROM #TempTable
WHERE Holiday = 0

--CLEAN UP
DROP TABLE  #TempTable

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sqldba2013Author Commented:
Thanks Tony303.

As per my requirement, I've to Count the work days from the item submitted date to Today's date and then exclude the public holidays from the count. And final result should be in single column like

LT
====
25

Could you please tel me where I have to add [Item Submitted Date] column in above script?
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

sarabhaiDeveloperCommented:
Replace your [Item Submitted Date]  by GetDate() , you will get result for that date.
Tony303Commented:
Hi again,

What sarabhai actually means is you need to put the "Item Submitted Date" into line 3 of my T-SQL.

PLEASE NOTE THIS IS WILL ONLY GIVE YOU 1 RESULT FOR 1 DATE.

If you need to do this for each line of a table, YOU SHOULD create the Calendar table with workdays only without holidays like Portlet Pete says. Then you can add a column to your query called LT that will be something like this...

SELECT COUNT(*)  as LT FROM WorkDaysTable where Date >=  [Item Submitted Date]  and Date <= GetDATE()

T
Scott PletcherSenior DBACommented:
I use a table with only NonWork days in it, since that's smaller that a work days only table and vastly smaller than a full calendar table.  You then use a calc like this:

 DATEDIFF(DAY, [submitted date], GETDATE()) - (SELECT COUNT(*) FROM dbo.NonWorkDays WHERE date >= [submitted date] AND date < DATEADD(DAY, 1, GETDATE()))

to compute total work days.
Tony303Commented:
I use a table with only NonWork days in it, since that's smaller that a work days only table and vastly smaller than a full calendar table.

Great idea.
PortletPaulEE Topic AdvisorCommented:
I use a table with only NonWork days in it...  

Great idea.
Yes and No...
While a table of just holidays it clearly very small (even over several decades) a table of all dates over a long period is also relatively small in row count. Attributes of every day are then available (e.g. work/non-work, fiscal year week number, fiscal "month" etc etc etc) and this can be of great benefit in aggregations/summarization for example.

As is so often the case with sql: "it depends"

A table with all dates over a decade is ~ 365.25*10 rows~= "not a lot"
sqldba2013Author Commented:
--
Scott PletcherSenior DBACommented:
>> A table with all dates over a decade is ~ 365.25*10 rows~= "not a lot" <<

Depends on how often it's read, and what is being pushed out of the buffer so those rows can be read in.

I've seen so many extra columns in calendar tables -- day names, month names, fully written out dates, **day names in a different language!**, etc. -- that the tables becomes rather wide.

ALL I/O is much more overhead than virtually anything else you do in SQL Server.  Save the use of the calendar table for the very rare times when you really need fiscal week, etc..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.