Solved

display rolling 12 month data

Posted on 2010-08-16
4
554 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:iepaul
[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
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
Larissa T earned 500 total points
ID: 33445606
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
 

Author Comment

by:iepaul
ID: 33452718
What happens when the dates move into 2011?  This will be a 12 month rolling display.
0
 
LVL 11

Expert Comment

by:Larissa T
ID: 33454376
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33454458
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 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