Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql query help

Posted on 2013-01-04
6
Medium Priority
?
231 Views
Last Modified: 2013-01-11
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?
0
Comment
Question by:welcome 123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 

Author Comment

by:welcome 123
ID: 38744879
I found a query to find out current fiscalyear online which is: select YEAR(DATEADD(month, 6 + DATEDIFF(month, 0, meetingdate), 0)) as 'fiscalyear'
0
 
LVL 9

Accepted Solution

by:
sarabhai earned 1200 total points
ID: 38745044
SELECT r.CBRNO, CASE YEAR(Meetingdate) WHEN fy1 THEN A1 WHEN fy2 THEN A2 ELSE 0 END as Amount
FROM request r
INNER JOIN (SELECT CBRNO , SUM(Amount1) AS A1, SUM(Amount2) AS A2,fy1,fy2
FROM fund GROUP BY fy1,fy2, CBRNO ) AS t2
ON r.CBRNO = t2.CBRNO
0
 

Author Comment

by:welcome 123
ID: 38745242
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
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 9

Expert Comment

by:sarabhai
ID: 38746147
you should get the idea how to handle query, only thing is remains your financial year logic.
0
 

Author Comment

by:welcome 123
ID: 38756059
sorry i was using different columns and hot the query right but how do I handle null values which is throwing exceptions?
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 800 total points
ID: 38761381
try this.
SELECT F.* 
  FROM Request R 
       JOIN (SELECT CBRNO,FY,SUM(Amount1) Amount 
               FROM (SELECT CBRNO,FY1 FY,Amount1 
                       FROM Fund 
                     UNION ALL 
                     SELECT CBRNO,FY2,Amount2 
                       FROM Fund) T1 
              GROUP BY CBRNO,FY) F 
         ON R.CBRNO = F.CBRNO 
            AND YEAR(DATEADD(month, 6 + DATEDIFF(month, 0, R.Meetingdate), 0)) = F.FY 

Open in new window

0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question