Current product being ran

Question

Current product being ran

cbrammer1219

SSCarpal Tunnel

Points: 4476
More actions
July 20, 2016 at 12:08 pm

#328022

I want to retrieve the current product being run on several lines. Each being different products, but want the current one being ran. it has date_time, but I just can't get the syntax correct. I tried using top(1), but didn't work. Any help would be appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You should already know how to properly ask questions here.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

Without at least some details to work with, anything we offer (beyond "use a SELECT") would be pure speculation.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Y.B. SSChampion Points: 11649 More actions · Answer 3

This is too vague for us to give you an answer...

SELECT quote FROM brain WHERE original = 1
0 rows returned

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 4

--===== Create the test table with

CREATE TABLE #vb

(

[BoxOnPallet] [int] NULL CONSTRAINT [DF_Boxes_BoxOnPallet] DEFAULT (0),

[BuffID] [varchar](50) NOT NULL,

[DateTime] [datetime] NULL CONSTRAINT [DF_Boxes_DateTime] DEFAULT (getdate()),

[DoneWithBox] [bit] NULL CONSTRAINT [DF_Boxes_DoneWithBox] DEFAULT (0),

[GrossWeight] [smallmoney] NULL CONSTRAINT [DF_Boxes_GrossWeight] DEFAULT (0),

[KeyedWeight] [varchar](20) NULL CONSTRAINT [DF_Boxes_KeyedWeight] DEFAULT (0),

[LotNumber] [varchar](20) NULL CONSTRAINT [DF_Boxes_LotNumber] DEFAULT (0),

[MakeDate] [datetime] NULL,

[NetWeight] [smallmoney] NULL CONSTRAINT [DF_Boxes_NetWeight] DEFAULT (0),

[PalletNumber] [int] NULL CONSTRAINT [DF_Boxes_PalletNumber] DEFAULT (0),

[ProductID] [varchar](20) NULL CONSTRAINT [DF_Boxes_ProductID] DEFAULT (''),

[SellByDate] [datetime] NULL CONSTRAINT [DF_Boxes_SellByDate] DEFAULT (getdate()),

[SerialNumber] [int] NULL CONSTRAINT [DF_Boxes_SerialNumber] DEFAULT (0),

[Status] [int] NULL CONSTRAINT [DF_Boxes_Status] DEFAULT ((-99)),

[TareWeight] [smallmoney] NULL CONSTRAINT [DF_Boxes_TareWeight] DEFAULT (0),

[UserID] [varchar](20) NULL CONSTRAINT [DF_Boxes_UserID] DEFAULT (''),

[StationID] [varchar](20) NULL,

[Flag] [char](1) NULL CONSTRAINT [DF_vbsBoxes_Flag] DEFAULT ('N'),

[ID] [int] IDENTITY(1,1) NOT NULL,

[CreateDate] [datetime] NULL CONSTRAINT [DF_vbsBoxes_CreateDate] DEFAULT (getdate()),

CONSTRAINT [PK_Boxes] PRIMARY KEY CLUSTERED

)

SELECT '0','01906704330102993202003122151606022122121613805371','May 18 2016 10:21AM','0','1216138','May 18 2016 10:21AM','1029','Jun 2 2016 12:00AM','0','14','19354727','May 18 2016 10:21AM' UNION ALL

SELECT '0','01906704330102993202003142151607302121121619625408','Jul 15 2016 2:19PM','0','1216196','Jul 15 2016 2:19PM','1029','Jul 30 2016 12:00AM','0','14','19778058','Jul 15 2016 2:19PM' UNION ALL

SELECT '0','01906704330102993202003167151606032121121613992372','May 19 2016 11:00AM','0','1216139','May 19 2016 11:00AM','1029','Jun 3 2016 12:00AM','0','14','19365971','May 19 2016 11:00AM' UNION ALL

SELECT '0','01906704330102993202003172151606032122121613906405','May 19 2016 11:06AM','0','1216139','May 19 2016 11:05AM','1029','Jun 3 2016 12:00AM','0','14','19366016','May 19 2016 11:06AM' UNION ALL

SELECT '0','01906704330102993202003182151606032122121613906399','May 19 2016 10:59AM','0','1216139','May 19 2016 10:59AM','1029','Jun 3 2016 12:00AM','0','14','19365961','May 19 2016 10:59AM' UNION ALL

SELECT '0','01906704330102993202003187151607212121121618718658','Jul 6 2016 1:39PM','0','1216187','Jul 6 2016 1:39PM','1029','Jul 21 2016 12:00AM','0','14','19705250','Jul 6 2016 1:39PM' UNION ALL

SELECT '0','01906704330102993202003192151606032122121613906397','May 19 2016 10:57AM','0','1216139','May 19 2016 10:57AM','1029','Jun 3 2016 12:00AM','0','14','19365937','May 19 2016 10:57AM' UNION ALL

SELECT '0','01906704330102993202003207151606252121121616103006','Jun 10 2016 9:47AM','0','1216161','Jun 10 2016 9:47AM','1029','Jun 25 2016 12:00AM','0','14','19526081','Jun 10 2016 9:47AM' UNION ALL

SELECT '0','01906704330102993202003207151607212122121618733114','Jul 6 2016 1:23PM','0','1216187','Jul 6 2016 1:22PM','1029','Jul 21 2016 12:00AM','0','17','19704960','Jul 6 2016 1:23PM' UNION ALL

SELECT '0','01906704330102993202003207151607212122121618733115','Jul 6 2016 1:23PM','0','1216187','Jul 6 2016 1:23PM','1029','Jul 21 2016 12:00AM','0','14','19704961','Jul 6 2016 1:23PM' UNION ALL

SELECT '0','01906704330102993202003207151607212122121618733118','Jul 6 2016 1:25PM','0','1216187','Jul 6 2016 1:25PM','1029','Jul 21 2016 12:00AM','0','14','19704976','Jul 6 2016 1:25PM' UNION ALL

SELECT '0','01906704330102993202003212151607312122121619742825','Jul 18 2016 1:44PM','0','1216197','Jul 18 2016 1:44PM','1029','Jul 31 2016 12:00AM','0','14','19786275','Jul 18 2016 1:44PM' UNION ALL

CREATE TABLE #prodplan

(

[ID] [int] IDENTITY(1,1) NOT NULL,

[LabelApprovedByQA] [tinyint] NULL CONSTRAINT [DF_PLANT_MAKE_PLAN_ProductLabelApprovedByQA] DEFAULT ((0)),

[MakeNow] [tinyint] NULL CONSTRAINT [DF_PLANT_MAKE_PLAN_MakeNow] DEFAULT ((0)),

[OutOfProduct] [tinyint] NULL CONSTRAINT [DF_PLANT_MAKE_PLAN_OutOfProduct] DEFAULT ((0)),

[MAKE_DATE] [date] NULL,

[LINE_NUMBER] [varchar](10) NULL,

[LOT_NUMBER] [varchar](10) NULL,

[PART_CODE] [varchar](10) NULL,

[BRAND] [varchar](10) NULL,

[SORT_PART] [int] NULL,

[MAKE_CASES] [int] NULL,

[PrintCount] [int] NULL CONSTRAINT [DF_PLANT_MAKE_PLAN_PrintCount] DEFAULT ((0)),

[RemainingToMake] AS ([MAKE_CASES]-[PrintCount]),

CONSTRAINT [PK_PLANT_MAKE_PLAN] PRIMARY KEY CLUSTERED

)

SELECT '79375','0','0','0','2016-07-08','6','1116189','7427','ORG','2','0','9','9' UNION ALL

SELECT '79451','0','0','0','2016-07-08','1','1216189','1837','VEG','6','0','11','11' UNION ALL

SELECT '79335','0','0','0','2016-07-08','2','1216189','1643','VEG','904','0','0','0' UNION ALL

SELECT '79339','0','0','0','2016-07-08','2','1116189','1465','ORG','5','9','196','187' UNION ALL

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

cbrammer1219 (7/20/2016)
--===== Create the test table with
CREATE TABLE #vb
(
[BoxOnPallet] [int] NULL CONSTRAINT [DF_Boxes_BoxOnPallet] DEFAULT (0),
[BuffID] [varchar](50) NOT NULL,
[DateTime] [datetime] NULL CONSTRAINT [DF_Boxes_DateTime] DEFAULT (getdate()),
[DoneWithBox] [bit] NULL CONSTRAINT [DF_Boxes_DoneWithBox] DEFAULT (0),
[GrossWeight] [smallmoney] NULL CONSTRAINT [DF_Boxes_GrossWeight] DEFAULT (0),
[KeyedWeight] [varchar](20) NULL CONSTRAINT [DF_Boxes_KeyedWeight] DEFAULT (0),
[LotNumber] [varchar](20) NULL CONSTRAINT [DF_Boxes_LotNumber] DEFAULT (0),
[MakeDate] [datetime] NULL,
[NetWeight] [smallmoney] NULL CONSTRAINT [DF_Boxes_NetWeight] DEFAULT (0),
[PalletNumber] [int] NULL CONSTRAINT [DF_Boxes_PalletNumber] DEFAULT (0),
[ProductID] [varchar](20) NULL CONSTRAINT [DF_Boxes_ProductID] DEFAULT (''),
[SellByDate] [datetime] NULL CONSTRAINT [DF_Boxes_SellByDate] DEFAULT (getdate()),
[SerialNumber] [int] NULL CONSTRAINT [DF_Boxes_SerialNumber] DEFAULT (0),
[Status] [int] NULL CONSTRAINT [DF_Boxes_Status] DEFAULT ((-99)),
[TareWeight] [smallmoney] NULL CONSTRAINT [DF_Boxes_TareWeight] DEFAULT (0),
[UserID] [varchar](20) NULL CONSTRAINT [DF_Boxes_UserID] DEFAULT (''),
[StationID] [varchar](20) NULL,
[Flag] [char](1) NULL CONSTRAINT [DF_vbsBoxes_Flag] DEFAULT ('N'),
[ID] [int] IDENTITY(1,1) NOT NULL,
[CreateDate] [datetime] NULL CONSTRAINT [DF_vbsBoxes_CreateDate] DEFAULT (getdate()),
CONSTRAINT [PK_Boxes] PRIMARY KEY CLUSTERED
)
SELECT '0','01906704330102993202003122151606022122121613805371','May 18 2016 10:21AM','0','1216138','May 18 2016 10:21AM','1029','Jun 2 2016 12:00AM','0','14','19354727','May 18 2016 10:21AM' UNION ALL
SELECT '0','01906704330102993202003142151607302121121619625408','Jul 15 2016 2:19PM','0','1216196','Jul 15 2016 2:19PM','1029','Jul 30 2016 12:00AM','0','14','19778058','Jul 15 2016 2:19PM' UNION ALL
SELECT '0','01906704330102993202003167151606032121121613992372','May 19 2016 11:00AM','0','1216139','May 19 2016 11:00AM','1029','Jun 3 2016 12:00AM','0','14','19365971','May 19 2016 11:00AM' UNION ALL
SELECT '0','01906704330102993202003172151606032122121613906405','May 19 2016 11:06AM','0','1216139','May 19 2016 11:05AM','1029','Jun 3 2016 12:00AM','0','14','19366016','May 19 2016 11:06AM' UNION ALL
SELECT '0','01906704330102993202003182151606032122121613906399','May 19 2016 10:59AM','0','1216139','May 19 2016 10:59AM','1029','Jun 3 2016 12:00AM','0','14','19365961','May 19 2016 10:59AM' UNION ALL
SELECT '0','01906704330102993202003187151607212121121618718658','Jul 6 2016 1:39PM','0','1216187','Jul 6 2016 1:39PM','1029','Jul 21 2016 12:00AM','0','14','19705250','Jul 6 2016 1:39PM' UNION ALL
SELECT '0','01906704330102993202003192151606032122121613906397','May 19 2016 10:57AM','0','1216139','May 19 2016 10:57AM','1029','Jun 3 2016 12:00AM','0','14','19365937','May 19 2016 10:57AM' UNION ALL
SELECT '0','01906704330102993202003207151606252121121616103006','Jun 10 2016 9:47AM','0','1216161','Jun 10 2016 9:47AM','1029','Jun 25 2016 12:00AM','0','14','19526081','Jun 10 2016 9:47AM' UNION ALL
SELECT '0','01906704330102993202003207151607212122121618733114','Jul 6 2016 1:23PM','0','1216187','Jul 6 2016 1:22PM','1029','Jul 21 2016 12:00AM','0','17','19704960','Jul 6 2016 1:23PM' UNION ALL
SELECT '0','01906704330102993202003207151607212122121618733115','Jul 6 2016 1:23PM','0','1216187','Jul 6 2016 1:23PM','1029','Jul 21 2016 12:00AM','0','14','19704961','Jul 6 2016 1:23PM' UNION ALL
SELECT '0','01906704330102993202003207151607212122121618733118','Jul 6 2016 1:25PM','0','1216187','Jul 6 2016 1:25PM','1029','Jul 21 2016 12:00AM','0','14','19704976','Jul 6 2016 1:25PM' UNION ALL
SELECT '0','01906704330102993202003212151607312122121619742825','Jul 18 2016 1:44PM','0','1216197','Jul 18 2016 1:44PM','1029','Jul 31 2016 12:00AM','0','14','19786275','Jul 18 2016 1:44PM' UNION ALL
CREATE TABLE #prodplan
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[LabelApprovedByQA] [tinyint] NULL CONSTRAINT [DF_PLANT_MAKE_PLAN_ProductLabelApprovedByQA] DEFAULT ((0)),
[MakeNow] [tinyint] NULL CONSTRAINT [DF_PLANT_MAKE_PLAN_MakeNow] DEFAULT ((0)),
[OutOfProduct] [tinyint] NULL CONSTRAINT [DF_PLANT_MAKE_PLAN_OutOfProduct] DEFAULT ((0)),
[MAKE_DATE] [date] NULL,
[LINE_NUMBER] [varchar](10) NULL,
[LOT_NUMBER] [varchar](10) NULL,
[PART_CODE] [varchar](10) NULL,
[BRAND] [varchar](10) NULL,
[SORT_PART] [int] NULL,
[MAKE_CASES] [int] NULL,
[PrintCount] [int] NULL CONSTRAINT [DF_PLANT_MAKE_PLAN_PrintCount] DEFAULT ((0)),
[RemainingToMake] AS ([MAKE_CASES]-[PrintCount]),
CONSTRAINT [PK_PLANT_MAKE_PLAN] PRIMARY KEY CLUSTERED
)
SELECT '79375','0','0','0','2016-07-08','6','1116189','7427','ORG','2','0','9','9' UNION ALL
SELECT '79451','0','0','0','2016-07-08','1','1216189','1837','VEG','6','0','11','11' UNION ALL
SELECT '79335','0','0','0','2016-07-08','2','1216189','1643','VEG','904','0','0','0' UNION ALL
SELECT '79339','0','0','0','2016-07-08','2','1116189','1465','ORG','5','9','196','187' UNION ALL

Maybe you could be troubled to actually test your scripts before you upload them? Your primary key constraints have no columns defined, I assume you want an INSERT statement before those selects? Also, the select statements don't work as posted. Even adding an INSERT the select statements have a different number of columns than the table.

You have to keep in mind that we are volunteers offering our time to help you with your project. When you can't post sample data in a format that we can work with it takes a lot of effort to try to fix it. Please look again at what you posted and try to post something we can use.

The other piece of this puzzle that is missing is what you expect for output.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 6

It looks like you need to look for uncompleted orders in the plan table and tie them by lot number to the temp table in order to get to the line items are being run on.

I am just guessing. Usually I deal with individual work orders and statuses. The plan, customer demand, and stocking levels drive the creation of work orders.

There may be some issues like an order closed short, or multiple lots assigned to a line in the plan to the same line at the same time.

Maybe some more information, and an example or 2 of what you have tried would help fill in some gaps.

And a larger sample of data, with some completed orders would help get a better answer.