Quick T-SQL help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Quick T-SQL help

Author

Topic

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-05-13 : 11:59:49

I have to implement some logic in a view and I don't know how to do it. Let me outline the logic:

- I'm working with a view where records represent participant activities.
- Some participants can have numerous activities.
- Each activity has a start date and an activity code.
- Only records with activity code 23 or 33 appear in the view currently.

What I need to do is NOT include records where the participant has another activity with code 26 (from another table, the one the current view is derived from) which has the same start date as the activity 23/33 record in the view currently. Also, this should only be implemented if the start date is the first start date (minimum start date for that participant).

Little help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 12:19:01

[code]CREATE VIEW YourView
AS
SELECT t1.*
FROM Table t1
INNER JOIN (SELECT MIN(startdate) AS mindate,participantid
FROM table
GROUP BY participantid)t2
ON t2.participantid=t1.participantid
AND t2.mindate=t1.startdate
LEFT JOIN table t3
ON t3.startdate=t1.startdate
AND t3.participantid=t1.participantid
AND t3.activitycode=26
WHERE (t1.activitycode=23
or t1.activitycode=23)
AND t3.participantid IS NULL
GO[/code]

This will provide you with a start. If you want correct solution post your tables involved along with some sample data and also view output expected.

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-05-14 : 10:20:00

Thanks for your reply. I'm not really sure how to follow that because it has 3 tables. Here is an example:

ID....CODE...BEGIN_DATE
666...26.....2008-04-18
666...33.....2008-04-18
666...23.....2008-04-29
666...28.....2008-05-08

Based on the logic that only codes 23 and 33 should be included, only records 2 and 3 should be there. But, the logic I need to implement would remove record 2 because it is a code 33 that has the earliest begin date for that ID which is the same begin date as a code 26 record. The logic I need to implement would result in only record 3 appearing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 10:30:24

[code]CREATE VIEW YourView
AS
SELECT t1.*
FROM Table t1
LEFT JOIN
(Select t3.*
FROM table t3
INNER JOIN (SELECT MIN(startdate) AS mindate,participantid
FROM table
GROUP BY participantid)t2
ON t2.participantid=t3.participantid
AND t2.mindate=t3.startdate)tmp
ON tmp.startdate=t1.startdate
AND tmp.participantid=t1.participantid
AND tmp.activitycode=26
WHERE (t1.activitycode=23
or t1.activitycode=33)
AND tmp.participantid IS NULL
GO[/code]

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-05-14 : 10:36:47

What are the 3 tables? The data is only coming from 1 table. Do I just use the same table for all 3 references?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 10:39:35

quote:
Originally posted by im1dermike

What are the 3 tables? The data is only coming from 1 table.

How can i know? I dont have access to your system. Either you provide me the tables along with the column data information or try to replace table in query with derived table taking records from your 3 tables.

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-05-14 : 10:43:08

You reference 3 tables in your code: t1, t2, and t3. My data is only coming from 1 table.

When I replace the three references to "table" in your code with my table name (all 3 times) it appears to work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 10:44:48

quote:
Originally posted by im1dermike

You reference 3 tables in your code: t1, t2, and t3. My data is only coming from 1 table.

When I replace the three references to "table" in your code with my table name (all 3 times) it appears to work.

ah you were referring to those. Its the same table which you use thats referrenced thrice. Once to take data,once to filter outminimum records and one to check if it has activity 26.

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-05-14 : 10:45:19

Awesome. In that case it looks like it works. Thanks so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 10:47:19

quote:
Originally posted by im1dermike

Awesome. In that case it looks like it works. Thanks so much.

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources