• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 176
  • Last Modified:

T-SQL Stored Procedure Count by recurring ID

I am developing in MSSQL 2005

I am creating a report off a vendor system. The tables in the system are accessible but don't make much sense outside of the vendor. The vendor themselves develop of a series of stored procedures that ties data together and makes it available to other Stored Procedures. NOTE: For the solution I am limited to a Stored Proc, funcs etc but no vendor data tables.

One of the columns in the stored procedure is ef.grade. Grade is a varchar(12) it comes in the form of sa14, EF55, XA7856 etc. Another column is a GradeOrdinal column that is populated by the
 Row_Number() OVER (Partitian BY ef.Grade Order By Ef.HeatNumber ASC) As GradeOrdinal ]

Open in new window

. This gives each row of a grade a number starting from 1 to .... if the grade changes it starts from 1 to ........ depending on the number of heats produced. My problem exist where we produce a grade, change to a grade then change back to the original grade. When a grade is repeated in the query window the count doesn't return to 1 it just adds to the last value.

The solution I need is how to make the SQL think a repeat grade is a new grade.
Example Current Result set

sa14            1
sa14            2
sa14            3
sa14            4
XA7856       1
XA7856       2
XA7856       3
sa14            5
sa14            6
sa14            7
sa14            8

Example Required Output

sa14            1
sa14            2
sa14            3
sa14            4
XA7856       1
XA7856       2
XA7856       3
sa14            5
sa14            6
sa14            7
sa14            8

I have added a copy of my code and a picture of the output which shows the issue I am having. Raw Dataset is difficult.
Stored-Proc-Select.txt
Example-Error.JPG
0
IT SSM
Asked:
IT SSM
  • 3
  • 2
1 Solution
 
SimonCommented:
Not clear enough: Your "Example Current results set" and "Example Required Output" are identical?
0
 
IT SSMAuthor Commented:
Apologies for that in short I need to have the count restart everytime there is a change in Grade regardless of if it has been counted before or not.


Example Current Result set

 sa14            1
 sa14            2
 sa14            3
 sa14            4
 XA7856       1
 XA7856       2
 XA7856       3
 sa14            5
 sa14            6
 sa14            7
 sa14            8

 Example Required Output

 sa14            1
 sa14            2
 sa14            3
 sa14            4
 XA7856       1
 XA7856       2
 XA7856       3
 sa14            1
 sa14            2
 sa14            3
 sa14            4
0
 
SimonCommented:
I'm not sure of the terminology for your process. It might be helpful to see a text sample of the full result set returned by the last section of your SP.
Select Top 35
			ef.HeatNumber,
			Row_Number() OVER (
					ORDER BY ef.HeatNumber ASC
				) as HeatCount,-- Generates a row number based on eaf.HeatNumber
			Row_Number() OVER (
					PARTITION BY ef.Grade ORDER BY ef.HeatNumber ASC
					) AS Grade_Ordinal,

			Grade,
			ScrapTotal as ChargeWeight,
			TapTime,
			PowerOnTime,
			[Tap-to-Tap],
			Cast(TotalPower as Decimal(6,1))/1000.0 as MWh, --Cast number to decimal
			TurnaroundTime,
			[Tap-to-Tap]-PowerOnTime as PowerOffTime,
			LadleNumber,
			CASE LadleNumber  --Case Statement to join ladle to correct lives
				WHEN 1 THEN Ladle1Lives
				WHEN 2 THEN Ladle2Lives
				WHEN 3 THEN Ladle3Lives
				WHEN 4 THEN Ladle4Lives
				WHEN 5 THEN Ladle5Lives
				WHEN 6 THEN Ladle6Lives
				ELSE NULL
				END AS LadleLives,
			BilletTonnes,
			ISNull(GeneralComments,'')+ ' ' +ISNull(Comment1,'') AS Comments

Open in new window


my best guess based on what I can make out so far would be to change your second ROW_NUMBER() column
from
                  Row_Number() OVER (
                              PARTITION BY ef.Grade ORDER BY ef.HeatNumber ASC
                              ) AS Grade_Ordinal
to
                  Row_Number() OVER (
                              PARTITION BY ef.heatnumber,ef.Grade ORDER BY ef.HeatNumber ASC
                              ) AS Grade_Ordinal
0
 
IT SSMAuthor Commented:
Hi Simoneadept,

No this produces the same result.
I think I need to do a check of the grade against the taptime column (DateTime) the heat column will always be a new unique number. Hence it leave me only the DateTime column and Grade column to compare against.
I could do this with a SCALAR function but I cant see how to do this without a table. The SCALAR function wont allow me to use the needed EXEC command to insert data into the tmp_table. Thanks OSTIT
0
 
SimonCommented:
Hi, please see my proposed solution on your re-formulated version of this question...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now