Counts and Row Number resets

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.
Counts-of-Rows.xlsx
IT SSMAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
For your batch number count it's pretty straightforward using ROW_NUMBER:

Select		Batch
		,	ROW_NUMBER() over (partition by day(TapTime) order by TapTime) as 'No_Batches_for_Day'
from	 RawData
order by	TapTime

Open in new window


The grade however is a bit more tricky because with the data given you can only partition on grade and date. That means that lines 8 and 9 in your required output example would be getting numbers 5 and 6 respectively, before the count resets the following day.
Let me puzzle a bit more on that and see if I can come up with something.
0
SimonCommented:
Try this. It works with your sample dataset. At least it does on MSSQL 2008 as demonstrated by this SQL Fiddle

;with cte as (select batch
,convert(date,taptime) as BatchDate
,grade 
,taptime
,row_number() over (partition by convert(date,taptime) order by batch) as BatchInDayNo
,row_number() over (partition by convert(date,taptime) order by batch) - row_number() over (partition by convert(date,taptime),grade order by batch) as Grp
from rawdata)

select batch,BatchInDayNo,grade,row_number() over (partition by batchdate, grade,grp order by batch) as GradeCount,taptime
from cte
order by batch

Open in new window


It uses the 'islands and gaps' principle to compare the difference between the row_number() values for the batch within the day and the batch within the grade within the day. The concept is that sequential batches of the same grade all have the same relationship between the two row_number values, but each new sequence of batches will get a different one. You can see this by including the 'grp' column in the final output if desired.

I've used batch number as the entity to order by, but you may want to use taptime instead.

In your sample data, there were two rows with the same batch number. Was that a typo or representative of real data?
0
IT SSMAuthor Commented:
batch number will always be different it was a typo. Thanks for the input guys I will give it a go and get back to you. I am in Australia so there is a bit of a time different I think cheers
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SimonCommented:
Cool. I'm online for the next few hours if you have questions.
0
IT SSMAuthor Commented:
Hi Guys sorry down for Christmas couldn't access the system looking at this now.
0
IT SSMAuthor Commented:
OK I have tried this and I am not sure if I have done something wrong or not.

I am using the Vendor call to create a variable table @_tmp_RawData

I have added the cte after this using @_tmp_RawData as the data source. When I do this it throws an Error on the date.

Type date is not a defined system type.

I will experiment a bit more.

Should I be using the cte in my stored procedure or as a function?
0
IT SSMAuthor Commented:
No its all good date is specific to SQL 2008 I will try DateTime
0
IT SSMAuthor Commented:
OK could us a bit of a help with the conversion from DateTime to emulate the date function in SQL2008 each row is returning 1 for each row no progressive count.
0
IT SSMAuthor Commented:
I have fiddled with the code and managed to get it working for 2005 there is one issue.

The Grade count is restarting on the start of a new day which I don't want I just need it to restart the grade count on each change of grade even if the same grade is made multiple times a day.

I have had a bit of a effort trying to work this out but not sure what to do next.

;with CTE as 
(SELECT HeatNumber 
	,CONVERT(DATETIME,(CONVERT(varchar(10),taptime,111))) as BatchDate
	,grade 
	,taptime
	,row_number() over (partition by convert(DATETIME,(Convert(varchar(10),taptime,111))) order by HeatNumber) as HeatsInDay
	,row_number() over (partition by convert(DATETIME,(Convert(varchar(10),taptime,111))) order by HeatNumber) - row_number() over (partition by convert(DATETIME,(Convert(varchar(10),taptime,111))),Grade order by HeatNumber) as Grp
	from @tmp_eaf_furntable
	Group By HeatNumber, TapTime, Grade)
	
	select HeatNumber,HeatsInDay,grade,row_number() over (partition by batchdate, grade,grp order by HeatNumber) as GradeCount,taptime
	from cte
	order by HeatNumber
	
	RETURN
End

Open in new window

0
SimonCommented:
Hi,

You just need to add this line to your cte
,row_number() over (order by batch)-row_number() over (partition by grade order by batch) as grp2

Open in new window


and this line to your final select statement
,row_number() over (partition by grade,grp2 order by batch) as GradeCount

Open in new window


I've updated my example on SQL Fiddle to include this and used the datetime conversion to make it suitable for 2005.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT SSMAuthor Commented:
This works exactly how I need it too. Thankyou so much.
0
IT SSMAuthor Commented:
Fantastic Expert prompt service and great solutions
0
SimonCommented:
Thanks for your kind words. Glad I could help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.