I have a background in Access Programming, and within the last year have ventured into SQL. I am currently using SQL SERVER 2008 and have assembled my tables and ran a few reports.
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 have a table that captures data for yearly training, and hours associated with that training. 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.
Further example: I may want to retrieve the data with training hours between 10 and 20. 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 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.
In Access, this was accomplished simply by writing "Between [Enter Low Range Here] AND [Enter High Range Here], which caused a pop up box to appear with a data input field. I am looking for the same procedure with SQL.
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 then need to use that Total Hour Query to find hours of training between specific ranges.
Some sample data:
Table Name Trainingrecords
Column1 - TrainingrecordID
Column2 - SerialNumber
Column3 - LName
Column4 - FName
Column5 - MI
Column6 - CourseNumberID
Column7 - CourseNumber
Column8 - CourseDescription
Column9 - CourseBeginDate
From this table I can create a query that returns the TOTAL of the CourseHourCredit. Then from this return, I need to write a select statement that prompts the user for the value ranges.
Query to retrieve SUM of Hours From Trainingrecords table:
SELECT DISTINCT LName, FName, MI, SUM(CourseHourCredit) AS Total
(Lets call that table/View: totalhoursperperson)
Then I want to run this user selectable data range against it.
Where total >= '?' AND <= '?'
(The ? of course would be the data supplied by the requestor).
Make ANY Sense?