T-SQL Statement to Pick only First record where column changes 2

[Kieran777]

Hello,
I need some tips/solution on creating an SQL statement that selects each row(record) in a table when a particular column value changes.

Some code follows to create an example table and also to insert some basic data with an example of the output that I need to achieve.

(This is for a Roster System I am developing, Each row/record is a 15min time inteval representing the product being manufactured at that time and the employee resource requirement during that time interval. I need to identify the datetime when a new product is scheduled to begin manufacture and the required employee resource at that point in time. I've provided a very cut down simplistic data example but its enough to show what I require).

CREATE TABLE [dbo].[Rstr_Requirements](
	[RstrReq_ID] [INT] IDENTITY(1,1) PRIMARY KEY,      
	[IntervalMins]		[int]		NULL,
	[IntervalNo]		[int]		NULL,
	[IntervalStartDT]	[DateTime]	NULL,
	[IntervalEndDT]		[DateTime]	NULL,
	[ProductCode]		[NCHAR](10) NULL,
	[prodtSchdlStartDT] [datetime] NULL,
	[prodtSchdlEndDT]	[datetime]	NULL,
	[Emp_ReqNo]			[int]		NULL)
ON [PRIMARY]

INSERT INTO [dbo].[Rstr_Requirements]
	(IntervalMins, IntervalNo, IntervalStartDT, IntervalEndDT, ProductCode,
	 prodtSchdlStartDT, prodtSchdlEndDT,Emp_ReqNo)
	 VALUES
	 (15, 0, '2009-10-05 00:00:00.000','2009-10-05 00:15:00.000', NULL,
	 NULL, NULL, NULL)

INSERT INTO [dbo].[Rstr_Requirements]
	(IntervalMins, IntervalNo, IntervalStartDT, IntervalEndDT, ProductCode,
	 prodtSchdlStartDT, prodtSchdlEndDT,Emp_ReqNo)
	 VALUES
	 (15, 1, '2009-10-05 00:15:00.000','2009-10-05 00:30:00.000', NULL,
	 NULL, NULL, NULL)
	
INSERT INTO [dbo].[Rstr_Requirements]
	(IntervalMins, IntervalNo, IntervalStartDT, IntervalEndDT, ProductCode,
	 prodtSchdlStartDT, prodtSchdlEndDT,Emp_ReqNo)
	 VALUES
	 (15, 2, '2009-10-05 00:30:00.000','2009-10-05 00:45:00.000', 'PRODA',
	 '2009-10-05 00:30:00.000', '2009-10-05 01:00:00.000', 10)

	
INSERT INTO [dbo].[Rstr_Requirements]
	(IntervalMins, IntervalNo, IntervalStartDT, IntervalEndDT, ProductCode,
	 prodtSchdlStartDT, prodtSchdlEndDT,Emp_ReqNo)
	 VALUES
	 (15, 3, '2009-10-05 00:45:00.000','2009-10-05 01:00:00.000', 'PRODA',
	 '2009-10-05 00:30:00.000', '2009-10-05 01:00:00.000', 10)

INSERT INTO [dbo].[Rstr_Requirements]
	(IntervalMins, IntervalNo, IntervalStartDT, IntervalEndDT, ProductCode,
	 prodtSchdlStartDT, prodtSchdlEndDT,Emp_ReqNo)
	 VALUES
	 (15, 4, '2009-10-05 01:00:00.000','2009-10-05 01:15:00.000', 'PRODC',
	 '2009-10-05 01:00:00.000', '2009-10-05 01:15:00.000', 25)

INSERT INTO [dbo].[Rstr_Requirements]
	(IntervalMins, IntervalNo, IntervalStartDT, IntervalEndDT, ProductCode,
	 prodtSchdlStartDT, prodtSchdlEndDT,Emp_ReqNo)
	 VALUES
	 (15, 5, '2009-10-05 01:15:00.000','2009-10-05 01:30:00.000', 'PRODC',
	 '2009-10-05 00:30:00.000', '2009-10-05 01:00:00.000', 25)

INSERT INTO [dbo].[Rstr_Requirements]
	(IntervalMins, IntervalNo, IntervalStartDT, IntervalEndDT, ProductCode,
	 prodtSchdlStartDT, prodtSchdlEndDT,Emp_ReqNo)
	 VALUES
	 (15, 6, '2009-10-05 01:30:00.000','2009-10-05 01:45:00.000', 'PRODC',
	 '2009-10-05 00:30:00.000', '2009-10-05 01:00:00.000', 25)

INSERT INTO [dbo].[Rstr_Requirements]
	(IntervalMins, IntervalNo, IntervalStartDT, IntervalEndDT, ProductCode,
	 prodtSchdlStartDT, prodtSchdlEndDT,Emp_ReqNo)
	 VALUES
	 (15, 7, '2009-10-05 01:45:00.000','2009-10-05 02:00:00.000', 'PRODB',
	 '2009-10-05 01:45:00.000', '2009-10-05 02:00:00.000', 5)


INSERT INTO [dbo].[Rstr_Requirements]
	(IntervalMins, IntervalNo, IntervalStartDT, IntervalEndDT, ProductCode,
	 prodtSchdlStartDT, prodtSchdlEndDT,Emp_ReqNo)
	 VALUES
	 (15, 8, '2009-10-05 00:00:00.000','2009-10-05 00:15:00.000', NULL,
	 NULL, NULL, NULL)

I NEED SOME T-SQL that lists the first record when the number of employees required changes (Emp_ReqNo changes - either more or less) to achieve a list from the above table as per below:

	ProductCode,			ProductStartDT,	EmpReq,	Length (Mins), Start(IntervalNo)
	PRODTA,		2009-10-05	00:30:00.000,	10		,30				2
	PRODTC,		2009-10-05	01:00:00.000,	25		,45				4
	PRODTD,		2009-10-05	01:45:00.000,	05		,15				7

Thank You Kieran

 

[markros]

Read this blog

http://blogs.lessthandot.com/index....rver/including-an-aggregated-column-s-related

for 6 different ways of getting the result.

 

[Emtucifor]

Kieran777,

Here you go. If you have SQL 2000 I'm afraid it's going to be a lot more involved than this:

WITH Calc AS (
   SELECT
      Emp_ReqNo,
      ProductCode,
      IntervalStartDT,
      IntervalNo,
      ID = DateAdd(mi, -15 * Dense_Rank() OVER (PARTITION BY Emp_ReqNo, ProductCode ORDER BY IntervalStartDT), IntervalStartDT)
   FROM Rstr_Requirements
   WHERE ProductCode IS NOT NULL
)
SELECT
   ProductCode,
   ProductStartDT = Min(IntervalStartDT),
   Emp_ReqNo,
   Length = DateDiff(mi, Min(IntervalStartDT), Max(IntervalStartDT)) + 15,
   Start = Min(IntervalNo)
FROM Calc
GROUP BY
   Emp_ReqNo,
   ProductCode,
   ID
ORDER BY
   ProductCode,
   Emp_ReqNo

I bet a LOT of something very valuable that no one's going to beat this query:

I had to make some assumptions about how the data would be grouped together. Specifically, what is the correct resultset if Emp_ReqNo doesn't change but ProductCode does? If my assumptions are incorrect, please give a new example with desired results and I'll help you get the query right.

markros,

This is actually not the related value problem but the grouped islands problem. Unless how ProductCode can change is different than I assumed, no values need to be pulled based on another column's aggregate. We can just perform the aggregates we need.