Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need help on SQL query

Posted on 2014-01-07
10
Medium Priority
?
702 Views
Last Modified: 2014-01-09
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
Comment
Question by:sqldba2013
[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
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39764159
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
 
LVL 12

Accepted Solution

by:
Tony303 earned 1000 total points
ID: 39764468
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
 

Author Comment

by:sqldba2013
ID: 39764502
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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 9

Expert Comment

by:sarabhai
ID: 39764515
Replace your [Item Submitted Date]  by GetDate() , you will get result for that date.
0
 
LVL 12

Expert Comment

by:Tony303
ID: 39764587
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39765869
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
 
LVL 12

Expert Comment

by:Tony303
ID: 39766681
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39767369
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
 

Author Closing Comment

by:sqldba2013
ID: 39767372
--
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39768220
>> 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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

721 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