SQL Help

[wiskoththa]

mata me attach karala tiyena result eka ganna script ekak dennako


Result eke enna ona max (year) eke max (month) eke figures

 

[wiskoththa]

 

[hspa3.5]

problem 1k not clear machan table tika ko,,,,

 

[wiskoththa]

problem 1k not clear machan table tika ko,,,,

machan attachment eke tiyenawa table 2i ona karana result 1i okkoma tiyenawa..

 

[wiskoththa]

bump

 

[hspa3.5]

machan oya table structure hatiyata oka karanna amarui bn

mehema karapan

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


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



Table3
======
ID Type Month Year Amount
-------------------------------------------
1 Incent 1 2011 500
3 Incent 1 2011 500


mehem num lesai

sql vala maximum kiyala function 1k thiyanava

eg select max(Amount) from table3 ;

 

[wiskoththa]

machan oya table structure hatiyata oka karanna amarui bn

mehema karapan

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


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



Table3
======
ID Type Month Year Amount
-------------------------------------------
1 Incent 1 2011 500
3 Incent 1 2011 500


mehem num lesai

sql vala maximum kiyala function 1k thiyanava

eg select max(Amount) from table3 ;

tnx 4 d effort bro...
tawa kauruwat nedda udawwata

 

[sudu4u]

Machan Meka try karapan,mama check kare naha


SELECT Table1.ID,Table1.TEAM,Table2.MONTH,Table2.YEAR,Table2.AMOUNT FROM Table1,Table2 WHERE (Table1.ID =Table2.ID) ORDER BY year,month DESC;

 

[wiskoththa]

Machan Meka try karapan,mama check kare naha


SELECT Table1.ID,Table1.TEAM,Table2.MONTH,Table2.YEAR,Table2.AMOUNT FROM Table1,Table2 WHERE (Table1.ID =Table2.ID) ORDER BY year,month DESC;

mata ona me widihata machan

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

 

[OCP]

SELECT T2.ID, T1.NAME, T1.TEAM T2.MONTH, T2.YEAR, DECODE(T2.TYPE,'ot',T2.AMOUNT) AS OT, DECODE(T2.TYPE,'Incent',T2.AMOUNT) AS INCENT
FROM TABLE2 T2, TABLE1
WHERE T1.ID = T2.ID

 

[OCP]

SELECT T2.ID, T1.NAME, T1.TEAM T2.MONTH, T2.YEAR, DECODE(T2.TYPE,'ot',T2.AMOUNT) AS OT, DECODE(T2.TYPE,'Incent',T2.AMOUNT) AS INCENT
FROM TABLE2 T2, TABLE1
WHERE T1.ID = T2.ID

boss meka nam hari yanna one...

 

[dushan1234]

try this

select table1.ID,table1.NAME,table1.Team ,table2.Type,MAXYEAR.Year,Max(Month) as Month ,Max(Amount) as Amount from table1 INNER JOIN table2 on table1.ID = table2.ID
Inner join
(select table1.ID,table2.Type,MAX(Year) as Year from table1 INNER JOIN table2 on table1.ID = table2.ID
group by table1.ID,table2.Type) MAXYEAR on
table1.ID = MAXYEAR.ID AND table2.Year = MAXYEAR.Year
Group by table1.ID,table1.NAME,table1.Team,MAXYEAR.Year,table2.Type

 

[wiskoththa]

SELECT T2.ID, T1.NAME, T1.TEAM T2.MONTH, T2.YEAR, DECODE(T2.TYPE,'ot',T2.AMOUNT) AS OT, DECODE(T2.TYPE,'Incent',T2.AMOUNT) AS INCENT
FROM TABLE2 T2, TABLE1
WHERE T1.ID = T2.ID

'DECODE' is not a recognized built-in function name.

 

[OCP]

oya mssql da? oracle wala nam gindara wage wada..

 

[¤--bACarDi--¤]

use CASE expression instead of Decode, decode works on oracle

 

[OCP]

use CASE expression instead of Decode, decode works on oracle

 

[¤--bACarDi--¤]

DECODE(T2.TYPE,'ot',T2.AMOUNT) AS OT

=

( CASE T2.TYPE WHEN 'ot' THEN T2.AMOUT )


would work, give it a try

 

[wiskoththa]

try this

select table1.ID,table1.NAME,table1.Team ,table2.Type,MAXYEAR.Year,Max(Month) as Month ,Max(Amount) as Amount from table1 INNER JOIN table2 on table1.ID = table2.ID
Inner join
(select table1.ID,table2.Type,MAX(Year) as Year from table1 INNER JOIN table2 on table1.ID = table2.ID
group by table1.ID,table2.Type) MAXYEAR on
table1.ID = MAXYEAR.ID AND table2.Year = MAXYEAR.Year
Group by table1.ID,table1.NAME,table1.Team,MAXYEAR.Year,table2.Type

machan et meken max(year) eke max(month) record eka witarak enne ne machan...
eka ID ekakin tiyena hema record ekakma enawa..

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

 

[wiskoththa]

oya mssql da? oracle wala nam gindara wage wada..

mssql bun

 

[twisted]

drop table1
go
drop table2
go