Solved

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

Posted on 2011-02-25
9
1,052 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

Example of the report i am working on with shift letters in datetime field
Example 2
0
Comment
Question by:srodgers45
  • 6
  • 2
9 Comments
 
LVL 5

Assisted Solution

by:robertg34
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

by:srodgers45
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.
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
 

Author Comment

by:srodgers45
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?  

Look Up 2 Report Setup
0
 
LVL 27

Assisted Solution

by:planocz
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:srodgers45
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!)
Table Expression
0
 
LVL 5

Accepted Solution

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

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

Data
0
 

Author Comment

by:srodgers45
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? Query for Look up[ Completion Query
0
 

Author Closing Comment

by:srodgers45
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SSRS reports 5 27
CRM 2011 Missing Fields in Dataset 13 76
when creating a subscription get error 1 34
SSRS report showing old data. 5 54
Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

706 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

13 Experts available now in Live!

Get 1:1 Help Now