SQL COMPLEX SELECT QUERY NEEDED..

Question

0.00/5 (No votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

Hi I need a query form 2 tables.

TABLE1:

ID MONTH YEAR AMT
1 2 2012 200
1 5 2012 300
1 12 2012 500
1 5 2013 600
1 12 2013 700
2 2 2012 800
2 5 2012 900
2 12 2012 1000
2 3 2013 2000
2 12 2013 7000

TABLE2:

Month YEAR
1 2012
2 2012
3 2012
4 2012
5 2012
6 2012
7 2012
8 2012
9 2012
10 2012
11 2012
12 2012
1 2013
2 2013
3 2013
4 2013
5 2013
6 2013
7 2013
8 2013
9 2013
10 2013
11 2013
12 2013

NOW I NEED TO GET A OUTPUT DATA LIKE

ID MONTH YEAR AMT
1 2 2012 200
1 3 2012 200
1 4 2012 200
1 5 2012 300
1 6 2012 300
1 7 2012 300
1 8 2012 300
1 9 2012 300
1 10 2012 300
1 11 2012 300
1 12 2012 500
1 1 2013 500
1 2 2013 500
1 3 2013 500
1 4 2013 500
1 5 2013 600
1 6 2013 600
1 7 2013 600
1 8 2013 600
1 9 2013 600
1 10 2013 600
1 11 2013 600
1 12 2013 700
2 2 2012 800
2 3 2012 800
2 4 2012 800
2 5 2012 900
2 6 2012 900
2 7 2012 900
2 8 2012 900
2 9 2012 900
2 10 2012 900
2 11 2012 900
2 12 2012 1000
2 1 2013 1000
2 2 2013 1000
2 3 2013 2000
2 4 2013 2000
2 5 2013 2000
2 6 2013 2000
2 7 2013 2000
2 8 2013 2000
2 9 2013 2000
2 10 2013 2000
2 11 2013 2000
2 12 2013 7000

THIS IS the desired output.

Can some SQL genius can provide the query for this..

Posted 23-Feb-14 18:53pm

praveen.victor

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

King Fisher · Answer 1 · 2014-02-23T19:07:00

Solution 1

Use This:

SQL

SELECT Table4.month,table4.year,  Max(Table3.Amt)as amt
FROM Table3, Table4
where table4.month>=table3.month  and table4.year>=table3.year
group by table4.month , table4.year

Posted 23-Feb-14 19:07pm

King Fisher

Comments

praveen.victor 24-Feb-14 7:51am

When the table3 Data is is:
id month year value
115206 3 2009 8780
115206 6 2009 8780
115206 10 2009 11025
115206 5 2010 13650
115206 5 2011 16975
115206 8 2011 16975
115206 11 2011 16975
115206 1 2012 16975
115206 6 2012 18450
115206 7 2012 18450
115206 8 2012 18450
115206 9 2012 18450
115206 10 2012 18450
115206 12 2012 18450
115206 4 2013 18450
115206 5 2013 18450
115206 6 2013 18450
115206 7 2013 18450
115206 8 2013 19675
115206 10 2013 19675

Table4 data is :

month year
4 2012
5 2012
6 2012
7 2012
8 2012
9 2012
10 2012
11 2012
12 2012
1 2013
2 2013
3 2013
4 2013
5 2013
6 2013
7 2013
8 2013
9 2013
10 2013
11 2013
12 2013

The out put i am getting is :

month year amt
4 2012 18450
5 2012 18450
6 2012 18450
7 2012 18450
8 2012 19675
9 2012 19675
10 2012 19675
11 2012 19675
12 2012 19675
1 2013 16975
2 2013 16975
3 2013 16975
4 2013 18450
5 2013 18450
6 2013 18450
7 2013 18450
8 2013 19675
9 2013 19675
10 2013 19675
11 2013 19675
12 2013 19675

in the output the 2013 year 1,2 and 3 month value are wrong..

PLease help on this

SQL COMPLEX SELECT QUERY NEEDED..

1 solution

Solution 1

Add your solution here

Preview 0

SQL COMPLEX SELECT QUERY NEEDED..

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us