Solved

Counts and Row Number resets

Posted on 2014-12-18
13
120 Views
Last Modified: 2015-01-08
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
0
Comment
Question by:OST-IS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
13 Comments
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40508663
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
 
LVL 18

Expert Comment

by:Simon
ID: 40508825
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
 

Author Comment

by:OST-IS
ID: 40509902
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:Simon
ID: 40509918
Cool. I'm online for the next few hours if you have questions.
0
 

Author Comment

by:OST-IS
ID: 40523106
Hi Guys sorry down for Christmas couldn't access the system looking at this now.
0
 

Author Comment

by:OST-IS
ID: 40523242
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
 

Author Comment

by:OST-IS
ID: 40523246
No its all good date is specific to SQL 2008 I will try DateTime
0
 

Author Comment

by:OST-IS
ID: 40523313
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
 

Author Comment

by:OST-IS
ID: 40537104
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
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40537523
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
 

Author Comment

by:OST-IS
ID: 40537641
This works exactly how I need it too. Thankyou so much.
0
 

Author Closing Comment

by:OST-IS
ID: 40537643
Fantastic Expert prompt service and great solutions
0
 
LVL 18

Expert Comment

by:Simon
ID: 40537673
Thanks for your kind words. Glad I could help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question