selecting unique data in stored proc

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

SQL Server Development (2000)

selecting unique data in stored proc

Author

Topic

shaminda
Starting Member

25 Posts

Posted - 2006-01-19 : 08:53:30

I have the following stored procedure

CREATE PROCEDURE [spAS400JTPLOADMixLotS3V]
@CABBV char(6),
@REQDATTODAY datetime,
@REQDATPLUSSEVEN datetime,
@VIN char(17)
AS
Select DISTINCT PARTNO, CABBV, REQDAT, QTYRQ, ENGREV, RANNO, CPART, VIN#, DABBV, OEML, TRNTYP, ARVTM, REQTIM, PONO, RLSDTE, PKGREQ
From AS400.dbo.JTPLoad
Where CABBV = @CABBV and REQDAT >= @REQDATTODAY AND REQDAT <= @REQDATPLUSSEVEN AND SUBSTRING(VIN#,5,2) = @VIN AND (CPART LIKE '78500S3V%' )
Order By RANNO DESC
GO

I want to narrow this query down to where all the values in the REQDAT field should be the same. How would I do this? In other words REQDAT is a date field, and on and of it returns two dates. But I only want to display the first date.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-19 : 08:58:30

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

shaminda
Starting Member

25 Posts

Posted - 2006-01-19 : 09:27:32

Here is an example of the data they are not in order. But if you follow closely you can see the date.

CABBV PARTNO DABBV CPART REQDAT QTYRQ VIN#
HCM1 62164 PLANT2 78500S9V A310M1 NH607L 1/19/2006 1 601348
HCM1 62164 PLANT2 78500S9V A310M1 NH607L 1/19/2006 2 601348
HCM1 62166 PLANT2 78500S9V A430M1 NH574L 1/19/2006 5 601348
HCM1 62163 PLANT2 78500S9V A310M1 NH574L 1/19/2006 1 601348
HCM1 62164 PLANT2 78500S9V A310M1 NH607L 1/19/2006 3 601348
HCM1 62170 PLANT2 78500S9V A630M1 NH607L 1/19/2006 18 601348
HCM1 62171 PLANT2 78500S9V A630M1 YR202L 1/25/2006 19 601448
HCM1 62169 PLANT2 78500S9V A630M1 NH574L 1/25/2006 11 601448

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-19 : 09:31:35

What is your expected result from the above sample data?

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-19 : 10:29:48

I suspect you want to implement a GROUP BY ... read up on that in books on-line or a good book in beginning SQL. GROUP BY allows to you specify the column you wish to group on distinctly, and from there you would specify how to summarize the other columns. By definition, you can only distinctly select the columns you are grouping on; you must aggregate (or summarize) the others.

shaminda
Starting Member

25 Posts

Posted - 2006-01-19 : 10:33:51

HCM1 62164 PLANT2 78500S9V A310M1 NH607L 1/19/2006 1 601348
HCM1 62164 PLANT2 78500S9V A310M1 NH607L 1/19/2006 2 601348
HCM1 62166 PLANT2 78500S9V A430M1 NH574L 1/19/2006 5 601348
HCM1 62163 PLANT2 78500S9V A310M1 NH574L 1/19/2006 1 601348
HCM1 62164 PLANT2 78500S9V A310M1 NH607L 1/19/2006 3 601348
HCM1 62170 PLANT2 78500S9V A630M1 NH607L 1/19/2006 18 601348

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-19 : 10:42:13

I think I got you .. the most efficient way might be to do it in two steps:

First part:



declare @DateToShow datetime;

set @DateToShow = Select Min(REQDAT)
From AS400.dbo.JTPLoad
Where CABBV = @CABBV and REQDAT >= @REQDATTODAY AND REQDAT <= @REQDATPLUSSEVEN AND SUBSTRING(VIN#,5,2) = @VIN AND (CPART LIKE '78500S3V%' )

and then:


select DISTINCT PARTNO, CABBV, REQDAT, QTYRQ, ENGREV, RANNO, CPART, VIN#, DABBV, OEML, TRNTYP, ARVTM, REQTIM, PONO, RLSDTE, PKGREQ
From AS400.dbo.JTPLoad
Where CABBV = @CABBV and REQDAT =@DateToShow
AND SUBSTRING(VIN#,5,2) = @VIN AND (CPART LIKE '78500S3V%' )
Order By RANNO DESC

Why do you have a DISTINCT there?

shaminda
Starting Member

25 Posts

Posted - 2006-01-19 : 11:15:13

How would you put both queries in one stored proc?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-19 : 12:40:47

You just put one statement after another all in the same proc. A stored proc can have a whole sequence of commands and T-SQL statements.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-19 : 20:24:43

Use BEGIN ... END

CREATE PROCEDURE [spAS400JTPLOADMixLotS3V]
    @CABBV           char(6),
    @REQDATTODAY     datetime,
    @REQDATPLUSSEVEN datetime,
    @VIN             char(17)
AS
BEGIN
    -- all your queries here
END

-----------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources