Solved

# Convert date time to show letters for shifts in SSRS report builder 3.0 (Take 2)

Posted on 2011-02-25
1,078 Views
Last Modified: 2012-05-11
I think i confused the last person to try to help me with this one. we were very close using public function code. They were very helpful I just think I could not explain clearly what I am trying to show. (Sorry) this is take 2

I need to convert a datetime field to represent a shift letter. I used a formula in crystal that works well. Just not sure how to do it in SSRS.

I need to convert with a start date of 12-31-2010 for the first day of "A" shift. From there it rotates every 48 hours to the next shift. This pattern will repeat every 6 days. The time period is that the shifts start at 0800 each day.

from 12-31-2010 @ 0800 to 1-1-2011 @0800 would = "A"
from 1-1-2011  @ 0800 to 1-2-2011 0800 would = "A"
from 1-2-2011 @0800 to 1-3-2011 0800 would = "B"
from 1-3-2011 @0800 to 1-4-2011 0800 would = "B"
from 1-4-2011 @0800 to 1-5-2011 0800 would = "C"
from 1-5-2011 @0800 to 1-6-2011 0800 would = "C"
then back to
from 1-6-2011 @0800 to 1-7-2011 0800 would = "A"

This pattern repeats every 6 days forever

This is the formula in crystal that works

Example of the report i am working on with shift letters in datetime field
0
Question by:srodgers45
[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
• 6
• 2
9 Comments

LVL 5

Assisted Solution

robertg34 earned 334 total points
ID: 34987635
I suggest building a lookup table that contains the shift name, a beginning datetime and an ending datetime.  After you have the lookup table, you simply query it for your shift name within your report.  This takes the complexity out of your report and makes your historical shift information consistent.

Here's the code to build the lookup table:

--step 1
create table shiftlookup
(shift varchar(1), start_dtm datetime, end_dtm datetime)

--step 2 this is the initial inserts
insert into shiftlookup
values ('A', '12/31/2010 8:00:00 am', dateadd(hh,48,'12/31/2010 8:00:00 am') )

insert into shiftlookup
select 'B', max(end_dtm), dateadd(hh, 48, max(end_dtm)) from shiftlookup

insert into shiftlookup
select 'C', max(end_dtm), dateadd(hh, 48, max(end_dtm)) from shiftlookup

--step 3 and going forward run this loop and extend the loop counter out as far as you like
declare @timestoloop int;
declare @loopcntr int;
set @loopcntr = 0;
set @timestoloop = 20;
while @loopcntr < @timestoloop
BEGIN
insert into shiftlookup
select 'A', max(end_dtm), dateadd(hh, 48, max(end_dtm)) from shiftlookup;

insert into shiftlookup
select 'B', max(end_dtm), dateadd(hh, 48, max(end_dtm)) from shiftlookup;

insert into shiftlookup
select 'C', max(end_dtm), dateadd(hh, 48, max(end_dtm)) from shiftlookup;

set @loopcntr = @loopcntr + 1
END

select * from shiftlookup
0

Author Comment

ID: 34988237
Do I do this in SQL or in the report as a dataset? I created the shift part in a SQL Table, not sure how to do the step 3 forward? This looks good, I am not really a programmer, just a report person, mainly Crystal, the SSRS is all new to me.
Right now there are no shift selections in the tables. I have to convert the datetime field to represent the shift instead of a date.
If I can use the excellent solution you provided how does it tie into the corresponding date in the report? The Crystal formula was tied into the "Created" date field. I just created the formula and dropped it in the area I needed it in. Unfortunately I have nultiple reports I need to see the shifts in.
It's probably not as complicated as I think it is.
THANKS.

I was given this example, it almost works right, just could not figure out how to insert a start date for the first shift to loop from:
____________________________________________________________________________________
Place this code in your Reports properties Code Tab.
Then have this code in your cell or textbox
=Code.ShiftChange(Fields!MyFirstShiftDate.Value, Fields!MyComp_StatusDate.Value)

Public Function ShiftChange(ByVal FirstShift As DateTime, ByVal CompStatus As DateTime) As String
Dim DayDiff As Integer = Nothing

DayDiff = Microsoft.VisualBasic.DateDiff(DateInterval.Day, FirstShift, CompStatus) Mod 6
Select Case DayDiff
Case 0
If CompStatus.Hour < 8 Then
Return "C"
Else
Return "A"
End If
Case 1
Return "A"
Case 2
If CompStatus.Hour < 8 Then
Return "A"
Else
Return "B"
End If
Case 3
Return "B"
Case 4
If CompStatus.Hour < 8 Then
Return "B"
Else
Return "C"
End If
Case 5
Return "C"
End Select
Return Nothing
End Function
0

Author Comment

ID: 34988362
I was able to create the rest, I think I had originally left something out? Now how do I insert to report to associate the shift for the dates in the report?

0

LVL 27

Assisted Solution

planocz earned 166 total points
ID: 34988779
Will the Last Code that I give you I just changed the Mod 7 back to mod 6 and
I get the correct answer each time.
0

Author Comment

ID: 34988833
Looking at the Tdate field on the DB it is not a 'datetime' but a 'varchar'. can that be causing the problem? I tried to change it in the code and it said error "Type 'varchar' is not defined. The only datetime is the "created", but i cannot use that for both in the expression. is there a set value that can be used instead?
Thanks (Again!)
0

LVL 5

Accepted Solution

robertg34 earned 334 total points
ID: 34988995
This should be done with sql code.  Something like:

select shiftlookup.shift
from shiftlookup, yourtable
where yourtable.CREATED between shiftlookup.start_dtm and shiftlookup.end_dtm

I don't know what is in the tdate column or the name of that table..(yourtable).  If you want to post some screen shots of sample data for that table I might be able to help more.

0

Author Comment

ID: 34989071
Here is the shot. Do i need to create a view?

0

Author Comment

ID: 34989367
robertg34:
After looking at it further, I ran the last query you gave me to get  the data in the first screen shot. How do I get that to the report? The second one shows what the report is using for data in SSRS, some how I need to merge the 2 together? I think?
0

Author Closing Comment

ID: 34989916
I was able to use this and the other table and information provided to create a view that included the shift, I have successfully created a report with the information needed. Thanks
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

Title # Comments Views Activity
SSRS Expression 2 49
Ssrs disable the home lonk 1 106
HIghlights of SSIS? 3 45
T-SQL Query 9 36
Written by Valentino Vranken. A while ago I wrote an article called Chart Optimization Tips (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Chart-Optimization-Tips.html).  This article explained how …
Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
Six Sigma Control Plans
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
###### Suggested Courses
Course of the Month4 days, 3 hours left to enroll

#### 751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.