June 21, 2013 at 11:31 am
This stemmed from my previous post yesterday but is a completely different question
What i Need to do: i Need to have the first Enddate Time of the first ID group to be set to 0
and the DateStart Time of the last of the IDs be set to 0
if the dateStart time is greater than dateEnd time
The end result is so that i can find out how many hours are in the AM and PM
The amount of Ids can vary but will always be different
Any help or suggestions would be appreciated
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
IF OBJECT_ID('TempDB..#outputtable','U') IS NOT NULL
DROP TABLE #outputtable
--------Create test table----------
CREATE TABLE #mytable
(
DateStart DATETIME,
DateEnd DATETIME,
ID int
)
---------Sample Data-------
Insert into #mytable (DateStart,DateEnd,ID)
Select '2013-05-25 17:00:00.000','2013-05-25 07:15:00.000', 879 UNION ALL
Select '2013-05-26 17:00:00.000','2013-05-26 07:15:00.000',879 UNION ALL
Select '2013-05-27 17:00:00.000','2013-05-27 07:15:00.000',879 Union ALL
Select '2013-05-31 17:00:00.000','2013-05-31 07:15:00.000',880 union ALL
Select '2013-06-01 17:00:00.000','2013-06-01 07:15:00.000',880 Union All
Select '2013-06-02 17:00:00.000','2013-06-02 07:15:00.000',880 union ALL
Select '2013-03-15 08:00:00.000','2013-03-15 17:00:00.000',1266 Union ALL
Select '2013-03-16 08:00:00.000','2013-03-16 17:00:00.000',1266 union ALL
Select '2013-03-17 08:00:00.000','2013-03-17 17:00:00.000',1266
--------Create desired Output table----------
CREATE TABLE #outputtable
(
DateStart DATETIME,
DateEnd DATETIME,
ID int
)
---------OutPut Data-------
Insert into #outPutTable(DateStart,DateEnd,ID)
Select '2013-05-25 17:00:00.000','2013-05-25 00:00:00.000', 879 UNION ALL -- the EndDate Time has changed to 0
Select '2013-05-26 17:00:00.000','2013-05-26 07:15:00.000',879 UNION ALL
Select '2013-05-27 00:00:00.000','2013-05-27 07:15:00.000',879 Union ALL -- the StartDate time has changed to 0
Select '2013-05-31 17:00:00.000','2013-05-31 00:00:00.000',880 union ALL -- the EndDate Time has changed to 0
Select '2013-06-01 17:00:00.000','2013-06-01 07:15:00.000',880 Union All
Select '2013-06-02 00:00:00.000','2013-06-02 07:15:00.000',880 union ALL -- the startDate Time has changed to 0
Select '2013-03-15 08:00:00.000','2013-03-15 17:00:00.000',1266 Union ALL-- this set is fine because the startdate time is less then enddate time
Select '2013-03-16 08:00:00.000','2013-03-16 17:00:00.000',1266 union ALL
Select '2013-03-17 08:00:00.000','2013-03-17 17:00:00.000',1266
select * from #mytable
select * from #OutPutTable
/**
What i Need to do: i Need to have the first Enddate Time of the first ID to be set to 0
and the DateStart Time of the last of the ID group be set to 0
if the dateStart time is greater than dateEnd time
**/
The amount of Ids can vary but will always be different
June 21, 2013 at 11:50 am
Would this help you?
SELECT CASE WHEN DateEnd = MAX(DateEnd) OVER( PARTITION BY ID)
AND DateStart > DateEnd
THEN DATEADD( dd, DATEDIFF( dd, 0,DateEnd),0)
ELSE DateStart END,
CASE WHEN DateStart = MIN(DateStart) OVER( PARTITION BY ID)
AND DateStart > DateEnd
THEN DATEADD( dd, DATEDIFF( dd, 0,DateStart),0)
ELSE DateEnd END,
ID
FROM #mytable
June 21, 2013 at 12:05 pm
Not sure how it works (ill have to study Over and Partition By) but all i had to do was add a extra case statement for the dateEnd Time being greater than DateStart Time, and it worked
thank you
Here is the code with a extra case statement that i added in case it helps someone else down the road
SELECT
case when Convert(time,DateEnd) > Convert(time,DateStart)
Then dateStart
else CASE
WHEN DateEnd = MAX(DateEnd) OVER( PARTITION BY ID)
THEN DATEADD( dd, DATEDIFF( dd, 0,DateEnd),0)
ELSE DateStart END
end as DateStart,
case when Convert(time,DateEnd) > Convert(time,DateStart)
Then dateend
else CASE
WHEN DateStart = MIN(DateStart) OVER( PARTITION BY ID)
THEN DATEADD( dd, DATEDIFF( dd, 0,DateStart),0)
ELSE DateEnd
END
end as DateEnd,
ID
FROM #mytable
June 21, 2013 at 12:10 pm
I'm sorry, I edited my response because I saw a rule when I was checking my post.
I'm sure you don't need an extra case, just the correct set of conditions. 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply