Regrouping from the same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Regrouping from the same table

Author

Topic

coagulance
Yak Posting Veteran

78 Posts

Posted - 2010-05-28 : 02:43:15

Hi,

Need some help here:

Table :
Main Prog SubProg StartTime EndTime Date
1 Weld 06:04:01 06:06:00 26.05.2010
1 Weld 06:08:01 06:09:00 26.05.2010
2 Weld 06:12:01 06:14:00 26.05.2010
2 Weld 06:16:01 06:18:00 26.05.2010
2 Weld 06:20:01 06:23:00 26.05.2010
100 Trim 06:25:01 06:26:00 26.05.2010
100 Trim 06:27:01 06:31:00 26.05.2010
100 Mill 06:32:01 06:41:00 26.05.2010
100 Mill 06:45:01 06:46:00 26.05.2010
1 Trim 06:47:01 06:51:00 26.05.2010
2 Weld 06:52:01 06:54:00 26.05.2010
2 Weld 06:56:01 06:57:00 26.05.2010
2 Weld 06:59:01 07:01:00 26.05.2010

Result-set
PrevProg CurProg ChangeTime Qty
None 1 00:00:00 2
1Weld 2Weld 00:03:01 3
2Weld 100Trim 00:02:01 2
100Trim 100Mill 00:01:01 2
100Mill 1Trim 00:01:01 1
1Trim 2Weld 00:01:01 3

Change time = Current Program start time - Last ProgramEnd Time

Any immediate help greatly appreciated.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 03:07:03

put code-tags around for formatting.
[C0DE]



Need some help here:

Table :
Main Prog SubProg    StartTime    EndTime   Date
1	   Weld        06:04:01     06:06:00  26.05.2010
1	   Weld        06:08:01     06:09:00  26.05.2010
2	   Weld        06:12:01     06:14:00  26.05.2010
2	   Weld        06:16:01     06:18:00  26.05.2010
2	   Weld        06:20:01     06:23:00  26.05.2010
100	   Trim        06:25:01     06:26:00  26.05.2010
100	   Trim        06:27:01     06:31:00  26.05.2010
100	   Mill        06:32:01     06:41:00  26.05.2010
100	   Mill        06:45:01     06:46:00  26.05.2010
1	   Trim        06:47:01     06:51:00  26.05.2010
2	   Weld        06:52:01     06:54:00  26.05.2010
2	   Weld        06:56:01     06:57:00  26.05.2010
2	   Weld        06:59:01     07:01:00  26.05.2010


Result-set
PrevProg  CurProg      ChangeTime	Qty
None	  1		00:00:00	2
1Weld	  2Weld		00:03:01	3
2Weld	  100Trim	00:02:01	2
100Trim	  100Mill	00:01:01	2
100Mill	  1Trim		00:01:01	1
1Trim	  2Weld		00:01:01	3

Change time = Current Program start time - Last ProgramEnd Time

Any immediate help greatly appreciated.

[/C0DE]

No, you're never too old to Yak'n'Roll if you're too young to die.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-28 : 04:38:15

Not getting your requirement please explain more deep...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

coagulance
Yak Posting Veteran

78 Posts

Posted - 2010-05-28 : 04:58:41

So Sorry.

Result set should indicate all programs (Program and sub program) along with the previous programs (Program and sub program) and the time it took to change to the new program. It should also indicate the qty of each program.
The result set should also identify if the same program+sub program is run later as it requires a change time again like the 2-Weld program in the table.

coagulance
Yak Posting Veteran

78 Posts

Posted - 2010-05-28 : 07:35:01

Any help ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 08:16:25

Seems like until now nobody understands the rules on how to come from the given data to the wanted result.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources