Thread: Select problem

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    4
    Rep Power
    0

    Select problem


    Hello,

    Im a newbie in SQL but I would like make an SQL view where I can group up some database row into one. Im not sure It's possible or not.

    Now I made a view what looks like this:

    Name Event Date
    Cargo1 Arrived 2012.08.26 07:00:00.000
    Cargo1 Checked 2012.08.26 08:10:00.000
    Cargo1 Shipped 2012.08.26 08:20:00.000
    Cargo2 Arrived 2012.08.26 12:20:00.000
    Cargo2 Shipped 2012.08.26 12:30:00.000
    Cargo2 Checked 2012.08.26 13:40:00.000
    Cargo3 Arrived 2012.08.26 03:10:00.000
    Cargo3 Shipped 2012.08.26 06:50:00.000
    Cargo3 Checked 2012.08.26 07:20:00.000
    ...


    And what I would like to do is this:

    Name Arrived Checked Shipped
    Cargo1 2012.08.26.. 2012.08.26.. 2012.08.26..
    Cargo2 2012.08.26.. 2012.08.26.. 2012.08.26..
    Cargo3 2012.08.26.. 2012.08.26.. 2012.08.26..

    We use Microsoft SQL Server 2008
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    Code:
    SELECT name
         , MAX(CASE WHEN event='Arrived' THEN date ELSE NULL END) AS Arrived
         , MAX(CASE WHEN event='Checked' THEN date ELSE NULL END) AS Checked
         , MAX(CASE WHEN event='Shipped' THEN date ELSE NULL END) AS Shipped
      FROM daTable
    GROUP
        BY name
    FYI i've moved your thread to the SQL Server forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by r937
    Code:
    SELECT name
         , MAX(CASE WHEN event='Arrived' THEN date ELSE NULL END) AS Arrived
         , MAX(CASE WHEN event='Checked' THEN date ELSE NULL END) AS Checked
         , MAX(CASE WHEN event='Shipped' THEN date ELSE NULL END) AS Shipped
      FROM daTable
    GROUP
        BY name
    FYI i've moved your thread to the SQL Server forum
    Thank you very much for your help.

    Its a lot better now, but still I'm confused because the name 'Cargo1' its not an uniqe identifier, so after one 'Cargo1' shipped its possible to an other come in with same name. (but never more then one same Cargo in the warehouse) In the SQL unfortune no Unique ID for every exact process from start to the end.

    In a select I filter the daterange when I would like to know what happened. After sort by name, and date. So the results will:

    Name Event Date
    Cargo 1 Shipped 2012.08.25 05:30:00.000
    Cargo 1 Arrived 2012.08.25 06:50:00.000
    Cargo 1 Shipped 2012.08.25 07:10:00.000
    Cargo 1 Checked 2012.08.25 08:30:00.000
    Cargo 1 Arrived 2012.08.25 13:10:00.000
    Cargo 1 Checked 2012.08.25 14:00:00.000
    Cargo 1 Shipped 2012.08.25 19:30:00.000

    So a cycle of one Cargo will start with the Arrived and end before the next Arrived (with Shipped or Checked depends on the personal staff).

    After If we should check the rows 1-by-1 and start a new row in the view when event = 'Arrived' . If event = 'Checked' or 'Shipped' then fill the right colums in the actual row.


    OR

    After I typed this I got a new idea, If we could add a new column to that select sort by name, and date, which identify every cycle then I could use your solution. Just need a variable and increase that on every event = 'arrived' row, and every new name row. (After Cargo1 Arrived It's posible to Cargo2 Shipped)
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    Originally Posted by Peter85
    After I typed this I got a new idea, If we could add a new column to that select sort by name, and date, which identify every cycle then I could use your solution. Just need a variable and increase that on every event = 'arrived' row, and every new name row. (After Cargo1 Arrived It's posible to Cargo2 Shipped)
    this is a good idea... you should pursue this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    348
    Originally Posted by Peter85
    After I typed this I got a new idea, If we could add a new column to that select sort by name, and date, which identify every cycle then I could use your solution. Just need a variable and increase that on every event = 'arrived' row, and every new name row. (After Cargo1 Arrived It's posible to Cargo2 Shipped)
    Should be quite easy with the row_number() function. Something like
    Code:
    row_number() over (partition by name order by event,date)
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by shammat
    Should be quite easy with the row_number() function. Something like
    Code:
    row_number() over (partition by name order by event,date)
    Okay, so here is my View_1:

    Code:
    SELECT     TOP (100) PERCENT Name AS Name, Event AS Event,  DateTimeStamp AS Timestamp
    FROM         dbo.Log
    WHERE     (DateTimeStamp < CONVERT(DATETIME, '2012-08-08 00:00:00', 102)) 
    ORDER BY Name, Timestamp
    Result is:

    Name Event Date
    Cargo 1 Shipped 2012.08.25 05:30:00.000
    Cargo 1 Arrived 2012.08.25 06:50:00.000
    Cargo 1 Shipped 2012.08.25 07:10:00.000
    Cargo 1 Checked 2012.08.25 08:30:00.000
    Cargo 1 Arrived 2012.08.25 13:10:00.000
    Cargo 1 Checked 2012.08.25 14:00:00.000
    Cargo 1 Shipped 2012.08.25 19:30:00.000

    After a new select for the rows:
    Code:
    SELECT row_number() over (partition by Name order by event,TimeStamp) AS UniqueId
    FROM dbo.View_1
    Result is:

    UniqueId
    1
    2
    3
    4
    5
    6
    7

    Its numbering the same name. Instead of

    1
    2
    2
    2
    3
    3
    3

    Need a new number at every Arrived, or new Name, to identify every cycle.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    348
    Ah, sorry. Then I misunderstood you.

    As SQL Server does not support the more advanced windowing functions like lag() it will be quite hard to detect a change in the values from one row to the other.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by shammat
    Ah, sorry. Then I misunderstood you.

    As SQL Server does not support the more advanced windowing functions like lag() it will be quite hard to detect a change in the values from one row to the other.
    Is that possible to check the rows one by one, and If the value = Arrive, then just start a new row in the view?
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    Originally Posted by Peter85
    Is that possible to check the rows one by one...
    this is best done with application programming logic (asp, vb, php, whatever), on a result set that simply has ORDER BY date

    then you can detect logical differences, e.g. missing rows, like you said "with Shipped or Checked depends on the personal staff"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo