0

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

  • Thanks Andrew- you're on the right track. See this post for more clarity on how to provide all necessary info to us. – LowlyDBA Jul 20 '19 at 14:03
2

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 > @startDate AND SOH.DueDate < @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

enter image description here

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.