MSSQL бүтээгдэхүүний ӨРТӨГ ОРЛОГО ТООЛЛОГО ЗАРЛАГА АШИГ тооцох

Санхүү эсвэл бүтээгдэхүүн үйлчилгээний програм хийхийг хүсдэг залуусд зориулж нэг ийм жишээ гаргалаа. MSSQL Editor дээр хуулж тавиад ажиллуулаад үзээрэй энэ их хэрэгтэй жишээ болсон байх гэж бодож байна. Бүтээгдэхүүн үйлчилгээний өртөг зардал орлого ашиг тооцоход энэ жишээ хэрэг болно. Миний зүгээс зориулж байна

USE [test]
GO

/****** Object:  Table [dbo].[Product]    Script Date: 06/21/2011 19:28:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Product](
    [ProductPkID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Size] [int] NOT NULL,
    [Price] [money] NOT NULL
) ON [PRIMARY]

GO


USE [test]
GO

/****** Object:  Table [dbo].[Income]    Script Date: 06/21/2011 19:28:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Income](
    [IncomePkID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [ProductPkID] [numeric](18, 0) NOT NULL,
    [IncomeSize] [int] NOT NULL,
    [IncomeAmount] [money] NOT NULL,
    [IncomeDate] [datetime] NOT NULL
) ON [PRIMARY]

GO


USE [test]
GO

/****** Object:  Table [dbo].[Outcome]    Script Date: 06/21/2011 19:28:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Outcome](
    [OutcomePkID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [ProductPkID] [numeric](18, 0) NOT NULL,
    [OutcomeSize] [int] NOT NULL,
    [OutcomeAmount] [money] NOT NULL,
    [OutcomeDate] [datetime] NOT NULL
) ON [PRIMARY]

GO


Өгөгдөлөөр дүүргие

insert Product values('Talh',1,950)
insert Product values('Tamhi',20,2000)
insert Product values('Juus',1,850)
insert Product values('Haraa',750,7500)

insert Income values(1,10,7500,'2011-06-21')--10sh 750
insert Income values(2,100,8000,'2011-06-21')--5 bottle 1600
insert Income values(3,16,11200,'2011-06-21')--16sh 700
insert Income values(3,8,5600,'2011-06-21')--8sh 700
insert Income values(4,3000,22000,'2011-06-21')--4 bottle 5500

insert Outcome values(1,8,950,'2011-06-21')--8sh 950
insert Outcome values(2,20,2000,'2011-06-21')--1 bottle 2000
insert Outcome values(2,20,2000,'2011-06-21')--1 bottle 2000
insert Outcome values(2,10,1000,'2011-06-21')--10sh 1000
insert Outcome values(3,10,8500,'2011-06-21')--10sh 850
insert Outcome values(3,2,1700,'2011-06-21')--2sh 850
insert Outcome values(3,1,850,'2011-06-21')--1sh 850
insert Outcome values(4,50,500,'2011-06-21')--50ml 500
insert Outcome values(4,50,500,'2011-06-21')--50ml 500
insert Outcome values(4,50,500,'2011-06-21')--50ml 500
insert Outcome values(4,750,7500,'2011-06-21')--1 bottle 7500
insert Outcome values(4,300,3000,'2011-06-21')--50ml 500


Ашиг орлого тооцие


FirstAmount зарагдсан барааг анх худалдаж авсан дүн
GetAmount зарагдсан бараанаас олсон ашиг

select I.*, O.OutcomeSize, O.OutcomeAmount
, (I.IncomeSize-O.OutcomeSize) as ChangeSize
, ((I.IncomeAmount / I.IncomeSize) * O.OutcomeSize) as FirstAmount
, (O.OutcomeAmount -((I.IncomeAmount / I.IncomeSize) * O.OutcomeSize)) as GetAmount
from (select ProductPkID, sum(IncomeSize) as IncomeSize, sum(IncomeAmount) as IncomeAmount, IncomeDate
    from Income group by ProductPkID, IncomeDate) I
inner join (select ProductPkID, sum(OutcomeSize) as OutcomeSize, sum(OutcomeAmount) as OutcomeAmount, OutcomeDate
    from Outcome group by ProductPkID, OutcomeDate) O
on I.ProductPkID=O.ProductPkID
order by ProductPkID

select * from Income order by ProductPkID
select * from Outcome order by ProductPkID