I am working with a proprietary database the vendor has provided a Stored Procedure that gives access to all of the data required. I don't have direct access to the vendor tables hence I need to develop an stored procedure that will return three columns of data and generate two columns which are counts.
The three columns of data I have are
Batch - increments 1 each time a heat is created
Grade - Identifies specification of what is being made
TapTime - TimeDate stamp in the format 2014-12-02 00:21:14.000
I can create the stored procedure and I can create a variable table and insert data into the table.
I have basic skills at this point for the Select statement and functions.
I have tried to use Row Count but it is limited and I couldn't work out how to make it reset at Midnight or restart the count on Grades already counted, it just picks up where is stopped previously.
I am having trouble with my count columns.
For Batch I need to count the number of Batches produced in a day this count also needs to restart at midnight. To clarify the first batch on or after midnight needs to be 1.
For Grade I need to count the number of Grades that have been created. The count needs to start back at 1 each time the grade changes. (Even if the same grade is made multiple times a day).
TapTime is a timedate stamp that is registered at the end of each batch.
Each Batch will have a Grade and TapTime
I have attached a spreadsheet that contains a sample data set and I have manually created a sample of what I am trying to achieve with the stored procedure.