Solved

Formulas in SQL View

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

776 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