?
Solved

display rolling 12 month data

Posted on 2010-08-16
4
Medium Priority
?
558 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 2000 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

777 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