Assigning Batchid to Cake Making Process

Question

Assigning Batchid to Cake Making Process

sunil.mvs

Ten Centuries

Points: 1031
More actions
April 10, 2014 at 8:04 am

#306539

Hi ,
Please observe below records of table Bakery . When cake is order it flows from BaseMaking----------> Decoration------------> Delivered/Reject . If cake is Rejected ,
again process has to start from BaseMaking ..
I need assign BatchID when it flows from BaseMaking to Delivered/Reject And For Analysis I need to exclude BatchID which are rejected..
Expected OutPut..
SQL Script
Create table dbo.Bakery ( CakeID int,EmployeeID Int ,ActionName varchar(20) ,LogTime datetime )
Insert into dbo.Bakery( CakeId, EmployeeID,ActionName,LogTime)Values
(1,1,'BaseMaking','2014-04-10 09:30:00.000' )
, (1,1,'Decoration','2014-04-10 10:30:00.000' )
, (2,2,'BaseMaking','2014-04-10 09:40:00.000' )
, (2,1,'Decoration','2014-04-10 10:43:00.000' )
, (2,3,'Reject','2014-04-10 10:50:00.000' )
, (2,2,'BaseMaking','2014-04-10 11:00:00.000' )
,( 2,1,'Decoration','2014-04-10 12:00:00.000' )
,( 2,3,'Delivered','2014-04-10 12:30:00.000' )

Viewing 14 posts - 1 through 13 (of 13 total)

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

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 1

I would first consider creating an enumeration type table for the Action and using that instead of the name, i.e. 'BaseMaking' etc. Can you supply the desired outcome for the sample data that you provided. What happens with the batchID after the rejection for CakeID 2 when it goes back to BaseMaking should it get the next BatchID or repeat the previous batchID for CakeID 2?

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

sunil.mvs Ten Centuries Points: 1031 More actions · Answer 2

Consider CakeID as something like OrderID from customer . If Cake is rejected ,again bakery start from Baking but CakeID remains Same but New batch number is assigned.

... You can assign intergers to ActionName (Categorical Values ) ,i have written to make you understand business flow ..

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 3

Can you supply the desired output give the sample data that you provided? That way we are on the same page.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

sunil.mvs Ten Centuries Points: 1031 More actions · Answer 4

I have provided expected output as 2nd snapshot (as a picture) in first thread itself . Let me know whether snapshot is available ...

Dohsan SSCrazy Points: 2972 More actions · Answer 5

It's a bit early and I haven't had a tea yet, but I think this does the job (Haven't tested this extensively).

IF OBJECT_ID(N'tempdb..#Bakery',N'U') IS NOT NULL

DROP TABLE #Bakery;

CREATE TABLE #Bakery

(

CakeIDINTNOT NULL,

EmployeeIDINTNOT NULL,

ActionNameVARCHAR(20) NOT NULL,

LogTimeDATETIMENOT NULL

);

INSERT INTO #Bakery( CakeId, EmployeeID,ActionName,LogTime)

VALUES(1,1,'BaseMaking','2014-04-10 09:30:00.000'),

(1,1,'Decoration','2014-04-10 10:30:00.000'),

(2,2,'BaseMaking','2014-04-10 09:40:00.000'),

(2,1,'Decoration','2014-04-10 10:43:00.000'),

(2,3,'Reject','2014-04-10 10:50:00.000'),

(2,2,'BaseMaking','2014-04-10 11:00:00.000'),

(2,1,'Decoration','2014-04-10 12:00:00.000'),

(2,3,'Delivered','2014-04-10 12:30:00.000');

SELECTB.CakeID,

B.EmployeeID,

B.ActionName,

B.LogTime,

(ROW_NUMBER() OVER (PARTITION BY B.CakeID ORDER BY B.LogTime ASC) - 1) /3 + 1 AS GroupCol

FROM#Bakery AS B

ORDERBY B.CakeID, B.LogTime;

Will they always be grouped up by 3 actions? As this is based on the order of actions always being in the order of basemaking, decoration and then either delivered or rejected with basemaking etc. happening again

sunil.mvs Ten Centuries Points: 1031 More actions · Answer 6

Hi Doshan ,

Query is working as expected ... Number of intermediate Steps in delivery of cake will vary . So /3 is hard coded .. Is there a way to calculate no of steps in Batchid .

to remove hard code as 3

Dohsan SSCrazy Points: 2972 More actions · Answer 7

sunil.mvs (4/11/2014)
Hi Doshan ,
Query is working as expected ... Number of intermediate Steps in delivery of cake will vary . So /3 is hard coded .. Is there a way to calculate no of steps in Batchid .
to remove hard code as 3

The step number will be different by each cake? Or can they be different by batch?

sunil.mvs Ten Centuries Points: 1031 More actions · Answer 8

Cake can have x steps and it depends on customer request .. Once work flow starts it starts with 1 and reaches nth step .. other cake may start from with 1 and ends at n-k step . if cake is rejected ,steps starts with 1 and ends either at same step of previous batch or at other ... I am sorry if i am making problem complex

Dohsan SSCrazy Points: 2972 More actions · Answer 9

sunil.mvs (4/11/2014)
Cake can have x steps and it depends on customer request .. Once work flow starts it starts with 1 and reaches nth step .. other cake may start from with 1 and ends at n-k step . if cake is rejected ,steps starts with 1 and ends either at same step of previous batch or at other ... I am sorry if i am making problem complex

So the number of steps could change by batch within the same cake id?

So 5 steps (rejected) and then only 4 for the next batch (delivered)?

sunil.mvs Ten Centuries Points: 1031 More actions · Answer 10

sunil.mvs

Ten Centuries

Points: 1031

April 11, 2014 at 10:09 am

#1705402

Yes you are right

David Burrows SSC Guru Points: 65138 More actions · Answer 11

sunil.mvs (4/11/2014)
Cake can have x steps and it depends on customer request .. Once work flow starts it starts with 1 and reaches nth step .. other cake may start from with 1 and ends at n-k step . if cake is rejected ,steps starts with 1 and ends either at same step of previous batch or at other ... I am sorry if i am making problem complex

This does not fit with your sample data and expected results.

Please post sample data and expected results that shows variable n steps (ie not the 3 you posted)

Far away is close at hand in the images of elsewhere.
Anon.

sunil.mvs Ten Centuries Points: 1031 More actions · Answer 12

Solution is fine . The other scenario will not arise which i clarified from business ..

Dohsan SSCrazy Points: 2972 More actions · Answer 13

sunil.mvs (4/12/2014)
Solution is fine . The other scenario will not arise which i clarified from business ..

If the number of steps can differ by cake ID, but is the same between batches, this simple change to above may also work for you. It will then work for more than 3 steps.