Sql Query

Srinivas Uttareshwar

i'm using sql server 2008 i'm having table where timesheet is filled weekely i'e from monday to sunday and i'm saving start and end date of the week but when retrieving the data dates selected may be any day of the week. example if selected date is 15/8/2013 and 30/8/2013 and dates saved in table is from 2013-08-12(mon) 2013-08-18(sun) 2013-08-19 2013-08-25 2013-08-26 2013-09-01 2013-09-02 2013-09-08 2013-07-08 2013-07-14 2013-06-03 2013-06-09 2013-07-22 2013-07-28 MON TUE WED THUR FRI SAT SUN 8 8 7 7 7 7 7 8 8 8 8 8 8 8 0 8 8 8 8 8 8 0 8 8 8 8 8 0 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 i need data between 15/8/2013 to 30/8/2013

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