Equal to Monday to Friday

[Ringers]

Hi All,

I am trying to get all invoices from tables invoices_head & invoices line for the previous working day.

The script I have goes back one day which will be fine for everyday apart from monday. This script is to be inserted into a package to be run everyday.

SELECT *
FROM invoice_head INNER JOIN
invoice_lines ON invoice_head.Invoice_id=Invoice_lines.Invoice_id
WHERE (invoice_head.PaymentDate >= GETDATE() - 1)

 

[Ringers]

I have worked out this but it only goes back one day which makes it useless for Monday's.

SELECT invoice_head.Invoice_id AS Invoice_Id, invoice_head.IssueDate AS [Invoice Date], invoice_head.Optional_1 AS [Divsion Buyer],
invoice_head.ScanDate AS [Scan Date], invoice_head.Supplier AS [Supplier/Vendor], invoice_head.Optional_2 AS [PO Number],
invoice_head.InvoiceNumber AS [Invoice Number], invoice_head.Rejected, invoice_head.Completed AS [Transaction Date], invoice_head.Notified,
invoice_head.Comments, invoice_head.Transferred, invoice_head.Optional_3 AS [Invoice Type], invoice_head.InvoiceTotal AS [Total Invoice Amount],
invoice_head.Optional_4 AS TFN, invoice_head.Optional_5 AS Orginator, invoice_head.Amount_1 AS [GST Total Amount], invoice_head.Responsible,
invoice_head.IsCredit AS [Credit Note], invoice_head.Currency, invoice_head.PaymentDate AS [BCC Upload Date 1],
invoice_head.Optional_7 AS [Supplier Code], invoice_lines.Account AS [Account Code], invoice_lines.Optional_6 AS [Job Number],
invoice_lines.Optional_8 AS [Cost Type Code], invoice_lines.Department AS [Department Code], invoice_lines.Optional_7 AS [Employee Code],
invoice_lines.Optional_1 AS [Product Code], invoice_lines.Optional_9 AS [Campaign Code], invoice_lines.Optional_10 AS Optional,
invoice_lines.LineText AS Description, invoice_lines.Approver AS [Completed By], invoice_lines.Optional_3 AS [Divsion Code],
invoice_lines.ApproveDate AS [BCC Upload Date], invoice_lines.Optional_5 AS [Client Code], invoice_lines.Responsible AS [Previous Approvers],
invoice_lines.Remarks, invoice_lines.Amount_2 AS [Total Excl], invoice_lines.Amount_3 AS GST
FROM invoice_head INNER JOIN
invoice_lines ON invoice_head.Invoice_id = invoice_lines.Invoice_id INNER JOIN
companies ON invoice_head.company_id = companies.company_id AND invoice_head.Optional_1 = companies.companyname
WHERE invoice_lines.ApproveDate >=DATEADD(day, DATEDIFF(day,0,GETDATE())-1,0) AND invoice_lines.ApproveDate < DATEADD(day, DATEDIFF(day,0,GETDATE()),0)
ORDER BY companies.companyname

 

[IanWaterman]

Change where clause

WHERE (invoice_head.PaymentDate >= (case when datepart(dw, getdate()) = 2 then getdate()-3 else then getdate()-1))

Ian

 

[Ringers]

nice thanks ian didn't think of that.

 

[Ringers]

mmm can't get it to work,

WHERE invoice_line.ApproveDate >= (CASE WHEN datepart(dw, getdate())=2 THEN (getdate()-3) ELSE (getdate()-1))

 

[Rhys666]

Have you considered business holidays as well? I.E., in the UK, (no idea where you are), at Easter we get a holiday on a Friday and immediately subsequent Monday so for us the previous working day from Tuesday 26th April was Thursday 21st April.

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

 

[Ringers]

I am in Sydney,yeah i have but thats not an issue the data is coming from Singapore and Malaysia. If it's blank then thats fine.

 

[IanWaterman]

sorry missed the "end" for the case statement

WHERE invoice_line.ApproveDate >= (CASE WHEN datepart(dw, getdate())=2 THEN (getdate()-3) ELSE (getdate()-1) end)

Ian

 

[Ringers]

thanks for that.