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:
KEYCODEDATE
DINO1234501/05/2015
DINO6789001/05/2015
DINO1234502/05/2015
DINO6789002/05/2015
DINO1234503/05/2015
DINO6789003/05/2015
DINO1234504/05/2015
DINO6789004/05/2015
DINO1234505/05/2015
DINO6789006/05/2015
DINO1234507/05/2015
DINO6789008/05/2015
DINO1234509/05/2015
XENI6789001/05/2015
XENI1234501/05/2015
XENI6789002/05/2015
XENI1234502/05/2015
XENI6789003/05/2015
XENI1234503/05/2015
XENI6789004/05/2015
XENI1234505/05/2015
XENI6789006/05/2015
XENI1234507/05/2015
XENI1234508/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]D67890ON D12345.[DATE] = D67890.[DATE]
FROM [DATA]D12345
JOIN [DATA]D67890ON D12345.[DATE] = D67890.[DATE]
WHERE D12345. = '12345'
ANDD67890. = '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