SELECT DISTINCT help!? - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

SELECT DISTINCT help!?

Author

Topic

ajustice
Starting Member

2 Posts

Posted - 2005-09-17 : 13:51:38

I need some help with writing a stored procedure. Any suggestions would be greatly appreciated!
I am going to give fictional data and circumstance to help simplify my question. Let’s say we have 4 columns:


ColA	smalldatetime	Time-stamp of record entry
ColB	int		Number identifying data (1 through 4)
ColC	int		Data
ColD	int		Data

First off, let me give you a sample data list to help me explain what I need (I used only dates for ColA for ease of explanation):


ColA	ColB	ColC	ColD
8/1	1	10	50
8/1	3	10	30
8/1	2	10	30
8/1	4	60	20
8/2	3	10	30
8/2	2	10	30
8/2	1	10	50
8/2	4	60	20
8/3	4	60	20
8/3	3	10	50
8/3	2	10	30
8/3	1	10	50
8/4	1	30	60
8/4	2	10	30
8/4	3	10	50
8/4	4	10	50
8/5	1	30	60
8/5	3	10	50
8/5	2	10	30
8/5	4	10	50
8/6	1	30	60
8/6	2	10	30
8/6	3	10	30
8/6	4	10	30
8/7	1	20	40
8/7	4	10	30
8/7	3	10	30
8/7	2	10	30
8/8	4	10	30
8/8	2	10	30
8/8	1	20	40
8/8	3	10	30

I want to find records that are DISTINCT in ColC and ColD for each ColB, and I can do this by the following:


//***********************************************************//
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE            PROCEDURE usp_TEST
  @start smalldatetime,
  @end smalldatetime
AS

SET NOCOUNT ON

SELECT DISTINCT

[ColB], [ColC], [ColD]

FROM [SQL].[dbo].[Test]
WHERE ColA >= @start
AND ColA <= @end

GROUP BY [ColB], [ColC], [ColD]

ORDER BY ColB

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
//***********************************************************//

Which would return:


ColB	ColC	ColD
1	10	50
1	30	60
1	20	40
2	10	30
3	10	30
3	10	50
4	60	20
4	10	50
4	10	30

But I want to know what was the first and last TimeStamp (ColA) for that particular record. And how many (COUNT) records were there. Giving a return like this:


ColB	ColC	ColD	StartDate	EndDate	   Count
1	10	50	8/1		8/3	   3
1	30	60	8/4		8/6	   3
1	20	40	8/7		8/8	   2
2	10	30	8/1		8/8	   8
3	10	30	8/1		8/2	   2
3	10	50	8/3		8/5	   3
3	10	30	8/6		8/8	   3
4	60	20	8/1		8/3	   3
4	10	50	8/4		8/5	   2
4	10	30	8/6		8/8	   3

Notice that for ColB = 3 that there are two sets of ColC = 10, and ColD = 30 BUT the "run" was interrupted by the ColC = 10 and ColD = 50, so I do in fact want BOTH returns.

BUT I don’t know how to put this into a stored procedure – I can’t include the ColA in the Select DISTINCT, or else I would get every record.

I am new with SQL, and I am trying my best!

The actual data I am looking at are Batch Records, which include what Recipe was used. So the return will tell me: what recipe was used and for how long, and for how many batches. Thank you in advance for your help!

Thanks,
ajustice

ajustice
Starting Member

2 Posts

Posted - 2005-09-17 : 15:27:56

UPDATE

I think I may have figured part of it out:


//***********************************************************//
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE            PROCEDURE usp_TEST
  @start smalldatetime,
  @end smalldatetime
AS

SET NOCOUNT ON

SELECT

min([ColA]) as FirstDate,
max([ColA]) as LastDate,
count([ColB]) as NumRecords,
[ColB], [ColC], [ColD]

FROM [SQL].[dbo].[Test]
WHERE ColA >= @start
AND ColA <= @end

GROUP BY [ColB], [ColC], [ColD]

ORDER BY ColB

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
//***********************************************************//

But I don't believe this will work for when a "run" is split into two differant times. I will run this on my data and try to verify. Please post anything you may see! Thanks!

It does work correctly EXCEPT it does not work when a "run" is split - so - I still need some advice on this - I will continue to search and try diff. things, and I will keep posting anything more that I figure out - Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-19 : 01:48:36

>>It does work correctly EXCEPT it does not work when a "run" is split

Can you explain it more?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources