Solved

Need help on SQL query

Posted on 2014-01-07
10
690 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 69

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 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