my first one - 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

General SQL Server Forums

New to SQL Server Programming

my first one

Author

Topic

pjnovak007
Starting Member

19 Posts

Posted - 2010-04-08 : 07:50:26

Ok this is my first question. I am a new DB person who really needs help.

I have 2 tables.

Respondents table
fields: respondentID
Password = is not null (means registered users)
createdate

Responses table
fields: respondentID
source
createdate

tidbits
A respondent can have many responses.
First i need to get the earliest date a response came in for a respondentid.

Hopefully i am not confusing you.

This give me the date i need. But i need to add the source field.

(select respondentId, Min(createDate) from responses
group by respondentId)

how do I do that? then i will post my next question so i dont confuse.

Patrick

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-08 : 07:57:12

Which SQL Server version are you using?

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 08:01:11

[code]SELECT r.*,rs.source,rs.createdate
FROM Respondents r
JOIN responses rs
ON rs.respondentID = r.respondentID
INNER JOIN (select respondentId, Min(createDate) as mindate from responses
group by respondentId)rs1
ON rs1.respondentID=rs.respondentID
AND rs1.mindate = rs.createDate
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 08:03:51

if sql 2005 then:-


SELECT respondentID,Password,createdate,source,responsedate 
FROM 
(
SELECT ROW_NUMBER() OVER (PARTITION BY r.responentID ORDER BY rs.createdate ASC) AS Seq,r.*,rs.source,rs.createdate as responsedate
FROM Respondents r
JOIN responses  rs
ON rs.respondentID = r.respondentID
)t
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 08:06:35

Assuming that there won't be 2 responses for the same respondent, starting at the same time, from different sources (If there are, this will return both in separate records):


SELECT r.RespondentID, r.CreateDate, r.Source
FROM Responses r
INNER JOIN (
		SELECT RespondentID, MIN(CreateDate) AS CreateDate 
		FROM Responses 
		GROUP BY RespondentID ) m
	ON r.RespondentID = m.RespondentID
		AND r.CreateDate = m.CreateDate

However, if you're on SQL 2005 or above, a CROSS APPLY would possibly give better performance.

There are 10 types of people in the world, those that understand binary, and those that don't.

pjnovak007
Starting Member

19 Posts

Posted - 2010-04-08 : 08:12:02

Hey visakh16

How can i get the Null values to not show up?

Patrick

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 08:18:45

quote:
Originally posted by pjnovak007
How can i get the Null values to not show up?

Which column contains the null values?

There are 10 types of people in the world, those that understand binary, and those that don't.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 08:19:47

quote:
Originally posted by pjnovak007

Hey visakh16

How can i get the Null values to not show up?

Patrick

which column you're getting NULL values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pjnovak007
Starting Member

19 Posts

Posted - 2010-04-08 : 08:23:40

Ok I am either confusing myself or really confusing myself. Let me backup a little bit and explain.

This code here works But I need to add the source field with out adding duplicates, etc.

(select respondentId, Min(createDate) from responses
group by respondentId)

Whats happening in your code is it is adding the source but incorrectly.

what i need is this:

respondentId source createdate
record-1 101 1 1/4/2010
record-2 101 7 1/1/2010
record-3 101 0 1/2/2010
record-4 102 1 1/4/2010
record-5 102 7 1/1/2010
etc..

I need to return
user Id source createdate
record-2 101 7 1/1/2010
record-5 102 7 1/1/2010
etc..

Patrick

pjnovak007
Starting Member

19 Posts

Posted - 2010-04-08 : 08:26:33

respondentId source createdate
101 1 1/4/2010
101 7 1/1/2010
101 0 1/2/2010
102 1 1/4/2010
102 7 1/1/2010
etc..

I need to return user Id source createdate

101 7 1/1/2010
102 7 1/1/2010
etc..

Patrick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 08:27:20

[code]SELECT r.*,rs.source,rs.createdate
FROM Respondents r
JOIN responses rs
ON rs.respondentID = r.respondentID
INNER JOIN (select source, Min(createDate) as mindate from responses
group by source)rs1
ON rs1.source=rs.source
AND rs1.mindate = rs.createDate
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pjnovak007
Starting Member

19 Posts

Posted - 2010-04-08 : 08:31:01

that only returns 4 records. I wish i knew more to tell you then that.

Patrick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 08:32:13

quote:
Originally posted by pjnovak007

respondentId source createdate
101 1 1/4/2010
101 7 1/1/2010
101 0 1/2/2010
102 1 1/4/2010
102 7 1/1/2010
etc..

I need to return user Id source createdate

101 7 1/1/2010
102 7 1/1/2010
etc..

Patrick

for this data you need


SELECT r.*,rs.source,rs.createdate 
FROM Respondents r
JOIN responses  rs
ON rs.respondentID = r.respondentID
INNER JOIN (select respondentID,source, Min(createDate) as mindate from responses 
group by respondentID,source)rs1
ON rs1.respondentID = rs.respondentID
AND rs1.source=rs.source
AND rs1.mindate = rs.createDate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 08:38:10

quote:

Originally posted by visakh16


SELECT r.*,rs.source,rs.createdate 
FROM Respondents r
JOIN responses  rs
ON rs.respondentID = r.respondentID
INNER JOIN (select respondentID,source, Min(createDate) as mindate from responses 
group by respondentID,source)rs1
ON rs1.respondentID = rs.respondentID
AND rs1.source=rs.source
AND rs1.mindate = rs.createDate

You shouldn't need to join to the Respondents table for this. The query I posted above will return what you want, as will this one

SELECT RespondentID, source, createdate
FROM 
(
SELECT ROW_NUMBER() OVER (PARTITION BY rs.RespondentID ORDER BY rs.createdate ASC) AS Seq, RespondentID, rs.source, rs.createdate
FROM responses  rs
)t
WHERE Seq=1

There are 10 types of people in the world, those that understand binary, and those that don't.

pjnovak007
Starting Member

19 Posts

Posted - 2010-04-08 : 08:43:46

Whats happening is that it's returning all records for that user. I need just the record that has the earliest date for the respondentid. Wish i could send screen shots so you dont think i am high.

example if user-1 has a response # of 1, 2 and 7

its return 3 records.

example if user-3 has a response # of 1, 2,3,4,5,6 and 7

its return 7 records.

Patrick

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 09:04:36

quote:
Originally posted by pjnovak007

Whats happening is that it's returning all records for that user. I need just the record that has the earliest date for the respondentid. Wish i could send screen shots so you dont think i am high.

example if user-1 has a response # of 1, 2 and 7

its return 3 records.

example if user-3 has a response # of 1, 2,3,4,5,6 and 7

its return 7 records.

Which query are you running. I tested both the queries I posted, based on your sample data, and they both returned the 2 row you requested.

There are 10 types of people in the world, those that understand binary, and those that don't.

pjnovak007
Starting Member

19 Posts

Posted - 2010-04-08 : 09:16:02

This is one:

SELECT r.*,rs.source,rs.createdate
FROM Respondents r
JOIN responses rs
ON rs.respondentID = r.respondentID
INNER JOIN (select respondentID,source, Min(createDate) as mindate from responses
group by respondentID,source)rs1
ON rs1.respondentID = rs.respondentID
AND rs1.source=rs.source
AND rs1.mindate = rs.createDate

and

SELECT RespondentID, source, createdate
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY rs.RespondentID ORDER BY rs.createdate ASC) AS Seq, RespondentID, rs.source, rs.createdate
FROM responses rs
)t
WHERE Seq=1

Patrick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 09:18:34

quote:
Originally posted by DBA in the making

quote:
Originally posted by visakh16

SELECT r.*,rs.source,rs.createdate
FROM Respondents r
JOIN responses rs
ON rs.respondentID = r.respondentID
INNER JOIN (select respondentID,source, Min(createDate) as mindate from responses
group by respondentID,source)rs1
ON rs1.respondentID = rs.respondentID
AND rs1.source=rs.source
AND rs1.mindate = rs.createDate

You shouldn't need to join to the Respondents table for this. The query I posted above will return what you want, as will this one
SELECT RespondentID, source, createdate
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY rs.RespondentID ORDER BY rs.createdate ASC) AS Seq, RespondentID, rs.source, rs.createdate
FROM responses rs
)t
WHERE Seq=1

There are 10 types of people in the world, those that understand binary, and those that don't.

i've given that already (see 04/08/2010 : 08:03:51). i'm not sure op is using sql 2005 and also if he wants columns from respondents thats why i gave this.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 09:25:51

quote:
Originally posted by visakh16

quote:
Originally posted by DBA in the making

quote:
Originally posted by visakh16

SELECT r.*,rs.source,rs.createdate
FROM Respondents r
JOIN responses rs
ON rs.respondentID = r.respondentID
INNER JOIN (select respondentID,source, Min(createDate) as mindate from responses
group by respondentID,source)rs1
ON rs1.respondentID = rs.respondentID
AND rs1.source=rs.source
AND rs1.mindate = rs.createDate

You shouldn't need to join to the Respondents table for this. The query I posted above will return what you want, as will this one
SELECT RespondentID, source, createdate
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY rs.RespondentID ORDER BY rs.createdate ASC) AS Seq, RespondentID, rs.source, rs.createdate
FROM responses rs
)t
WHERE Seq=1

There are 10 types of people in the world, those that understand binary, and those that don't.

i've given that already (see 04/08/2010 : 08:03:51)

It's not the same query. Yours joins to the Respondents table, mine doesn't.

quote:
i'm not sure op is using sql 2005 and also if he wants columns from respondents thats why i gave this.

No, all the fields he's asked for are available in the Responses table.

There are 10 types of people in the world, those that understand binary, and those that don't.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 09:30:36

quote:
Originally posted by pjnovak007

This is one:

SELECT r.*,rs.source,rs.createdate
FROM Respondents r
JOIN responses rs
ON rs.respondentID = r.respondentID
INNER JOIN (select respondentID,source, Min(createDate) as mindate from responses
group by respondentID,source)rs1
ON rs1.respondentID = rs.respondentID
AND rs1.source=rs.source
AND rs1.mindate = rs.createDate

and

SELECT RespondentID, source, createdate
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY rs.RespondentID ORDER BY rs.createdate ASC) AS Seq, RespondentID, rs.source, rs.createdate
FROM responses rs
)t
WHERE Seq=1

The second one works on the sample data you supplied. I tested it on a sample table. Here is the exact script I ran to test it, including sample data. It tests both the queries I posted. Both return 2 row, one for each RespondantID.



CREATE TABLE #Responses (
	respondentId INT,
	source INT,
	createdate DATETIME)
GO
INSERT INTO #Responses
SELECT 101, 1, '1/4/2010'
UNION ALL SELECT 101, 7, '1/1/2010'
UNION ALL SELECT 101, 0, '1/2/2010'
UNION ALL SELECT 102, 1, '1/4/2010'
UNION ALL SELECT 102, 7, '1/1/2010'
GO

SELECT r.RespondentID, r.CreateDate, r.Source
FROM #Responses r
INNER JOIN (
		SELECT RespondentID, MIN(CreateDate) AS CreateDate 
		FROM #Responses 
		GROUP BY RespondentID ) m
	ON r.RespondentID = m.RespondentID
		AND r.CreateDate = m.CreateDate

SELECT RespondentID, source, createdate
FROM 
(
SELECT ROW_NUMBER() OVER (PARTITION BY rs.RespondentID ORDER BY rs.createdate ASC) AS Seq, RespondentID, rs.source, rs.createdate
FROM #Responses  rs
)t
WHERE Seq=1

DROP TABLE #Responses

Here's the results for the 2 queries


101	2010-01-01 00:00:00.000	7
102	2010-01-01 00:00:00.000	7

101	7	2010-01-01 00:00:00.000
102	7	2010-01-01 00:00:00.000

There are 10 types of people in the world, those that understand binary, and those that don't.

pjnovak007
Starting Member

19 Posts

Posted - 2010-04-08 : 15:11:28

thanks guys but still no luck

Patrick

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources