Datetime and other stuff

Overview. Machine data is fed into DOWNTIMELOG via a Cimplicity. I do not have the ability to change the way that it goes in. It contains the time that a machine has stopped and where the stoppage has occurred. Via a web page I want to give the operator the amount of time the machine stopped. From that they will provide some more detail. As they enter time, I need to subtract what they entered from the total (DOWNTIMELOG). That is the reason I created ENTERED_TIME. [PDNTOTAL_TEMP_VAL0] hold the total time for each stoppage, [TOTTIME] is intended to hold the value that has been accounted for. [DWNCATEGORY_TEMP_VAL0] holds the category and matches up with [DWNCATEGORY]. Also, I need to match Date and Shift. The problem is that DOWNTIMELOG does not capture shift. However, 1st shift occurs between 700 and 1500, 2nd shift occurs between 1500 and 2300, 3rd shift occurs between 2300 and 700. 3rd shift presents a problem as it occurs across 2 dates. Could someone please help me with a query that provides the net between the two tables that is matched by date, shift and category?
CREATE TABLE [dbo].[DOWNTIMELOG] (
[timestamp] [datetime] NOT NULL ,
[DWNTIMESTAMP_VAL0] [varchar] (25) NULL ,
[UPTIMESTAMP_VAL0] [varchar] (25) NULL ,
[PDNHOUR_VAL0] [int] NULL ,
[PDNMIN_VAL0] [int] NULL ,
[PDNSEC_VAL0] [int] NULL ,
[PDNTOTAL_TEMP_VAL0] [int] NULL ,
[DWNMSG_TEMP_VAL0] [int] NULL ,
[DWNCATEGORY_TEMP_VAL0] [varchar] (50) NULL

CREATE TABLE [dbo].[ENTERED_TIME] (
[REC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[DWNCATEGORY] [varchar] (50) NULL ,
[DWNMSG] [int] NULL ,
[ENTRYDATE] [smalldatetime] NULL ,
[SHIFT] [int] NULL ,
[TOTTIME] [int] NULLWould someone please review this approach to getting a shift from a timestamp AND changing the date to the following when the Hour is greater than 23:00? I keep getting nulls for hours outside 7 and 14.
SELECT [timestamp] AS thaTimeStamp, (CASE WHEN DATEPART(hh, [timestamp])
= 23 THEN CAST(FLOOR(CAST(DATEADD(d, 1, [timestamp]) AS Float(53))) AS DateTime) ELSE CAST(FLOOR(CAST([timestamp] AS Float(53)))
AS DateTime) END) AS thaDate, (CASE WHEN DATEPART(hh, [timestamp]) > 6 AND DATEPART(hh, [timestamp]) < 15 THEN 1 WHEN DATEPART(hh,
[timestamp]) > 14 AND DATEPART(hh, [timestamp]) < 23 THEN 2 WHEN CAST(DATEPART(hh, [timestamp]) AS INT) > 23 AND CAST(DATEPART(hh,
[timestamp]) AS INT) < 7 THEN 3 END) AS thaShift, DATEPART(hh, [timestamp]) AS thaOutPut
FROM DOWNTIMELOG
WHERE ([timestamp] >= CONVERT(DATETIME, '2005-12-10 00:00:00', 102))

Out put
thaTimeStamp thaDate thaShift thaOutPut
12/10/2005 6:29:05 AM 12/10/2005 <NULL> 6
12/10/2005 7:18:03 AM 12/10/2005 1 7
12/10/2005 7:22:07 AM 12/10/2005 1 7
12/10/2005 7:24:01 AM 12/10/2005 1 7
12/10/2005 7:24:39 AM 12/10/2005 1 7
12/12/2005 6:06:46 AM 12/12/2005 <NULL> 6
12/12/2005 6:19:20 AM 12/12/2005 <NULL> 6
12/12/2005 6:25:28 AM 12/12/2005 <NULL> 6
12/12/2005 7:12:41 AM 12/12/2005 1 7|||SELECT [timestamp] AS thaTimeStamp
, (CASE
WHEN DATEPART(hh, [timestamp]) = 23 THEN CAST(FLOOR(CAST(DATEADD(d, 1, [timestamp]) AS Float(53))) AS DateTime)
ELSE CAST(FLOOR(CAST([timestamp] AS Float(53))) AS DateTime) END) AS thaDate
, (CASE
WHEN DATEPART(hh, [timestamp]) > 6 AND DATEPART(hh, [timestamp]) < 15 THEN 1
WHEN DATEPART(hh, [timestamp]) > 14 AND DATEPART(hh, [timestamp]) < 23 THEN 2
WHEN CAST(DATEPART(hh, [timestamp]) AS INT) > 23 OR CAST(DATEPART(hh, [timestamp]) AS INT) < 7 THEN 3 END) AS thaShift
, DATEPART(hh, [timestamp]) AS thaOutPut
FROM DOWNTIMELOG
WHERE ([timestamp] >= CONVERT(DATETIME, '2005-12-10 00:00:00', 102))-PatP|||Thanks Pat,
That returns what I need. Now how/can I take this and using temp table join it to the ENTERED_TIME ( as in previous posts) table and provide the net between the two? If so can you give me an example of how to go about this?|||I figured it out. I managed by taking Pat's help and creating a view then joining it to the table. Now sure if this is the best way, but it seems to work.