Probleme de requette

Version imprimable

02/07/2004, 13h37
nmerydem

Probleme de requette

Voila mon probleme :
J'ai une table avec une colonne date.
Je voudrais selectionner toute les lignes, et faire un order by different selon que la date soit passee ou future ?

je ne sais pas si c tres calir, mais je pense..!

en vous remerkiant

Ca, ce serait sympa, mis ce n'est malheureusement pas possible en SQL

Code:

1
2
3
4
5
6
7
select * from MaTable
where Madate>getdate()
order by Madate ASC
union
select * from MaTable
where Madate<=getdate()
order by Madate DESC

Il reste donc l'utilisation de la procedure stockee

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
create proc Mesdates as
begin
create table #tmp
(id numeric(10,0) identity, dte datetime, ...)
 
insert into #tmp
select * from MaTable
where Madate>getdate()
order by Madate ASC
 
insert into #tmp
select * from MaTable
where Madate<=getdate()
order by Madate DESC
 
select * from #tmp
order by ID
 
drop table #tmp
end

02/07/2004, 15h25
nmerydem

merki bien... je vais checker ca..!

ca marche bien, j'ai mon code comme, mais si je veux trier sur un substring, ca veut pas, comment faire :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
CREATE PROCEDURE dbo.DateRequest (@myType Varchar(20))
AS
begin 
create table #tmp 
(id numeric(10, 0)  identity, ContactUniqueID Int, Date_Requested datetime, Type varchar(20), Requested_By varchar(50), Form_Type varchar(10), System_Date datetime, Updated bit, User_Date Datetime, User_Updated bit, Telecom_Updated bit , Equipment_Update bit, tmp varchar(1))
 
insert into #tmp 
select  ContactUniqueID , Date_Requested , Type, Requested_By , Form_Type , System_Date , Updated , User_Date , User_Updated , Telecom_Updated  , Equipment_Updated from Main_Contact_Info_Bis, Substring(Type, 1, 1)
where User_date< getdate() 
and Updated='0' 
and Substring(Form_Type,1,1) <> 'P' 
and @myType <>'1'
order by Substring(Type, 1, 1), User_Date
 
insert into #tmp 
select ContactUniqueID , Date_Requested , Type, Requested_By , Form_Type , System_Date , Updated , User_Date , User_Updated , Telecom_Updated  , Equipment_Updated from Main_Contact_Info_Bis
where User_date >=getdate() 
and Updated='0' 
and Substring(Form_Type,1,1) <> 'P' 
and @myType <>'1'
order by User_Date

je pense que c'est a cause de la denomination de la colonne tmp, alors que dedans je met substring(Type, 1 ,1 )... mais bon, je ne vois pas trop comment fixer ca..!
any idea ?