Solved

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

Posted on 2014-02-09
15
314 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

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 …
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.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

896 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

14 Experts available now in Live!

Get 1:1 Help Now