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

closed as unclear what you're asking by LowlyDBA, mustaccio, John Eisbrener, Marcello Miorelli, Paul White Jul 23 at 9:53

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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 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

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