Sql Query

[Richard Deeming]

You can get the data for any weeks which overlap the selected date range with:

WHERE
WeekStartDate <= @SelectedEndDate
And
WeekEndDate >= @SelectedStartDate

For your example, that would give you the data between 2013-08-12 and 2013-09-01. Since the data for a week appears to be on a single row, it's not obvious how you intend to return only part of a week.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Srinivas Uttareshwar]

from this query we are getting data for whole week but selected startdate may be thur or any day i need data from that particular day to the particular enddate selected

[Richard Deeming]

As I said, since the data for the entire week appears to be on a single row, it's not obvious how you intend to return only part of a week. Once you explain that, then we might be able to help you. :)

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Srinivas Uttareshwar]

any alternate solution to go with suggest please.

[Richard Deeming]

Since you haven't explained how you want the data to be returned, how is anyone supposed to suggest a solution?!

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Srinivas Uttareshwar]

from selected date i need date,day of the week and no of hrs he worked on that day and same till the end date selected

[Richard Deeming]

So you essentially want one row per day?

DECLARE @SelectedStartDate date = '20130815';
DECLARE @SelectedEndDate date = '20130830';

WITH cteOverlappingWeeks As
(
SELECT
StartDate,
MON,
TUE,
WED,
THUR,
FRI,
SAT,
SUN
FROM
dbo.Timesheet
WHERE
StartDate <= @SelectedEndDate
And
EndDate >= @SelectedStartDate
),
ctePivotedWeeks (WorkDay, Hours) As
(
SELECT
StartDate,
MON
FROM
cteOverlappingWeeks

UNION ALL

SELECT
    DateAdd(day, 1, StartDate),
    TUE
FROM
    cteOverlappingWeeks

UNION ALL

SELECT
    DateAdd(day, 2, StartDate),
    WED
FROM
    cteOverlappingWeeks

UNION ALL

SELECT
    DateAdd(day, 3, StartDate),
    THUR
FROM
    cteOverlappingWeeks

UNION ALL

SELECT
    DateAdd(day, 4, StartDate),
    FRI
FROM
    cteOverlappingWeeks

UNION ALL

SELECT
    DateAdd(day, 5, StartDate),
    SAT
FROM
    cteOverlappingWeeks

UNION ALL

SELECT
    DateAdd(day, 6, StartDate),
    SUN
FROM
    cteOverlappingWeeks

)
SELECT
WorkDay,
Hours
FROM
ctePivotedWeeks
WHERE
WorkDay Between @SelectedStartDate And @SelectedEndDate
ORDER BY
WorkDay
;

Example: http://www.sqlfiddle.com/#!3/b8a7a/1[^]

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Simon_Whale]

:thumbsup: Nice tool never knew about SQLFiddle Thanks :laugh:

Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON

[Mycroft Holmes]

The reason Richard had so much trouble with the query is that your data structure is atrocious, you need to fix that otherwise you are going to have continuing and compounding problems in future. Your time recording table should have 1 record per person per date. The the query would be a very simple one instead of that horror Richard had to supply! He deserves the upvote just for dealing with your structure!

Never underestimate the power of human stupidity RAH

[Jorgen Andersson]

Have another upvote for the sqlfiddle link.

Be excellent to each other. And... PARTY ON, DUDES! Abraham Lincoln