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

General SQL Server Forums

New to SQL Server Programming

Query Help

Author

Topic

wisko
Starting Member

8 Posts

Posted - 2011-06-22 : 07:16:48

hi all, im a newbie to this forum as well as for SQL Queries

hope you'll help me to sort this out as im really really tired of this..

and i hope im not posting this in an irrelevant forum

heres my case..

i have 2 tables as follows

Table1	
======					
ID      Name    Team	
---------------------			
1       A       X				
2       B       Y				
3       C       Z

Table2	
======					
ID       Type       Month       Year       Amount	
-------------------------------------------	
1        OT         1           2011       1000		
1        Incent	    1           2011       500		
2        OT         12          2010       2000		
2        OT         1           2011       1500		
3        OT         12          2010       1000		
3        Incent	    1           2011       500

all i wanted is to generate the following result by running a query

Result
======						
ID      Name      team      Month      Year      OT      Incent
-------------------------------------------------------
1       A         X         1          2011      1000    500
2       B         Y         1          2011      1500    -
3       C         Z         1          2011      -       500

i hope i made myself clear to you all... and hope someone would take me out of this trouble

thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-22 : 07:46:56

Would this work for you? Month 12 does not appear in your results, but it would in this query, so I am not sure if this is what you are looking for


SELECT 
	t1.id, t1.name, t1.team,
	t2.month,
	t2.year,
	SUM(CASE WHEN t2.type = 'OT' THEN Amount END) AS OT,
	SUM(CASE WHEN t2.type = 'Incent' THEN Amount END) AS Incent
FROM
	Table1 t1
	INNER JOIN Table2 t2 ON
		t1.id = t2.id
GROUP BY
	t1.id, t1.name, t1.team,
	t2.month,
	t2.year

wisko
Starting Member

8 Posts

Posted - 2011-06-22 : 08:13:27

quote:
Originally posted by sunitabeck

Would this work for you? Month 12 does not appear in your results, but it would in this query, so I am not sure if this is what you are looking for

SELECT
t1.id, t1.name, t1.team,
t2.month,
t2.year,
SUM(CASE WHEN t2.type = 'OT' THEN Amount END) AS OT,
SUM(CASE WHEN t2.type = 'Incent' THEN Amount END) AS Incent
FROM
Table1 t1
INNER JOIN Table2 t2 ON
t1.id = t2.id
GROUP BY
t1.id, t1.name, t1.team,
t2.month,
t2.year

Hi sunita

thnks for your help... well.. there are few things to be changed..
infact the results should generate OT & Incent amounts for the max(month) of the max(year) for each table1.ID. thats why i omitted the data of 12 month.

i hope you got a better idea of my requirement.. thnks again dear

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-22 : 11:49:50

Probably easiest to use row_number function if you are on SQL 2005 or higher:


SELECT * FROM 
(
SELECT 
	ROW_NUMBER() OVER (PARTITION BY t1.id, t1.name, t1.team ORDER BY t2.year*12+t2.month desc) AS RN,
	t1.id, t1.name, t1.team,
	t2.month,
	t2.year,
	SUM(CASE WHEN t2.type = 'OT' THEN Amount END) AS OT,
	SUM(CASE WHEN t2.type = 'Incent' THEN Amount END) AS Incent
FROM
	Table1 t1
	INNER JOIN Table2 t2 ON
		t1.id = t2.id
GROUP BY
	t1.id, t1.name, t1.team,
	t2.month,
	t2.year	
) T
WHERE RN=1

wisko
Starting Member

8 Posts

Posted - 2011-06-22 : 12:02:02

pls help

wisko
Starting Member

8 Posts

Posted - 2011-06-22 : 13:14:03

quote:

Originally posted by sunitabeck

Probably easiest to use row_number function if you are on SQL 2005 or higher:


SELECT * FROM 
(
SELECT 
	ROW_NUMBER() OVER (PARTITION BY t1.id, t1.name, t1.team ORDER BY t2.year*12+t2.month desc) AS RN,
	t1.id, t1.name, t1.team,
	t2.month,
	t2.year,
	SUM(CASE WHEN t2.type = 'OT' THEN Amount END) AS OT,
	SUM(CASE WHEN t2.type = 'Incent' THEN Amount END) AS Incent
FROM
	Table1 t1
	INNER JOIN Table2 t2 ON
		t1.id = t2.id
GROUP BY
	t1.id, t1.name, t1.team,
	t2.month,
	t2.year	
) T
WHERE RN=1

great! thanks alot dear...
that worked exactly as i wanted

wisko
Starting Member

8 Posts

Posted - 2011-06-23 : 07:07:26

quote:

Originally posted by sunitabeck

Probably easiest to use row_number function if you are on SQL 2005 or higher:


SELECT * FROM 
(
SELECT 
	ROW_NUMBER() OVER (PARTITION BY t1.id, t1.name, t1.team ORDER BY t2.year*12+t2.month desc) AS RN,
	t1.id, t1.name, t1.team,
	t2.month,
	t2.year,
	SUM(CASE WHEN t2.type = 'OT' THEN Amount END) AS OT,
	SUM(CASE WHEN t2.type = 'Incent' THEN Amount END) AS Incent
FROM
	Table1 t1
	INNER JOIN Table2 t2 ON
		t1.id = t2.id
GROUP BY
	t1.id, t1.name, t1.team,
	t2.month,
	t2.year	
) T
WHERE RN=1

one small thing...
how can i get the data of only one ID by simply adding

 
) T
WHERE RN=1 and t1.id=

clause at the end of this script

it says

The multi-part identifier "t1.id" could not be bound.

when i was trying to do so... pls help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-23 : 07:15:42

What you are doing should work (but the table name would be "t" and NOT t1, because we aliased the virtual table from the sub-query to be T:


....
WHERE RN=1 and t.id=2

But it may be a little bit more efficient to add that condition inside the sub-query like this:


SELECT * FROM 
(
SELECT 
	ROW_NUMBER() OVER (PARTITION BY t1.id, t1.name, t1.team ORDER BY t2.year*12+t2.month desc) AS RN,
	t1.id, t1.name, t1.team,
	t2.month,
	t2.year,
	SUM(CASE WHEN t2.type = 'OT' THEN Amount END) AS OT,
	SUM(CASE WHEN t2.type = 'Incent' THEN Amount END) AS Incent
FROM
	Table1 t1
	INNER JOIN Table2 t2 ON
		t1.id = t2.id
WHERE
	t1.id = 2
GROUP BY
	t1.id, t1.name, t1.team,
	t2.month,
	t2.year	
) T
WHERE RN=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources