display rolling 12 month data

I am trying to replace an Excel spreadsheet and need to display rolling 12 month data. Here is some sample data.

tblProject                        
fldProjID,fldRegistration,fldStartDate,fldEndDate,fldHours
1,111ABC,25/07/2010,02/08/2010,16000
2,222ABC,09/08/2010,10/09/2010,24000
3,333ABC,02/09/2010,20/09/2010,24000
4,444ABC,20/10/2010,05/11/2010,32000

tblHours                        
fldHoursID,fldProjID,fldDate,fldHours      
1,1,01/07/2010,12000      
2,1,01/08/2010,4000      
3,2,01/08/2010,15750      
4,2,01/09/2010,8250      
5,3,01/09/2010,24000      
6,4,01/10/2010,22000      
7,4,01/11/2010,10000

I have attached a screenshot of how I would like the data to appear.  I have given two views and you can see how the months have rolled.

The user needs to be able to edit the data.  How can I achieve this with SQL server?

I will use VB.NET to present the data to the user.


screenshot.png
iepaulAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lara FEACommented:
Add lines for all 12 month as in code below
drop table #tblProject
drop table #tblHours
create table #tblProject       (                
fldProjID int,fldRegistration varchar(20),fldStartDate datetime,fldEndDate datetime,fldHours int)
insert into #tblProject values(1,'111ABC','07/25/2010','08/02/2010',16000)
insert into #tblProject values(2,'222ABC','08/09/2010','09/10/2010',24000)
insert into #tblProject values(3,'333ABC','09/02/2010','09/20/2010',24000)
insert into #tblProject values(4,'444ABC','10/20/2010','11/05/2010',32000)

create table #tblHours   (fldHoursID int,fldProjID int,fldDate datetime,fldHours      int)
insert into #tblHours values(1,1,'08/01/2010',12000      )
insert into #tblHours values(2,1,'08/01/2010',4000      )
insert into #tblHours values(3,2,'08/01/2010',15750     ) 
insert into #tblHours values(4,2,'09/01/2010',8250      )
insert into #tblHours values(5,3,'09/01/2010',24000     ) 
insert into #tblHours values(6,4,'10/01/2010',22000      )
insert into #tblHours values(7,4,'10/01/2010',10000)

select p.fldProjID, p.fldRegistration, min(h.fldDate), max(fldDate), sum (h.fldHours)
	,sum(case when datepart(month,fldDate)=1 then h.fldHours else 0 end) jan
	,sum(case when datepart(month,fldDate)=8 then h.fldHours else 0 end) aug
	,sum(case when datepart(month,fldDate)=9 then h.fldHours else 0 end) sep
	,sum(case when datepart(month,fldDate)=10 then h.fldHours else 0 end) sep

from #tblProject p join #tblHours h on p.fldProjID=h.fldProjID
group by p.fldProjID, p.fldRegistration

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
iepaulAuthor Commented:
What happens when the dates move into 2011?  This will be a 12 month rolling display.
0
Lara FEACommented:
Well,
datepart(month,fldDate)=1 function returns month number. January was 1 month of the year in 2010 and it will be 1-st month of the year in 2011. :-)

To have report for 12 month only add to where clause
fldDate>=dateadd(month, 12,getadate())
or
fldDate>=getadete()- 365

depending on your definition of 12 month
0
Rey Obrero (Capricorn1)Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.