Solved

SQL Query to add a non unique "sub index" to a table

Posted on 2014-02-09
15
327 Views
Last Modified: 2014-02-21
I work in a sawmill where we cut what we refer to as "stems" into what we refer to as "logs".  I track all of the data in a MSSQL database.  I have a stems table that has the data for each stem.  The CREATE code follows.

CREATE TABLE [dbo].[ProductionData](
	[productiondata_ndx] [int] IDENTITY(1,1) NOT NULL,
	[StemNumber] [int] NULL,
	[SmallDiameter] [int] NULL,
	[LargeDiameter] [int] NULL,
	[ButtFirst] [int] NULL,
	[StemLength] [int] NULL,
	[ButtEndTrim] [int] NULL,
	[TrailingEndTrim] [int] NULL,
	[Log0Length] [int] NULL,
	[Log1Length] [int] NULL,
	[Log2Length] [int] NULL,
	[Log3Length] [int] NULL,
	[Log4Length] [int] NULL,
	[t_stamp] [datetime] NULL,
	[TimeStamp] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[productiondata_ndx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Open in new window


There is a stem number for the shift, and the length of each log that came out of the stem (there can be up to five).  If we only make two logs out of a stem, then there will be zeroes in the lengths for logs 2, 3, and 4.

I needed to build a table that contains data on the logs that were produced for the shift and not include the 0 length logs.

I did that with the following code.

CREATE TABLE [dbo].[LogsTemp](
	[logs_ndx] [int] IDENTITY(1,1) NOT NULL,
	[LogIndex] [int] NULL,
	[StemNumber] [int] NULL,
	[Position] [tinyint] NULL,
	[Length] [decimal](6, 3) NULL,
	[LengthName] [varchar](50) NULL,
	[TimeStamp] [datetime] NULL,
	[LengthInt] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


INSERT INTO [dbo].[LogsTemp]
           ([StemNumber]
           ,[Position]
           ,[Length]
		   ,[LengthInt]
		   ,[TimeStamp])
 
SELECT [StemNumber]
	  ,0
      ,Cast([Log0Length]/1000.0 As Decimal(6,3))
	  ,[Log0Length]
	  ,Coalesce([TimeStamp],[t_stamp]  )
  FROM [dbo].[ProductionData]
  Where Log0Length > 2000
GO

INSERT INTO [dbo].[LogsTemp]
           ([StemNumber]
           ,[Position]
           ,[Length]
		   ,[LengthInt]
		   ,[TimeStamp])
 
SELECT [StemNumber]
	  ,1
      ,Cast([Log1Length]/1000.0 As Decimal(6,3))
	  ,[Log1Length]
	  ,Coalesce([TimeStamp],[t_stamp]  )
  FROM [dbo].[ProductionData]
  Where Log1Length > 2000
GO
INSERT INTO [dbo].[LogsTemp]
           ([StemNumber]
           ,[Position]
           ,[Length]
		   ,[LengthInt]
		   ,[TimeStamp])
 
SELECT [StemNumber]
	  ,2
      ,Cast([Log2Length]/1000.0 As Decimal(6,3))
	  ,[Log2Length]
	  ,Coalesce([TimeStamp],[t_stamp]  )
  FROM [dbo].[ProductionData]
  Where Log2Length > 2000
GO

INSERT INTO [dbo].[LogsTemp]
           ([StemNumber]
           ,[Position]
           ,[Length]
		   ,[LengthInt]
		   ,[TimeStamp])
 
SELECT [StemNumber]
	  ,3
      ,Cast([Log3Length]/1000.0 As Decimal(6,3))
	  ,[Log3Length]
	  ,Coalesce([TimeStamp],[t_stamp]  )
  FROM [dbo].[ProductionData]
  Where Log3Length > 2000
GO

INSERT INTO [dbo].[LogsTemp]
           ([StemNumber]
           ,[Position]
           ,[Length]
		   ,[LengthInt]
		   ,[TimeStamp])
 
SELECT [StemNumber]
	  ,4
      ,Cast([Log4Length]/1000.0 As Decimal(6,3))
	  ,[Log4Length]
	  ,Coalesce([TimeStamp],[t_stamp]  )
  FROM [dbo].[ProductionData]
  Where Log4Length > 2000
GO

Update [dbo].[LogsTemp]

Set [LengthName] =

	(
	Select LengthName
	From [dbo].[MerchLengthNames] b
	where LengthInt = b.LengthIntFt AND SolutionID = 50 AND Species = 2 AND DiamClass = 3
	)

GO

CREATE TABLE [dbo].[Logs](
	[logs_ndx] [int] IDENTITY(1,1) NOT NULL,
	[LogIndex] [int] NULL,
	[StemNumber] [int] NULL,
	[Position] [tinyint] NULL,
	[Length] [decimal](6, 3) NULL,
	[LengthName] [varchar](50) NULL,
	[TimeStamp] [datetime] NULL,
	[LengthInt] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

INSERT INTO [dbo].[Logs]
           ([StemNumber]
           ,[Position]
           ,[Length]
           ,[LengthName]
           ,[TimeStamp]
           ,[LengthInt])
     SELECT 
       [StemNumber]
      ,[Position]
      ,[Length]
      ,[LengthName]
      ,[TimeStamp]
      ,[LengthInt]
  FROM [Merch].[dbo].[LogsTemp]
  Order By TimeStamp, Position

GO

Open in new window


My new table looks good so far except my LogIndex field contains NULLS.  I would like to number the logs produced for each shift.  The stem numbers for each shift start over at 1 at the beginning of the shift and the log numbers need to also.   If it wasn't for the need to start re-numbering at the beginning of each shift, my log_ndx field would be great since the logs are already in the correct order.

I need to do this to maintain compatibility with some reporting that has already been done.

As long as you're looking at all that code anyway, fell free to suggest a better way to do what I have already done.
0
Comment
Question by:StudmillGuy
  • 8
  • 7
15 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39846208
Hi StudMillGuy,

The simple solution would be to filter out the nulls in the last statement:

INSERT INTO [dbo].[Logs]
           ([StemNumber]
           ,[Position]
           ,[Length]
           ,[LengthName]
           ,[TimeStamp]
           ,[LengthInt])
     SELECT 
       [StemNumber]
      ,[Position]
      ,[Length]
      ,[LengthName]
      ,[TimeStamp]
      ,[LengthInt]
  FROM [Merch].[dbo].[LogsTemp]
  WHERE [Position] is not NULL
  Order By TimeStamp, Position

Open in new window


But I suspect that it's more complicated than that...

Kent
0
 

Author Comment

by:StudmillGuy
ID: 39846214
I have already gotten all of the nulls filtered out that I need to.  The only ones remaining are the entire LogIndex column, which I need to populate and is what I am asking for help with.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39846222
Yeah.  I misread it.  :)

If you already have the all of the data except an ordinal for that one column, use ROW_NUMBER to enumerate all of the logs.

INSERT INTO [dbo].[Logs]
           ([StemNumber]
           ,[Position]
           ,[Length]
           ,[LengthName]
           ,[TimeStamp]
           ,[LengthInt]
           ,[LogIndex])
     SELECT 
       [StemNumber]
      ,[Position]
      ,[Length]
      ,[LengthName]
      ,[TimeStamp]
      ,[LengthInt]
      , ROW_NUMBER () over (order by TimeStamp, Position)
  FROM [Merch].[dbo].[LogsTemp]
  Order By TimeStamp, Position

Open in new window


See if that doesn't do it for you.


Kent
0
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 

Author Comment

by:StudmillGuy
ID: 39846234
RowNumber will just number every single row in the table instead of starting the numbering over when the StemNumber starts over.  I already have a correctly ordered index in the log_ndx column.
0
 

Author Comment

by:StudmillGuy
ID: 39846244
I will grant, though, that RowNumber would work if I were willing to write many different queries to separate the data for each shift by using BETWEEN in the where clauses.  I already thought of that and was trying to avoid all of that work, because after all, the purpose of computers is to avoid work.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39846332
No Problem..

  ROW_NUMBER () over (partition by StemNumber order by TimeStamp, Position)
0
 

Author Comment

by:StudmillGuy
ID: 39846368
We're getting closer.  This is starting the numbering over every time the stem number changes.  What I need is to start the row numbering over whenever the stem number is less than the previous stem number.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39847447
It looks like it may easier to accomplish in two passes.  One to install the "base" data as you've done, and another to update the column.

Does the LogIndex value reset with the first run of the new day?  That is, when the timestamp value indicates a new day do we start counting from 1 again?
0
 

Author Comment

by:StudmillGuy
ID: 39847455
No.  Unfortunately our night shift spans midnight.  Therefore the only reliable way to detect a shift change is when the stem numbers reset to 1.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39847464
Ok.  

How about if we group them by starting with a StemNumber of 1, then add all of the other logs where the timestamp is for the same day?
0
 

Accepted Solution

by:
StudmillGuy earned 0 total points
ID: 39847746
I came up with something myself that worked.

Declare @Counter int
		,@StemNum int
SET @Counter = 0
Set @StemNum = 0
UPDATE [dbo].[Logs]

   SET @Counter = (case WHEN [StemNumber] < @StemNum then 1 else @Counter + 1 END) ,
   LogIndex =@Counter
   ,@StemNum = [StemNumber]

Open in new window


I know this depends on SQL operating on the table in a particular order, which is bad.  I'm not sure if an "Order By" is possible in an update query though.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39848066
I don't think that that's a good long-term solution.  Any change to process or data will generate some really weird results.  So let's look for a better plan.  :)

A preferred method would be to generate a list with the desired results, and perform the UPDATE based on joining that list to the existing data.  It's pretty easy, and standard.  I just don't understand (yet) all of the rules for assigning the correct value.

-- Does the [Logs] table contain a single day's values or multiple?
-- Does the data in the [Logs] table contain more than one days' values where LogIndex is not set?

And finally, does the StemNum always get reset to 1 with the first Log/Stem of the day?

Kent
0
 

Author Comment

by:StudmillGuy
ID: 39848079
This is just a temporary fix for the data already collected.  Going forward I will be inserting data directly into the Logs table.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39848101
Oh.  Ok.  If you're just patching data, then any code what works should be fine.
0
 

Author Closing Comment

by:StudmillGuy
ID: 39876311
I answered this myself.  It is not a very good solution, but it works for me.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

713 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