• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 711
  • Last Modified:

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.
0
sqldba2013
Asked:
sqldba2013
  • 3
  • 2
  • 2
  • +2
1 Solution
 
PortletPaulfreelancerCommented:
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.
0
 
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

0
 
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?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
sarabhaiCommented:
Replace your [Item Submitted Date]  by GetDate() , you will get result for that date.
0
 
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
0
 
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.
0
 
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.
0
 
PortletPaulfreelancerCommented:
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"
0
 
sqldba2013Author Commented:
--
0
 
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..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now