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

I was trying to avoid putting everything in the group by, which why i put the invoice_lines columns in a nested select. I orginally had it with the invoice_head columns...

This might be "Concatenating values when the number of items is not known" never done that before.

I worked it out, the problem was the string were delceared as variables.

This works,

([DivisionCode]=="MV"?"Mediavest":([DivisionCode]=="SC"?"Starcom - Non Tech":[DivisionCode]=="SP"?"Starcom - P&G":[DivisionCode]=="ST"?"Starcom - Tech":"Starcom - Non Tech"))