The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Databases
> MS SQL Development
|
Select problem
Discuss Select problem in the MS SQL Development forum on Dev Shed.
Select problem MS SQL Development forum discussing administration, MS SQL queries, and other MS SQL-related topics. SQL Server is Microsoft's enterprise database engine.
|
|
 |
|
|
|
|
Dev Shed Forums Sponsor:
|
|
|
August 25th, 2012, 06:56 PM
|
|
Registered User
|
|
Join Date: Aug 2012
Posts: 4
Time spent in forums: 1 h 21 m 35 sec
Reputation 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
|
August 25th, 2012, 09:40 PM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
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
|
August 26th, 2012, 05:30 AM
|
|
Registered User
|
|
Join Date: Aug 2012
Posts: 4
Time spent in forums: 1 h 21 m 35 sec
Reputation Power: 0
|
|
Quote: | 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)
|
August 26th, 2012, 06:28 AM
|
|
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
Quote: | 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
|
August 26th, 2012, 06:44 AM
|
|
Contributing User
|
|
Join Date: Oct 2003
Location: Germany
|
|
Quote: | 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
|
August 26th, 2012, 09:29 AM
|
|
Registered User
|
|
Join Date: Aug 2012
Posts: 4
Time spent in forums: 1 h 21 m 35 sec
Reputation Power: 0
|
|
Quote: | 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. 
|
August 26th, 2012, 09:38 AM
|
|
Contributing User
|
|
Join Date: Oct 2003
Location: Germany
|
|
|
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.
|
August 26th, 2012, 09:46 AM
|
|
Registered User
|
|
Join Date: Aug 2012
Posts: 4
Time spent in forums: 1 h 21 m 35 sec
Reputation Power: 0
|
|
Quote: | 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?
|
August 26th, 2012, 12:29 PM
|
|
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
Quote: | 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"
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
 Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
