Comparing a prior row to the next row and to criteria 1

[RedSparks]

I am trying to write a SQL statement that will compare one row to the next, then eliminate the rows not needed.

Example:
Table: TLV_DischargeDates

FamilyKey Program
5723 2
5830 3
8473 5
8473 6
8493 1
8930 3
14710 5
14710 6
14710 10

The FamilyKey must not be in more than one program. Those that are need to be eliminated from the list. Only the FamilyKey equal Program 6 are to remain in the list. How can I write this in a SQL statement?

 

[SQLSister]

SO if the Family Key does not ahave a program value of 6 in any record, do you want to delete the record, change it to 6 or what?

Questions about posting. See faq183-874

 

[RedSparks]

I want to have it left out of the returned data. We are closing all clients enrolled in program 6 at the end of the year. The Update function will be used to set their Agency Discharge Date at 12/31/04. However, if they are enrolled in any other program, we don't want to give them this date.

Let's say family 12345 was enrolled in program 10 and 5 only. Then when the query is ran they would not show up on the report to have the discharge date updated with 12/31/04.

Then there is Family 8473 who's enrolled in program 5 and 6, but because they are enrolled in another program other than just 6, they too must be left off the report, because we want their Discharge Date left open.

If the family is only enrolled in program 6, then they need to show up on the report, so that their Discharge Date can be updated with 12/31/04.

How do I write a query to only give me this final scenerio? If I just say the program code = 6, then it will bring in families that also have enrollments in other programs.

Thanks for your assistance.

 

[SQLSister]

Try:

Select t.FamilyKey, t.Program from TLV_DischargeDates t
Join (Select FamilyKey from TLV_DischargeDates where count(FamilyKey) = 1 group by FamilyKey) a
on t.FamilyKey = a.FamilyKey
Where t.Program = 6

Questions about posting. See faq183-874

 

[RedSparks]

I'm receiving this error.

Server: Msg 147, Level 16, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is
in a subquery contained in a HAVING clause or a select list,
and the column being aggregated is an outer reference.


Any suggestions?

 

[SQLSister]

oops. Try:

Select t.FamilyKey, t.Program from TLV_DischargeDates t
Join (Select FamilyKey from TLV_DischargeDates  group by FamilyKey having count(FamilyKey) = 1) a
on t.FamilyKey = a.FamilyKey
Where t.Program = 6

Questions about posting. See faq183-874

 

[RedSparks]

I put this in:

Select t.FamilyKey, t.ProgramCode from TLV_NullDischargeDate t
From(Select FamilyKey from TLV_NullDischargeDate
Group by FamilyKey
having count(FamilyKey) = 1) a
on t.FamilyKey = a.FamilyKey
Where t.ProgramCode = 6


And got this:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'From'.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'a'.

What am I doing?

 

[yksvaan]

You are having
From(Select ..
when SQLSister wrote
Join (Select ..

Why don't you Copy/Paste SQLSister code, I would.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[RedSparks]

The query runs, but it is still bringing back familykeys that are enrolled in other programs beside program 6.

Any other suggestions?

 

[SQLSister]

Select t.FamilyKey, t.Program from TLV_DischargeDates t
Join (Select FamilyKey from TLV_DischargeDates  group by FamilyKey having count(FamilyKey) = 1 and Program = 6) a
on t.FamilyKey = a.FamilyKey

Questions about posting. See faq183-874