SELECT statement, can't decide on the approach

Question

SELECT statement, can't decide on the approach

dtopicdragovic

Old Hand

Points: 365
More actions
October 11, 2012 at 9:34 am

#265615

Hi!
I have a table of the following structure:
CREATE TABLE [dbo].[Doc_Milestone](
[DocMilestoneID] [int] IDENTITY(1,1) NOT NULL,
[DocID] [int] NOT NULL,
[MilestoneID] [int] NOT NULL,
[MilestoneDate] [date] NULL)
INSERT INTO [dbo].[Doc_Milestone] ([DocID],[MilestoneID],[MilestoneDate]) VALUES (30,10,'2012-10-10')
GO
INSERT INTO [dbo].[Doc_Milestone] ([DocID],[MilestoneID],[MilestoneDate]) VALUES (30,11,'2012-10-12')
GO
INSERT INTO [dbo].[Doc_Milestone] ([DocID],[MilestoneID],[MilestoneDate]) VALUES (30,35,'2012-10-18')
GO
INSERT INTO [dbo].[Doc_Milestone] ([DocID],[MilestoneID],[MilestoneDate]) VALUES (31,10,'2012-09-10')
GO
INSERT INTO [dbo].[Doc_Milestone] ([DocID],[MilestoneID],[MilestoneDate]) VALUES (31,11,'2012-09-12')
GO
INSERT INTO [dbo].[Doc_Milestone] ([DocID],[MilestoneID],[MilestoneDate]) VALUES (31,12,'2012-09-18')
GO
In order to get
DocID, Completed
30 2012-10-18
31 NULL
I tried:
select dm.DocID,
(CASE dm.MilestoneId WHEN 35 Then dm.MilestoneDate ELSE Null END) Completed
from Doc_Milestone dm
But obtained result:
DocIDCompleted
30NULL
30NULL
302012-10-18
31NULL
31NULL
31NULL
I can see why it does what it does, but don't know how to get what I want 🙂
Thanks in adavnce

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Roland Alexander STL SSCrazy Points: 2915 More actions · Answer 1

dtopicdragovic (10/11/2012)
Hi!
I have a table of the following structure:
CREATE TABLE [dbo].[Doc_Milestone](
[DocMilestoneID] [int] IDENTITY(1,1) NOT NULL,
[DocID] [int] NOT NULL,
[MilestoneID] [int] NOT NULL,
[MilestoneDate] [date] NULL)
INSERT INTO [dbo].[Doc_Milestone] ([DocID],[MilestoneID],[MilestoneDate]) VALUES (30,10,'2012-10-10')
GO
INSERT INTO [dbo].[Doc_Milestone] ([DocID],[MilestoneID],[MilestoneDate]) VALUES (30,11,'2012-10-12')
GO
INSERT INTO [dbo].[Doc_Milestone] ([DocID],[MilestoneID],[MilestoneDate]) VALUES (30,35,'2012-10-18')
GO
INSERT INTO [dbo].[Doc_Milestone] ([DocID],[MilestoneID],[MilestoneDate]) VALUES (31,10,'2012-09-10')
GO
INSERT INTO [dbo].[Doc_Milestone] ([DocID],[MilestoneID],[MilestoneDate]) VALUES (31,11,'2012-09-12')
GO
INSERT INTO [dbo].[Doc_Milestone] ([DocID],[MilestoneID],[MilestoneDate]) VALUES (31,12,'2012-09-18')
GO
In order to get
DocID, Completed
30 2012-10-18
31 NULL
I tried:
select dm.DocID,
(CASE dm.MilestoneId WHEN 35 Then dm.MilestoneDate ELSE Null END) Completed
from Doc_Milestone dm
But obtained result:
DocIDCompleted
30NULL
30NULL
302012-10-18
31NULL
31NULL
31NULL
I can see why it does what it does, but don't know how to get what I want 🙂
Thanks in adavnce

Try:

select

DocID,

case when max(MilestoneID) = 35 then max(MilestoneDate) else NULL end as Completed

from

dbo.Doc_Milestone

group by

DocID

Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

dtopicdragovic Old Hand Points: 365 More actions · Answer 2

Thanks for help

seems to be working.

How would you do it in the following case:

If MilestoneID = 35 Then

MilestoneDate

Else

getData()

So in our case result should be:

DocIDLatestDate

302012-10-18

312012-10-11

Thanks

Roland Alexander STL SSCrazy Points: 2915 More actions · Answer 3

dtopicdragovic (10/11/2012)
Thanks for help
seems to be working.
How would you do it in the following case:
If MilestoneID = 35 Then
MilestoneDate
Else
getData()
So in our case result should be:
DocIDLatestDate
302012-10-18
312012-10-11
Thanks

Replace NULL with GETDATE() in the CASE statement

Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

dtopicdragovic Old Hand Points: 365 More actions · Answer 4

dtopicdragovic

Old Hand

Points: 365

October 11, 2012 at 12:29 pm

#1548312

🙂 Should have known !!!

Thanks

dtopicdragovic Old Hand Points: 365 More actions · Answer 5

Thanks for the reply!

There is a primary key in my table (DocMilestoneID) which I omitted to include in the code I gave you 🙁

Sorry about that.

I will try your code.

Thanks

dtopicdragovic Old Hand Points: 365 More actions · Answer 6

This code works great.

I would elaborate on my question if you don't mind.

Basicly what I want is

if MilestoneID = 10 or MilestoneID = 11 and not empty select more recent date out of two

else

select MIN (latest) Milestonedate out of any MilestoneID that exist for particular DocID.

I hope you understand what I am trying to do. Please let me know if you need more info.

Thanks

dtopicdragovic Old Hand Points: 365 More actions · Answer 7

I think I should be more clear on what I need 🙂

Here is the set of data from which I have to select

DocID MilestoneDate MilestoneID

302001-10-0110

302001-10-1511

302002-11-0613

302004-06-0122

302004-06-1523

302003-11-0127

302004-03-0128

302003-11-0130

302004-03-0131

302004-06-1535

312003-06-2713

312005-08-3123

312004-05-0527

312004-05-0530

312005-07-2035

361997-03-0123

361996-10-0127

361996-12-0128

361996-10-0130

361996-12-0131

361997-03-1135

372001-06-3010

372002-09-0311

372002-05-0127

372002-07-0128

372002-05-0130

372002-07-0131

372002-09-0335

What I need is somehow to get the following

Column EarliestDate =

If MilestoneID 10 0r 11 not empty give me more recent (max) MilestoneDate

else

give me the earliest date out of any exisitng MilestoneIDs for DocID (in case of Docid = 30 MilestonesID would be 13, 22, 23 ... 35)

so result would be something like:

DocID EarliestDate

302001-10-15

312003-06-27

361996-10-01

372002-09-03

Thanks