Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123

    Unanswered: 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] NULL

  2. #2
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123

    Been Trying

    Would 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

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    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

  4. #4
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123

    Step 1 works

    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?

  5. #5
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123

    Thanks!

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •