Rusty SQL = Heavily scratched head 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] &gt;= @invoicemonthstart and [date] &lt;=@invoicemonthend
union all
Select * from PTSInvoiceLineView WHERE [date] &gt;= @invoicemonthstart and [date] &lt;=@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
Due to the lack of information, I can only give you an example code that could solve this problem.

This is based on the AdventureWorks2014 database. I created a simple testing table that only has 2 attributes, the name of the sales person and the amount of sales he's made in total (count):

CREATE TABLE T (
PersonName nvarchar(30),
Sales int
)
GO


And this procedure takes 2 dates as input, selects the required data and inserts it into the table:

CREATE PROCEDURE test
    @startDate date,
    @endDate date
AS
BEGIN

WITH CTE AS (
SELECT  E.BusinessEntityID, 
        COUNT(SOH.SalesOrderID) AS [Sales]
FROM AdventureWorks2014.HumanResources.Employee AS E
    INNER JOIN AdventureWorks2014.Sales.SalesOrderHeader AS SOH
        ON SOH.SalesPersonID = E.BusinessEntityID
WHERE SOH.DueDate &gt; @startDate AND SOH.DueDate &lt; @endDate
GROUP BY E.BusinessEntityID
)
INSERT INTO T (
PersonName,
Sales
)
SELECT P.FirstName, CTE.Sales
FROM AdventureWorks2014.HumanResources.Employee AS E
    INNER JOIN AdventureWorks2014.Person.Person AS P
        ON P.BusinessEntityID = E.BusinessEntityID
    INNER JOIN CTE ON CTE.BusinessEntityID = E.BusinessEntityID

END
GO


For every sales person this calculates the amount of sales they have made between the @startDate and @endDate. You're going to have to edit this solution to fit your own tables and data.

Testing this procedure:

EXEC test '2011-06-30', '2012-06-30'


Gives the table this result