Link to home
Start Free TrialLog in
Avatar of IT SSM
IT SSMFlag for Australia

asked on

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
Avatar of Koen Van Wielink
Koen Van Wielink
Flag of Netherlands image

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.
Avatar of Simon
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?
Avatar of IT SSM

ASKER

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
Cool. I'm online for the next few hours if you have questions.
Avatar of IT SSM

ASKER

Hi Guys sorry down for Christmas couldn't access the system looking at this now.
Avatar of IT SSM

ASKER

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?
Avatar of IT SSM

ASKER

No its all good date is specific to SQL 2008 I will try DateTime
Avatar of IT SSM

ASKER

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.
Avatar of IT SSM

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of Simon
Simon
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of IT SSM

ASKER

This works exactly how I need it too. Thankyou so much.
Avatar of IT SSM

ASKER

Fantastic Expert prompt service and great solutions
Thanks for your kind words. Glad I could help.