Need help with a query.... - 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)

Need help with a query....

Author

Topic

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2008-05-06 : 09:47:18

I am stuck with a query since few days. I tried to solve this, but not much familar with various methods to extract the data. Could anyonne please help me...

My problem is ...I have 2 tables
First table:
Agencyid,
Positionid,
levelid,
T,
C,
N ,
Updateddate
Second Table
Levelid,
Levelname

For every single agency id in the first table there are differnet positionids associated with that.

for eg: the data in the first table will be like this: i.e., for each agency theer are multiple positions and for each position theer are five levels associated with that position.

Agencyid 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2

PositioniD 1 1 1 1 1 2 2 2 2 2 3 3 3 3 3

Levelid 1 2 3 4 5 1 2 3 4 5 1 2 3 4 5
T int
C int
N int
Updateddate

THere are 5 levels in the second table:
LevelA --id 1,
LevelB --id 2,
LevelC --id 3,
LevelD --id 4,
LevelE --id 5

I want to retrive the following feilds from both the tables.

Agencyid
PositioniD
LevelA-T
LevelA-C
LevelA-N
LevelB-T
LevelB-C
LevelB-N
LevelC-T
LevelC-C
LevelC-N
LevelD-T
LevelD-C
LevelD-N
LevelE-T
LevelE-C
LevelE-N

.. I dont know how to query that. I tried using joins , but i am getting multiple records. You help will be very much appreciated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 09:54:32

SELECT *
FROM Table1
CROSS JOIN Table2

E 12°55'05.25"
N 56°04'39.16"

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2008-05-06 : 10:00:40

Hi Peso,

I think this is not the one that i want.. I want to retrive the t,c,n of different levels for a particluar position ina single row. This doesn't solve my purpose

Thanks,
Ragha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 10:04:52

It does for me according to your specification.

Otherwise see
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2008-05-06 : 10:26:09

This one i have written is working fine.... Iit populates me the data in way that i need , but i want to optimize the query..plz help...

select max(agencyid) agencyid,max(positionid) positionid,

LevelA_T=(select t from table1 t1 where t1.agencyid=t.agencyid and t1.positionid=t.positionid and t1.levelid=1),

LevelA_C=(select C from table1 t2 where t2.agencyid=t.agencyid and t2.positionid=t.positionid and t2.levelid=1),

LevelA_N=(select N from table1 t3 where t3.agencyid=t.agencyid and t3.positionid=t.positionid and t3.levelid=1) ,

LevelB_T=(select t from table1 t1 where t1.agencyid=t.agencyid and t1.positionid=t.positionid and t1.levelid=2),

LevelB_C=(select C from table1 t2 where t2.agencyid=t.agencyid and t2.positionid=t.positionid and t2.levelid=2),

LevelB_N=(select N from table1 t3 where t3.agencyid=t.agencyid and t3.positionid=t.positionid and t3.levelid=2),

LevelC_T=(select t from table1 t1 where t1.agencyid=t.agencyid and t1.positionid=t.positionid and t1.levelid=3),

LevelC_C=(select C from table1 t2 where t2.agencyid=t.agencyid and t2.positionid=t.positionid and t2.levelid=3),

LevelC_N=(select N from table1 t3 where t3.agencyid=t.agencyid and t3.positionid=t.positionid and t3.levelid=3),

LevelD_T=(select t from table1 t1 where t1.agencyid=t.agencyid and t1.positionid=t.positionid and t1.levelid=4),

LevelD_C=(select C from table1 t2 where t2.agencyid=t.agencyid and t2.positionid=t.positionid and t2.levelid=4),

LevelD_N=(select N from table1 t3 where t3.agencyid=t.agencyid and t3.positionid=t.positionid and t3.levelid=4),

LevelE_T=(select t from table1 t1 where t1.agencyid=t.agencyid and t1.positionid=t.positionid and t1.levelid=5),

LevelE_C=(select C from table1 t2 where t2.agencyid=t.agencyid and t2.positionid=t.positionid and t2.levelid=5),

LevelE_N=(select N from table1 t3 where t3.agencyid=t.agencyid and t3.positionid=t.positionid and t3.levelid=5)

from table1 T group by t.agencyid, t.positionid

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 11:21:53

[code]SELECT AgencyID,
PositionID,
MAX(CASE WHEN LevelID = 1 THEN T ELSE '' END) AS LevelA_T,
MAX(CASE WHEN LevelID = 1 THEN C ELSE '' END) AS LevelA_C,
MAX(CASE WHEN LevelID = 1 THEN N ELSE '' END) AS LevelA_N,
MAX(CASE WHEN LevelID = 2 THEN T ELSE '' END) AS LevelB_T,
MAX(CASE WHEN LevelID = 2 THEN C ELSE '' END) AS LevelB_C,
MAX(CASE WHEN LevelID = 2 THEN N ELSE '' END) AS LevelB_N,
MAX(CASE WHEN LevelID = 3 THEN T ELSE '' END) AS LevelC_T,
MAX(CASE WHEN LevelID = 3 THEN C ELSE '' END) AS LevelC_C,
MAX(CASE WHEN LevelID = 3 THEN N ELSE '' END) AS LevelC_N,
MAX(CASE WHEN LevelID = 4 THEN T ELSE '' END) AS LevelD_T,
MAX(CASE WHEN LevelID = 4 THEN C ELSE '' END) AS LevelD_C,
MAX(CASE WHEN LevelID = 4 THEN N ELSE '' END) AS LevelD_N,
MAX(CASE WHEN LevelID = 5 THEN T ELSE '' END) AS LevelE_T,
MAX(CASE WHEN LevelID = 5 THEN C ELSE '' END) AS LevelE_C,
MAX(CASE WHEN LevelID = 5 THEN N ELSE '' END) AS LevelE_N
FROM Table1
GROUP BY AgencyID,
PositionID[/code]

E 12°55'05.25"
N 56°04'39.16"

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2008-05-06 : 11:50:28

Hi Peso,

Thank you very much for your help.

Regards,
Ragha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 11:56:34

Thank you for your feedback.

E 12°55'05.25"
N 56°04'39.16"

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2008-06-02 : 13:09:29

Can anyone let me know , what isf I dont want t o use the max function

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 13:15:57

quote:
Originally posted by RaghaSM

Can anyone let me know , what isf I dont want t o use the max function

You need to use some kind of aggregation function on other columns when you group your results. Thats the reason why the query uses MAX() function. DIdnt understand why you dont want to use it?

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2008-06-02 : 13:38:37

quote:
Originally posted by visakh16

quote:
Originally posted by RaghaSM

Can anyone let me know , what isf I dont want t o use the max function

You need to use some kind of aggregation function on other columns when you group your results. Thats the reason why the query uses MAX() function. DIdnt understand why you dont want to use it?

Hi ,

Did ou gt a chance to see my query that posted at the top. I want to retrive the data in a similar manner , but while checking h ething that the updateddate from the table1 is between 2 dates.
can you let me know. How should i do that?
Your help will be very much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 13:55:34

quote:
Originally posted by RaghaSM

quote:
Originally posted by visakh16

quote:
Originally posted by RaghaSM

Can anyone let me know , what isf I dont want t o use the max function

You need to use some kind of aggregation function on other columns when you group your results. Thats the reason why the query uses MAX() function. DIdnt understand why you dont want to use it?

Hi ,

Did ou gt a chance to see my query that posted at the top. I want to retrive the data in a similar manner , but while checking h ething that the updateddate from the table1 is between 2 dates.
can you let me know. How should i do that?
Your help will be very much appreciated.

To be honest your explanation is pretty confusing. Anyways i'll make a try. I'm hoping this is what you want

SELECT ft.Agencyid,ft.PositioniD,
SUM(CASE WHEN Levelname='LevelA' THEN ft.T ELSE 0 END) AS [LevelA-T],
SUM(CASE WHEN Levelname='LevelA' THEN ft.C ELSE 0 END) AS [LevelA-C],
SUM(CASE WHEN Levelname='LevelA' THEN ft.N ELSE 0 END) AS [LevelA-N],
SUM(CASE WHEN Levelname='LevelB' THEN ft.T ELSE 0 END) AS [LevelB-T],
SUM(CASE WHEN Levelname='LevelB' THEN ft.C ELSE 0 END) AS [LevelB-C],
SUM(CASE WHEN Levelname='LevelB' THEN ft.N ELSE 0 END) AS [LevelB-N],
SUM(CASE WHEN Levelname='LevelC' THEN ft.T ELSE 0 END) AS [LevelC-T],
SUM(CASE WHEN Levelname='LevelC' THEN ft.C ELSE 0 END) AS [LevelC-C],
SUM(CASE WHEN Levelname='LevelC' THEN ft.N ELSE 0 END) AS [LevelC-N],
SUM(CASE WHEN Levelname='LevelD' THEN ft.T ELSE 0 END) AS [LevelD-T],
SUM(CASE WHEN Levelname='LevelD' THEN ft.C ELSE 0 END) AS [LevelD-C], 
       ...

FROM FirstTable ft
INNER JOIN  SecondTable st
ON st.Levelid=ft.Levelid
GROUP BY ft.Agencyid,ft.PositioniD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources