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.

DateTime

PLZ help me!!!
i got to make a access frontend for a MSSQL server.
i need to select some stuff per week how can i make thet? PLZZ HELP I GOT
TO KNOW IT TOMAROW
TNX
Peter
If by "select some stuff per week" you mean calculate aggregates (sum,
min, max, count) per week, you can do something like this:
declare @.baseSunday datetime
set @.baseSunday = '19000107'
select
dateadd(week, datediff(week, @.baseSunday, OrderDate), @.baseSunday)
as WeekBeginning,
count(OrderID) as numOrdersThisWeek
from Northwind..Orders
group by dateadd(week, datediff(week, @.baseSunday, OrderDate), @.baseSunday)
order by dateadd(week, datediff(week, @.baseSunday, OrderDate), @.baseSunday)
go
If you want result rows for weeks that have no corresponding data in
your table, you can create an auxiliary table with all the Sundays you
would need and do
select
Sunday as WeekBeginning,
count(OrderID) as numOrdersThisWeek
from SundayTable left outer join yourDataTable
on yourDataTable.OrderDate >= Sunday
and yourDataTable.OrderDate < DateAdd(week, 1, Sunday)
group by Sunday
order by Sunday
A search of groups.google.com for sqlserver+group+week will probably
give you some other ideas.
Steve Kass
Drew University
Crazy Pete wrote:

>PLZ help me!!!
>i got to make a access frontend for a MSSQL server.
>i need to select some stuff per week how can i make thet? PLZZ HELP I GOT
>TO KNOW IT TOMAROW
>TNX
>Peter
>
>