July 12, 2011 at 2:54 am
Also check that you are using my latest query (repeated below)
Also check the WHERE clause as this could drop lines giving a false impression that the query is not working
SELECT h.Invoice_id,
h.IssueDate AS [Invoice Date],
h.Optional_1 AS [Division Buyer],
h.ScanDate AS [Scan Date],
h.Supplier AS [Supplier/Vendor],
h.Optional_2 AS [PO Number],
h.InvoiceNumber AS [Invoice Number],
h.Rejected,
h.Completed AS [Transaction Date],
h.Notified,
h.Comments,
h.Transferred,
h.Optional_3 AS [Invoice Type],
h.InvoiceTotal AS [Total Invoice Amount],
h.Optional_4 AS [TFN],
h.Optional_5 AS [Orginator],
h.Amount_1 AS [GST Total Amount],
h.Responsible,
h.IsCredit AS [Credit Note],
h.Currency,
h.PaymentDate AS [BCC Upload Date1],
h.Optional_7 AS [Supplier Code],
l.Account AS [Account Code],
l.Optional_6 AS [Job Number],
l.Optional_8 AS [Cost Type Code],
l.Department AS [Department Code],
l.Optional_7 AS [Employee Code],
l.Optional_1 AS [Product Code],
l.Optional_9 AS [Campaign Code],
l.Optional_10 AS [Optional],
l.LineText AS [Description],
l.Approver AS [Completed By],
l.Optional_3 AS [Division Code],
l.ApproveDate AS [BCC Upload Date],
l.Optional_5 AS [Client Code],
l.Responsible AS [Previous Approvers],
l.Remarks,
l.Amount_2 AS [Total Excl GST],
l.Amount_3 AS [GST],
l.LineTotal AS [Total Inc GST]
FROM Companies c
JOIN Invoice_Head x
ON x.company_id = c.company_id
JOIN Invoice_Lines l
ON l.Invoice_id = x.Invoice_id
LEFT JOIN Invoice_Head h
ON h.Invoice_id = l.Invoice_id
AND l.line_id = 1
WHERE c.company_id = 44
AND c.companyname = h.Optional_1
AND l.Invoice_id ='135291'
AND l.ApproveDate >= CASE WHEN DATEPART(dw, GETDATE()) = 2 THEN (GETDATE() - 3) ELSE (GETDATE() - 1) END
ORDER BY l.Invoice_id ASC, l.Line_id ASC
Far away is close at hand in the images of elsewhere.
Anon.
July 12, 2011 at 8:33 pm
Hi,
I have uploaded two spreadsheets one shows the acutal results and the other expected results. The example invoice is number 132887, you can see the differences.
There is also two text files, one for the table scehma's and the other is the SQL script upto this point and finally a excel file with the output.
Thanks
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply