Hey Rhys666
Thanks for the reply.
not sure what you mean by "Also, I'm not sure why you're doing this, but I would strongly suggest you change your logic and format the output at point of display, not in the SQL."?
I have designed a package to extract some data and then I will add the package to a daily job.
here is my full code to put it in context:
WITH cte ( Invoice_id, IssueDate,optional_1,scandate,supplier,optional_2,invoicenumber,rejected,completed,notified,comments,transferred,optional_3,invoiceTotal,optional_4,optional_5, amount_1,responsible,creditnote,currency,paymentdate,optional_7,
Account, Optional_6, optional_8,department,optional_7l,optional_1l,linetext,approver,optional_3l,approvedatel,optional_5l,responsiblel,remarks,amount_2l,amount3l,linetotal,RowId)
AS (
SELECT h.Invoice_id,h.IssueDate,h.optional_1,h.scandate,h.supplier,h.optional_2,h.invoicenumber,h.rejected,h.completed,h.notified,h.comments,h.transferred,h.optional_3,h.InvoiceTotal,h.optional_4,h.optional_5,h.amount_1,h.responsible,h.iscredit,h.currency,h.paymentdate,h.optional_7,
l.Account,l.optional_6,l.optional_8,l.Department,l.Optional_7,l.optional_1,l.linetext,l.approver,l.optional_3,l.approveDate,l.optional_5,l.Responsible,remarks,l.amount_2,l.amount_3,l.linetotal,
ROW_NUMBER() OVER ( PARTITION BY h.Invoice_id ORDER BY h.Invoice_id ASC )
FROM Companies c
JOIN Invoice_head h ON h.company_id = c.company_id
JOIN Invoice_lines l ON l.invoice_id = h.invoice_id
WHERE ( c.company_id = 35 )
AND ( l.ApproveDate >= ( CASE WHEN DATEPART(dw, GETDATE()) = 2 THEN ( GETDATE() - 3 )
ELSE ( GETDATE() - 1 )
END ) )
)
SELECT CASE WHEN RowId = 1 THEN CAST(Invoice_id AS VARCHAR(20))
ELSE ''
END AS [Invoice_id],
CASE WHEN RowId = 1 THEN CAST(IssueDate AS VARCHAR(20))
ELSE ''
END AS [Inovice Date],
CASE WHEN RowId = 1 THEN CAST(optional_1 AS VARCHAR(50))
ELSE ''
END AS [Division\Buyer],
CASE WHEN RowId = 1 THEN CAST(ScanDate AS VARCHAR(20))
ELSE ''
END AS [Scan Date],
CASE WHEN RowId = 1 THEN CAST(Supplier AS VARCHAR(50))
ELSE ''
END AS [Supplier\Vendor],
CASE WHEN RowId = 1 THEN CAST(optional_2 AS VARCHAR(50))
ELSE ''
END AS [PONumber],
CASE WHEN RowId = 1 THEN CAST(invoicenumber AS VARCHAR(50))
ELSE ''
END AS [Invoice Number],
CASE WHEN RowId = 1 THEN CAST(rejected AS VARCHAR(20))
ELSE ''
END AS [Rejected],
CASE WHEN RowId = 1 THEN CAST(completed AS VARCHAR(20))
ELSE ''
END AS [Transaction Date],
CASE WHEN RowId = 1 THEN CAST(Notified AS VARCHAR(20))
ELSE ''
END AS [Notified],
CASE WHEN RowId = 1 THEN CAST(Comments AS VARCHAR(500))
ELSE ''
END AS [Comments],
CASE WHEN RowId = 1 THEN CAST(Transferred AS VARCHAR(20))
ELSE ''
END AS [Transferred],
CASE WHEN RowId = 1 THEN CAST(Optional_3 AS VARCHAR(50))
ELSE ''
END AS [InvoiceType],
CASE WHEN RowId = 1 THEN CAST(ScanDate AS VARCHAR(20))
ELSE ''
END AS [Scan Date],
CASE WHEN RowId = 1 THEN CAST(InvoiceTotal AS VARCHAR(20))
ELSE ''
END AS [Total Invoice Amount],
CASE WHEN RowId = 1 THEN CAST(Optional_4 AS VARCHAR(20))
ELSE ''
END AS [TFN],
CASE WHEN RowId = 1 THEN CAST(Optional_5 AS VARCHAR(50))
ELSE ''
END AS [Orginator],
CASE WHEN RowId = 1 THEN CAST(Amount_1 AS VARCHAR(20))
ELSE ''
END AS [GST Total Amount],
CASE WHEN RowId = 1 THEN CAST(Responsible AS VARCHAR(20))
ELSE ''
END AS [Responsible],
CASE WHEN RowId = 1 THEN CAST(creditnote AS VARCHAR(20))
ELSE ''
END AS [Credit Note],
CASE WHEN RowId = 1 THEN CAST(Currency AS VARCHAR(20))
ELSE ''
END AS [Currency],
CASE WHEN RowId = 1 THEN CAST(PaymentDate AS VARCHAR(20))
ELSE ''
END AS [BCC Upload Date 1],
CASE WHEN RowId = 1 THEN CAST(Optional_7 AS VARCHAR(20))
ELSE ''
END AS [Supplier Code],Account,Optional_6 AS [Job Number], Optional_8 AS [Cost Type Code],Department AS [Department Code],
Optional_7l AS [Employee Code], optional_1l AS [Product Code],linetext AS [Description], Approver AS [Completed By],
optional_3l AS [Division Code], approvedatel AS [BCC Upload Date], optional_5l AS [Client Code], Responsiblel AS [Responsible],remarks,
amount_2l AS [Total Excl GST], amount3l AS [GST], linetotal AS [Total Incl GST]
FROM cte
