welcome 123
asked on
sql query help
I have a table which looks like the below:
Fund Table
CBRNO item FY1 Amount1 FY2 Amount2
CCC01 1 2012 100 2013 200
CCC01 2 2012 100 2013 100
AAA01 1 2012 100 2013 300
Request Table
CBRNO Meetingdate
CCC01 05/05/2012
AAA01 09/05/2012
I want results like this:
CBRNO Amount
CCC01 200
AAA01 300
That means I want the result of total amount of the request where meeting date falls under he curent fiscal year i.e for CCC01 the meetingdate is 2012 fiscalyear(before jube 30th 2012) so the FY1 amount is tottaled for all items and similarly the meetingdate for AAA01 is 2013 fiscal year do FY2 amount was considered.
I need to know how I can make up a sql query for this. I use sqlserver and use .Net,kindly help?
Fund Table
CBRNO item FY1 Amount1 FY2 Amount2
CCC01 1 2012 100 2013 200
CCC01 2 2012 100 2013 100
AAA01 1 2012 100 2013 300
Request Table
CBRNO Meetingdate
CCC01 05/05/2012
AAA01 09/05/2012
I want results like this:
CBRNO Amount
CCC01 200
AAA01 300
That means I want the result of total amount of the request where meeting date falls under he curent fiscal year i.e for CCC01 the meetingdate is 2012 fiscalyear(before jube 30th 2012) so the FY1 amount is tottaled for all items and similarly the meetingdate for AAA01 is 2013 fiscal year do FY2 amount was considered.
I need to know how I can make up a sql query for this. I use sqlserver and use .Net,kindly help?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
when I ran the above query the first thing I got was a error message :Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'NULL' to data type int.
Then I Took only one cbrno whose valuse in both tables as below:
Fund Table
CBRNO Item FY1 FY2 Amount1 Amount2
LOR01 1 2008 2009 25946116.00 27085265.00
LOR01 2 2008 2009 18743774.00 18693328.00
LOR01 3 2008 2009 2554500.00 2446500.00
LOT01 4 2008 2009 50419630.00 51250704.00
Request Table
CBRNO Meetingdate
LOR01 05/05/2008
and the result came different when I ran the above query which is:
SELECT r.CBRNO, CASE YEAR(r.Meetingdate) WHEN fy1 THEN A1 WHEN fy2 THEN A2 ELSE 0 END as Amount
FROM request r
INNER JOIN (SELECT CBRNO , SUM(newfund1) AS A1, SUM(newfund2) AS A2,fy1,fy2
FROM fund GROUP BY fy1,fy2, CBRNO ) AS t2
ON r.CBRNO = t2.CBRNO
where r.cbrno in ('LOR01')
CBRNO Amount
LOT0100004 9832000.00
where as the amount total should be 97664020
Conversion failed when converting the varchar value 'NULL' to data type int.
Then I Took only one cbrno whose valuse in both tables as below:
Fund Table
CBRNO Item FY1 FY2 Amount1 Amount2
LOR01 1 2008 2009 25946116.00 27085265.00
LOR01 2 2008 2009 18743774.00 18693328.00
LOR01 3 2008 2009 2554500.00 2446500.00
LOT01 4 2008 2009 50419630.00 51250704.00
Request Table
CBRNO Meetingdate
LOR01 05/05/2008
and the result came different when I ran the above query which is:
SELECT r.CBRNO, CASE YEAR(r.Meetingdate) WHEN fy1 THEN A1 WHEN fy2 THEN A2 ELSE 0 END as Amount
FROM request r
INNER JOIN (SELECT CBRNO , SUM(newfund1) AS A1, SUM(newfund2) AS A2,fy1,fy2
FROM fund GROUP BY fy1,fy2, CBRNO ) AS t2
ON r.CBRNO = t2.CBRNO
where r.cbrno in ('LOR01')
CBRNO Amount
LOT0100004 9832000.00
where as the amount total should be 97664020
you should get the idea how to handle query, only thing is remains your financial year logic.
ASKER
sorry i was using different columns and hot the query right but how do I handle null values which is throwing exceptions?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER