June 1, 2017 at 6:33 am
I have a table like this...
ID Type Date
1 Initial 1/5/15
1 Periodic 3/5/15
2 Initial 2/5/15
3 Initial 1/10/15
3 Periodic 3/6/15
4
5 Initial 3/8/15
6
6 Periodic 4/5/15
I need to get all of the ID numbers that are "Periodic" or NULL (only NULL values if there is no periodic) and corresponding date. So I want a to get query results that looks like this...
ID Type Date
1 Periodic 3/5/15
3 Periodic 3/6/15
4
6 Periodic 4/5/15
I've tried a few things, but I'm stuck...
June 1, 2017 at 6:38 am
Not exactly sure what your (homework?) question is? Show records that have NULL or 'Periodic' in the type field?
What have you tried?
Take a look at ISNULL() and COALESCE() in books onlne
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql
SELECT Id, Type, Date
FROM dbo.YourTable
WHERE COALESCE(Type, 'Periodic') = 'Periodic'
ORDER BY Id;
I hope this helps,
Rob
June 1, 2017 at 7:12 am
I've tried
select id, type, date1from Table1 as t
where (t.type Is Null) or (t.type = "periodic");
But then I get...
ID Type Date
1 Periodic 3/5/15
3 Periodic 3/6/15
4
6
6 Periodic 4/5/15
I don't want to get that "6 Null"
So I tried this...
SELECT id, type, date1
from Table1 as t
where (t.type Is Null) or (t.type = "") or (t.type = "periodic")
Group by id, type, date1;
But then I get the same thing as above...
ID Type Date
1 Periodic 3/5/15
3 Periodic 3/6/15
4
6
6 Periodic 4/5/15
June 1, 2017 at 7:35 am
So I'm getting closer...
SELECT t.id, Max(t.type) AS MaxOftype, Max(t.date1) AS MaxOfdate1
FROM Table1 AS t
WHERE (((t.type) Is Null Or (t.type)="" Or (t.type)="periodic"))
GROUP BY t.id;
I get...
ID Type Date
1 Periodic 3/5/15
3 Periodic 3/6/15
4
6 Periodic 4/5/15
However, there are some table entries that have no type but have a date such as "6 5/1/17" as shown below...
ID Type Date
1 Initial 1/5/15
1 Periodic 3/5/15
2 Initial 2/5/15
3 Initial 1/10/15
3 Periodic 3/6/15
4
5 Initial 3/8/15
6
6 Periodic 4/5/15
6 5/1/17
When I run the above query, I get ....
ID Type Date
1 Periodic 3/5/15
3 Periodic 3/6/15
4
6 Periodic 5/1/17
The date on "6 Periodic" should be "4/5/15"
June 1, 2017 at 8:43 am
The problem with your last query is each MAX() function is evaluated separately over the GROUP. To really solve this, we'd need to understand your data more, is it possible for an ID to have multiple rows with Type = 'Periodic'? What might make more sense to ensure you grab the date for the specific record in the group you want, is to rank the rows within a group based on your criteria, so maybe something like:
WITH ranked AS
(SELECT id, [Type], Date1, ROW_NUMBER() OVER (PARTITION BY id ORDER BY [Type] DESC, Date1 DESC) AS ranking
FROM #Table1
WHERE ([Type] IS NULL OR [Type] = 'Periodic'))
SELECT id, [Type], Date1
FROM ranked
WHERE ranking = 1;
https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql
June 1, 2017 at 9:44 am
Thanks for your help! This is a lot more complicated that I had anticipated.... I'm exploring the ranking...
The periodic can only have 1 date....
The type can have "Initial", "Periodic", or be blank.
There can be an entry such as...
ID Type Date
"6 blank 5/1/17" or
"6 blank blank"
So the table looks like...
ID Type Date
1 Initial 1/5/15
1 Periodic 3/5/15
2 Initial 2/5/15
3 Initial 1/10/15
3 Periodic 3/6/15
4
5 Initial 3/8/15
6
6 Periodic 4/5/15
6 5/1/17
I want to get ....
ID Type Date
1 Periodic 3/5/15
3 Periodic 3/6/15
4
6 Periodic 4/5/15
June 2, 2017 at 9:14 am
Try this:
DECLARE @Table1 AS TABLE (
ID int,
[Type] varchar(20),
Date1 date
);
INSERT INTO @Table1 (ID, [Type], Date1)
SELECT 1, 'Initial', '01/05/2015' UNION ALL
SELECT 1, 'Periodic', '03/05/2015' UNION ALL
SELECT 2, 'Initial', '02/05/2015' UNION ALL
SELECT 3, 'Initial', '01/10/2015' UNION ALL
SELECT 3, 'Periodic', '03/06/2015' UNION ALL
SELECT 4, NULL, NULL UNION ALL
SELECT 5, 'Initial', '03/08/2015' UNION ALL
SELECT 6, NULL, NULL UNION ALL
SELECT 6, 'Periodic', '04/05/2015' UNION ALL
SELECT 6, NULL, '05/01/2017';
SELECT M.ID, NULLIF(M.[Type], '') AS [Type], T.Date1
FROM (
SELECT ID, MAX(ISNULL([Type], '')) AS [Type]
FROM @Table1
GROUP BY ID
HAVING MAX(ISNULL([Type], '')) IN ('', 'Periodic')
) AS M
INNER JOIN @Table1 AS T
ON M.ID = T.ID
AND M.[Type] = ISNULL(T.[Type], '')
ORDER BY M.ID;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 2, 2017 at 9:38 am
SELECT t.*
FROM @Table1 t
WHERE [Type] = 'Periodic'
OR
([Type] IS NULL AND NOT EXISTS (
SELECT 1
FROM @Table1 ti
WHERE ti.ID = t.ID
AND ti.[Type] IS NOT NULL))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply