Professional Web Applications Themes

SQL Query - Microsoft SQL / MS SQL Server

Can any one help me. Here is the table Create table test3( starttime varchar(50), endtime varchar(50)) insert into test3 values('2003-07-14 00:02:34','2003-07-14 00:05:57') insert into test3 values('2003-07-14 00:09:43','2003-07-14 00:09:51') insert into test3 values('2003-07-14 00:17:01','2003-07-14 00:17:35') I want to find out the Average time, Totaltime My Output should be Total No Average Time Total Time 3 00:01:21 00:03:65 I am subtracting timeend - timestart and adding all the three values for Total Time and for average time is(Total Time/3) Note : Time Start and Time End are Datetime Datatype in my table *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in ...

  1. #1

    Default SQL Query

    Can any one help me.

    Here is the table

    Create table test3(
    starttime varchar(50),
    endtime varchar(50))

    insert into test3 values('2003-07-14 00:02:34','2003-07-14 00:05:57')
    insert into test3 values('2003-07-14 00:09:43','2003-07-14 00:09:51')
    insert into test3 values('2003-07-14 00:17:01','2003-07-14 00:17:35')

    I want to find out the Average time, Totaltime

    My Output should be

    Total No Average Time Total Time
    3 00:01:21 00:03:65

    I am subtracting timeend - timestart and adding all the three values for
    Total Time and for average time is(Total Time/3)

    Note : Time Start and Time End are Datetime Datatype in my table




    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Madhu Guest

  2. #2

    Default SQL Query

    Hi,

    Madhu Try this

    select
    (select count(*) from test4) as [Total No]
    , convert(varchar(2),avg (datediff( day,convert
    (datetime,starttime),convert(datetime,endtime)))) + ':' +
    convert(varchar(2),(avg(datediff( hour,convert
    (datetime,starttime),convert(datetime,endtime)))%6 0))+ ':'
    +
    convert(varchar(2),(avg (datediff( minute,convert
    (datetime,starttime),convert(datetime,endtime)))%6 0))
    + ':'+
    convert(varchar(2),(avg (datediff( Second,convert
    (datetime,starttime),convert(datetime,endtime)))%6 0))
    as [Average Time],
    convert(varchar(2),Sum (datediff( day,convert
    (datetime,starttime),convert(datetime,endtime)))) + ':' +
    convert(varchar(2),(sum(datediff( hour,convert
    (datetime,starttime),convert(datetime,endtime)))%6 0))+ ':'
    +
    convert(varchar(2),(sum (datediff( minute,convert
    (datetime,starttime),convert(datetime,endtime)))%6 0))
    + ':'+
    convert(varchar(2),(sum (datediff( Second,convert
    (datetime,starttime),convert(datetime,endtime)))%6 0))
    as [Total Time]
    from test4

    Should work fine

    Kannan


     
    14 00:05:57') 
    14 00:09:51') 
    14 00:17:35') 
    three values for 
    my table 
    *** 
    Kannan Guest

  3. #3

    Default SQL Query

    Hi all

    I need to write an SQL like

    Select distinct column1 from table1
    where column1='Test'
    Group by Column1
    Order by Column2

    B'coz ORDER BY items must appear in the select list if
    SELECT DISTINCT is specified.

    Is it possible using sub query we can sole this. I don't
    want to use a temp table.

    Thanks in advance
    Anand
    Anand Guest

  4. #4

    Default Re: SQL Query

    Okay, I am in no way a querying expert or even a novice, but "select
    distinct column1 where column1='something'" will always return just one
    single record, yes? So, why would ordering even matter, unless you're
    trying to get just one column with a lowest column2 value. If that's the
    case, I'd do:

    select top 1 column1 where column1='Test' order by column2

    Ray at home

    --
    Will trade ASP help for SQL Server help


    "Anand" <com> wrote in message
    news:072101c358ba$1523b990$gbl... 


    Ray Guest

  5. #5

    Default Re: SQL Query

    then i wonder what does a DISTINCT do with a GROUP BY clause!?

    re the original question:

    select country
    from customers
    group by country
    order by max(companyname)

    works just fine in Northind db.

    cheers,
    </wqw>

    "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
    news:#phx.gbl... 
    >
    >[/ref]


    Vlad Guest

  6. #6

    Default Re: SQL Query

    You're missing the "Where country='something'" line, which was what Ray was
    commenting on. Put that line into the query, and then tell us what the use
    of either the "group by" or "order by" clauses is.

    Bob Barrows

    Vlad Vissoultchev wrote: [/ref][/ref]



    Bob Guest

  7. #7

    Default Re: SQL Query

    i'm not "missing" it -- just "adding" more food for thought :-)) something's
    fundamentally wrong with the query/question.

    cheers,
    </wqw>

    "Bob Barrows" <com> wrote in message
    news:#phx.gbl... 
    was [/ref]
    >
    >
    >[/ref]


    Vlad Guest

  8. #8

    Default SQL query

    Hi all -

    I am trying to generate a query that returns a subset of data from a table.
    Here is the table:

    S_N DATE_TIME
    001 8/8/03 10:00 AM
    001 8/8/03 9:00 AM
    001 8/8/03 8:00 AM
    001 8/7/03 9:00 AM
    001 8/7/03 8:00 AM
    002 8/8/03 9:00 AM
    002 8/8/03 8:00 AM
    002 8/7/03 8:00 AM
    003 8/8/03 10:00 AM
    003 8/8/03 9:00 AM
    003 8/8/03 8:00 AM

    Each record contains a serial number (S_N) and the DATE_TIME the unit was
    programmed.

    I am trying to get a list of the Serial numbers and the latest programming
    attempt for a particular day. For example, in this data set only the
    indicated records would be returned.

    S_N DATE_TIME
    001 8/8/03 10:00 AM <--- Latest one on this date
    001 8/8/03 9:00 AM
    001 8/8/03 8:00 AM
    001 8/7/03 9:00 AM <--- Latest one on this date
    001 8/7/03 8:00 AM
    002 8/8/03 9:00 AM <--- Latest one on this date
    002 8/8/03 8:00 AM
    002 8/7/03 8:00 AM <--- Latest (and only) one on this date
    003 8/8/03 10:00 AM <--- Latest one on this date
    003 8/8/03 9:00 AM
    003 8/8/03 8:00 AM

    How can I do this?

    Thanks,

    Paul Hastings


    Paul Guest

  9. #9

    Default Re: SQL query

    something like this...
    --tb has only these two columns
    select s_n,max(date_time) dt
    from tb
    group by s_n

    --tb has other columns
    select *
    from tb t1
    where date_time=(select max(date_time) from tb t2 where t2.s_n=t1.s_n)

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Paul Hastings" <jorge-ga.com> wrote in message
    news:phx.gbl... 
    table. 


    oj Guest

  10. #10

    Default Re: SQL query

    SELECT S_N, CONVERT(CHAR(8), DATE_TIME, 112),
    MAX(DATE_TIME)
    FROM <table_name_not_specified>
    GROUP BY
    S_N, CONVERT(CHAR(8), DATE_TIME, 112)



    "Paul Hastings" <jorge-ga.com> wrote in message
    news:phx.gbl... 
    table. 


    Aaron Guest

  11. #11

    Default Re: SQL query

    Try:

    select
    *
    from
    MyTable o
    where
    o.DATE_TIME =
    (
    select
    max (i.DATE_TIME)
    from
    MyTable i
    where
    i.S_N = o.S_N
    and convert (char (8), i.DATE_TIME) = convert (char (8), o.DATE_TIME)
    )


    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Paul Hastings" <jorge-ga.com> wrote in message news:phx.gbl...
    Hi all -

    I am trying to generate a query that returns a subset of data from a table.
    Here is the table:

    S_N DATE_TIME
    001 8/8/03 10:00 AM
    001 8/8/03 9:00 AM
    001 8/8/03 8:00 AM
    001 8/7/03 9:00 AM
    001 8/7/03 8:00 AM
    002 8/8/03 9:00 AM
    002 8/8/03 8:00 AM
    002 8/7/03 8:00 AM
    003 8/8/03 10:00 AM
    003 8/8/03 9:00 AM
    003 8/8/03 8:00 AM

    Each record contains a serial number (S_N) and the DATE_TIME the unit was
    programmed.

    I am trying to get a list of the Serial numbers and the latest programming
    attempt for a particular day. For example, in this data set only the
    indicated records would be returned.

    S_N DATE_TIME
    001 8/8/03 10:00 AM <--- Latest one on this date
    001 8/8/03 9:00 AM
    001 8/8/03 8:00 AM
    001 8/7/03 9:00 AM <--- Latest one on this date
    001 8/7/03 8:00 AM
    002 8/8/03 9:00 AM <--- Latest one on this date
    002 8/8/03 8:00 AM
    002 8/7/03 8:00 AM <--- Latest (and only) one on this date
    003 8/8/03 10:00 AM <--- Latest one on this date
    003 8/8/03 9:00 AM
    003 8/8/03 8:00 AM

    How can I do this?

    Thanks,

    Paul Hastings



    Tom Guest

  12. #12

    Default Re: SQL query

    He doesn't want the last date per S_N. He wants the last entry per day per S_N.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "oj" <com> wrote in message news:phx.gbl...
    something like this...
    --tb has only these two columns
    select s_n,max(date_time) dt
    from tb
    group by s_n

    --tb has other columns
    select *
    from tb t1
    where date_time=(select max(date_time) from tb t2 where t2.s_n=t1.s_n)

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Paul Hastings" <jorge-ga.com> wrote in message
    news:phx.gbl... 
    table. 



    Tom Guest

  13. #13

    Default Re: SQL query

    So did my solution work for ya?

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Paul Hastings" <jorge-ga.com> wrote in message news:phx.gbl...
    Yep. Thats a fact - Just the last entry per day.

    Paul
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:%phx.gbl...
    He doesn't want the last date per S_N. He wants the last entry per day per S_N.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "oj" <com> wrote in message news:phx.gbl...
    something like this...
    --tb has only these two columns
    select s_n,max(date_time) dt
    from tb
    group by s_n

    --tb has other columns
    select *
    from tb t1
    where date_time=(select max(date_time) from tb t2 where t2.s_n=t1.s_n)

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Paul Hastings" <jorge-ga.com> wrote in message
    news:phx.gbl... 
    table. 



    Tom Guest

  14. #14

    Default Re: SQL query

    Yep. Just got done testing it. Works like a champ!

    Thanks much,

    Paul
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:%phx.gbl...
    Try:

    select
    *
    from
    MyTable o
    where
    o.DATE_TIME =
    (
    select
    max (i.DATE_TIME)
    from
    MyTable i
    where
    i.S_N = o.S_N
    and convert (char (8), i.DATE_TIME) = convert (char (8), o.DATE_TIME)
    )


    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Paul Hastings" <jorge-ga.com> wrote in message news:phx.gbl...
    Hi all -

    I am trying to generate a query that returns a subset of data from a table.
    Here is the table:

    S_N DATE_TIME
    001 8/8/03 10:00 AM
    001 8/8/03 9:00 AM
    001 8/8/03 8:00 AM
    001 8/7/03 9:00 AM
    001 8/7/03 8:00 AM
    002 8/8/03 9:00 AM
    002 8/8/03 8:00 AM
    002 8/7/03 8:00 AM
    003 8/8/03 10:00 AM
    003 8/8/03 9:00 AM
    003 8/8/03 8:00 AM

    Each record contains a serial number (S_N) and the DATE_TIME the unit was
    programmed.

    I am trying to get a list of the Serial numbers and the latest programming
    attempt for a particular day. For example, in this data set only the
    indicated records would be returned.

    S_N DATE_TIME
    001 8/8/03 10:00 AM <--- Latest one on this date
    001 8/8/03 9:00 AM
    001 8/8/03 8:00 AM
    001 8/7/03 9:00 AM <--- Latest one on this date
    001 8/7/03 8:00 AM
    002 8/8/03 9:00 AM <--- Latest one on this date
    002 8/8/03 8:00 AM
    002 8/7/03 8:00 AM <--- Latest (and only) one on this date
    003 8/8/03 10:00 AM <--- Latest one on this date
    003 8/8/03 9:00 AM
    003 8/8/03 8:00 AM

    How can I do this?

    Thanks,

    Paul Hastings


    Paul Guest

  15. #15

    Default Re: SQL query

    sorry...didn't read the whole post thoroughly...adding "and convert(varchar,t1.date_time,112)=convert(varchar, t2.date_time,112)" to the sub query should do.

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Tom Moreau" <spam.me.cips.ca> wrote in message news:#phx.gbl...
    He doesn't want the last date per S_N. He wants the last entry per day per S_N.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "oj" <com> wrote in message news:phx.gbl...
    something like this...
    --tb has only these two columns
    select s_n,max(date_time) dt
    from tb
    group by s_n

    --tb has other columns
    select *
    from tb t1
    where date_time=(select max(date_time) from tb t2 where t2.s_n=t1.s_n)

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Paul Hastings" <jorge-ga.com> wrote in message
    news:phx.gbl... 
    table. 



    oj Guest

  16. #16

    Default SQL Query

    Hi Folks,
    I have two tables which I would like to query and return
    a result set. Here is an example of the two tables and
    the result set.

    table1
    CH_ID D_ID GRD CRS
    Al 06 P En
    Al 11 P Ma
    Al 12 P Ch
    Al 12 P Gy

    Table2
    CH_ID D_ID ADV_CRS
    Al 06 En2
    Al 11 Ma2
    Al 11 Ma3
    Al 12 Ch2
    Al 12 Gy2

    Result Should look like this.
    CH_ID D_ID GRD CRS ADV_CRS
    Al 06 P En En2
    Al 11 P Ma Ma2
    Al 11 P Ma Ma3
    Al 12 P Ch Ch2
    Al 12 P Gy Gy2

    Does anyone have any ideas?
    Thanks in Advance..

    RP
    RP Guest

  17. #17

    Default Re: SQL Query

    Hello Paul !

    Try this:

    SELECT Table1.CH.ID,
    Table1.D_ID,
    Table1.GRD_CRS,
    Table2.ADV_CRS
    From
    Table1 inner join Table2
    ON
    Table1.CH_ID=Table2.CH_ID AND
    Table1.D_ID=Table2.D_ID

    Is it that what you wanted ?


    Jens Süßmeyer.

    "RP" <com> schrieb im Newsbeitrag
    news:032f01c3667e$779cc0e0$gbl... 


    Jens Guest

  18. #18

    Default Re: SQL Query

    Hi Jens Süßmeyer
    Thanks for you quick response. I ran your query and it
    gave me the following resultset

    CH_ID D_ID GRD CRS ADV_CRS
    Al 06 P En En2
    Al 11 P Ma Ma2
    Al 11 P Ma Ma3
    Al 12 P Ch Ch2
    Al 12 P Gy Ch2 5
    A1 12 P Ch Gy2 6
    Al 12 P Gy Gy2

    Rows five and six should not be there.

    Thanks!
    RP
     [/ref]
    return 
    >
    >
    >.
    >[/ref]
    RP Guest

  19. #19

    Default Re: SQL Query

    Hello Paul !

    Sorry, but if the solution is not ok, there is a misdesigning in your DB.
    The Join will always bring out this resultset, because every combination auf
    the
    PK AL and 12 will be used.

    It would be much easier to help you, if you could post the Script of the
    tables.
    Though a result like yours could be achieved by trimming the column value in
    the second table
    by one character (because it has something in common with the first table),
    but i think these
    entries are only casual and it would be only a workaround (a poor one :(( ).

    Jens Süßmeyer.


    "RP" <com> schrieb im Newsbeitrag
    news:019301c36687$51cad010$gbl...
    Hi Jens Süßmeyer
    Thanks for you quick response. I ran your query and it
    gave me the following resultset

    CH_ID D_ID GRD CRS ADV_CRS
    Al 06 P En En2
    Al 11 P Ma Ma2
    Al 11 P Ma Ma3
    Al 12 P Ch Ch2
    Al 12 P Gy Ch2 5
    A1 12 P Ch Gy2 6
    Al 12 P Gy Gy2

    Rows five and six should not be there.

    Thanks!
    RP
     [/ref]
    return 
    >
    >
    >.
    >[/ref]


    Jens Guest

  20. #20

    Default Re: SQL Query

    Hi Jens Süßmeyer
    The Table1 and table2 are really resultsets which are
    used to create the final resultset. This resultset is
    used to create a report.. There are no PK's defined. What
    you mentioned is correct, I will get the combinations. I
    may have to find another way to accomplish that result..

    Thanks
    RP
     
    misdesigning in your DB. 
    every combination auf 
    the Script of the 
    the column value in 
    with the first table), 
    workaround (a poor one :(( ). [/ref]
    >return 
    >>
    >>
    >>.
    >>[/ref]
    >
    >
    >.
    >[/ref]
    RP Guest

Page 1 of 2 12 LastLast

Similar Threads

  1. Creating data query from mySQL stored query!?
    By johnegbert in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 5th, 09:28 PM
  2. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  3. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 PM
  4. CAML Query: Multiple Query Fields Issue
    By Jon F. in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 12th, 08:19 PM
  5. Replies: 1
    Last Post: July 2nd, 09:09 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139