group sort issue

[codrutza]

SQL Server 2005

Please help me to write a script for a report. (with Crystal Reports)

If a job includes vessel and the job has more than one im/ex mov
then the following rule is applied to determine which im/ex mov is used for the report:
Export jobs
The mov with the latest departure date
Import jobs
The mov with the earliest departure date

Problem: so I have to have a group by status (Intransit, Scheduled, No status) and I have to follow the rule above. But I don’t know how to do it, because if I make in the report a group by job, the records will be sort by jobs, and in the same time, I need them to be sorted by date, and by vessel.

job mov vessel dep loc dep date arr loc arr date ie
Intransit
100
1002 ar Qingdao 19/02/2012 Dub 17/03/2012 ex
1001 de Singapore 21/02/2012 Dub 19/03/2012 im
101
1011 be Yantian 20/02/2012 Dub 01/04/2012 im
143
1431 be Hong Kong 08/02/2012 Dub 30/04/2012 im
145
1451 ci Yantian 17/02/2012 Dub 02/04/2012 im
150
1501 ae Hong Kong 10/02/2012 Dub 23/03/2012 im
300
3001 br Qingdao 18/02/2012 Dub 28/03/2012 ex

No status
134

190

200


Scheduled
120
1201 ar Yantian 21/02/2012 Dub 12/04/2012 im
180
1801 fe Hong Kong 09/02/2012 Dub 17/04/2012 im
300
3002 no Yantian 19/02/2012 Dub 24/04/2012 im


Finally, I have to have only a record for a job.
Is it a way to retrieve only a record for a job from beginning, knowing the rule?

If I didn’t explain better, tell me what information should I provide.

 

[Olaf Doschke]

In shorter: You want to group by job, but on condition of the jobtype want to get a max() or a min() aggregate in that job. Should be doable via CASE.

I assume there would be an additional field "jobtype" containing the job type 'Export' or 'Import', then something like:

SELECT ...,
CASE jobtype='Export' Max(DepartureDate) Else Min(DepartureDate) End As jobDepartureDate,
From yourtable
Group By job, jobtype

PS: you have to add jobtype to the Group clause, otherwise you can't read and use it, that's a restriction of SQL Grouping.

But since a job should either be Export or Import this does not introduce more groups, it's just technically needed to not need to use Min(jobtype) or Avg(jobtype) to make it a valid SQL.

Bye, Olaf.

 

[Olaf Doschke]

Sorry, wrong syntax.

Case When 
   jobtype='Export'
Then
   Max(DepartureDate)
Else 
   Min(DepartureDate)
End
As jobDepartureDate

Bye, Olaf.