Results 1 to 2 of 2

Thread: select and join

  1. #1
    Join Date
    Apr 2003
    Posts
    8

    Question Unanswered: select and join

    Hi there,

    Could someone please shed some light how to join my tables.

    This is part of my select statement I want to accomplish.

    select distinct convert(char(8), endTime, 112) as 'Date',
    datediff(ss, convert(char(8), startTime, 112), dateadd(hh, 0, startTime)) as 'Start (7)',
    datediff(ss, convert(char(8), endTime, 112), dateadd(hh, 0, endTime)) as 'End (7)'
    from transmission
    where feedID = 7
    and convert(char(8), endTime, 112) in
    (select distinct convert(char(8), endTime, 112)
    from transmission
    where endTime between '20030601 00:00' and '20030620 23:59' )


    Now I like to include the feedID 8 and 9 to display the same row for each date.


    My current result is
    Date Start (7) End (7)
    -------- ----------- -----------
    20030602 NULL 45438
    20030603 NULL 45586
    20030604 NULL 45925
    20030605 NULL 45457
    20030606 NULL 45449
    20030609 NULL 45198
    20030610 NULL 45365
    20030611 NULL 45318
    20030612 NULL 45701
    20030613 NULL 45345
    20030616 NULL 45423
    20030617 NULL 45309
    20030618 NULL 45445
    20030619 NULL 45206
    20030620 NULL 45401

    want to add these columns to each row above

    Start(8) End (8) Start(9) End (9)


    Thanks a lot
    Last edited by bedford; 06-24-03 at 11:17.

  2. #2
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159
    I'm not saying it's perfect, because it probably isn't but may give you some inspiration on how to solve the problem.



    select distinct into #results
    feedID,
    convert(char(8), endTime, 112) as 'Date',
    datediff(ss, convert(char(8), startTime, 112), dateadd(hh, 0, startTime)) as 'start',
    datediff(ss, convert(char(8), endTime, 112), dateadd(hh, 0, endTime)) as 'end_'
    from
    transmission
    where
    feedID in ( 7, 8, 9) -- Include the values of feedID here
    and convert(char(8), endTime, 112) in
    (select distinct
    convert(char(8), endTime, 112)
    from
    transmission
    where
    endTime between '20030601 00:00' and '20030620 23:59'
    )

    -- You'll need a start and end column select for each value of feed ID here.

    select
    "Date" = Date,
    "Start (7)" = (select start from #results where feedID = 7)
    "End (7)" = (select end_ from #results where feedID = 7)
    "Start (8)" = (select start from #results where feedID = 8)
    "End (8)" = (select end_ from #results where feedID = 8)
    "Start (9)" = (select start from #results where feedID = 9)
    "End (9)" = (select end_ from #results where feedID = 9)

Posting Permissions

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