Complex 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)

Complex query help

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-07-28 : 11:56:47

I need display the counts from table @AYT based on the parameters

declare @FRMDSDT 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 =01

I hope that my question is clear.

Below the sample data.

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'

*(PID PrimaryKey)

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'

Output:

[AYTCD] [AYTCDCOUNT]
------- -----------
15.15 1
15.14 1
15.13 1

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-28 : 12:05:55

quote:
Originally posted by sqldba2k6

I need display the counts from table @AYT based on the parameters

declare @FRMDSDT 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 =01

I hope that my question is clear.

Which Question ?

Also what u asked for as Output is not seems to be related to more than 80% of the information u provided.!!

Srinika

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-28 : 12:21:07

[code]Select AYTCD, Count(AYTCD) as AYTCDCount
from @AYT
group by AYTCD[/code]

Harsh Athalye
India.
"Nothing is Impossible"

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-07-28 : 13:21:56

Thanks for the response..

I need to display the data counts from AYT table.. with the following input parameters

@FRMDSDT : (@Pin table, column SDT)
@TOSDT :(@Pin table, column SDT)
@X :(@GX table, column x)
@PEID :(@pin table,column PEID)
@OPT :(@HIT table,column OPT)

with 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

I need the below output.

[AYTCD] [AYTCDCOUNT]
------- -----------
15.15 1
15.14 1
15.13 1

I hope that my question is clear.

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-07-28 : 14:37:09

Based on the sample data you've provided, there is no logical way to relate the data to get the output you want. For example, for the @AYT table, you've only provided sample data for PID=121. But the PEID you provided is for 9497, which only relates to PID=120 in the @Pin table. Therefore, you can't relate those records in any natural way. You have either provided erroneous sample data, erroneous parameters, or you are trying to create relationships that don't logically exist.

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-07-28 : 15:08:25

The sample data which provided is correct.If you look the data in @pin table fmid is unique for the pied and pid.

In other way.I am trying to achieve the below results:

AYTCD PID PEID OPT x
15.15 121 9497 01 01
15.14 121 9497 01 01
15.13 121 9497 01 01

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-07-28 : 17:54:33

OK, I came up with this, which seems to work, but it required making relationships between data that was entirely unnatural and illogical (if this is really the data, it needs to be normalized badly):

SELECT @ayt.AYTCD, B.*
FROM @ayt
JOIN
(SELECT @pin.PID, A.maxPEID, A.maxOPT, A.maxX
FROM @pin
FULL JOIN
(SELECT @pin.FMID, MAX(@pin.PID) AS maxPID, MAX(@pin.PEID) AS maxPEID, MAX(@hit.OPT) AS maxOPT, MAX(@gx.x) AS maxX
FROM @pin
LEFT JOIN @hit ON @pin.PID = @hit.PID AND @hit.OPT = CONVERT(int, @OPT)
LEFT JOIN @gx ON @pin.PID = @gx.PID AND @gx.x = CONVERT(int, @X)
WHERE @pin.SDT >= @FRMSDT
AND @pin.SDT <= @TOSDT
AND @pin.PEID = @PEID
GROUP BY @pin.FMID) AS A
ON @pin.FMID = A.FMID) AS B
ON @ayt.PID = B.PID

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-07-28 : 18:04:03

Wow, sqldba2k6.. did you even try to run the code you provided? You changed the name of the first variable between declaring it and setting it.. And I'm not sure what you mean by saying the FMID is unique when every row has the same one.. unique usually means only one row would match...

The row of data in the @pin table for PID=121 has a PEID of null, you would not get the results you display in your last post...

selecting all the data joined together gives this:


SELECT p.*, h.OPT, g.x, a.AYTCD 
FROM #pin p
   LEFT OUTER JOIN #HIT h ON p.pid = h.pid
   LEFT OUTER JOIN #GX g ON p.pid = g.pid
   LEFT OUTER JOIN #AYT a ON p.pid = a.pid

PID  PEID   FMID   TP   SDT                           OPT  x    AYTCD 
---- ------ ------ ---- ----------------------------- ---- ---- ----- 
120  9497   5107   A    2006-07-29 00:00:00.000       NULL 1    NULL
121  NULL   5107   B    2006-07-30 00:00:00.000       NULL NULL 15.15
121  NULL   5107   B    2006-07-30 00:00:00.000       NULL NULL 15.14
121  NULL   5107   B    2006-07-30 00:00:00.000       NULL NULL 15.13
122  NULL   5107   B    2006-07-30 00:00:00.000       1    NULL NULL
123  NULL   5107   B    2006-07-30 00:00:00.000       NULL NULL NULL

There are no rows that match your parameters (@x=1, @PEID=9497, @OPT=1)

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-07-28 : 18:40:15

IF you assume the AYTCD table is the primary table, and the parameters are optional, you could go this route :


SELECT a.AYTCD,  Count(A.AYTCD) as AYTCDCount
FROM #AYT a
   LEFT OUTER JOIN #HIT h ON a.pid = h.pid
   LEFT OUTER JOIN #GX g on a.pid = g.pid
   LEFT OUTER JOIN #PIN p on a.pid = p.pid
where (SDT >= @FRMSDT and SDT <= @TOSDT)
OR h.OPT = @OPT
OR g.x = @X
OR p.PEID = @PEID
group by a.AYTCD

you end up with:


AYTCD AYTCDCount  
----- ----------- 
15.13 1
15.14 1
15.15 1

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-07-31 : 14:27:44

Thanks Nosepicker.I got the desired when i removed or commented out the parameters condition in the query but when i tried with input parameters i am not able to get the results getting nulls for all max cols.

Can you please help me in this issue...

Thanks for your help in advance...

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-07-31 : 15:13:40

Nosepicker!
I am able to pull the data with the query but not with the filter results
For Ex:
if i tried with @PEID =9497 i have to get only the results of 9497..
I hope i am clear with my question

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources