Solved

Need help on SQL query

Posted on 2014-01-07
10
687 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
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 48

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 250 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 69

Expert Comment

by:ScottPletcher
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 48

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 69

Expert Comment

by:ScottPletcher
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now