Solved

display rolling 12 month data

Posted on 2010-08-16
4
555 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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