Improve company productivity with a Business Account.Sign Up

x
?
Solved

Formulas in SQL View

Posted on 2011-02-20
4
Medium Priority
?
610 Views
Last Modified: 2012-05-11
I want to create a formula used in crystal for a sql view. it would have to create a column that would show a shift leter for a corresponding date. I.E. 02-20-11 equals "B" and so on. The one attached works well in crystal. just not sure how to apply in a SQL View.

I would also like the view to show just the current date when executed.

Thanks in advance

SHift Formula from crystal
0
Comment
Question by:srodgers45
  • 2
  • 2
4 Comments
 
LVL 10

Accepted Solution

by:
John Claes earned 2000 total points
ID: 34940774
You can use the folowing in Microsft SQL

declare @FirstAtShift datetime
set @FirstAtShift = '20100105'

declare @intDaydiff int
set @intDaydiff = datediff(day,@FirstAtShift,getdate())% 6

select case 
		when @intDaydiff=0 then 
					case when datepart(hour,getdate()) < 8 then 'C' else 'A' end
		when @intDaydiff= 1 then 
					case when datepart(hour,getdate()) < 8 then 'A' else 'A' end
		when @intDaydiff= 2 then 
					case when datepart(hour,getdate()) < 8 then 'A' else 'B' end
		when @intDaydiff= 3 then 
					case when datepart(hour,getdate()) < 8 then 'B' else 'B' end
		when @intDaydiff= 4 then 
					case when datepart(hour,getdate()) < 8 then 'B' else 'C' end
		when @intDaydiff= 5 then 
					case when datepart(hour,getdate()) < 8 then 'C' else 'C' end
		end

Open in new window


regards
poor beggar
0
 

Author Comment

by:srodgers45
ID: 34944134
I tried to insert that to the view, it ran the query without changing or adding the field? Am I supposed to add the date table to the formula somewhere? should it be a stored procedure. What I need to show is the shift in a table. I usually use crystal and create the formula, drop it in and is shows the result. I have to create some reports using SSRS Reportbuilder 3.0. I am not sure how to put these formula types into those reports. Maybe I should figure out how to add it there? I am very new to that program and do not typically add a formulas in SQL.. I also need to show just the current date in the view as well.

Thanks

It gives this warning, runs without making changes

  Formula Error
0
 
LVL 10

Expert Comment

by:John Claes
ID: 34949390
srodgers45:

What I gave you was the SQL- Syntax :
You can make a Stored procedure of it but you can't use it in Crystel reports

regards
poor beggar
0
 

Author Closing Comment

by:srodgers45
ID: 34966968
Thanks, this works in SQL, I am going to repost in SSRS to see if this can be done in the report dataset query.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

606 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