I am trying to figure out the best way to sort this solution.
I am trying to produce a SP (or series) to generate invoices so that the contractors know how much they will be paid.
I have created two views that query the staff allocation table and produce the invoice lines
select * from EventInvoiceLineView WHERE [date] >= @invoicemonthstart and [date] <=@invoicemonthend union all Select * from PTSInvoiceLineView WHERE [date] >= @invoicemonthstart and [date] <=@invoicemonthend Order By StaffID Asc, [date] Asc
This gives me my invoice line table. [StaffID], [EventID], [JobType], [EventName], [date]
So I now need to use this information to produce an invoice header for each staff member that has a record from these views.
my invoice header has: InvoiceRef (PK, Int, not null) InvoiceDate (date, not null) StaffID (FK, int, not null) InvoicePayDate (date, not null) InvoiceSortCode (nvarchar(6), not null) InvoiceAccount (nvarchar(8)), not null)
so... I am thinking
Insert Into InvoiceHeader InvoiceDate = date.now StaffID = cycling through the StaffID from the view InvoicePayDate = @scheduledpayday InvoiceSortcode = (Select [sortcode] from tblStaff where [staffid] = staffid) InvoiceAccount = (Select [Account] from tblStaff where [staffid] = staffid]
I then need to get the InvoiceRef PK int and use that to insert the rows from the view into my InvoiceLineItems which is laid out as: LineID (PK, Int, not null) InvoiceRef (FK, Int, not null) -- Invoice header record id JobID (int, not null) -- JobID from View Jobdate (date, not null) -- date from View JobType (nvarchar(10), not null) -- JobType from View JobName (NVarchar(250), not null) -- EventName from View JobPaid (money, not null) -- Calculated field
Any hints, tips, pointers or templates would be greatly appreciated