(The ? of course would be the data supplied by the requestor). Then I want to run this user selectable data range against it. (Lets call that table/View: totalhoursperperson) SELECT DISTINCT LName, FName, MI, SUM(CourseHourCredit) AS Total Query to retrieve SUM of Hours From Trainingrecords table: To get help information at the SQL prompt, you can use the HELP command as shown in the following tutorial example. Then from this return, I need to write a select statement that prompts the user for the value ranges. You can write a custom script in SSMS SELECT FROM YOURTABLE -COULD ALSO BE A USER PROMPT WHERE COLUMNNAME '' -USER PROMPT where first the PARAMETERNAME is what you name the parameter, nvarchar(50) is where you put the datatype of the column and DEFAULTVALUE is what you want as the default value if no input to be provided. I then need to use that Total Hour Query to find hours of training between specific ranges.įrom this table I can create a query that returns the TOTAL of the CourseHourCredit. I run a select query using a DISTINCT statement to SUM the total hours for each employee, but of course, cannot include the training course data since it's not DISTINCT. I am looking for the same procedure with SQL. In Access, this was accomplished simply by writing 'Between AND, which caused a pop up box to appear with a data input field.

When you have several END USERS needing this data, I'd rather have a dialogue box pop up and prompt the user for the data range(s) they are requesting. Now I can write that procedure as a VIEW using a between statement, but I also need various other reports showing other hour ranges. When the end of the year comes around, I am required to submit several reports showing various hours, and dates of training between certain, but variable data values.įurther example: I may want to retrieve the data with training hours between 10 and 20. I have a table that captures data for yearly training, and hours associated with that training. However, I now need to either write a VIEW or other query (or maybe this is accomplished through another means) where I can supply the value for a BETWEEN Statement to retrieve data based on those dates. I am currently using SQL SERVER 2008 and have assembled my tables and ran a few reports. I have a background in Access Programming, and within the last year have ventured into SQL.

Sql prompt input