Issue with Group by Mysql query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Issue with Group by Mysql query

Author

Topic

newtoitall
Starting Member

5 Posts

Posted - 2014-09-11 : 06:16:13

Hello
I have been stumped by this query for the past day,and seeking some expert help before i throw my PC out of the window, lol.

I have a table that consists of invoices; invoice number column name is Trans_ref$$ (primary key).

each invoice is linked to a job , column name is OPSREF$$_ORIGINAL (foreign key).

Both columns are in the same table.

a job can have multiple invoice.

Now the issue is i'm trying to create a query that will identify the FIRST invoice (Trans_ref$$) in a job group and if that FIRST invoice is with a date range then output the job (OPSREF$$_ORIGINAL).
The invoice number in a sequential / unique number.

I have tried using a case / min group by statement to identify the first invoice in a job group within a data range, however this query is not isolating the first invoice within a date range, instead it is just outputting 1st or 2nd or 3rd positioned invoice in job group that matches the date range….I hope this makes sense .

I have included a screen shot of the table and the desired output, and would appreciate if someone can assist with the query.

I have attached a screen shot to help understand.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-11 : 09:22:20

Would you please post your query and CREATE TABLE statements for the tables the query references

newtoitall
Starting Member

5 Posts

Posted - 2014-09-11 : 09:32:07

sorry, thought i did ...




SET @START_DATE := "2014-08-27";
SET @END_DATE:= "2014-09-02";

select 
count(TRANS_REF$$),
Case 
when min(TRANS_REF$$) and INVOICE_DATE_D8>=@START_DATE and INVOICE_DATE_D8<=@END_DATE
then OPSREF$$_ORIGINAL
end as OPSREF$$_ORIGINAL
from INVOICE_HEADER_UNS
where 
TRANSACTION_STATUS = 9
and Tmode$$="06"
group by OPSREF$$_ORIGINAL

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-11 : 10:25:17

This:


when min(TRANS_REF$$) and INVOICE_DATE_D8>=@START_DATE and INVOICE_DATE_D8<=@END_DATE

is invalid syntax. Do you mean min(TRANS_REF$$) = (something)?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources