Solved

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

Posted on 2014-02-09
15
309 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:Kdo
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:Kdo
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
 

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:Kdo
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 45

Expert Comment

by:Kdo
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:Kdo
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:Kdo
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:Kdo
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

747 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

9 Experts available now in Live!

Get 1:1 Help Now