November 18, 2005 at 8:20 am
I have listed a view below and a portion of the result set that is returned when I run the code in Query Analyzer. This is part of a timesheet application that logs hours per SCHLSTUID per SECTIONID per week. This returns the SCHLSTUID(user's ID), SECTIONID, Date that the week starts, the first date that time was logged. The user could be in several SECTIONID's for the same week. I need to modify this so that it returns the date that the first time was logged for any of the SECTIONID's per week. I know that this is probably something simple that I'm overlooking but I just can't get it to work correctly.
Example:
SCHLSTUID SECTIONID ATTSTARTDT FirstTimeEnteredDOn
601868445 EN4AR001 2005-09-18 20:59:21.120 2005-09-19 20:59:21.120
601868445 MAA1R001 2005-09-18 20:59:21.120 2005-09-18 20:59:21.120
This would need to return 2005-09-18 20:59:21.120
------------------------------------------------------------------------------------------------------
601868445 EN4AR001 2005-10-02 20:59:37.427 2005-10-02 20:59:37.427
601868445 MAA1R001 2005-10-02 20:59:37.427 2005-10-02 20:59:37.427
This would need to return either 2005-10-02 20:59:37.427
------------------------------------------------------------------------------------------------------
601868445 EN4AR001 2005-10-09 20:59:37.823 2005-10-09 20:59:37.823
601868445 MAA1R001 2005-10-09 20:59:37.823 2005-10-13 20:59:37.823
This would need to return 2005-10-09 20:59:37.823
----------------------------------------------------------------------------------------------------------------------------------------------------
CREATE VIEW dbo.vExportStartWeek
AS
SELECT TOP 100 PERCENT schlstuid, sectionid, ATTSTARTDT, MIN(TimesheetDate) AS FirstTimeEnteredOn
FROM (SELECT schlstuid, sectionid, ATTSTARTDT, ATTSTARTDT AS TimesheetDate, sunmns AS TimeEntered
FROM TimeSheetDailyAttendance
UNION ALL
SELECT schlstuid, sectionid, ATTSTARTDT, ATTSTARTDT AS TimesheetDate, sunhrs AS TimeEntered
FROM TimeSheetDailyAttendance
UNION ALL
SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 1, ATTSTARTDT) AS TimesheetDate, monmns AS TimeEntered
FROM TimeSheetDailyAttendance
UNION ALL
SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 1, ATTSTARTDT) AS TimesheetDate, monhrs AS TimeEntered
FROM TimeSheetDailyAttendance
UNION ALL
SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 2, ATTSTARTDT) AS TimesheetDate, tuemns AS TimeEntered
FROM TimeSheetDailyAttendance
UNION ALL
SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 2, ATTSTARTDT) AS TimesheetDate, tuehrs AS TimeEntered
FROM TimeSheetDailyAttendance
UNION ALL
SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 3, ATTSTARTDT) AS TimesheetDate, wedmns AS TimeEntered
FROM TimeSheetDailyAttendance
UNION ALL
SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 3, ATTSTARTDT) AS TimesheetDate, wedhrs AS TimeEntered
FROM TimeSheetDailyAttendance
UNION ALL
SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 4, ATTSTARTDT) AS TimesheetDate, Thrmns AS TimeEntered
FROM TimeSheetDailyAttendance
UNION ALL
SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 4, ATTSTARTDT) AS TimesheetDate, Thrhrs AS TimeEntered
FROM TimeSheetDailyAttendance
UNION ALL
SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 5, ATTSTARTDT) AS TimesheetDate, Frimns AS TimeEntered
FROM TimeSheetDailyAttendance
UNION ALL
SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 5, ATTSTARTDT) AS TimesheetDate, Frihrs AS TimeEntered
FROM TimeSheetDailyAttendance
UNION ALL
SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 6, ATTSTARTDT) AS TimesheetDate, Satmns AS TimeEntered
FROM TimeSheetDailyAttendance
UNION ALL
SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 6, ATTSTARTDT) AS TimesheetDate, Sathrs AS TimeEntered
FROM TimeSheetDailyAttendance) TimesheetDates
WHERE (TimeEntered <> 0)
GROUP BY schlstuid, sectionid, ATTSTARTDT
ORDER BY schlstuid
----------------------------------------------------------------------------------------------------------------------------------------------------
This is a portion of what is returned:
SCHLSTUID SECTIONID ATTSTARTDT FirstTimeEnteredDOn
601868445 EN4AR001 2005-09-18 20:59:21.120 2005-09-19 20:59:21.120
601868445 MAA1R001 2005-09-18 20:59:21.120 2005-09-18 20:59:21.120
601868445 EN4AR001 2005-09-25 20:59:36.670 2005-09-25 20:59:36.670
601868445 EN4AR001 2005-10-02 20:59:37.427 2005-10-02 20:59:37.427
601868445 MAA1R001 2005-10-02 20:59:37.427 2005-10-02 20:59:37.427
601868445 EN4AR001 2005-10-09 20:59:37.823 2005-10-09 20:59:37.823
601868445 MAA1R001 2005-10-09 20:59:37.823 2005-10-13 20:59:37.823
----------------------------------------------------------------------------------------------------------------------------------------------------
Thank you for any help that you can give me.
Scott
November 18, 2005 at 2:22 pm
OK, maybe I'm being stupid, but I cannot see why you have all those UNIONS:
You are getting MIN TimeSheetDate and TimeSheetDate is done as a 7 different date adds of 0-6 days to a given date.
So when is MIN TimeSheetDate ever going to not be = to ATTSTARTDT?
Is there something you're not showing here?
Regardless, you are probably going to want to use a case statement, which you can embed within an aggregate:
MIN( CASE WHEN date1<date2 THEN date1 ELSE date2 END )
November 18, 2005 at 2:45 pm
Here is why I'm using all of the unions:
The application stores time that is entered by week. The only date that is tied to the week is the day that the week starts on. All timesheets start on Sunday. I needed to find the day of the week where the first time was entered and the day of the week where the last time was entered for every timesheet.
example:
Week Starts 08-07-2005 - that is a sunday. First time was entered on tuesday so this adds 2 to the attstartdt and returns returns 08-09-2005. I know that it is confusing but the requirements changed half way through the development process. Origonally I was told that the start of the week must be Sunday, then I was told that the start of the week must be the day of the week that time was first entered, for the same "week" that starts on Sunday.
Anyway, thanks for your advice. I just made a view off of this view that pulled what I needed. I know that's probably not the most efficient method but it worked for now.
Thanks again,
Scott
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply