Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Formulas in SQL View

Posted on 2011-02-20
4
Medium Priority
?
604 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

972 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