Query Issue 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

SQL Server Development (2000)

Query Issue help

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-07-28 : 16:00:07

In continuation with already posted issue which may not be clear with that post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69749

I have written a query for a search which should get the data..based on the parameters..
I got struck up,not able to produce the correct output.
Appreciate some one can help me in correcting the query ..

Declare @Pin table (PID tinyint, PEID smallint, FMID smallint, TP char(1),SDT datetime)
insert @Pin
select 120, 9497, 5107, 'A','2006-07-29 00:00:00.000' union all
select 121, NULL, 5107, 'B','2006-07-30 00:00:00.000'union all
select 122, NULL, 5107, 'B','2006-07-30 00:00:00.000' union all
select 123, NULL, 5107, 'B','2006-07-30 00:00:00.000'

Declare @HIT table (PID tinyint, OPT tinyint )
insert @HIT
select 122,01

declare @GX table (PID tinyint,x tinyint)

insert @GX
select 120,01

Declare @AYT table (PID tinyint,AYTCD varchar(5))
insert @AYT
select 121,'15.15' UNION ALL
select 121,'15.14' UNION ALL
select 121,'15.13'

declare @FRMSDT DATETIME
declare @TOSDT DATETIME
declare @X INT
declare @PEID INT
declare @OPT INT

SET @FRMSDT ='2006-07-29 00:00:00.000'
SET @TOSDT ='2006-07-30 00:00:00.000'
SET @X =01
SET @PEID =9497
SET @OPT =0

SELECT AYTCD,
AA.PID,Z.PEID,A.X,z.sdt,e.opt
FROM
(SELECT s.PID ,w.PEID,s2.PID pid2,S.SDT
FROM (
SELECT FMID,
MAX(PEID) PEID
FROM @PIN
GROUP BY FMID
) w
INNER JOIN @PIN s ON s.FMID = w.FMID
INNER JOIN @PIN s2 ON s2.FMID = w.FMID AND s2.PEID = w.PEID

) z

JOIN @AYT AA ON AA.PID = Z.PID
JOIN @GX A ON A.PID = z.pid2
LEFT OUTER JOIN @HIT E ON E.PID = z.PID

WHERE
(@X=0 OR A.X=@X)
AND (@PEID=0 OR Z.PEID=@PEID)
AND (@OPT=0 OR E.OPT=@OPT)
AND (@FRMSDT IS NULL OR Z.SDT Between @FRMSDT And @TOSDT)
AND (@TOSDT IS NULL OR Z.SDT Between @FRMSDT And @TOSDT)

with below values to the parameters

SET @FRMSDT ='2006-07-29 00:00:00.000'
SET @TOSDT ='2006-07-30 00:00:00.000'
SET @X =01
SET @PEID =9497
SET @OPT =01

Desired output:

AYTCD PID PEID X SDT OPT
----- --- ---- -- ------------------------ -----
15.15 121 9497 1 2006-07-30 00:00:00.000 1
15.14 121 9497 1 2006-07-30 00:00:00.000 1
15.13 121 9497 1 2006-07-30 00:00:00.000 1

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-07-28 : 18:42:10

Just curious why you started a new topic when you were getting replies on the original?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources