RIAS
asked on
Sort by on two colums
Hello,
I have to columns in a table
Date : Data type is Datetime
Time: Data type is Datetime.
need to sort it by Date and time in asc manner
Cheers
I have to columns in a table
Date : Data type is Datetime
Time: Data type is Datetime.
need to sort it by Date and time in asc manner
Cheers
ASKER
Hello Vitor,
I tried that and it didn't work.
Please find my Sp below:
I tried that and it didn't work.
Please find my Sp below:
ALTER PROCEDURE [dbo].[MCAL_Select_Diary]
@RequestedBy Nvarchar(255)=null, @AuthBy Nvarchar(50)=null, @Date1 VARCHAR(50) =null, @Date2 VARCHAR(50) =null,@Time VARCHAR(20) = NULL ,@PickUpTime VARCHAR(20) = NULL ,@PatientName Nvarchar(MAX)=null,
@Ref Nvarchar(50)=null ,@Auth Nvarchar(50)=null,@PickUpAddress Nvarchar(50)=null,@Address Nvarchar(50)=null ,@Comments Nvarchar(MAX)=null,
@Interpreter Nvarchar(50)=null,@CarRequest Nvarchar(50)=null,@SpecialInstructions Nvarchar(MAX)=null ,@FaxAuth Nvarchar(50)=null ,
@MedicalReportRecieved Nvarchar(50)=null,@EnteredBy Nvarchar(MAX)=null ,@Attachments Nvarchar(50) =null ,
@Title Nvarchar(10)= null ,@FaxNumber Nvarchar(50)=null ,@Attn Nvarchar(50)=null,@DoctorClinic Nvarchar(50)=null,@Year_Ref1 VARCHAR(8) =null,
@Year_Ref2 VARCHAR(8) =null,@DIARY_UID Nvarchar(MAX)=null,@Contact Nvarchar(50)= NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'SELECT EnteredBy,RequestedBy,AuthBy,Date,Time,PickUpTime,Title,PatientName,Contact,Ref ,Auth,PickUpAddress,DoctorClinic,Attn,Address,Comments,Interpreter,CarRequest,
FaxAuth,FaxNumber,SpecialInstructions,MedicalReportRecieved,DIARY_UID
FROM DIARY WHERE ( DeleteRecord is null or DeleteRecord = 0) '
--IF @Date <> ''
-- SET @SQL = @SQL + ' AND DATE = ''' + CAST(@Date AS VARCHAR(20)) + ''''
IF @Date1 <> '' AND @Date2 <> ''
SET @SQL =@SQL+ ' AND Date >= ' + ''''+ CAST(@Date1 AS VARCHAR(20)) + '''' + ' and Date <= ' + ''''+ CAST(@Date2 AS VARCHAR(20)) +''''
ELSE IF @Date1 <> ''
SET @SQL = @SQL+ ' AND Date >= '+ '''' + CAST(@Date1 AS VARCHAR(20)) +''''
Else IF @Date2 <> ''
SET @SQL = @SQL+ ' AND Date <= '+'''' + CAST(@Date2 AS VARCHAR(20))+''''
IF @PickUpTime <> ''
SET @SQL = @SQL + ' AND CAST(PickUpTime AS Time) = ''' + @PickUpTime + ''''
IF @PatientName <> ''
SET @SQL = @SQL + ' AND PatientName LIKE ''%' + CAST(@PatientName AS Nvarchar(MAX)) + '%'''
IF @PickUpAddress <> ''
SET @SQL = @SQL + ' AND PickUpAddress LIKE ''%'+ CAST(@PickUpAddress AS Nvarchar(50)) + '%'''
IF @AuthBy <> ''
SET @SQL = @SQL + ' AND AuthBy = ''' + CAST(@AuthBy AS NVARCHAR(50))+ ''''
IF @Contact <> ''
SET @SQL = @SQL + ' AND Contact = ''' + CAST(@Contact AS Nvarchar(50)) + ''''
IF @Time <> ''
SET @SQL = @SQL + ' AND CAST(Time AS Time) = ''' + @Time + ''''
IF @Ref <> ''
SET @SQL = @SQL + ' AND Ref = ''' + CAST(@Ref AS Nvarchar(50)) + ''''
IF @Auth <> ''
SET @SQL = @SQL + ' AND Auth LIKE ''%'+ CAST(@Auth AS Nvarchar(50)) + '%'''
IF @RequestedBy <> ''
SET @SQL = @SQL + ' AND RequestedBy = ''' + CAST(@RequestedBy AS NVARCHAR(50))+ ''''
IF @DoctorClinic <> ''
SET @SQL = @SQL + ' AND DoctorClinic LIKE ''%'+ CAST(@DoctorClinic AS NVARCHAR(50)) + '%'''
IF @Address <> ''
SET @SQL = @SQL + ' AND Address LIKE ''%'+ CAST(@Address AS NVARCHAR(50)) + '%'''
IF @Comments <> ''
SET @SQL = @SQL + ' AND Comments LIKE ''%'+ CAST(@Comments AS NVARCHAR(max)) + '%'''
IF @Interpreter <> ''
SET @SQL = @SQL + ' AND Interpreter LIKE ''%'+ CAST(@Interpreter AS NVARCHAR(50)) + '%'''
IF @CarRequest <> ''
SET @SQL = @SQL + ' AND CarRequest LIKE ''%'+ CAST(@CarRequest AS NVARCHAR(50)) + '%'''
IF @SpecialInstructions <> ''
SET @SQL = @SQL + ' AND SpecialInstructions LIKE ''%'+ CAST(@SpecialInstructions AS NVARCHAR(max)) + '%'''
IF @FaxAuth <> ''
SET @SQL = @SQL + ' AND FaxAuth = ''' + CAST(@FaxAuth AS Nvarchar(50)) + ''''
IF @MedicalReportRecieved <> ''
SET @SQL = @SQL + ' AND MedicalReportRecieved LIKE ''%'+ CAST(@MedicalReportRecieved AS NVARCHAR(50)) + '%'''
IF @EnteredBy <> ''
SET @SQL = @SQL + ' AND EnteredBy LIKE ''%'+ CAST(@EnteredBy AS NVARCHAR(max)) + '%'''
IF @Title <> ''
SET @SQL = @SQL + ' AND Title LIKE ''%'+ CAST(@Title AS NVARCHAR(10)) + '%'''
IF @FaxNumber <> ''
SET @SQL = @SQL + ' AND FaxNumber LIKE ''%'+ CAST(@FaxNumber AS NVARCHAR(50)) + '%'''
IF @Attn <> ''
SET @SQL = @SQL + ' AND Attn LIKE ''%'+ CAST(@Attn AS NVARCHAR(50)) + '%'''
IF @Year_Ref1 <> '' AND @Year_Ref2 <> ''
SET @SQL =@SQL+ ' AND Year_Ref >= ' + CAST(@Year_Ref1 AS VARCHAR(8)) + 'and Year_Ref <= ' + CAST(@Year_Ref2 AS VARCHAR(8))
ELSE IF @Year_Ref1 <> ''
SET @SQL = @SQL+ ' AND Year_Ref >= ' + CAST(@Year_Ref1 AS VARCHAR(8))
Else IF @Year_Ref2 <> ''
SET @SQL = @SQL+ ' AND Year_Ref <= ' + CAST(@Year_Ref2 AS VARCHAR(8))
---- A period of time --> WHERE date >= @date1 and date <= @date2 (you will to receive two parameters for the date period (min and max dates).
----Before than ---> WHERE date < @date
----Later than --> WHERE date > @date
SET @SQL = @SQL + 'ORDER BY Date'
--Print @SQL
END
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Vitor,
Please refer https://www.experts-exchange.com/questions/29020396/Sql-Query.html
You helped me with in the past.
But,Still not fixed.
Please refer https://www.experts-exchange.com/questions/29020396/Sql-Query.html
You helped me with in the past.
But,Still not fixed.
Oh, just now I've realized that they were datetime columns.
By the way, if Date is datetime why do you need a Time column? You could store the time in the Date column and avoid having an extra column.
By the way, if Date is datetime why do you need a Time column? You could store the time in the Date column and avoid having an extra column.
ASKER
Vitor,
Thats the design of the table and I am not authorised to change it.
Cheers!
Thats the design of the table and I am not authorised to change it.
Cheers!
At least report it :)
ASKER
Sure Sir!
>Please refer https://www.experts-exchange.com/questions/29020396/Sql-Query.html
RIAS - Do us a favor and include all relevant details in the question, as opposed to a 'go fish through this link and find what I'm asking'. Thanks in advance.
>Thats the design of the table and I am not authorised to change it.
In that case it might not be a bad idea to create a view that consumes both date(datetime) and time(datetime) columns and returns a single datetime, and then use the view in your coding. Having two datetime columns this way is just begging for errors, and I'm having a vague memory of being in that same situation with an airline client working on a database that was 30+ years old and could not be modified.
RIAS - Do us a favor and include all relevant details in the question, as opposed to a 'go fish through this link and find what I'm asking'. Thanks in advance.
>Thats the design of the table and I am not authorised to change it.
In that case it might not be a bad idea to create a view that consumes both date(datetime) and time(datetime) columns and returns a single datetime, and then use the view in your coding. Having two datetime columns this way is just begging for errors, and I'm having a vague memory of being in that same situation with an airline client working on a database that was 30+ years old and could not be modified.
ASKER
Jim,
I have given all information in proceeding post.
Cheers
I have given all information in proceeding post.
Cheers
ASKER
Cheers!
Open in new window