Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-02-09
15
Medium Priority
?
349 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 46

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 46

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 46

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 46

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 46

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 46

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 46

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

715 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