Help with select - 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)

Help with select

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-01-28 : 11:49:49

[code]
Hi,

I have 2 tables one called MomDetails and the other called MomAppointments.

1. I have to write a query that will return those customers whose cancellationdate is less than 3 days and whose classid =0 or classid=null. That with your much appreciated help I accomplished.

2. However, now I have to make it more complicated and return also in the same query those customers who have an AssignedDate is less than 3 days and whose classid <>0 or classid<>null.

I have included the tables below.

Below is the stored procedure that works for the first part but I'm not sure how to modify it to include the second part of the query.
ALTER PROCEDURE [dbo].[UnAssigned][/
as

SELECT m.id
,infoid
,pregnancyweek
,c.ClassId,
MID,
ClassNumber,
CancellationDate,
isnull (CancellationReason,'') as CancellationReason,
a.CancellationReasonid,
isnull(m.AdditionalInfo,'') as AdditionalInfo
,isnull(CancellationReason2, '') as CancellationReason2,
FirstName + ' ' + FamilyName AS CustomerName,
datediff(day, A.CancellationDate, getdate()) as NumberOfDays,
Telephone
,Cellular
from dbo.MomDetails as M
inner JOIN dbo.MomClassAppointment as A ON M.ID=A.ID and (a.classid=0 or a.classid is null)
and (A.CancellationDate > DATEADD(day, -2, getdate())or A.CancellationDate is null )
left join dbo.Classes as c ON A.Classid=C.CLASSID
left join dbo.ClassStatus as s on s.StatusId=c.StatusId
left JOIN dbo.CancellationReason as CR ON CR.CancellationReasonId = A.CancellationReasonId

order by PregnancyWeek desc
return

CREATE TABLE [dbo].[MomClassAppointment](size=3]

[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])

[size=3]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,
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])

[/code]

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-28 : 13:46:12

well this is your existing filter

(a.classid=0 or a.classid is null)
and (A.CancellationDate > DATEADD(day, -2, getdate())or A.CancellationDate is null )

you just need to add the new criteria using an "or" to get the extra rows.

This might look better as a where clause rather than a join.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-01-29 : 02:02:50

Thanks it worked :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources