Need help sorting based on 2 different column DateTime
-
I've this table and data as following, CreatedDate CANNOT NULL. LatestRepliedDate can NULL
CREATE TABLE [dbo].[IncidentMasterSimulation](
[Id] [int] NOT NULL,
[IncidentDate] [datetime] NOT NULL,
[IncidentDateDisplay] [nvarchar](100) NOT NULL,
[HowLong] [nvarchar](100) NOT NULL,
[IncidentNo] [nvarchar](100) NOT NULL,
[ProjectName] [nvarchar](100) NOT NULL,
[Categories] [nvarchar](100) NOT NULL,
[PriorityLevel] [nvarchar](100) NOT NULL,
[IncidentDescription] [nvarchar](100) NOT NULL,
[CreatedBy] [nvarchar](100) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedDateDisplay] [nvarchar](100) NOT NULL,
[IncidentStatusLUId] [int] NOT NULL,
[LatestRepliedBy] [nvarchar](100) NULL,
[LatestRepliedDate] [datetime] NULL,
[LatestRepliedDateDisplay] [nvarchar](100) NULL,
[LatestIncidentStatusLUId] [int] NULL,
[IncidentStatus] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_IncidentMasterSimulation] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[IncidentMasterSimulation] ([Id], [IncidentDate], [IncidentDateDisplay], [HowLong], [IncidentNo], [ProjectName], [Categories], [PriorityLevel], [IncidentDescription], [CreatedBy], [CreatedDate], [CreatedDateDisplay], [IncidentStatusLUId], [LatestRepliedBy], [LatestRepliedDate], [LatestRepliedDateDisplay], [LatestIncidentStatusLUId], [IncidentStatus]) VALUES (2, CAST(N'2021-08-03T01:30:00.000' AS DateTime), N'03/08/2021 1:30 AM', N'28 Days, 15 Hours, 4 Minutes ago', N'ISPDHLPDK/202108/00001', N'Isianpadu Helpdesk', N'Hardware', N'High', N'descript', N'Jai Achong', CAST(N'2021-08-03T02:30:00.000' AS DateTime), N'03/08/2021 2:30 AM', 1, N'Sharul Punya', CAST(N'2021-08-06T02:32:07.377' AS DateTime), N'06/08/2021 2:32 AM', 5, N'CLOSED')
GO
INSERT [dbo].[IncidentMasterSimulation] ([Id], [IncidentDate], [IncidentDateDisplay], [HowLong], [IncidentNo], [ProjectName], [Categories], [PriorityLevel], [IncidentDescription], [CreatedBy], [CreatedDate], [CreatedDateDisplay], [IncidentStatusLUId], [LatestRepliedBy], [LatestRepliedDate], [LatestRepliedDateDisplay], [LatestIncidentStatusLUId], [IncidentStatus]) VALUES (3, CAST(N'2021-08-03T01:30:00.000' AS DateTime), N'03/08/2021 1:30 AM', N'28 Days, 15 Hours, 4 Minutes ago', N'ISPDHLPDK/202108/00002', N'Isianpadu Helpdesk', N'Software', N'High', N'tak boleh lgin', N'Jai Achong', CAST(N'2021-08-09T08:13:00.000' AS DateTime), N'09/08/2021 8:13 AM', 1, N'Maznilawati Ahmad', CAST(N'2021-08-10T15:00:23.000' AS DateTime), N'10/08/2021 3:00 PM', 2, N'OPEN')
GO
INSERT [dbo].[IncidentMasterSimulation] ([Id], [IncidentDate], [IncidentDateDisplay], [HowLong], [IncidentNo], [ProjectName], [Categories], [PriorityLevel], [IncidentDescription], [CreatedBy], [CreatedDate], [CreatedDateDisplay], [IncidentStatusLUId], [LatestRepliedBy], [LatestRepliedDate], [LatestRepliedDateDisplay], [LatestIncidentStatusLUId], [IncidentStatus]) VALUES (4, CAST(N'2021-08-02T07:00:00.000' AS DateTime), N'02/08/2021 7:00 AM', N'29 Days, 9 Hours, 34 Minutes ago', N'ISPDHLPDK/202108/00003', N'Isianpadu Helpdesk', N'Software', N'High', N'graph tak kelua', N'Sharul Nizam', CAST(N'2021-08-11T03:13:00.000' AS DateTime), N'11/08/2021 3:13 AM', 1, NULL, NULL, NULL, NULL, N'OPEN')
GO
INSERT [dbo].[IncidentMasterSimulation] ([Id], [IncidentDate], [IncidentDateDisplay], [HowLong], [IncidentNo], [ProjectName], [Categories], [PriorityLevel], [IncidentDescription], [CreatedBy], [CreatedDate], [CreatedDateDisplay], [IncidentStatusLUId], [LatestRepliedBy], [LatestRepliedDate], [LatestRepliedDateDisplay], [LatestIncidentStatusLUId], [IncidentStatus]) VALUES (5, CAST(N'2021-08-03T03:00:00.000' AS DateTime), N'03/08/2021 3:00 AM', N'28 Days, 13 Hours, 34 Minutes ago', N'ISPDHLPDK/202108/00004', N'Isianpadu Helpdesk', N'Software', N'High', N'graph tak kelua', N'Salmi Aman', CAST(N'2021-08-12T18:49:00.000' AS DateTime), N'12/08/2021 6:49 PM', 1, N'Abang Kucing', CAST(N'2021-08-13T14:30:00.000' AS DateTime), N'13/08/2021 2:30 PM', 3, N'OPEN')
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IncidentMasterSimulation_UQ001] Script Date: 31/8/2021 5:44:39 PM ******/ALTER TABLE [dbo].[IncidentMasterSimulation] ADD CONSTRAINT [IncidentMasterSimulation_UQ001] UNIQUE NONCLUSTERED
(
[IncidentNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GOResultset must choose LatestRepliedDate first for DateTime Sorting. If LatestRepliedDate is NULL, default value is CreatedDate for DateTime Sorting
Expected Output as folllowing,
Id | IncidentNo | CreatedDate | LatestRepliedDate
---------------------------------------------------------------------------------
5 | ISPDHLPDK/202108/00004 |2021-08-12 18:49:00.000 | 2021-08-13 14:30:00.000
4 | ISPDHLPDK/202108/00003 |2021-08-11 03:13:00.000 | NULL
3 | ISPDHLPDK/202108/00002 |2021-08-09 08:13:00.000 | 2021-08-10 15:00:23.000
2 | ISPDHLPDK/202108/00001 |2021-08-03 02:30:00.000 | 2021-08-06 02:32:07.377Please help
- This topic was modified 3 years, 2 months ago by Adelia.
-
maybe ?
SELECT
Id,
IncidentNo,
CreatedDate,
LatestRepliedDate
FROM IncidentMasterSimulation
ORDER BY ISNULL(LatestRepliedDate, CreatedDate) DESC________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
It's perfect
-
Function coalesce() should also do the job. In this example, I assumed that order of using dates for sorting is LastReplied, IncidentDate, CreatedDate. I also reordered columns to my liking - nothing wrong with what you did. Finally, there is a new column, SortDate, calculated the same way as ORDER clause - it is useful to see how sort looks like, for you, not for the boss.
SELECT
Id,
IncidentNo,
LatestRepliedDate,
IncidentDate,
CreatedDate,
SortDate = coalesce(LatestRepliedDate, IncidentDate, CreatedDate)
FROM IncidentMasterSimulation
ORDER BY coalesce(LatestRepliedDate, IncidentDate, CreatedDate) DESC
;Also, tale like this would benefit if a few additional constraints are provided:
ALTER TABLE IncidentMasterSimulation WITH CHECK
ADD CONSTRAINT ck_IncidentMasterSimulation_order_of_dates
CHECK (CreatedDate>=IncidentDAte AND LatestRepliedDate>=CreatedDate)
GO
Commands completed successfully.
Completion time: 2021-08-31T15:48:28.0440741-04:00Cheers,
Zidar
Zidar's Theorem: The best code is no code at all...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply