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

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

Example of the report i am working on with shift letters in datetime field
Example 2
srodgers45Asked:
Who is Participating?
 
robertg34Connect With a Mentor Commented:
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
 
robertg34Connect With a Mentor Commented:
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
 
srodgers45Author Commented:
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.
Look Up Table
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
srodgers45Author Commented:
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?  

Look Up 2 Report Setup
0
 
planoczConnect With a Mentor Commented:
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
 
srodgers45Author Commented:
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!)
Table Expression
0
 
srodgers45Author Commented:
Here is the shot. Do i need to create a view?

Data
0
 
srodgers45Author Commented:
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? Query for Look up[ Completion Query
0
 
srodgers45Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.