order by/group 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

SQL Server 2005 Forums

Transact-SQL (2005)

order by/group by

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-02-01 : 02:31:41

Hi,

I have a stored procedure (below).

I need to sort the result set in the following way:

1.Those customers that have AssignedDate on top
2.Those with no AssignedDate or CancellationDate
3.Those with CancellationDate
It gets more complicate: Those customers mentioned in point 2 above have to be further sorted according to their pregnancyweek. Although it'll also be nice if all the points be further sorted according to their pregnancyweek.

Can this be done?

Thanks

SELECT
m.id
,infoid
,pregnancyweek
,c.ClassId,
MID
,ClassNumber
,CancellationDate
,FirstName + ' ' + FamilyName AS CustomerName,
,AssignedDate
from dbo.MomDetails as M
inner
JOIN dbo.MomClassAppointment as A ON M.ID=A.ID
where
((a.classid=0 or a.classid is null)
and
(A.CancellationDate > DATEADD(day, -2, getdate())or A.CancellationDate is null ))
or
((A.AssignedDate> DATEADD(day, -2, getdate())) and (a.classid=@classid ))

____________________________________
USE [ChildbirthClasses]
GO
/****** Object: Table [dbo].[MomClassAppointment] Script Date: 02/01/2007 09:30:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MomClassAppointment](
[InfoId] [int] IDENTITY(1,1) NOT NULL,
[id] [int] NOT NULL,
[Classid] [int] NULL,
[CancellationDate] [datetime] NULL,
[CancellationReasonId] [int] NULL,
[CancellationReason2] [nvarchar](600) COLLATE Latin1_General_CI_AS NULL,
[isCancel] [int] NULL,
[AdditionalInfo] [nvarchar](500) COLLATE Latin1_General_CI_AS NULL,
[AssignedDate] [datetime] NULL,
CONSTRAINT [PK_MomClassAppointment] PRIMARY KEY CLUSTERED
(
[InfoId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [ChildbirthClasses]
GO
ALTER TABLE [dbo].[MomClassAppointment] WITH CHECK ADD CONSTRAINT [FK_MomClassAppointment_CancellationReason] FOREIGN KEY([CancellationReasonId])
REFERENCES [dbo].[CancellationReason] ([CancellationReasonId])
GO
ALTER TABLE [dbo].[MomClassAppointment] WITH CHECK ADD CONSTRAINT [FK_MomClassAppointment_MomClassAppointment] FOREIGN KEY([Classid])
REFERENCES [dbo].[Classes] ([ClassId])
GO
ALTER TABLE [dbo].[MomClassAppointment] WITH CHECK ADD CONSTRAINT [FK_MomClassAppointment_MomDetails] FOREIGN KEY([id])
REFERENCES [dbo].[MomDetails] ([id])
_______________-
USE [ChildbirthClasses]
GO
/****** Object: Table [dbo].[MomDetails] Script Date: 02/01/2007 09:31:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MomDetails](
[id] [int] IDENTITY(1,1) NOT NULL,
[mid] [nvarchar](9) COLLATE Latin1_General_CI_AS NOT NULL,
[FamilyName] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[FirstName] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[BirthDate] [datetime] NULL,
[Cityid] [smallint] NULL,
[Street] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL,
[Flat] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[House] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Mikud] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Telephone] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Cellular] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[PartnerName] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[LastMenstrualDate] [datetime] NULL,
[TimesParticipated] [int] NULL,
[PregnancyWeek] [int] NULL,
[Comment2] [nvarchar](500) COLLATE Latin1_General_CI_AS NULL,
[CommentId] [int] NULL,
[DatePhoned] [datetime] NULL,
[AdditionalInfo] [nvarchar](500) COLLATE Latin1_General_CI_AS NULL,
[PartnerPhone1] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[PartnerPhone2] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[IsDelete] [int] NULL,
[StatusId] [int] NULL,
CONSTRAINT [PK_MomDetails_1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'mom''s identification number' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MomDetails', @level2type=N'COLUMN', @level2name=N'mid'

GO
USE [ChildbirthClasses]
GO
ALTER TABLE [dbo].[MomDetails] WITH CHECK ADD CONSTRAINT [FK_MomDetails_Comments] FOREIGN KEY([CommentId])
REFERENCES [dbo].[Comments] ([CommentId])
GO
ALTER TABLE [dbo].[MomDetails] WITH CHECK ADD CONSTRAINT [FK_MomDetails_MomDetails] FOREIGN KEY([id])
REFERENCES [dbo].[MomDetails] ([id])
GO
ALTER TABLE [dbo].[MomDetails] WITH CHECK ADD CONSTRAINT [FK_MomDetails_MomStatus] FOREIGN KEY([StatusId])
REFERENCES [dbo].[MomStatus] ([StatusId])

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-01 : 02:38:00

try this


order by
case 	when AssignedDate is not null then 0
	when AssignedDate is null or CancellationDate is null then 1
	when CancellationDate is not null then 2
	end,
	pregnancyweek desc

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-02-01 : 03:05:32

Thanks. It works great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources