T-SQL Order By ASC or DESC dynamically ??

Dominic34
Dominic34 used Ask the Experts™
on
Hi,

I have that give the following result:

2      27 novembre 2005      MATIN      1      1      Advil      c:\template\gravol5.tmp
3      27 novembre 2005      MIDI      2      1      Advil      c:\template\advil500.tmp
5      27 novembre 2005      SOUPER      3      2      Gravol      c:\template\gravol5.tmp
6      27 novembre 2005      COUCHER      4      3      Advil      c:\template\advil500.tmp
7      26 novembre 2005      MATIN      1      1      Advil      c:\template\advil500.tmp
8      26 novembre 2005      MIDI      2      1      Advil      c:\template\advil500.tmp
9      26 novembre 2005      SOUPER      3      2      Gravol      c:\template\gravol5.tmp
10      26 novembre 2005      COUCHER      4      3      Advil      c:\template\advil500.tmp
12      25 novembre 2005      MATIN      1      1      Advil      c:\template\advil500.tmp
13      25 novembre 2005      MIDI      2      1      Advil      c:\template\advil500.tmp
14      25 novembre 2005      SOUPER      3      2      Gravol      c:\template\gravol5.tmp
15      25 novembre 2005      COUCHER      4      3      Advil      c:\template\advil500.tmp
....

the order statement is: ORDER BY rxDate DESC, per.perID, cnt.cntOrdre
perID is the 1st field. rxDate is the second. cntOrdre is the fourth field is the previous result.

I need the recordset to be order by cntOrder ASC on the first day and DESC on the second day and so on, so I'd have this:

2      27 novembre 2005      MATIN      1      1      Advil      c:\template\gravol5.tmp
3      27 novembre 2005      MIDI      2      1      Advil      c:\template\advil500.tmp
5      27 novembre 2005      SOUPER      3      2      Gravol      c:\template\gravol5.tmp
6      27 novembre 2005      COUCHER      4      3      Advil      c:\template\advil500.tmp
10      26 novembre 2005      COUCHER      4      3      Advil      c:\template\advil500.tmp
9      26 novembre 2005      SOUPER      3      2      Gravol      c:\template\gravol5.tmp
8      26 novembre 2005      MIDI      2      1      Advil      c:\template\advil500.tmp
7      26 novembre 2005      MATIN      1      1      Advil      c:\template\advil500.tmp
12      25 novembre 2005      MATIN      1      1      Advil      c:\template\advil500.tmp
13      25 novembre 2005      MIDI      2      1      Advil      c:\template\advil500.tmp
14      25 novembre 2005      SOUPER      3      2      Gravol      c:\template\gravol5.tmp
15      25 novembre 2005      COUCHER      4      3      Advil      c:\template\advil500.tmp


is it possible?? how can I do this??

thanks a lot for your time and help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
if you add a new field to your query (not to your table), and this field contains cntOrdre when rxDate = max(date) else it contains (cntOrder * -1), you will be able to sort on that new field.
it's possible
you can achive that by using case expressions
but i will need more details
tell me the column names and how you decide if it should be asc or desc

Author

Commented:
first day, it should be ASC, second day, DESC, third day, ASC, fourth day DESC, and so on.

recordset will be 28 rows (4 time period in the 7 days of a week).
MATIN = Morning
MIDI = Noon
SOUPER = evening
COUCHER = bed time
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Senior .Net Consultant
Top Expert 2016
Commented:
>>first day, it should be ASC, second day, DESC, third day, ASC, fourth day DESC, and so on.

you need to know which dates are even or odds, you can do this by creating a distinct list of dates with rowid and then use this rowid to discover if the row is even or odd and multply the cntOrder of even rows by -1 to order by this column

you will need to generate a column that will have the values according to which you want to sort, for example:


order by  rxDate,
               case when datepart(day, rxDate) in (1,3,5,7) then perId else -1*perId end

Author

Commented:
ok, I have the following now:

CREATE TABLE #Date
(
      rowID      INT IDENTITY(1,1),
      rxDate VARCHAR(50)
)
INSERT INTO #Date
SELECT DISTINCT rxDate FROM dbo.TB_disRxPeriode
INNER JOIN dbo.TB_disFichePatient ON dbo.TB_disRxPeriode.ficheID = dbo.TB_disFichePatient.ficheID
WHERE dbo.TB_disFichePatient.ficheNoDossier = 'GAGD07077502'
ORDER BY rxDate DESC;

WITH CTE AS (
SELECT  
      per.perID,
      per.rxDate,
      rowID,
      cnt.cntNom,
                     cnt.cntOrdre,
    CASE       WHEN cntNom = 'MATIN' THEN med.medFreqAM
      WHEN cntNom = 'MIDI' THEN med.medFreqMD
            WHEN cntNom = 'SOUPER' THEN med.medFreqSP
            WHEN cntNom = 'COUCHER' THEN med.medFreqHS
    END Quantite,
    medi.medNom,
    medi.medTemplateFile
FROM dbo.TB_disFichePatient fic
INNER JOIN dbo.TB_disRxPeriode per ON fic.ficheID = per.ficheID
INNER JOIN dbo.TB_disRxList list ON per.perID = list.perID
INNER JOIN dbo.TB_disListMed med ON list.idxList = med.idxList AND med.ficheID = fic.ficheID
INNER JOIN dbo.TB_disConstante cnt ON per.nomID = cnt.cntID
INNER JOIN dbo.TB_disMedicament medi ON med.medID = medi.medID
INNER JOIN #Date ON #Date.rxDate = per.rxDate
WHERE fic.ficheNoDossier = 'GAGD07077502'
)
SELECT DISTINCT
      a.perID,
      a.rxDate,
      a.rowID,
      a.cntNom,
      a.cntOrdre,
      STUFF(
      (SELECT '; ' + CAST(Quantite AS VARCHAR) + ',' + medNom + ',' + medTemplateFile FROM CTE WHERE perID = a.perID FOR XML PATH('')),
      1, 2, '') as Posologie
FROM CTE a
ORDER BY a.rxDate DESC, CASE WHEN (a.rowID % 2 = 1) THEN a.cntOrdre END, CASE WHEN (a.rowID % 2 = 0) THEN a.cntOrdre END DESC;

1) can I do this without the temporary table?
2) I get the following error:  ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Author

Commented:
I figured it out. I used a derived table:

SELECT * FROM
(
SELECT DISTINCT
a.perID,
a.rxDate,
a.rowID,
a.cntNom,
a.cntOrdre,
STUFF(
(SELECT '; ' + CAST(Quantite AS VARCHAR) + ',' + medNom + ',' + medTemplateFile FROM CTE WHERE perID = a.perID FOR XML PATH('')),
1, 2, '') as Posologie
FROM CTE a) AS x
ORDER BY x.rxDate DESC, CASE WHEN (x.rowID % 2) = 1 THEN x.cntOrdre END, CASE WHEN (x.rowID % 2 = 0) THEN x.cntOrdre END DESC;

works fine now.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial