Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL View Based on User Input

Posted on 2014-01-29
5
Medium Priority
?
343 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
[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
5 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 600 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 1400 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 66

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

719 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