Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jan 2005
    Location
    Zurich Switzerland
    Posts
    17

    Smile Unanswered: Time frame "between 3:00am and 4:00am"

    Hi! all
    How do I get student_ID's that attended prep between 3:00am and 4:00 am on daily basis from table below

    CREATE TABLE [dbo].[PrepTime] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [PrepNo] [int] NOT NULL ,
    [Log_Time] datetime NOT NULL DEFAULT, CURRENT_TIMESTAMP,
    [coming] [int] NULL ,
    [going] [int] NULL ,
    [Student_ID] [bigint] NULL ,
    [Study_Type] [int] NULL ,
    [State] [smallint] NULL

    ) ON [PRIMARY]
    GO

    Thank you in Advance
    Programming could be Addictive!!!

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Post

    SELECT <....>
    FROM PrepTime
    WHERE datePart(hour, Log_Time) Between 3 AND 4

    Hope this helps
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    DoktorBlue, won't that give the times from 3:00 to 4:59?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2005
    Location
    Zurich Switzerland
    Posts
    17

    Smile Did not work

    Hi!
    Did not work I have this error message

    "Server: Msg 156, Level 15, State 1, Line 4
    Incorrect syntax near the keyword 'Between'.
    "

    Thanks
    Programming could be Addictive!!!

  5. #5
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Between should work, please post your query.

    However, R2D2 ( sorry for making fun of your id) is right, you don't even need BETWEEN. Its already sufficient to have

    SELECT <....>
    FROM PrepTime
    WHERE datePart(hour, Log_Time) = 3
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  6. #6
    Join Date
    Jan 2005
    Location
    Zurich Switzerland
    Posts
    17

    Exclamation Here is the code

    I have these values in the Table


    1062 1091 1/8/2005 3:43:57 PM 4 4
    1063 1092 1/8/2005 3:43:57 PM 4 4
    1064 1093 1/8/2005 3:43:57 PM 4 4
    1065 1094 1/8/2005 3:43:57 PM 4 4
    1067 1096 1/8/2005 3:43:57 PM 4 4
    1068 1000 1/8/2005 4:45:57 PM 1 1
    1069 1001 1/8/2005 4:45:57 PM 1 1
    1070 1002 1/8/2005 4:45:57 PM 1 1
    1071 1003 1/8/2005 4:45:57 PM 1 1
    1072 1004 1/8/2005 4:45:57 PM 1 1
    1073 1005 1/8/2005 4:45:57 PM 1 1


    query

    SELECT *

    FROM PrepTime
    WHERE datePart(hour, Log_Time)= Between 3 AND 4


    Thanks in Advance
    Programming could be Addictive!!!

  7. #7
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    you asked for 3 am, but your data is of 3 and 4 pm, so you need to have 15 and 16 instead of 3 and 4.

    Do you need both 3 pm and 4pm, your original question was between 3 and 4, which would exclude 4.

    Your syntax error is that you have a "=" sign before the between operator, which isn't allowed here.

    This statement will select the rows in your table.

    SELECT *
    FROM PrepTime
    WHERE datePart(hour, Log_Time) Between 15 AND 16
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ZurinetA, the equal sign is wrong when you use BETWEEN

    DoctorBlew, WHERE hour=3 is problematic, as this excludes 4:00, so if you interpret the original requirements "between 3:00am and 4:00 am" as excluding 4:00, then you should probably also exclude 3:00



    ZurinetA, try this --

    where convert(char(5),Log_Time,14) between '03:00' and '04:00'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Quote Originally Posted by r937
    DoctorBlew
    Thank you for the "e"!
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  10. #10
    Join Date
    Jan 2005
    Location
    Zurich Switzerland
    Posts
    17

    Thumbs up It worked

    Thank you DoktorBlue
    It worked... You guys are helpfull

    Nice Weekend
    Programming could be Addictive!!!

  11. #11
    Join Date
    Jan 2005
    Location
    Zurich Switzerland
    Posts
    17

    Smile 30mins

    What will be the diffrence if I want query for only 30mins time diffrence?

    Thanks in Advance
    Programming could be Addictive!!!

  12. #12
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    You can consider to use r937's solution, specifying your time frame as strings.

    Alternatively, you can use the DatePart function also to get the minutes of a timestamp, like

    SELECT *
    FROM PrepTime
    WHERE datePart(hour, Log_Time) * 60 + datePart(minute, Log_Time) Between 15*60 +30 AND 16 *60
    to get everything between 03:30 PM and 04:00 PM
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  13. #13
    Join Date
    Jan 2005
    Location
    Zurich Switzerland
    Posts
    17

    Question Amount of time Spent in Minutes

    Hi! I would like to know if it's possible to calculate the amount of time in Minutes spent by each student in Prep. Bearing in mind that coming and going of each student have diffrent state. eg coming State 1 going State 2.


    Quote Originally Posted by ZurinetA
    Hi! all
    How do I get student_ID's that attended prep between 3:00am and 4:00 am on daily basis from table below

    CREATE TABLE [dbo].[PrepTime] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [PrepNo] [int] NOT NULL ,
    [Log_Time] datetime NOT NULL DEFAULT, CURRENT_TIMESTAMP,
    [coming] [int] NULL ,
    [going] [int] NULL ,
    [Student_ID] [bigint] NULL ,
    [Study_Type] [int] NULL ,
    [State] [smallint] NULL

    ) ON [PRIMARY]
    GO

    Thank you in Advance
    Programming could be Addictive!!!

  14. #14
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Sounds like home work, what is the title?

    Since arrival and departure time are in two different records, you need to join both records. Question is: on what? You will need probably the Student_ID, but since a student cames and goes more than once, you will also need to take the same day. However, what is the meaning of [PrepNo], [Coming] and [Going]? Moreover, which values may [State] have? Finally notice, that [Student_Type] should not belong here, its functional dependiong on [Student_ID] and should be stored in the Student table.

    Let me know if these tips are sufficient for you to solve the problem by yourself.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  15. #15
    Join Date
    Jan 2005
    Location
    Zurich Switzerland
    Posts
    17
    Quote Originally Posted by DoktorBlue
    Sounds like home work, what is the title?



    Let me know if these tips are sufficient for you to solve the problem by yourself.
    Hi! DoktorBlue :-)
    You have been most helpful.. Yes your are right this happen to be my School Project Work :-)

    Here is an example of my Table

    Stu_ID State Log_Time Movment
    0012 1 3:25:57 PM coming
    0014 1 3:25:59 PM coming
    0015 1 3:30:10 PM coming
    0012 2 3:50:57 PM going
    0020 1 3:51:10 PM coming
    0014 2 3:59:57 PM going

    How do I calculate how much time spent by all the students in the Prep period bearing in mind that each Prep last for 60mins.. And how much time spent by each student in Mins. Eg. Student with ID 0012 spent 25mins.. However I have to put this in SP_Query..

    Thanks a lot for your help in Advance

    Ed
    Programming could be Addictive!!!

Posting Permissions

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