Need T-SQL Query Help- Urgent

Question

Post reply

Need T-SQL Query Help- Urgent

Mr. Kapsicum

SSCertifiable

Points: 6128
More actions
July 29, 2013 at 1:11 am

#296020

I have a table Called Employee_Attendane
Its result Is:
Employee_idIS_Al Attendance
1998 1 2013-03-18
1998 1 2013-03-19
1998 1 2013-03-21
2830 1 2013-03-04
2830 1 2013-03-05
2830 1 2013-03-06
Here, for employee_id=1998, its IS_AL=1 for Date '2013-03-18','2013-03-19', '2013-03-21'
And My required Result IS:
Employee_idFrom_date To_date
1998 2013-03-18 2013-03-19
1998 2013-03-21 2013-03-21
2830 2013-03-04 2013-03-06
please help with T-SQL QUERY.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

DROP TABLE #MySampleData

CREATE TABLE #MySampleData (Employee_id INT, IS_Al INT, Attendance DATE)

INSERT INTO #MySampleData (Employee_id, IS_Al, Attendance)

SELECT 1998, 1, '2013-03-18' UNION ALL

SELECT 1998, 1, '2013-03-19' UNION ALL

SELECT 1998, 1, '2013-03-21' UNION ALL

SELECT 1998, 1, '2013-03-23' UNION ALL

SELECT 2830, 1, '2013-03-04' UNION ALL

SELECT 2830, 1, '2013-03-05' UNION ALL

SELECT 2830, 1, '2013-03-06';

WITH TweakedData AS (

SELECT Employee_id, IS_Al, Attendance,

seq1 = DATEDIFF(DD,0,Attendance),

seq2 = ROW_NUMBER() OVER(ORDER BY Attendance)

FROM #MySampleData

)

SELECT

Employee_id,

--IS_Al,

From_date = MIN(Attendance),

To_date = MAX(Attendance)

FROM TweakedData a

GROUP BY Employee_id, IS_Al, seq1-seq2

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 2

Thank you Mr. Chris

The query is JUST PERFECT.

Thanks a TON. 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Thanks for the very generous feedback, Mr Kapsicum.

If you're interested in how the method works, here's an excellent article by Dwain Camps[/url].

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Alan Burstein SSC Guru Points: 61136 More actions · Answer 4

ChrisM@Work (7/29/2013)
Thanks for the very generous feedback, Mr Kapsicum.
If you're interested in how the method works, here's an excellent article by Dwain Camps[/url].

I have nothing to add to this thread except to say that I just looked at the Dwain Camps' gaps/islands article you posted - it's excellent. Thanks for posting that Chris.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Dwain Camps SSC Guru Points: 86908 More actions · Answer 5

ChrisM@Work (7/29/2013)
Thanks for the very generous feedback, Mr Kapsicum.
If you're interested in how the method works, here's an excellent article by Dwain Camps[/url].

Oh my! The word is out.

And here I was hoping to fly under the radar.

Actually, Jeff Moden's SQL Spackle article Group Islands of Contiguous Dates[/url] probably offers a better description of the approach you took. Not to mention that it is directly about dates.

But I do appreciate the plug. You too Alan.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St