SQL Query - Count only workdays...

[prisonbreaker82]

Dear all,

I am very new at SQL programming and are using the Microsoft Query to avoid alot of SQL programming, but I am stuck on one thing when I try to filter some data from the database...

I want to show all delayed deliveries to our customers...

Our Policy is to deliver the goods to customer within 4 workingdays. I have created a filter in the end of this SQL query..., please find the bold marked part.

We do only count workdays... How do I make the query to only count workdays and not Saturday and Sundays?

Please help me out! :)

SELECT Min(PedidoDeVenda.CdChamada) AS 'Mín de CdChamada', Pessoa.NmPessoa, PedidoDeVenda.DtEntrada, Usuario_1.NmUsuario, Documento.DtEmissao, Usuario.NmUsuario FROM ALTERDATA.dbo.Documento Documento, ALTERDATA.dbo.DocumentoItem DocumentoItem, ALTERDATA.dbo.Operacao Operacao, ALTERDATA.dbo.PedidoDeVenda PedidoDeVenda, ALTERDATA.dbo.PedidoDeVendaItem_DocumentoItem PedidoDeVendaItem_DocumentoItem, ALTERDATA.dbo.Pessoa Pessoa, ALTERDATA.dbo.Produto Produto, ALTERDATA.dbo.Usuario Usuario, ALTERDATA.dbo.Usuario Usuario_1 WHERE PedidoDeVendaItem_DocumentoItem.IdPedidoDeVenda = PedidoDeVenda.IdPedidoDeVenda AND DocumentoItem.IdDocumentoItem = PedidoDeVendaItem_DocumentoItem.IdDocumentoItem AND DocumentoItem.IdDocumento = Documento.IdDocumento AND Pessoa.IdPessoa = Documento.IdPessoa AND Usuario.IdUsuario = Documento.IdUsuario AND Produto.IdProduto = DocumentoItem.IdProduto AND Usuario_1.IdUsuario = PedidoDeVenda.IdUsuario AND Operacao.IdOperacao = Documento.IdOperacao GROUP BY Pessoa.NmPessoa, PedidoDeVenda.DtEntrada, Usuario_1.NmUsuario, Documento.DtEmissao, Usuario.NmUsuario, Documento.StDocumentoCancelado, Documento.StDocumentoImpresso, Operacao.TpOperacao, (DOCUMENTO.DTEMISSAO-PEDIDODEVENDA.DTENTRADA) HAVING (PedidoDeVenda.DtEntrada>={ts '2010-01-01 00:00:00'}) AND (Documento.StDocumentoCancelado='N') AND (Documento.StDocumentoImpresso='S') AND (Operacao.TpOperacao='V') AND ((DOCUMENTO.DTEMISSAO-PEDIDODEVENDA.DTENTRADA)>4) ORDER BY PedidoDeVenda.DtEntrada

DTEMISSAO is when we have sent the goods and created a invoice
DTENTRADA is when we have entered the order in the system

[pclfw]

One way you might do this would be to identify the day (of the week) number of the start date. i.e. Sun = 1; Mon = 2 etc. For valid starting dates (Mon to Fri) you have a range of 2 to 6.

Now It is easy to see that only Monday has its offset in the same week, i.e. dateadd(d, 4, STARTDATE) all of the other days of the week need an offset of 6, i.e. dateadd(d, 6, STARTDATE) Drop these into a CASE clause within the SELECT statement and jobs a goodun. :)