Solved

Counts and Row Number resets

Posted on 2014-12-18
13
113 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
  • 8
  • 4
13 Comments
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
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:SimonAdept
Comment Utility
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
Comment Utility
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
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
Cool. I'm online for the next few hours if you have questions.
0
 

Author Comment

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

Author Comment

by:OST-IS
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

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

Author Comment

by:OST-IS
Comment Utility
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
Comment Utility
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:
SimonAdept earned 500 total points
Comment Utility
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
Comment Utility
This works exactly how I need it too. Thankyou so much.
0
 

Author Closing Comment

by:OST-IS
Comment Utility
Fantastic Expert prompt service and great solutions
0
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
Thanks for your kind words. Glad I could help.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now