[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Counts and Row Number resets

Posted on 2014-12-18
13
Medium Priority
?
126 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:IT SSM
[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:IT SSM
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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:IT SSM
ID: 40523106
Hi Guys sorry down for Christmas couldn't access the system looking at this now.
0
 

Author Comment

by:IT SSM
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:IT SSM
ID: 40523246
No its all good date is specific to SQL 2008 I will try DateTime
0
 

Author Comment

by:IT SSM
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:IT SSM
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 2000 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:IT SSM
ID: 40537641
This works exactly how I need it too. Thankyou so much.
0
 

Author Closing Comment

by:IT SSM
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

650 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