Can this be done in sql?
I am making an ordering system
I want to do this, When all the statuses of the dishes are "FULL", the order is automatically changed to completion
I have 4 tables
1 ORDER
ID_ORDER: OR00001
STATUS: IN PROCESS / CHANGE AUTOMATIC "FULL"
CREATION_DATE: 17/07/2017
3 DISH
1 DISH PASTE
ID: PA00001
NAME: PASTA MIDELA
CHEF: JEFF
ID_ORDER: OR00001
STATUS: IN PROCESS
2 DISH SALAD
ID: SA00001
NAME: DIET SALAD
CHEF: ASTON
ID_ORDER: OR00001
STATUS: IN PROCESS
3 DISH MEAT
ID: ME00001
NAME: MEAT BBF
CHEF: JEFF
ID_ORDER: OR00001
STATUS: IN PROCESS
How is this possible? thank
CREATE TABLE [dbo].[ORDER](
[ID_OR] [int] IDENTITY(1,1) NOT NULL,
[ID_ORDER] AS ('OR'+right('00000'+CONVERT([varchar],[ID_OR],(0)),(5))),
[STATUS] [datetime] NOT NULL,
[CREATION_DATE] [datetime] NOT NULL,
CONSTRAINT [PK_RR] PRIMARY KEY CLUSTERED
(
[ID_ORDER] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
CREATE TABLE [dbo].[1_DISH](
[ID_D1] [int] IDENTITY(1,1) NOT NULL,
[ID_PASTA] AS ('PA'+right('00000'+CONVERT([varchar],[ID_D1],(0)),(5))),
[NAME] [varchar](20),
[CHEF] [varchar](20),
[STATUS] [datetime] NOT NULL,
[ID_ORDER] [varchar](20)
CONSTRAINT [PK_RR] PRIMARY KEY CLUSTERED
(
[ID_PASTA] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
CREATE TABLE [dbo].[2_DISH](
[ID_D2] [int] IDENTITY(1,1) NOT NULL,
[ID_SALAD] AS ('SA'+right('00000'+CONVERT([varchar],[ID_D2],(0)),(5))),
[NAME] [varchar](20),
[CHEF] [varchar](20),
[STATUS] [datetime] NOT NULL,
[ID_ORDER] [varchar](20)
CONSTRAINT [PK_RR] PRIMARY KEY CLUSTERED
(
[ID_SALAD] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
CREATE TABLE [dbo].[3_DISH](
[ID_D3] [int] IDENTITY(1,1) NOT NULL,
[ID_MEAT] AS ('ME'+right('00000'+CONVERT([varchar],[ID_D3],(0)),(5))),
[NAME] [varchar](20),
[CHEF] [varchar](20),
[STATUS] [datetime] NOT NULL,
[ID_ORDER] [varchar](20)
CONSTRAINT [PK_RR] PRIMARY KEY CLUSTERED
(
[ID_MEAT] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
source to share
Here is some sample code that might help you.
Two tables have been created: one for Orders and one for the Dishes that contain the order.
I added a DishType column to the Dish table. I changed Status as data type varchar
.
declare @ORDERTABLE table
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[STATUS] [varchar](20) NOT NULL,
[CREATION_DATE] [datetime] NOT NULL
)
declare @DISHTABLE table
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[DISHTYPE] varchar(20),
[NAME] [varchar](20),
[CHEF] [varchar](20),
[STATUS] [varchar](20) NOT NULL,
[ID_ORDER] int
)
Some sample data is inserted.
insert @ORDERTABLE (STATUS, CREATION_DATE) values
('IN PROCESS', getdate()),
('IN PROCESS', getdate());
insert @DISHTABLE (DISHTYPE, NAME, CHEF, STATUS, ID_ORDER) values
('PASTA','PASTA MIDELA','JEFF','IN PROCESS',1),
('SALAD','DIET SALAD','KIA','IN PROCESS',1),
('MEAT','MEAT BBF','BART','IN PROCESS',1),
('SALAD','CAESAR SALAD','KIA','IN PROCESS',2),
('MEAT','ROAST CHICKEN','BART','IN PROCESS',2);
update @DISHTABLE set STATUS = 'COMPLETE' where ID = 1;
update @DISHTABLE set STATUS = 'COMPLETE' where ID = 2;
update @DISHTABLE set STATUS = 'COMPLETE' where ID = 3;
select * from @DISHTABLE;
An easy way to automatically change the order status: whenever you change the status of a dish, check how many dishes are in order versus the number of dishes. If all dishes are completed, change the order status to completion.
declare @ORDERID int = 1;
update @ORDERTABLE
set STATUS = 'COMPLETE'
where
ID = @ORDERID
and (select count(*) from @DISHTABLE where ID_ORDER = @ORDERID) = (select count(*) from @DISHTABLE where ID_ORDER = @ORDERID and STATUS = 'COMPLETE');
select
o.ID,
DISHES = (select count(*) from @DISHTABLE where ID_ORDER = o.ID),
COMPLETED = (select count(*) from @DISHTABLE where ID_ORDER = o.ID and STATUS = 'COMPLETE'),
o.STATUS
from @ORDERTABLE o;
source to share