Need Sql Code 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)

Need Sql Code Help

Author

Topic

gualm
Starting Member

11 Posts

Posted - 2007-12-14 : 16:22:45

I have only been using Transact Sql for a short time but I am having problems with a program that I am doing which is based off preceding data, i.e. effective dates.

Example: someone belongs to a volleyball team and they were going to a special team but had to meet other criteria b4 they were selected.
Each person on the team were in different squads. But only those who
were on the red squad, then green squad and now are orange can be considered (in that order). So, you have Julie who meets this criteria. The coach asks me to create a program showing who meets this criteria. Also what would I do if I wanted to show the dates for each squad + list the squads for Julie on the output. How would I approach this.
Julie's information
red squad effective date: 1/01/06 end date: 3/15/06
yellow squad
green squad effective date: 3/15/06 end date: 5/31/06
orange squad (top squad) effective date: 5/31/06 to present

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-15 : 00:23:57

I think you need a table PersonSquad of following structure:-

PersonID Person Squad EffectiveDate EndDate

For each assignment of person to a squad you can populate a record into table with date assigned. So when at 1/01/06 we will have
i.e 100 Julie Red 1/01/06 NULL

the NULL value for EndDate means person hasnt moved out of squad yet
Now at 3/15/06 we are moving person to green squad say the we populate the new record as

100 Julie Green 3/15/06 NULL and we need to close down first record

This can be done as

;
With Person_CTE(RowNo,PersonID,Person,EffectiveDate,EndDate) AS
(
SELECT Row_Number() OVER (PARTITION BY Person Order by EffectiveDate) AS 'RowNo',
PersonID,
Person,
EffectiveDate,
EndDate
FROM PersonSquad
WHERE EndDate IS NULL
)

UPDATE t1
SET t1.EndDate=t2.EffectiveDate
FROM Person_CTE t1
INNER JOIN Person_CTE t2
ON t1.PersonID=t2.PersonID
AND t1.RowNo=t2.RowNo-1

this will take:-
1 100 Julie Red 1/01/06 NULL
2 100 Julie Green 3/15/06 NULL

AND assign EffectiveDate of Second to EndDate of First and we get

1 100 Julie Red 1/01/06 3/15/06
2 100 Julie Green 3/15/06 NULL

Now you can check the criteria as follows (Red->Green->Orange)

;
With Person_CTE(RowNo,PersonID,Person,Squad,EffectiveDate,EndDate) AS
(
SELECT Row_Number() OVER (PARTITION BY Person Order by EffectiveDate desc) AS 'RowNo',
PersonID,
Person,
Squad,
EffectiveDate,
EndDate
FROM PersonSquad)

SELECT p.* FROM Person_CTE p
INNER JOIN (SELECT RowNo,PersonID FROM Person_CTE WHERE Squad='Green')g
ON p.PersonID=g.PersonID
INNER JOIN (SELECT RowNo,PersonID FROM Person_CTE WHERE Squad='red')r
p.PersonID=r.PersonID
WHERE p.RowNo=1 AND p.Squad='Orange'
AND g.RowNo>p.RowNo
AND r.RowNo > g.RowNo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources