simple sql query 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)

simple sql query help

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-07-16 : 00:32:23

-- data
declare @sin table (SID tinyint, SEID smallint, FMID smallint, TP char(1))

insert @sin
select 126, 4000, 5107, 'A' union all
select 115, NULL, 5107, 'B' union all
select 116, NULL, 5107, 'B'

declare @HIT table (RNRSNP char(2), SID tinyint)

insert @HIT
select '08', 116

declare @GX table (x char(2), SID tinyint)

insert @GX
select '01', 126

-- work
SELECT h.RNRSNP,
h.SID,
z.SEID
FROM (
SELECT s.SID,
w.SEID
FROM (
SELECT FMID,
MAX(SEID) SEID
FROM @SIN
GROUP BY FMID
) w
INNER JOIN @sin s ON s.FMID = w.FMID
) z
INNER JOIN @HIT h ON h.SID = z.SID

I want the below output.plese help me in rewriting the above query

RNRSNP SID SEID XID
------ ---- ----- ---
08 116 4000 1

DMWCincy
Starting Member

11 Posts

Posted - 2006-07-16 : 00:57:54

Given the amount of data you have given:

select max(RNRSNP), max(h.sid), max(seid), max(x) from @sin s
full outer join @hit h
on s.sid = h.sid
full outer join @GX g
on s.sid = g.sid

This will get the correct output but I'm not sure if this will be valid with more data in the system. If I can have a better understanding of the tables and data, I may be able to help write a better query for you.

Anyway, hope this helps

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-16 : 18:47:18

This is a never-ending story. Why don't you post what you want the first time?

-- data
declare @sin table (SID tinyint, SEID smallint, FMID smallint, TP char(1))

insert @sin
select 126, 4000, 5107, 'A' union all
select 115, NULL, 5107, 'B' union all
select 116, NULL, 5107, 'B'

declare @HIT table (RNRSNP char(2), SID tinyint)

insert @HIT
select '08', 116

declare @GX table (x char(2), SID tinyint)

insert @GX
select '01', 126

-- work
SELECT		h.RNRSNP,
		h.SID,
		z.SEID,
		gx.x
FROM		(
			SELECT		s.SID,
					w.SEID,
					s2.SID sid2
			FROM		(
						SELECT		FMID,
								MAX(SEID) SEID
						FROM		@SIN
						GROUP BY	FMID
					) w
			INNER JOIN	@sin s ON s.FMID = w.FMID
			INNER JOIN	@sin s2 ON s2.FMID = w.FMID AND s2.SEID = w.SEID
		) z
INNER JOIN	@HIT h ON h.SID = z.SID
INNER JOIN	@GX gx ON gx.SID = z.SID2

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-07-16 : 20:20:58

Thanks peso for your help!.I got the results.
The requirements are getting changed often.

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-07-17 : 12:49:02

Peso Sorry again.I got final reqs now.
Please help me to get the final output.

-- data
declare @sin table (SID tinyint, SEID smallint, FMID smallint, TP char(1),SDT datetime)
insert @sin
select 126, 4000, 5107, 'A','2006-07-29 00:00:00.000' union all
select 115, NULL, 5107, 'B','2006-07-30 00:00:00.000'union all
select 116, NULL, 5107, 'B','2006-07-30 00:00:00.000'

declare @HIT table (RNRSNP char(2), SID tinyint, RSLT tinyint )

insert @HIT
select '08', 116,01

declare @GX table (x char(2), SID tinyint)

insert @GX
select '01', 126

DECLARE @FRMDT DATETIME
DECLARE @TODT DATETIME
DECLARE @X INT
DECLARE @SEID INT
DECLARE @RSLT INT

SET @FRMDT ='2006-07-29 00:00:00.000'
SET @TODT ='2006-07-30 00:00:00.000'
SET @X ='01'
SET @SEID =4000
SET @OP =01

-- work
SELECT h.RNRSNP,
h.SID,
z.SEID,
gx.x
FROM (
SELECT s.SID,
w.SEID,
s2.SID sid2

FROM (
SELECT FMID,
MAX(SEID) SEID
FROM @SIN
GROUP BY FMID
) w
INNER JOIN @sin s ON s.FMID = w.FMID
INNER JOIN @sin s2 ON s2.FMID = w.FMID AND s2.SEID = w.SEID
) z
INNER JOIN @HIT h ON h.SID = z.SID
INNER JOIN @GX gx ON gx.SID = z.SID2
WHERE RNRSNP IS NOT NULL
AND (@FRMDT IS NULL OR ?.SID_DT Between @FRMDT And @TODT)
AND (@TODT IS NULL OR ?.SID_DT Between @FRMDT And @TODT)
AND (@X=0 OR GX.X=@X)
AND (@SEID=0 OR Z.SEID=@SEID)
AND (@RSLT=0 OR H.RSLT=@RSLT)

I want the below output.plese help me in rewriting the above query

RNRSNP SID SEID X RSLT
------ ---- ----- --- ----
08 116 4000 01 01

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-17 : 14:24:56

Something like this?

SELECT		h.RNRSNP,
		h.SID,
		z.SEID,
		gx.x,
		h.rslt
FROM		(
			SELECT		s.SID,
					s.sdt sid_dt,
					w.SEID,
					s2.SID sid2
			FROM		(
						SELECT		FMID,
								MAX(SEID) SEID
						FROM		@SIN
						GROUP BY	FMID
					) w
			INNER JOIN	@sin s ON s.FMID = w.FMID
			INNER JOIN	@sin s2 ON s2.FMID = w.FMID AND s2.SEID = w.SEID
		) z
INNER JOIN	@HIT h ON h.SID = z.SID
INNER JOIN	@GX gx ON gx.SID = z.SID2
WHERE		RNRSNP IS NOT NULL
		AND (z.SID_DT Between ISNULL(@FRMDT, convert(datetime, 'January 1, 1753')) And ISNULL(@TODT, convert(datetime, 'December 31, 9999')))
		AND (@X = '00' OR GX.X = @X) 
		AND (@SEID = 0 OR Z.SEID = @SEID)
		AND (@RSLT = 0 OR H.RSLT = @RSLT)

Peter Larsson
Helsingborg, Sweden

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-07-19 : 22:35:52

Thanks peter for your help.I got the results with the query.But when i tested with multiple records then the results showing are incorrect.
For Ex: Table GX has 10 records and Hit table has 9 records output is showing 9 rows it should 10 rows..

What could be the problem.
Thanks for your help in advance..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-19 : 23:35:26

Can you post the sample data of the scenario that you describe ?

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-07-20 : 12:55:13

Thanks for your response!.

Below is the sample data..Which gives the count..
gx table has two records,output (count) should be 2 when no parameters are passed.

declare @sin table (SID tinyint, SEID smallint, FMID smallint, TP char(1),SDT datetime)
insert @sin
select 126, 4000, 5107, 'A','2006-07-29 00:00:00.000' union all
select 115, NULL, 5107, 'B','2006-07-30 00:00:00.000'union all
select 116, NULL, 5107, 'B','2006-07-30 00:00:00.000' union all
select 136, NULL, 5108, 'A','2006-07-30 00:00:00.000'

declare @HIT table (RNRSNP char(2), SID tinyint, RSLT tinyint )

insert @HIT
select '08', 116,01

declare @GX table (x tinyint, SID tinyint)

insert @GX
select 01,126 union all
select 01,136

DECLARE @FRMDT DATETIME
DECLARE @TODT DATETIME
DECLARE @X INT
DECLARE @SEID INT
DECLARE @RSLT INT

SET @FRMDT =NUll
SET @TODT =NULL
SET @X =0
SET @SEID =0
SET @RSLT =0

SELECT COUNT(GX.X) AS X_Count
FROM (
SELECT s.SID,
s.sdt sid_dt,
w.SEID,
s2.SID sid2
FROM (
SELECT FMID,
MAX(SEID) SEID
FROM @SIN
GROUP BY FMID
) w
INNER JOIN @sin s ON s.FMID = w.FMID
INNER JOIN @sin s2 ON s2.FMID = w.FMID AND s2.SEID = w.SEID
) z

INNER JOIN @HIT h ON h.SID = z.SID
INNER JOIN @GX gx ON gx.SID = z.SID2

WHERE (z.SID_DT Between ISNULL(@FRMDT, convert(datetime, 'January 1, 1753')) And ISNULL(@TODT, convert(datetime, 'December 31, 9999')))
AND (@X = 0 OR GX.X = @X)
AND (@SEID = 0 OR Z.SEID = @SEID)
AND (@RSLT = 0 OR H.RSLT = @RSLT)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-20 : 16:32:00

The 5108 FMID has no value for any SEID.

Peter Larsson
Helsingborg, Sweden

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-07-20 : 20:10:03

Sorry peter again! i forgot the SEID value..

The value for SEID is 1010 of the FMID 5108.

Below is the correct data...

insert @sin
select 126, 4000, 5107, 'A' union all
select 115, NULL, 5107, 'B' union all
select 116, NULL, 5107, 'B'union all
select 136, 1010, 5108, 'A'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-21 : 04:49:16

The hit table ony contains 1 row. That's what produces onw row only. GX table has two rows.

Peter Larsson
Helsingborg, Sweden

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-07-21 : 10:40:59

How to do to get the output two (count) from gx table.
Sorry Peter i may not be clear abt my explaination in earlier post.

if no parameters are passed it should 2 rows.
if only Parameters with @X =1 then ouput 2

if only parameter @RSLT =1 then output 1

Below are the details:

Declare @sin table (SID tinyint, SEID smallint, FMID smallint, TP char(1),SDT datetime)
insert @sin
select 126, 4000, 5107, 'A','2006-07-29 00:00:00.000' union all
select 115, NULL, 5107, 'B','2006-07-30 00:00:00.000'union all
select 116, NULL, 5107, 'B','2006-07-30 00:00:00.000' union all
select 136, NULL, 5108, 'A','2006-07-30 00:00:00.000' union all

Declare @HIT table (SID tinyint, RSLT tinyint )

insert @HIT
select 116,01

declare @GX table (x tinyint, SID tinyint)

insert @GX
select 01,126 union all
select 01,136

DECLARE @FRMDT DATETIME
DECLARE @TODT DATETIME
DECLARE @X INT
DECLARE @SEID INT
DECLARE @RSLT INT

SET @FRMDT =NUll
SET @TODT =NULL
SET @X =0
SET @SEID =0
SET @RSLT =0

SELECT COUNT(GX.X) AS X_Count
FROM (
SELECT s.SID,
s.sdt sid_dt,
w.SEID,
s2.SID sid2
FROM (
SELECT FMID,
MAX(SEID) SEID
FROM @SIN
GROUP BY FMID
) w
INNER JOIN @sin s ON s.FMID = w.FMID
INNER JOIN @sin s2 ON s2.FMID = w.FMID AND s2.SEID = w.SEID
) z

INNER JOIN @HIT h ON h.SID = z.SID
INNER JOIN @GX gx ON gx.SID = z.SID2

WHERE (z.SID_DT Between ISNULL(@FRMDT, convert(datetime, 'January 1, 1753')) And ISNULL(@TODT, convert(datetime, 'December 31, 9999')))
AND (@X = 0 OR GX.X = @X)
AND (@SEID = 0 OR Z.SEID = @SEID)
AND (@RSLT = 0 OR H.RSLT = @RSLT)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources