Solved

Need help recreating an excel lieu time sheet in SSRS

Posted on 2014-12-01
6
274 Views
Last Modified: 2014-12-09
I'm trying to recreate a lieu time report currently in excel.  I have a table storing hours entered, the code (billable, personal, vacation, etc.) against which it is entered, the date and the employee id.  I can easily pull out all the data for a given employee beginning with the start of our fiscal year (May 1) but I'm not sure about how to group it by the week ending.  Plus I have a separate table where I've generated all the days of the year and marked it as either a work day, weekend or holiday (as well as other information).  

This is the sheet I'm trying to recreate:
Lieu-report.png  

And here is the code I used to create the calendar table (I didn't include the last part where I mark the holidays).  Though I think perhaps I should have included the week ending day.
IF EXISTS(SELECT * FROM sys.tables WHERE name='WorkCalendar') 
	DROP TABLE WorkCalendar
GO

CREATE TABLE WorkCalendar (
	PKDate date primary key, 
	-- Years
	calendar_year smallint,
	-- Quarters 
	calendar_quarter tinyint, 
	calendar_quarter_desc varchar(10),  
	-- Months
	calendar_month tinyint, 
	calendar_month_name_long varchar(30), 
	calendar_month_name_short varchar(10), 
	-- Weeks
	calendar_week_in_year tinyint, 
	calendar_week_in_month tinyint, 
  
    -- added
	pay_week_in_month tinyint,  
  
	-- Days
	calendar_day_in_year smallint, 
	calendar_day_in_week tinyint,  -- The first of the month 
	calendar_day_in_month tinyint, 
	mdy_name_long varchar(30), 
	mdy_name_long_with_suffix varchar(30), 
	day_name_long varchar(10), 
	day_name_short varchar(10), 
	description varchar(30), 
	is_weekend tinyint,            -- Tinyint and not bit so you can add the 1's. 
	is_holiday tinyint,            -- Tinyint and not bit so you can add the 1's. 
	is_workday tinyint,            -- Tinyint and not bit so you can add the 1's. 
	is_event tinyint               -- Used to indicate any special event days. 
	)
;

DECLARE @dt_start date = '20120101'
      , @dt_end date   = '20160430'
      , @total_days int
      , @i int = 0
      , @start_Month int = 5 -- added


SELECT @total_days = DATEDIFF(d, @dt_start, @dt_end) 

WHILE @i <= @total_days
	begin
	INSERT INTO WorkCalendar (PKDate) 
	SELECT CAST(DATEADD(d, @i, @dt_start) as DATE) 

	SET @i = @i + 1
	end
;

UPDATE WorkCalendar
SET 
      calendar_year = YEAR(PKDate), 
      calendar_quarter = DATEPART(q, PKDate),
      calendar_month = DATEPART(m, PKDate), 
      calendar_week_in_year = DATEPART(WK, PKDate), 
      calendar_day_in_year = DATEPART(dy, PKDate), 
      calendar_day_in_week = DATEPART(Weekday, PKDate),
      calendar_day_in_month = DATEPART(d, PKDate),
      day_name_long = datename(weekday, PKDate)
;

UPDATE WorkCalendar
SET 
      is_weekend = CASE DATEPART(weekday, PKDate) WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END, 
      calendar_quarter_desc =  'Q' + CAST(calendar_quarter as char(1)) + ' ' + CAST(calendar_year as char(4)), 
      calendar_month_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_year as CHAR(4)),
      mdy_name_long = DATENAME(m, PKDate) + ' ' + CAST(DATEPART( d, PKDate ) as varchar(2)) + ', '  + CAST(calendar_year as CHAR(4)),
      day_name_short = LEFT(datename(weekday, PKDate),3)
;
UPDATE WorkCalendar
SET calendar_week_in_month = 
CASE
    WHEN DATEPART(DAY, PKDate) BETWEEN 1 AND 7 THEN 1
    WHEN DATEPART(DAY, PKDate) BETWEEN 8 AND 14 THEN 2
    WHEN DATEPART(DAY, PKDate) BETWEEN 15 AND 21 THEN 3
    WHEN DATEPART(DAY, PKDate) BETWEEN 22 AND 28 THEN 4
    ELSE 5
END
;

-- added
update WorkCalendar
set pay_week_in_month = sq.PAY_WEEK_IN_MONTH
from WorkCalendar
inner join (
              
              SELECT
                      dense_rank() over (partition by fy_start
                                                    , month(paywk_end)
                                         order by paywk_start)         as PAY_WEEK_IN_MONTH
                    , pkdate
              FROM WorkCalendar AS cal
              cross apply (
                           /* calc the number of days to reach Saturday of this "week" */
                           select abs((datediff(day,6,pkdate) % 7) - 6) 
                          ) ca1 (to_sat)
              cross apply (
                           select
                                  dateadd(day,-6,dateadd(day,to_sat,pkdate))
                                , dateadd(day,to_sat,pkdate)
                                , dateadd(month,@start_month-1,
                                          case when month(pkdate) >= @start_month
                                                    then cast(dateadd(year,year(pkdate)-1900,0) as date)
                                               else cast(dateadd(year,year(pkdate)-1901,0) as date)
                                          end)
                          ) ca2 (pay_start,pay_end, fy_start)
              cross apply (
                           select
                                   case when month(pay_start) < @start_Month and
                                             month(pay_end)   = @start_Month and
                                             datediff(day,fy_start, pay_end) < 8
                                               then fy_start
                                        else pay_start
                                   end
                
                                 ,  case when month(pay_start) < @start_Month and
                                             month(pay_end)   = @start_Month and
                                             datediff(day,fy_start, pay_end) > 365
                                               then dateadd(day,-1,dateadd(year,1,fy_start))
                                        else pay_end
                                   end
                
                          ) ca3 (paywk_start,paywk_end)
              WHERE cal.pkdate BETWEEN @dt_start AND @dt_end
              ) sq on WorkCalendar.pkdate = sq.pkdate
; 

Open in new window


I am not sure how to generate this report.  Should it be a Matrix with the codes at the top?  Should I group codes together in the ds query, such as the PE3 and PE6 Personal Time?  Perhaps I need the initial query to return results for each week with grouped codes?  

Any suggestions on how to get this up and running would be greatly appreciated!!!
0
Comment
Question by:HSI_guelph
  • 3
  • 3
6 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40475489
You've given the WorkCalendar table, but not the actual data table.

I know what you have in Excel, but presumably have you just transferred the Excel table into SQL Server as is, or have you normalised it, or what?

A bit more information there is essential.

I would suggest starting with a Matrix, and then see if there is anything you need to add afterwards (such as Parameters). Start small, and work up.
0
 

Author Comment

by:HSI_guelph
ID: 40476262
Here are the results from the tables
Work Calendar dataWIP table data
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40476310
Are these tables related? I can see you have a "Week number" in "Work Calendar data", but do you have a date there for the start of the Week? That seems to be the thing you are lacking (unless it is hidden from your screenshots).

It can be added with a little SQL if you haven't got it using the WEEKDAY function. It's easier if you have SQL Server 2008R2 or greater, because then you can use the DATE function, i.e.

DATE(year(MyDate), month(MyDate), day(MyDate) - WEEKDAY(MyDate,2) + 1)

Once that is in place, so do the Matrix, with Service going across, and Week Ending going down.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:HSI_guelph
ID: 40476763
No there is no start of the week but should I put a start of the week or end of the week?  This was originally set up because I was going to make a report that would show the hours per week of the month so dividing the weeks up where one month ends and another begins was a goal.  We are using SQL Server 2008 R2 so I can add that column to the table. I'm just not sure how to get the rows to group by week.  Does this have to be done in the query or can it be done in the row groups pane?
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40478081
Two answers:

1. Yes, it can be done in the row groups pane. However, you will need to pull in all the data from SQL Server for SSRS to give you the report you need, and that will increase network traffic, processor usage etc.
2. You can do it in the query if you want, which will reduce the amount of data you need.

So, either way.

Start of the week or the end of the week? Your initial spreadsheet using week ending dates, so if you are trying to recreate that, using W/E dates seems sensible.
0
 

Author Closing Comment

by:HSI_guelph
ID: 40489095
Got something working for now but I'll continue to work with it to achieve the result I want.  Thanks for helping me work through this!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now