thanks for the code tip.

Unfortunately isnull() forces you to declare a replacement of the same data type of the column.

Invoice_Id(int, not null)

Code: ISNULL(invoice_id, 0) returns a zero

Code: ISNULL(invoice_ID,)...

I am adding the code to package flow I have designed that will be added to a SQL job so it can run automatically everyday. I tried doing it in...

I have finally got it after a couple of weeks, here's the code if anyone is interested;

[Code="SQL"]

WITH cte ( Invoice_id, IssueDate,optional_1,scandate,supplier,optional_2,invoicenumber,rejected,completed,notified,comments,transferred,

...

ALZDBA (7/14/2011)

---

just to add to the replies ...

1) of course. Your operator is ">=" !

You should alter it with a ">=" and "<" ( between can have...

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

Many thanks for your help :-D, but it is still not what I need.

I have uploaded a spreadsheet that shows the difference. Invoice ID 135393 only has one purchase...

thanks for update Jeff, it is more like.

invoice_id Line_id

1..............1

1..............2

1..............3

2..............1

2..............2

3..............1

4..............1

5..............1

6..............1

6..............2

And you got gets an error when I compile...

Sorry I thought it was clear, the previous provided SQL script is almost exactly what I need.

I have attached a excel spreadsheet that show the format of invoice that...

Can you explain the end of the second case statement, why the it has

'END AS [Invoice Date], Account,ApproveDate', but the first case only has [Invoice Date]?(what do you have...

David Burrows (7/7/2011)

---

Just add the alias to the end...

Jeff Moden (7/7/2011)

---

Take a look at the first link in my signature line below... it'll help...

Thanks David and opc.three you guys are awesome, it seem to give the basic structure I need even though I don't understand the code that well.

A couple of questions

1. where...

opc.three (7/6/2011)

---

ringovski (7/5/2011)

---

Jeff Moden (7/5/2011)

---

It may make a difference...

Where will the result set of this query be used? In a reporting tool? In a GUI? or...

I am...

No I don't want a cross join I was only trying to reference the companies table as it is used in the lower join.

SELECT ...