Solved

Formulas in SQL View

Posted on 2011-02-20
4
592 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

617 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