35 lines
3.2 KiB
Transact-SQL
35 lines
3.2 KiB
Transact-SQL
CREATE TABLE [dbo].[Entities_VoucherSettlements](
|
||
[VoucherSettlementID] [int] IDENTITY(1,1) NOT NULL,
|
||
[VoucherID] [int] NULL,
|
||
[Settled] [int] NOT NULL,
|
||
[Amount] [decimal](19,5) NULL,
|
||
CONSTRAINT [PK__Entities_VoucherSettlements] PRIMARY KEY CLUSTERED
|
||
(
|
||
[VoucherSettlementID] 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
|
||
ALTER TABLE [dbo].[Entities_VoucherSettlements] WITH CHECK ADD CONSTRAINT [FK__VoucherSettlements_Vouchers] FOREIGN KEY([VoucherID])
|
||
REFERENCES [dbo].[Entities_Vouchers] ([VoucherID])
|
||
GO
|
||
ALTER TABLE [dbo].[Entities_VoucherSettlements] CHECK CONSTRAINT [FK__VoucherSettlements_Vouchers]
|
||
GO
|
||
ALTER TABLE [dbo].[Entities_Vouchers] DROP COLUMN Settled
|
||
GO
|
||
INSERT INTO Entities_VoucherSettlements (VoucherID, Settled, Amount)
|
||
SELECT v.VoucherID, CASE WHEN v.Settled = 6 THEN 5 ELSE v.Settled END, SUM(i.Amount)
|
||
FROM Entities_Vouchers v INNER JOIN Entities_Kots k on v.voucherid = k.voucherid
|
||
inner join entities_inventories i on k.kotid = i.kotid
|
||
group by v.voucherid, v.settled
|
||
GO
|
||
INSERT INTO Entities_VoucherSettlements (VoucherID, Settled, Amount)
|
||
SELECT v.VoucherID, 8, round(SUM(i.Amount),0) * -1
|
||
FROM Entities_Vouchers v INNER JOIN Entities_Kots k on v.voucherid = k.voucherid
|
||
inner join entities_inventories i on k.kotid = i.kotid
|
||
group by v.voucherid, v.settled
|
||
GO
|
||
INSERT INTO Entities_VoucherSettlements (VoucherID, Settled, Amount)
|
||
SELECT vs.voucherid, 7, sum(vs.amount) * -1
|
||
from entities_vouchersettlements vs group by vs.voucherid
|
||
GO |