Solved

SQL View Based on User Input

Posted on 2014-01-29
5
337 Views
Last Modified: 2014-01-31
Hello,

I have a view request from a user to create a report that simply shows which of our techs are working or not based on input from the user which is a date range. This is an example of the expected output:

Column 1                       Date1         Date2            Date3              Date4             Date5

Employee Name               S                  S                   A                      A                      N


The S equals scheduled, the A equals available, and the N equals not available.

My problem is I am used to building sql views based on Static columns not on data that changes based on user input. Obviously i would be determining the value in each date column based on information in several tables which is tracked by the day with an hour value so that piece should be easy. Any ideas would be much appreciated.

Thanks
0
Comment
Question by:drizzt34953
  • 2
  • 2
5 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 150 total points
ID: 39819252
>I have a view request .. based on input from the user
For starters, views by themselves do not allow for a user-defined parameter, so if the scope of your question is restricted to using a view, then this is not possible.   So, it sounds like what you really need is a Stored Procedure that accepts a parameter, and can return different sets populated differently based on that parameter.
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 350 total points
ID: 39819280
To add to Jim point here...
you can either use a stored procedure or a SQL function which will return a table.

The experts from MS (microsoft) are branding function which returns a table as a parameterized views in their books (70-461 SQL Server development)


you can do some thing like this

CREATE FUNCTION dbo.<YourViewName>
(
--Parameter List
@Param1 VARCHAR(100)
....
@Paramn VARCHAR(100)
)
RETURNS TABLE
AS
RETURN
(
<Your View code using the @params for the function.
)

--Now you can use it in the your code similar to the view

select * FROM <yourViewName>(a,b...) 
--returns the results of what you are looking for 

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39819655
>I have a ..  request .. create a report .. on data that changes based on user input.
Also if 'data that changes' means the columns would change, keep in mind that SSRS (like SSIS) requires a 'contract' between a data set and it's columns such that those columns are static and do not change, and will often throw errors if those columns change based on a user-provided variable in a manner that SSRS can't handle.

If this is your situation, I'd recommend spelling out in more generic terms what you're trying to pull off here, and maybe we'll be able to provide a workaround.
0
 

Author Comment

by:drizzt34953
ID: 39820782
I have reviewed what you both have contributed but i need to go back and think about it a bit. I want to make sure i am not making this more difficult than it has to be. Having two tables, one which tracks the time a person can work daily and the other that has the actual scheduled time i know that if i dragged this data out in rows instead of columns i could achieve the same thing without reinventing the wheel.

On the user end though the visual of having 70 employees one on each line for a certain date range would be very convenient for scheduling purposes. Not to mention if i then add in colors to indicate the status i think they would be very pleased.
0
 

Author Comment

by:drizzt34953
ID: 39824672
Hello,

I ending up going with a static 14 day report which I was able to build in Crystal with a view i created. Its not exactly what i was going for but it will work for now. Thank you both for your time and knowledge.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
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…

932 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

17 Experts available now in Live!

Get 1:1 Help Now