October 22, 2014 at 12:50 pm
Good Evening All,
I am having serious TSQL brain freeze today.
I have a table of data example:
KEY CODE DATE
DINO 12345 01/05/2015
DINO 67890 01/05/2015
DINO 12345 02/05/2015
DINO 67890 02/05/2015
DINO 12345 03/05/2015
DINO 67890 03/05/2015
DINO 12345 04/05/2015
DINO 67890 04/05/2015
DINO 12345 05/05/2015
DINO 67890 06/05/2015
DINO 12345 07/05/2015
DINO 67890 08/05/2015
DINO 12345 09/05/2015
XENI 67890 01/05/2015
XENI 12345 01/05/2015
XENI 67890 02/05/2015
XENI 12345 02/05/2015
XENI 67890 03/05/2015
XENI 12345 03/05/2015
XENI 67890 04/05/2015
XENI 12345 05/05/2015
XENI 67890 06/05/2015
XENI 12345 07/05/2015
XENI 12345 08/05/2015
All I want to do is select data where CODE = '12345' AND CODE = '67890'
So basically I want retrieve and group KEY WHERE CODE = '12345' AND CODE = '67890' on the same DATE.
I was writing code like this e.g.:
SELECT
,
,[DATE]
FROM [DINO].[dbo].[DATA]
WHERE ( = '12345' AND = '67890')
Obviously this doesnt work, I havent grouped it here either as I wanted to check the results.
Can anyone tell me how to achieve this as its really annoying me now.
Many thanks in advance
😀
October 22, 2014 at 1:05 pm
Does this get you close? I assumed that KEY and PMI were supposed to be the same field.
- Adam
;with [DATA] as
(
select 'DINO' as [PMI], '12345' as , '01/05/2015' as [DATE]
union all select 'DINO' ,'67890', '01/05/2015'
union all select 'DINO' ,'12345', '02/05/2015'
union all select 'DINO' ,'67890', '02/05/2015'
union all select 'DINO' ,'12345', '03/05/2015'
union all select 'DINO' ,'67890', '03/05/2015'
union all select 'DINO' ,'12345', '04/05/2015'
union all select 'DINO' ,'67890', '04/05/2015'
union all select 'DINO' ,'12345', '05/05/2015'
union all select 'DINO' ,'67890', '06/05/2015'
union all select 'DINO' ,'12345', '07/05/2015'
union all select 'DINO' ,'67890', '08/05/2015'
union all select 'DINO' ,'12345', '09/05/2015'
union all select 'XENI' ,'67890', '01/05/2015'
union all select 'XENI' ,'12345', '01/05/2015'
union all select 'XENI' ,'67890', '02/05/2015'
union all select 'XENI' ,'12345', '02/05/2015'
union all select 'XENI' ,'67890', '03/05/2015'
union all select 'XENI' ,'12345', '03/05/2015'
union all select 'XENI' ,'67890', '04/05/2015'
union all select 'XENI' ,'12345', '05/05/2015'
union all select 'XENI' ,'67890', '06/05/2015'
union all select 'XENI' ,'12345', '07/05/2015'
union all select 'XENI' ,'12345', '08/05/2015'
)
SELECT D12345.[PMI]
,D12345.
,D67890.[PMI]
,D67890.
,D12345.[DATE]
-- FROM [DINO].[dbo].[DATA] D12345
-- JOIN [DINO].[dbo].[DATA] D67890 ON D12345.[DATE] = D67890.[DATE]
FROM [DATA] D12345
JOIN [DATA] D67890 ON D12345.[DATE] = D67890.[DATE]
WHERE D12345. = '12345'
AND D67890. = '67890'
October 22, 2014 at 1:20 pm
TSQL_Newbie (10/22/2014)
WHERE (= '12345' AND= '67890')
You'll always get 0 rows returned with that...seems like you are implying an OR or IN operator. The column value can't equal 12345 and 67890 at the same time, so it will always evaluate false.
I may be misunderstanding what you are trying to do, but it sounds like you actually are grouping by date (maybe also PMI?) and you only want dates where both 12345 and 67890 records are returned. You might try this:
SELECT PMI, DATE
FROM DATA
WHERE CODE IN ('12345', '67890')
GROUP BY PMI, DATE
HAVING COUNT(*) > 1
October 22, 2014 at 1:49 pm
Maybe something like this:
SELECT
,[DATE]
FROM [dbo].[DATA]
WHERE ( = '12345' OR = '67890')
GROUP BY , [DATE]
HAVING COUNT( DISTINCT code) > 1
Or like this:
SELECT
,[DATE]
FROM [dbo].[DATA]
WHERE = '12345'
INTERSECT
SELECT
,[DATE]
FROM [dbo].[DATA]
WHERE = '67890'
October 22, 2014 at 2:10 pm
Thank you everyone for your suggestions.
Definitely have helped me.
I will try out a few of these suggestions when I get into work tomorrow.
It will be part of a bigger query so it will be a sub query within a CASE statement.
I'll let you know who the winner is tomorrow 🙂
Again, cheers everyone.
October 24, 2014 at 10:14 am
It sounds to me like you're looking for cases where you have rows with both values for that given date? Here's how I would do that:
SELECT
,[DATE]
FROM [DINO].[dbo].[DATA] a
WHERE = '12345'
AND EXISTS (
SELECT 1
FROM [DINO].[dbo].[DATA] b
WHERE a. = b.
AND a.[DATE] = b.[DATE]
AND b. = '67890')
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply