January 28, 2015 at 8:13 pm
Hello,
I wish to make a query with if condition Implemented in a database sql server 2008 R2.
I would like to set up a system FEFO (first expired first out) based on batch number based on the dates of Lapsed but since I struggle to put my request in place.
the principle of my request is:
if amount of movement (QTEMVT)> = amount entered by the user via a user interface then withdraws the amount entered by the user in the batch (NUMLOT) the amount of movement of the item that lapses the first (execution of my request).
else if amount of movement (QTEMVT) <amount entered by the user
then the difference between the amount entered by the user and the amount of movement (QTEMVT) (execution of my request) and the following conditions:
the amount of movement (QTEMVT) = the amount of movement (QTEMVT) of this item stored in my database and the amount of movement (QTEMVT) <> 0
by taking the difference of the item requested directly from the batch (NUMLOT) of the item directly after lapses.
Basically I want to set up an item management query based on batch number and expiration dates.
ps: QTEMVT = quantity of the item stored in my database
NUMLOT = batch number items
DATFABRIC = manufacturing date items
DATPEREMP = expiry date items
Here is my request:
SELECT f.NUMLOT,f.DATFABRIC,f.DATPEREMP,f.QTEMVT
FROM FAIRE f INNER JOIN mouvement m ON f.CODMVT = m.CODMVT
INNER JOIN TYPE_MOUVEMENT tm ON tm.CODTYPMVT = m.CODTYPMVT
INNER JOIN SOUS_SITE ss ON ss.CODSOUSIT = m.CODSOUSIT
INNER JOIN SITE s ON s.CODSITE = ss.CODSITE
INNER JOIN ARTICLE a ON f.CODART = a.CODART
INNER JOIN DESTINATION d ON m.CODDEST = d.CODDEST
INNER JOIN EMPLOYER e ON m.MATEMP=e.MATEMP
WHERE a.codart='M0001'and f.NUMLOT=30 and
f.DATPEREMP=(select min(f.datperemp)
from faire f INNER JOIN mouvement m ON f.CODMVT = m.CODMVT
INNER JOIN TYPE_MOUVEMENT tm ON tm.CODTYPMVT = m.CODTYPMVT
INNER JOIN SOUS_SITE ss ON ss.CODSOUSIT = m.CODSOUSIT
INNER JOIN SITE s ON s.CODSITE = ss.CODSITE
INNER JOIN ARTICLE a ON f.CODART = a.CODART
INNER JOIN DESTINATION d ON m.CODDEST = d.CODDEST
INNER JOIN EMPLOYER e ON m.MATEMP=e.MATEMP
WHERE s.CODSITE= 'PharmaF2')
here is the script to my database:
GO
/****** Object: Table [dbo].[TYPE_MOUVEMENT] Script Date: 01/29/2015 02:58:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TYPE_MOUVEMENT](
[CODTYPMVT] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
[LIBTYPMVT] [varchar](50) NOT NULL,
CONSTRAINT [PK_TYPE_MOUVEMENT] PRIMARY KEY NONCLUSTERED
(
[CODTYPMVT] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[SOUS_SITE] Script Date: 01/29/2015 02:58:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SOUS_SITE](
[CODSOUSIT] [varchar](10) NOT NULL,
[CODSITE] [varchar](10) NOT NULL,
[LIBSOUSIT] [varchar](30) NULL,
CONSTRAINT [PK_SOUS_SITE] PRIMARY KEY CLUSTERED
(
[CODSOUSIT] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SOUS_SITE] WITH CHECK ADD CONSTRAINT [FK_SOUS_SIT_RENFERMER_SITE] FOREIGN KEY([CODSITE])
REFERENCES [dbo].[SITE] ([CODSITE])
GO
ALTER TABLE [dbo].[SOUS_SITE] CHECK CONSTRAINT [FK_SOUS_SIT_RENFERMER_SITE]
GO
/****** Object: Table [dbo].[SITE] Script Date: 01/29/2015 02:58:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SITE](
[CODSITE] [varchar](10) NOT NULL,
[LIBSITE] [varchar](30) NULL,
CONSTRAINT [PK_SITE] PRIMARY KEY CLUSTERED
(
[CODSITE] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[MOUVEMENT] Script Date: 01/29/2015 02:58:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MOUVEMENT](
[CODMVT] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
[CODSOUSIT] [varchar](10) NOT NULL,
[CODDEST] [varchar](6) NOT NULL,
[MATEMP] [numeric](6, 0) NOT NULL,
[CODTYPMVT] [numeric](6, 0) NOT NULL,
[DATMVT] [date] NOT NULL,
[CODCONSULT] [varchar](6) NULL,
CONSTRAINT [PK_MOUVEMENT] PRIMARY KEY NONCLUSTERED
(
[CODMVT] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MOUVEMENT] WITH CHECK ADD CONSTRAINT [FK_MOUVEMEN_AVOIR_DESTINAT] FOREIGN KEY([CODDEST])
REFERENCES [dbo].[DESTINATION] ([CODDEST])
GO
ALTER TABLE [dbo].[MOUVEMENT] CHECK CONSTRAINT [FK_MOUVEMEN_AVOIR_DESTINAT]
GO
ALTER TABLE [dbo].[MOUVEMENT] WITH CHECK ADD CONSTRAINT [FK_MOUVEMEN_ETRE_TYPE_MOU] FOREIGN KEY([CODTYPMVT])
REFERENCES [dbo].[TYPE_MOUVEMENT] ([CODTYPMVT])
GO
ALTER TABLE [dbo].[MOUVEMENT] CHECK CONSTRAINT [FK_MOUVEMEN_ETRE_TYPE_MOU]
GO
ALTER TABLE [dbo].[MOUVEMENT] WITH CHECK ADD CONSTRAINT [FK_MOUVEMEN_EXERCER_EMPLOYER] FOREIGN KEY([MATEMP])
REFERENCES [dbo].[EMPLOYER] ([MATEMP])
GO
ALTER TABLE [dbo].[MOUVEMENT] CHECK CONSTRAINT [FK_MOUVEMEN_EXERCER_EMPLOYER]
GO
ALTER TABLE [dbo].[MOUVEMENT] WITH CHECK ADD CONSTRAINT [FK_MOUVEMEN_INCLURE_SOUS_SIT] FOREIGN KEY([CODSOUSIT])
REFERENCES [dbo].[SOUS_SITE] ([CODSOUSIT])
GO
ALTER TABLE [dbo].[MOUVEMENT] CHECK CONSTRAINT [FK_MOUVEMEN_INCLURE_SOUS_SIT]
GO
/****** Object: Table [dbo].[FAMILLE_ARTICLE] Script Date: 01/29/2015 02:58:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FAMILLE_ARTICLE](
[CODFAMART] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
[LIBFAMART] [varchar](18) NULL,
CONSTRAINT [PK_FAMILLE_ARTICLE] PRIMARY KEY NONCLUSTERED
(
[CODFAMART] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[FAIRE] Script Date: 01/29/2015 02:58:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FAIRE](
[CODART] [varchar](8) NOT NULL,
[CODMVT] [numeric](6, 0) NOT NULL,
[QTEMVT] [numeric](6, 0) NOT NULL,
[DATPEREMP] [date] NOT NULL,
[DATFABRIC] [date] NULL,
[NUMLOT] [numeric](10, 0) NOT NULL,
CONSTRAINT [PK_FAIRE] PRIMARY KEY CLUSTERED
(
[CODART] ASC,
[CODMVT] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[FAIRE] WITH CHECK ADD CONSTRAINT [FK_FAIRE_FAIRE_ARTICLE] FOREIGN KEY([CODART])
REFERENCES [dbo].[ARTICLE] ([CODART])
GO
ALTER TABLE [dbo].[FAIRE] CHECK CONSTRAINT [FK_FAIRE_FAIRE_ARTICLE]
GO
ALTER TABLE [dbo].[FAIRE] WITH CHECK ADD CONSTRAINT [FK_FAIRE_FAIRE2_MOUVEMEN] FOREIGN KEY([CODMVT])
REFERENCES [dbo].[MOUVEMENT] ([CODMVT])
GO
ALTER TABLE [dbo].[FAIRE] CHECK CONSTRAINT [FK_FAIRE_FAIRE2_MOUVEMEN]
GO
/****** Object: Table [dbo].[EMPLOYER] Script Date: 01/29/2015 02:58:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EMPLOYER](
[MATEMP] [numeric](6, 0) NOT NULL,
[NOMEMP] [varchar](10) NULL,
[PRENEMP] [varchar](25) NULL,
[SEXEMP] [char](1) NULL,
[DATNAISSEMP] [date] NULL,
[LIEUNAISSEMP] [varchar](30) NULL,
[TELEMP] [varchar](11) NULL,
[PROFIL] [varchar](15) NULL,
[MDP] [varchar](50) NULL,
[CODSITE] [varchar](10) NULL,
CONSTRAINT [PK_EMPLOYER] PRIMARY KEY NONCLUSTERED
(
[MATEMP] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[EMPLOYER] WITH CHECK ADD FOREIGN KEY([CODSITE])
REFERENCES [dbo].[SITE] ([CODSITE])
GO
ALTER TABLE [dbo].[EMPLOYER] WITH CHECK ADD FOREIGN KEY([CODSITE])
REFERENCES [dbo].[SITE] ([CODSITE])
GO
ALTER TABLE [dbo].[EMPLOYER] WITH CHECK ADD FOREIGN KEY([CODSITE])
REFERENCES [dbo].[SITE] ([CODSITE])
GO
ALTER TABLE [dbo].[EMPLOYER] WITH CHECK ADD FOREIGN KEY([CODSITE])
REFERENCES [dbo].[SITE] ([CODSITE])
GO
/****** Object: Table [dbo].[DESTINATION] Script Date: 01/29/2015 02:57:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DESTINATION](
[CODDEST] [varchar](6) NOT NULL,
[LIBDEST] [varchar](20) NOT NULL,
CONSTRAINT [PK_DESTINATION] PRIMARY KEY NONCLUSTERED
(
[CODDEST] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[CONTENIR] Script Date: 01/29/2015 02:57:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CONTENIR](
[CODART] [varchar](8) NOT NULL,
[CODCMD] [numeric](6, 0) NOT NULL,
[QTECMD] [numeric](6, 0) NOT NULL,
CONSTRAINT [PK_CONTENIR] PRIMARY KEY CLUSTERED
(
[CODART] ASC,
[CODCMD] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[CONTENIR] WITH CHECK ADD CONSTRAINT [FK_CONTENIR_CONTENIR_ARTICLE] FOREIGN KEY([CODART])
REFERENCES [dbo].[ARTICLE] ([CODART])
GO
ALTER TABLE [dbo].[CONTENIR] CHECK CONSTRAINT [FK_CONTENIR_CONTENIR_ARTICLE]
GO
ALTER TABLE [dbo].[CONTENIR] WITH CHECK ADD CONSTRAINT [FK_CONTENIR_CONTENIR2_COMMANDE] FOREIGN KEY([CODCMD])
REFERENCES [dbo].[COMMANDE] ([CODCMD])
GO
ALTER TABLE [dbo].[CONTENIR] CHECK CONSTRAINT [FK_CONTENIR_CONTENIR2_COMMANDE]
GO
/****** Object: Table [dbo].[COMMANDE] Script Date: 01/29/2015 02:57:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[COMMANDE](
[CODCMD] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
[NUMBONCMD] [numeric](5, 0) NOT NULL,
[LIBCMD] [varchar](30) NOT NULL,
[DATCMD] [date] NOT NULL,
CONSTRAINT [PK_COMMANDE] PRIMARY KEY NONCLUSTERED
(
[CODCMD] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[ARTICLE] Script Date: 01/29/2015 02:56:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ARTICLE](
[CODART] [varchar](8) NOT NULL,
[CODFAMART] [numeric](6, 0) NOT NULL,
[DESIGART] [varchar](60) NULL,
[PUBRUTART] [money] NOT NULL,
[PVPUBLIC] [money] NULL,
[SEUILMINI] [numeric](4, 0) NOT NULL,
CONSTRAINT [PK_ARTICLE] PRIMARY KEY NONCLUSTERED
(
[CODART] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ARTICLE] WITH CHECK ADD CONSTRAINT [FK_ARTICLE_CONCERNER_FAMILLE_] FOREIGN KEY([CODFAMART])
REFERENCES [dbo].[FAMILLE_ARTICLE] ([CODFAMART])
GO
ALTER TABLE [dbo].[ARTICLE] CHECK CONSTRAINT [FK_ARTICLE_CONCERNER_FAMILLE_]
GO
help me please
January 28, 2015 at 10:37 pm
up help me please
January 29, 2015 at 8:04 am
Your script has some foreign keys in the wrong order but I was able to work that out. What we don't have is any sample data or any kind of idea of what you are wanting to do.
_______________________________________________________________
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/
January 29, 2015 at 10:16 am
hello SSChampion
thank you for answered
As done in my previous request I would like to apply the FEFO system so if I have for example the following product data:
Numlot 1 Prod: A: Manufacturing Date: 12/02/2008: Date Expiration: 12/02/2012 QTEMVT: 120 Units.
Numlot 2 Prod: B Date made: 06/12/209: Date Expiration: 12/06/2014 QTEMVT: 80 Units.
Numlot 3: Prod C Date made: 17/08/2010 Date Expiration: 17/08/2015 QTEMVT: 400 Units.
management rule: subtraction is done on the NUMLOT product that has the date of the nearest expiration so on.
in my case if I have to do 400 units out of stock, I really must get out:
120 units 1 lines.
+ 80 units of the two lines.
200 units of the 3 lines.
Sample data is stored in the table (FAIRE)
joins I did in my previous request are indispensable to me
January 29, 2015 at 10:48 am
136romy (1/29/2015)
hello SSChampionthank you for answered
As done in my previous request I would like to apply the FEFO system so if I have for example the following product data:
Numlot 1 Prod: A: Manufacturing Date: 12/02/2008: Date Expiration: 12/02/2012 QTEMVT: 120 Units.
Numlot 2 Prod: B Date made: 06/12/209: Date Expiration: 12/06/2014 QTEMVT: 80 Units.
Numlot 3: Prod C Date made: 17/08/2010 Date Expiration: 17/08/2015 QTEMVT: 400 Units.
management rule: subtraction is done on the NUMLOT product that has the date of the nearest expiration so on.
in my case if I have to do 400 units out of stock, I really must get out:
120 units 1 lines.
+ 80 units of the two lines.
200 units of the 3 lines.
Sample data is stored in the table (FAIRE)
joins I did in my previous request are indispensable to me
After running your ddl and the query you provided there is no data because there are no inserts. I am having a really hard time visualizing what you are trying to do with text alone. Some sample data and desired output based on that sample would really clear this up.
_______________________________________________________________
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/
January 29, 2015 at 11:17 am
Interesting problem. I see you want to consume inventory in expiration date order.
Personally I would do this procedurally in a loop in a stored procedure, filling a local table variable one row at a time and then return the results.
January 29, 2015 at 12:20 pm
Bill Talada (1/29/2015)
Interesting problem. I see you want to consume inventory in expiration date order.Personally I would do this procedurally in a loop in a stored procedure, filling a local table variable one row at a time and then return the results.
Not sure we need to resort to looping here. We just need a running total which can be done a number of ways without looping. Once we have some sample data to start with this should be pretty straight forward.
_______________________________________________________________
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/
January 29, 2015 at 5:21 pm
Chiming in so I see sample data when it is posted because I'd like to take a crack at it too. Inventory problems can be lots of fun, particularly if you want to stick with a set-based query.
Too bad this isn't SQL 2012. That might help make it a bit easier (although is not guaranteed to).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 29, 2015 at 7:44 pm
Hello,
Here is a sample insertion of relevant tables by my request.
--insert table article
INSERT INTO [HEALTHBOARD].[dbo].[ARTICLE]
([CODART]
,[CODFAMART]
,[DESIGART]
,[PUBRUTART]
,[PVPUBLIC]
,[SEUILMINI])
VALUES
('M0001',1,'Amoxiciline ubi 250mg pdre susp F/60ml',93500,100000,10)
GO
INSERT INTO [HEALTHBOARD].[dbo].[ARTICLE]
([CODART]
,[CODFAMART]
,[DESIGART]
,[PUBRUTART]
,[PVPUBLIC]
,[SEUILMINI])
VALUES
('M0002',1,'Amoxico 500mg gel b/100',6025,7025,20)
GO
-- insert table destination
INSERT INTO [HEALTHBOARD].[dbo].[DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('BPH1','Bon de pharmacie1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('BPH2','Bon de pharmacie2')
GO
INSERT INTO [HEALTHBOARD].[dbo].[DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('Phcie1','Pharmacie interne F1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('Phcie2','Pharmacie interne F2')
GO
--insert table site
INSERT INTO [HEALTHBOARD].[dbo].[SITE]
([CODSITE]
,[LIBSITE])
VALUES
('PharmaF1','Pharmacie1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[SITE]
([CODSITE]
,[LIBSITE])
VALUES
('PharmaF2','Pharmacie2')
go
--insert table sous_site
INSERT INTO [HEALTHBOARD].[dbo].[SOUS_SITE]
([CODSOUSIT]
,[CODSITE]
,[LIBSOUSIT])
VALUES
('DISP1','PharmaF1','Dispensaire1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[SOUS_SITE]
([CODSOUSIT]
,[CODSITE]
,[LIBSOUSIT])
VALUES
('DISP2','PharmaF2','Dispensaire2')
go
--insert table type_mouvement
INSERT INTO [HEALTHBOARD].[dbo].[TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Sortie gratuite1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Sortie gratuite2')
GO
INSERT INTO [HEALTHBOARD].[dbo].[TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Entrée pharmacie F1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Entrée pharmacie F2')
GO
--insert table employer
INSERT INTO [HEALTHBOARD].[dbo].[EMPLOYER]
([MATEMP]
,[NOMEMP]
,[PRENEMP]
,[SEXEMP]
,[DATNAISSEMP]
,[LIEUNAISSEMP]
,[TELEMP]
,[PROFIL]
,[MDP]
,[CODSITE])
VALUES
(171819,'YAO','kan','M','13/06/1984','ferké','01-20-96-63','gestionnaire','cogito23','PharmaF1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[EMPLOYER]
([MATEMP]
,[NOMEMP]
,[PRENEMP]
,[SEXEMP]
,[DATNAISSEMP]
,[LIEUNAISSEMP]
,[TELEMP]
,[PROFIL]
,[MDP]
,[CODSITE])
VALUES
(4021,'HUNT','kim','F','20/10/1980','bouaké','03-25-89-21','gestionnaire','lynx_123p','PharmaF2')
GO
--insert table mouvement
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP1','Phcie1',171819,3,'28/01/2015')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP1','Phcie1',171819,3,'29/01/2015')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP1','Phcie1',171819,3,'30/01/2015')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP2','Phcie2',4021,4,'28/01/2015')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP2','Phcie2',4021,4,'29/01/2015')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP2','Phcie2',4021,4,'30/01/2015')
GO
--insert table faire
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0001',1,120,'12/02/2012','12/02/2008',101023)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0001',2,80,'06/12/2014','06/12/209',119620)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0001',3,400,'17/08/2015','17/08/2010',124560)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0002',4,120,'25/04/2017','25/04/2010',203645)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0002',5,80,'18/12/2020','18/12/2010',308912)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0002',6,400,'03/07/2022','03/07/2011',970562)
GO
January 29, 2015 at 8:02 pm
So what results do you want to get out of that sample data?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 29, 2015 at 9:23 pm
I would like for item 'M0001 apply the FEFO(First Expired First Out) system.
In my case I would like to withdraw the stock of 400 units this item but first I would like to get the units of that item where the batch number (NUMLOT) lapses first and then if the amount requested was not sufficient in batch which expires first then removes the difference in batch lapses immediatly after.
Clearly out of the 400 units of my item 'M0001' request should do this:
120 (the number in stock of the item that the expiry date is closest)
80 (the number in stock of the item that the expiry date immediately following)
200 (which will be deducted from the third batch of this item is to say, one whose expiration date is furthest from the first two).
So if an article to a near expiration date but its stock = 0. It must be returned by the query as an article which expires first
January 30, 2015 at 6:20 am
up please
January 30, 2015 at 9:27 am
136romy (1/30/2015)
up please
You do realize that the people that help here are unpaid volunteers doing this when we have time between our lives and work to provide back to the community that has helped us grow.
If this is an urgent need perhaps you should look at hiring a consultant instead of relying on anonymous volunteers on the web.
January 30, 2015 at 9:45 am
hello Lynn Pettis,
You're right, I'm really sorry. I apologize to you and to the whole community for this inconvenience.
January 30, 2015 at 9:53 am
I just ran the script to create your tables and the code failed:
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_SOUS_SIT_RENFERMER_SITE' references invalid table 'dbo.SITE'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_SOUS_SIT_RENFERMER_SITE' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_MOUVEMEN_AVOIR_DESTINAT' references invalid table 'dbo.DESTINATION'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_MOUVEMEN_AVOIR_DESTINAT' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_MOUVEMEN_EXERCER_EMPLOYER' references invalid table 'dbo.EMPLOYER'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_MOUVEMEN_EXERCER_EMPLOYER' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_FAIRE_FAIRE_ARTICLE' references invalid table 'dbo.ARTICLE'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_FAIRE_FAIRE_ARTICLE' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_CONTENIR_CONTENIR_ARTICLE' references invalid table 'dbo.ARTICLE'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_CONTENIR_CONTENIR_ARTICLE' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_CONTENIR_CONTENIR2_COMMANDE' references invalid table 'dbo.COMMANDE'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_CONTENIR_CONTENIR2_COMMANDE' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Suggestion, before posting the DDL for your tables run the scripts in an empty database to ensure that they work without errors. I'll check back later to see you have posted new DDL as I don't have time to fix the issues myself.
It does look like it may just be an issue with the order in which things are created.
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply