Solved

Formulas in SQL View

Posted on 2011-02-20
4
569 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 500 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

920 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

15 Experts available now in Live!

Get 1:1 Help Now