May 2, 2012 at 12:53 am
Hi Guys,
I have a main table called File:
/****** Object: Table [dbo].[File] Script Date: 05/02/2012 08:29:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[File](
[FileID] [bigint] IDENTITY(1,1) NOT NULL,
[VesselID] [bigint] NULL,
[VoyageNo] [varchar](255) NULL,
[OpenedDate] [datetime] NULL,
[FileStatusID] [int] NULL,
[ClosedDate] [datetime] NULL,
[ReopenedDate] [datetime] NULL,
[ClosedReason] [varchar](255) NULL,
[InstructedHowID] [bigint] NULL,
[ClaimTypeID] [bigint] NULL,
[ClaimSubType1ID] [bigint] NULL,
[ClaimSubType2ID] [bigint] NULL,
[ClaimSubType3ID] [bigint] NULL,
[OpenedByID] [uniqueidentifier] NULL,
[InstructedByID] [bigint] NULL,
[DiarisedToID] [uniqueidentifier] NULL,
[DiarisedDate] [datetime] NULL,
[InvoiceDiary] [datetime] NULL,
[OfficeHandlerID] [uniqueidentifier] NULL,
[OutToID] [uniqueidentifier] NULL,
[BranchID] [bigint] NULL,
[Remarks] [text] NULL,
[BoxedFile] [bit] NULL,
[AgentID] [bigint] NULL,
[FileNumber] [varchar](100) NULL,
[TimeBar] [datetime] NULL,
[Summons] [bit] NULL,
[LOU] [bit] NULL,
[Samples] [bit] NULL,
[DebtorID] [bigint] NULL,
CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED
(
[FileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[File] ON
INSERT [dbo].[File] ([FileID], [VesselID], [VoyageNo], [OpenedDate], [FileStatusID], [ClosedDate], [ReopenedDate], [ClosedReason], [InstructedHowID], [ClaimTypeID], [ClaimSubType1ID], [ClaimSubType2ID], [ClaimSubType3ID], [OpenedByID], [InstructedByID], [DiarisedToID], [DiarisedDate], [InvoiceDiary], [OfficeHandlerID], [OutToID], [BranchID], [Remarks], [BoxedFile], [AgentID], [FileNumber], [TimeBar], [Summons], [LOU], [Samples], [DebtorID]) VALUES (69, 4990, N'1', CAST(0x00009EF000000000 AS DateTime), 2, NULL, NULL, N'', 1, 93, 630, 152, 417, N'9758029f-7bec-4d77-8e45-f2241c43b654', 1, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x0000A03700000000 AS DateTime), CAST(0x0000A03900000000 AS DateTime), NULL, NULL, 1, N'', 1, 1, N'001', CAST(0x00009EF600000000 AS DateTime), 0, 1, 0, NULL)
INSERT [dbo].[File] ([FileID], [VesselID], [VoyageNo], [OpenedDate], [FileStatusID], [ClosedDate], [ReopenedDate], [ClosedReason], [InstructedHowID], [ClaimTypeID], [ClaimSubType1ID], [ClaimSubType2ID], [ClaimSubType3ID], [OpenedByID], [InstructedByID], [DiarisedToID], [DiarisedDate], [InvoiceDiary], [OfficeHandlerID], [OutToID], [BranchID], [Remarks], [BoxedFile], [AgentID], [FileNumber], [TimeBar], [Summons], [LOU], [Samples], [DebtorID]) VALUES (70, 1, N'2', CAST(0x00009EF400000000 AS DateTime), 1, NULL, NULL, N'', 1, 6, 46, NULL, NULL, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', 1, N'9758029f-7bec-4d77-8e45-f2241c43b654', CAST(0x00009EF000000000 AS DateTime), NULL, NULL, NULL, 1, N'', 0, NULL, N'002', NULL, 0, 0, 0, 1)
INSERT [dbo].[File] ([FileID], [VesselID], [VoyageNo], [OpenedDate], [FileStatusID], [ClosedDate], [ReopenedDate], [ClosedReason], [InstructedHowID], [ClaimTypeID], [ClaimSubType1ID], [ClaimSubType2ID], [ClaimSubType3ID], [OpenedByID], [InstructedByID], [DiarisedToID], [DiarisedDate], [InvoiceDiary], [OfficeHandlerID], [OutToID], [BranchID], [Remarks], [BoxedFile], [AgentID], [FileNumber], [TimeBar], [Summons], [LOU], [Samples], [DebtorID]) VALUES (71, 839, N'3', CAST(0x00009EF400000000 AS DateTime), 1, NULL, NULL, N'', 1, 8, 49, NULL, NULL, NULL, 1, N'9758029f-7bec-4d77-8e45-f2241c43b654', CAST(0x00009EF500000000 AS DateTime), CAST(0x0000A02B00000000 AS DateTime), NULL, NULL, 3, N'', 0, 1, N'003', NULL, 0, 1, 0, NULL)
Then I have a table called Costings:
USE [FileMan]
GO
/****** Object: Table [dbo].[Costings] Script Date: 05/02/2012 08:29:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Costings](
[CostingID] [bigint] IDENTITY(1,1) NOT NULL,
[Description] [varchar](150) NULL,
[isDisbursement] [bit] NULL,
[Active] [bit] NULL,
[sortOrder] [int] NULL,
CONSTRAINT [PK_Costings] PRIMARY KEY CLUSTERED
(
[CostingID] 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
SET IDENTITY_INSERT [dbo].[Costings] ON
INSERT [dbo].[Costings] ([CostingID], [Description], [isDisbursement], [Active], [sortOrder]) VALUES (1, N'Surveyor''s Fees', 1, 1, 1)
INSERT [dbo].[Costings] ([CostingID], [Description], [isDisbursement], [Active], [sortOrder]) VALUES (2, N'Chemists', 1, 1, 2)
INSERT [dbo].[Costings] ([CostingID], [Description], [isDisbursement], [Active], [sortOrder]) VALUES (3, N'Lawyers', 1, 1, 3)
INSERT [dbo].[Costings] ([CostingID], [Description], [isDisbursement], [Active], [sortOrder]) VALUES (4, N'Office Fee', 0, 1, 4)
INSERT [dbo].[Costings] ([CostingID], [Description], [isDisbursement], [Active], [sortOrder]) VALUES (5, N'Photocopy', 0, 1, 5)
SET IDENTITY_INSERT [dbo].[Costings] OFF
I display each costing in a gridview for each file. Every File must have each costing associated with it, so when I click save I insert the FileID and CostingID in a table called File_Costings:
/****** Object: Table [dbo].[File_Costings] Script Date: 05/02/2012 08:29:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[File_Costings](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[FileID] [bigint] NULL,
[CostingID] [bigint] NULL,
[Value] [decimal](18, 2) NULL,
[InvoiceID] [bigint] NULL,
CONSTRAINT [PK_File_Costings] PRIMARY KEY CLUSTERED
(
[ID] 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 IDENTITY_INSERT [dbo].[File_Costings] ON
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (16, 69, 1, CAST(2100.00 AS Decimal(18, 2)), 27)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (17, 69, 1, CAST(1200.00 AS Decimal(18, 2)), 29)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (18, 69, 1, CAST(300.00 AS Decimal(18, 2)), 31)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (19, 69, 1, CAST(100.00 AS Decimal(18, 2)), 30)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (20, 69, 2, CAST(500.00 AS Decimal(18, 2)), 27)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (21, 69, 2, CAST(600.00 AS Decimal(18, 2)), 29)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (22, 69, 2, CAST(700.00 AS Decimal(18, 2)), 31)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (23, 69, 2, CAST(800.00 AS Decimal(18, 2)), 30)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (24, 69, 3, CAST(900.00 AS Decimal(18, 2)), 27)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (25, 69, 3, CAST(0.00 AS Decimal(18, 2)), 29)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (26, 69, 3, CAST(0.00 AS Decimal(18, 2)), 31)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (27, 69, 3, CAST(0.00 AS Decimal(18, 2)), 30)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (28, 69, 4, CAST(120.00 AS Decimal(18, 2)), 27)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (29, 69, 4, CAST(0.00 AS Decimal(18, 2)), 29)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (30, 69, 4, CAST(0.00 AS Decimal(18, 2)), 31)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (31, 69, 4, CAST(0.00 AS Decimal(18, 2)), 30)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (32, 69, 5, CAST(150.00 AS Decimal(18, 2)), 27)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (33, 69, 5, CAST(0.00 AS Decimal(18, 2)), 29)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (34, 69, 5, CAST(0.00 AS Decimal(18, 2)), 31)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (35, 69, 5, CAST(0.00 AS Decimal(18, 2)), 30)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (36, 69, 1, CAST(0.00 AS Decimal(18, 2)), 33)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (37, 69, 2, CAST(0.00 AS Decimal(18, 2)), 33)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (38, 69, 3, CAST(110.00 AS Decimal(18, 2)), 33)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (39, 69, 4, CAST(0.00 AS Decimal(18, 2)), 33)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (40, 69, 5, CAST(50.00 AS Decimal(18, 2)), 33)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (41, 73, 1, CAST(73.00 AS Decimal(18, 2)), 34)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (42, 73, 2, CAST(74.00 AS Decimal(18, 2)), 34)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (43, 73, 3, CAST(75.00 AS Decimal(18, 2)), 34)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (44, 73, 4, CAST(76.00 AS Decimal(18, 2)), 34)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (45, 73, 5, CAST(77.00 AS Decimal(18, 2)), 34)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (46, 73, 1, CAST(70.00 AS Decimal(18, 2)), 35)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (47, 73, 1, CAST(78.00 AS Decimal(18, 2)), 36)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (48, 73, 2, CAST(0.00 AS Decimal(18, 2)), 35)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (49, 73, 2, CAST(0.00 AS Decimal(18, 2)), 36)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (50, 73, 3, CAST(0.00 AS Decimal(18, 2)), 35)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (51, 73, 3, CAST(0.00 AS Decimal(18, 2)), 36)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (52, 73, 4, CAST(0.00 AS Decimal(18, 2)), 35)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (53, 73, 4, CAST(0.00 AS Decimal(18, 2)), 36)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (54, 73, 5, CAST(0.00 AS Decimal(18, 2)), 35)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (55, 73, 5, CAST(0.00 AS Decimal(18, 2)), 36)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (56, 72, 1, CAST(1.00 AS Decimal(18, 2)), 37)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (57, 72, 2, CAST(0.00 AS Decimal(18, 2)), 37)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (58, 72, 3, CAST(0.00 AS Decimal(18, 2)), 37)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (59, 72, 4, CAST(0.00 AS Decimal(18, 2)), 37)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (60, 72, 5, CAST(0.00 AS Decimal(18, 2)), 37)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (61, 74, 1, CAST(74.00 AS Decimal(18, 2)), 38)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (62, 74, 2, CAST(75.00 AS Decimal(18, 2)), 38)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (63, 74, 3, CAST(10.00 AS Decimal(18, 2)), 38)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (64, 74, 4, CAST(10.00 AS Decimal(18, 2)), 38)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (65, 74, 5, CAST(20.00 AS Decimal(18, 2)), 38)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (66, 70, 1, CAST(170.00 AS Decimal(18, 2)), 39)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (67, 70, 2, CAST(10.00 AS Decimal(18, 2)), 39)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (68, 70, 3, CAST(10.00 AS Decimal(18, 2)), 39)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (69, 70, 4, CAST(10.00 AS Decimal(18, 2)), 39)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (70, 70, 5, CAST(0.00 AS Decimal(18, 2)), 39)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (71, 69, 1, CAST(0.00 AS Decimal(18, 2)), 40)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (72, 69, 2, CAST(0.00 AS Decimal(18, 2)), 40)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (73, 69, 3, CAST(0.00 AS Decimal(18, 2)), 40)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (74, 69, 4, CAST(0.00 AS Decimal(18, 2)), 40)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (75, 69, 5, CAST(0.00 AS Decimal(18, 2)), 40)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (76, 69, 1, CAST(1500.00 AS Decimal(18, 2)), 0)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (81, 185, 1, CAST(2000.00 AS Decimal(18, 2)), 0)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (82, 185, 2, CAST(100.00 AS Decimal(18, 2)), 0)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (83, 185, 3, CAST(0.00 AS Decimal(18, 2)), 0)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (84, 185, 4, CAST(0.00 AS Decimal(18, 2)), 0)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (85, 185, 5, CAST(0.00 AS Decimal(18, 2)), 0)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (86, 185, 1, CAST(5000.00 AS Decimal(18, 2)), 41858)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (87, 185, 2, CAST(1.00 AS Decimal(18, 2)), 41858)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (88, 185, 3, CAST(0.00 AS Decimal(18, 2)), 41858)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (89, 185, 4, CAST(0.00 AS Decimal(18, 2)), 41858)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (90, 185, 5, CAST(0.00 AS Decimal(18, 2)), 41858)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (91, 185, 1, CAST(100.00 AS Decimal(18, 2)), 41859)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (92, 185, 2, CAST(1.00 AS Decimal(18, 2)), 41859)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (93, 185, 3, CAST(1.00 AS Decimal(18, 2)), 41859)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (94, 185, 4, CAST(14.00 AS Decimal(18, 2)), 41859)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (95, 185, 5, CAST(0.00 AS Decimal(18, 2)), 41859)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (96, 69, 2, CAST(0.00 AS Decimal(18, 2)), 0)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (97, 69, 3, CAST(0.00 AS Decimal(18, 2)), 0)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (98, 69, 4, CAST(0.00 AS Decimal(18, 2)), 0)
INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (99, 69, 5, CAST(0.00 AS Decimal(18, 2)), 0)
SET IDENTITY_INSERT [dbo].[File_Costings] OFF
The above table also saves an InvoiceID because a costing is also attached to an Invoice.
USE [FileMan]
GO
/****** Object: Table [dbo].[Invoice] Script Date: 05/02/2012 08:39:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Invoice]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Invoice](
[InvoiceID] [bigint] IDENTITY(1,1) NOT NULL,
[FileID] [bigint] NULL,
[InvoiceNumber] [varchar](50) NULL,
[InvoiceDate] [datetime] NULL,
[StartDate] [datetime] NULL,
[Amount] [decimal](18, 2) NULL,
[PaidDate] [datetime] NULL,
[PaidAmount] [decimal](18, 2) NULL,
[Reference] [text] NULL,
[CreatedBy] [uniqueidentifier] NULL,
[CreatedDate] [datetime] NULL,
[DebtorID] [bigint] NULL,
[CreditNoteNumber] [varchar](150) NULL,
CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED
(
[InvoiceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Invoice] ON
INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [Reference], [CreatedBy], [CreatedDate], [DebtorID], [CreditNoteNumber]) VALUES (33, 69, N'001E', CAST(0x00009F6E00000000 AS DateTime), CAST(0x00009F5100000000 AS DateTime), CAST(500.00 AS Decimal(18, 2)), CAST(0x00009F7200000000 AS DateTime), NULL, NULL, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009F6300BA96CC AS DateTime), 1, NULL)
INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [Reference], [CreatedBy], [CreatedDate], [DebtorID], [CreditNoteNumber]) VALUES (34, 73, N'73A', CAST(0x00009F7300000000 AS DateTime), CAST(0x00009F5100000000 AS DateTime), CAST(2000.00 AS Decimal(18, 2)), NULL, NULL, NULL, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009F64002DFB7C AS DateTime), 1, NULL)
INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [Reference], [CreatedBy], [CreatedDate], [DebtorID], [CreditNoteNumber]) VALUES (35, 73, N'73B', CAST(0x00009F8A00000000 AS DateTime), CAST(0x00009F7300000000 AS DateTime), CAST(300.00 AS Decimal(18, 2)), NULL, NULL, NULL, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009F64003B5A88 AS DateTime), 1, NULL)
INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [Reference], [CreatedBy], [CreatedDate], [DebtorID], [CreditNoteNumber]) VALUES (36, 73, N'73C', CAST(0x00009FAB00000000 AS DateTime), CAST(0x00009F9C00000000 AS DateTime), CAST(100.00 AS Decimal(18, 2)), NULL, NULL, NULL, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009F64003D0518 AS DateTime), 1, NULL)
INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [Reference], [CreatedBy], [CreatedDate], [DebtorID], [CreditNoteNumber]) VALUES (37, 72, N'72A', CAST(0x00009F5000000000 AS DateTime), CAST(0x00009F3200000000 AS DateTime), CAST(200.00 AS Decimal(18, 2)), NULL, NULL, NULL, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009F6500B1240C AS DateTime), 1, NULL)
INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [Reference], [CreatedBy], [CreatedDate], [DebtorID], [CreditNoteNumber]) VALUES (38, 74, N'74A', CAST(0x00009F3100000000 AS DateTime), CAST(0x00009F0200000000 AS DateTime), CAST(500.00 AS Decimal(18, 2)), NULL, NULL, NULL, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009F6500BFB3C8 AS DateTime), 1, NULL)
INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [Reference], [CreatedBy], [CreatedDate], [DebtorID], [CreditNoteNumber]) VALUES (39, 70, N'70A', CAST(0x00009FBF00000000 AS DateTime), CAST(0x00009F5C00000000 AS DateTime), CAST(14000.00 AS Decimal(18, 2)), NULL, NULL, NULL, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009F65001967E8 AS DateTime), 1, NULL)
INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [Reference], [CreatedBy], [CreatedDate], [DebtorID], [CreditNoteNumber]) VALUES (40, 69, N'001A', CAST(0x00009F8500000000 AS DateTime), CAST(0x00009F7000000000 AS DateTime), CAST(3000.00 AS Decimal(18, 2)), NULL, NULL, NULL, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009F7300D34910 AS DateTime), 2, NULL)
SET IDENTITY_INSERT [dbo].[Invoice] OFF
So in order to display my data as in the attached image I use this SP to pivot the data per InvoiceNumber:
/****** Object: StoredProcedure [dbo].[PivotBreakdown] Script Date: 05/02/2012 08:29:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[PivotBreakdown]
-- Add the parameters for the stored procedure here
@FileID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @ColumnHeaders VARCHAR(MAX)
SELECT @ColumnHeaders =
COALESCE(
@ColumnHeaders + ',[' + convert(varchar(MAX),invoiceid) + ']'
,'[' + convert(varchar(MAX),invoiceid) + ']'
) FROM
(
SELECT 0 as invoiceid
UNION ALL
SELECT DISTINCT invoiceid -- get distinct invoice numbers
FROM invoice WHERE FileID = @FileID
)aliasName
/* view results from above statement */
--SELECT @ColumnHeaders
DECLARE @PivotTable NVARCHAR(MAX)
SET @PivotTable = N'
SELECT *
FROM (
SELECT c.CostingID, c.Description, c.isDisbursement, 0 as isTimeSheet, fc.invoiceid, fc.value as [value], c.sortorder
FROM Costings C
LEFT JOIN File_Costings FC ON (FC.CostingID = c.CostingID) AND (fc.FileID=' + CONVERT(varchar(MAX),@FileID) + ')
LEFT JOIN Invoice i ON i.invoiceid = fc.invoiceid
WHERE (C.Active = 1)
GROUP BY i.invoiceid
) AS TableToPivot
PIVOT (
SUM(value)
FOR invoiceid IN (
' + @ColumnHeaders + '
)
) AS PivotedTable
ORDER BY sortorder'
EXECUTE(@PivotTable)
END
GO
As you can see, if I pass through a FileID of 69 I get all the costings listed and the Invoice Numbers attached to that file are displayed as columns in my grid. If I save an entry is made into the File_Costings Table for each row of data with the FileID, CostingID and InvoiceID.
This all works great as the need at the time was to save all costings per file, but my client now needs to add new Costings per file. So the open FileID 69. They want to add a Costing to the Grid called "Example Cost". This new costing must only apply to FileID 69.
How can I alter my existing structure to cater for this. Do I add a Boolean flag "isdefault" , set to true to all the current costings(because all current costings must appear for each file), then for any new costings save the flag as false into the Costings table, then insert a record into the File_Costings table?
Please help. I need to have this done as soon as possible. Thanks
In terms of functionality is this the best way? What other options do I have?
May 23, 2012 at 11:35 am
Hi Guys,
i need help to design a database,
what am suppose to do is this
1. need to measure the availability of a telecom devices on different locations daily,
the locations have sub locations or areas, the area/sub locations have different telecom devices and the telecom devices have to be check if they are in good shape or working.
first i have the location table,sublocation table,devices table and deviceLocationLink table,
location table
locationID int autoincrement PK.
locationName varcher(20).
sublocation table
subLocationID int auto_increment PK.
locationID int FK.
sublocationName varchar(20).
device table
deviceID int auto_increment PK.
deviceName varchar(20).
deviceLocationLink
deviceLocationID int auto_increment PK.
deviceID int FK.
locationID int FK.
sublocationID int FK.
that's all i can do don't know what to do anymore i need help to continue fron here.
thanks
joedbug
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply