Solved

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

Posted on 2014-02-09
15
335 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
[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
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

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 …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

636 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