[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

display rolling 12 month data

Posted on 2010-08-16
4
Medium Priority
?
562 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
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
Lara F 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:Lara F
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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
Suggested Courses

872 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