Order By - SQL Server Forums

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

Other Forums

MS Access

Order By

Author

Topic

mateenmohd
Constraint Violating Yak Guru

297 Posts

Posted - 2006-11-07 : 08:14:32

SELECT SHOPDRAWING.DCIL_NO AS SHOPDRAWING_DCIL_NO, SHOPDRAWING.REF_NO,
SHOPDRAWING.DWG_NO,
SHOPDRAWING.DWG_TITLE, SHOPDRAWING.DATE_FS,
SHOPDRAWING.ZONE, SHOPDRAWING.CONTRACTOR AS SHOPDRAWING_CONTRACTOR,
SHOPDRAWING.CONSULTANT, CONTRACTOR.CONTRACTOR AS CONTRACTOR_CONTRACTOR,
CONTRACTOR.CONTRACTOR_ID, SHOPDWGDETAILS.REC_ID,
SHOPDWGDETAILS.DCIL_NO AS SHOPDWGDETAILS_DCIL_NO, SHOPDWGDETAILS.DWG_TYPE,
SHOPDWGDETAILS.CONTRACTOR AS SHOPDWGDETAILS_CONTRACTOR,
SHOPDWGDETAILS.BY, SHOPDWGDETAILS.REV,
SHOPDWGDETAILS.ACTION_BY, SHOPDWGDETAILS.DATE_SUBMITTED,
SHOPDWGDETAILS.DATE_RETURNED, SHOPDWGDETAILS.STATUS,
SHOPDWGDETAILS.REMARKS, SHOPDWGDETAILS.DIST_SOFT,
SHOPDWGDETAILS.DIST_HARD
FROM (CONTRACTOR INNER JOIN SHOPDRAWING ON CONTRACTOR.CONTRACTOR = SHOPDRAWING.CONTRACTOR)
INNER JOIN SHOPDWGDETAILS ON SHOPDRAWING.DCIL_NO = SHOPDWGDETAILS.DCIL_NO
order by SHOPDWGDETAILS.DATE_SUBMITTED asc;

above query return the record like this.

date_submitted..date_return...status
------------------------------------
.................27-08-2005.....AAN
.................12-12-2005.....AAN
23-08-2005.........................
10-12-2005.........................

I want to retreive the record order by like this

date_submitted..date_return...status
------------------------------------
23-08-2005.........................
.................27-08-2005.....AAN
10-12-2005.........................
.................12-12-2005.....AAN
....

this is shopdrawings in and out query. ie
first drawing submitt and after that necessary action
drawing return to contractor.
how use the date submitted and date return in order by
IIF clause with ISNULL ?

Thanks

Mateen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 08:22:10

Maybe
ORDER BY IIF(ISNULL(date_submitted), date_return, date_submitted)

Peter Larsson
Helsingborg, Sweden

mateenmohd
Constraint Violating Yak Guru

297 Posts

Posted - 2006-11-12 : 02:24:08

Thanks for response.
I use order by in the query like this

ORDER BY IIF(ISNULL(SHOPDWGDETAILS.DATE_SUBMITTED),SHOPDWGDETAILS.DATE_RETURNED,SHOPDWGDETAILS.DATE_SUBMITTED)

when query run it retreive/display the record properly as I want.
but when I run the report where same query use it not
dislay the records, as I want above.
it display like this

date_submitted..date_return...status
------------------------------------
.................12-12-2005.....AAN
.................27-08-2005.....AAN
23-08-2005.........................
10-12-2005.........................
...

it should be display like this.

date_submitted..date_return...status
------------------------------------
23-08-2005.........................
.................27-08-2005.....AAN
10-12-2005.........................
.................12-12-2005.....AAN
...

I also save the requery, some other properties will change?

Mateen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-12 : 03:58:42

Do you have some sorting in the report on the date_submitted column?
Remove all sorting on the report.

Peter Larsson
Helsingborg, Sweden

mateenmohd
Constraint Violating Yak Guru

297 Posts

Posted - 2006-11-30 : 00:51:48

Thanks for response.

no sorting in the report on the date_submitted column.
I remove all sorting on the report.
I use only above query on report

Mateen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources