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.
| Author |
Topic |
|
dabooj
Starting Member
7 Posts |
 Posted - 2009-04-16 : 07:45:22
|
| Hi,I have a funding table which has records listed per year for a business case. BUsiness case is the parent table & the funding table is the child table with a one-to-many relationship.Each business case can therefore have one funding record listed which shows 1 year or it can have many funding records for a number of years. The years will fall into 2007-2012 as this is the length of business case timeline.EG, Business case 1 has funding record £5000 for 2007.Business case 2 has funding records £3000 for 2009, £5000 for 2010, £7000 for 2011.For a main report that i have, i have to list the year data grouped by the year so i have totals for each year BUT i'm struggling to figure out how to do this because i need to list all possible years in a matrix. Further more, this needs to be grouped by the project lead field so it lists all possible years of funding per person (each project lead can be a lead for 1 or more cases).EG, Joe Bloggs (project lead) (lead of 3 business cases grouped in the following totals)year - total (£)2007 - £100002008 - NULL2009 - £200002010 - £150002011 - NULL2012 - £4000Jim Bob (project lead) (lead of 1 business case grouped in the following totals)year - total (£)2007 - NULL2008 - NULL2009 - £2002010 - NULL2011 - NULL2012 - £3500I created a static table to hold the years as records in the table. I then tried to link this table with my funding table as a right outer join to show all records from the year table but this shows the related years and then the additional years as single entiries. I need to seperate the records so ALL years are listed for each project lead & the amounts are summed by year.I know this is a bit confusing so i hope i have explained this well. Can anyone help with this one?Regards,ShujaShuja AhmadSystems Developer |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2009-04-16 : 07:50:53
|
| Hi. Follow the first link in my signature, then restate your question following the suggestions in the link. It will make your question MUCH easier to understand AND answer. Now that I have said this, "Entry Level DBA"™ Peso will probably answer it instantly.[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQL or How to sell Used CarsFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
 |
|
|
aprichard
Yak Posting Veteran
62 Posts |
Posted - 2009-04-16 : 08:02:01
|
| Can you post sample data and proposed output |
|
|
|
dabooj
Starting Member
7 Posts |
|
|
|
|
|
|
|
Show all records from one table for EACH record in