Almost there... - 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 2000 Forums

Transact-SQL (2000)

Almost there...

Author

Topic

mlam
Starting Member

5 Posts

Posted - 2004-06-14 : 17:16:21

Help!

I've been working on this Aging Report problem for over a week and I think I'm close.

This T-SQL Statement:


SELECT DISTINCT(SubSSN), PTDate, DateDiff(Day, PTDate, '5/31/04') AS AgingDays FROM Commissions
ORDER BY SubSSN, PTDate DESC, AgingDays

yields the following table:


SubSSN    PTDate                AgingDays
000000000 2004-06-01 00:00:00	-1
000000000 2004-05-15 00:00:00	16
000000000 2004-05-01 00:00:00	30
000000000 2004-04-15 00:00:00	46
000000000 2004-04-01 00:00:00	60
000000000 2004-03-15 00:00:00	77
000000000 2004-03-01 00:00:00	91
000000000 2004-02-15 00:00:00	106
000000000 2004-02-01 00:00:00	120
111111111 2004-05-01 00:00:00	30
111111111 2004-04-01 00:00:00	60
111111111 2004-03-01 00:00:00	91
111111111 2004-02-01 00:00:00	120
222222222 2004-06-14 00:00:00	-14
222222222 2004-05-14 00:00:00	17
222222222 2004-04-14 00:00:00	47
222222222 2004-03-14 00:00:00	78

What I need to do now is get the first row for each distinct SubSSN, or put another way, I need to get the lowest AgingDay for each SubSSN (000000000 = -1, 111111111 = 30, 222222222 = -14). What is the best way of doing this?

I've looked at cursors, while loops, if statements, and probably 20 other things and still can't find an answer. If only there was some sort of For...Each structure in T-SQL...

Thanks in advance!

Mark

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-14 : 17:23:35

select t1.* from
(
SELECT DISTINCT(SubSSN), PTDate, DateDiff(Day, PTDate, '5/31/04') AS AgingDays FROM Commissions
) t1
where PTDate = (select max(c.PTDate) from Commissions c where c.SubSSN = t1.SubSSN

or just
SELECT SubSSN, PTDate, DateDiff(Day, PTDate, '5/31/04') AS AgingDays
FROM Commissions t1
where t1.PTDate = (select max(c.PTDate) from Commissions c where c.SubSSN = t1.SubSSN

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mlam
Starting Member

5 Posts

Posted - 2004-06-14 : 17:41:20

nr,

Thank you! The first statement is exactly what I was looking for!

Mark

gates_micro
Starting Member

29 Posts

Posted - 2004-06-15 : 02:44:35

Another way of doing it.

SELECT t.SubSSN,b.PTDate, t.AgingDays from
(SELECT SubSSN,min(AgingDays) as AgingDays from COMMISSIONS group by SubSSN)t,
(SELECT PTDate,subssn,agingdays from COMMIssions) b
where b.subssn=t.subssn and b.agingdays=t.agingdays

mlam
Starting Member

5 Posts

Posted - 2004-06-15 : 09:49:20

Gates_Micro,

Thanks for the input, but it doesn't appear to be working. When I run it in the Query Analyzer, I get the following:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'from'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'b'.

I'd be interested in seeing the output from your approach as well. NR's first statement worked, but after looking through the result set last night I found a few duplicates. It still is doing what I want it to do - I'm guessing the duplicates are coming from corrupted data. Anyhow, I'd like to look at several possibilities.

Also, I'm still pretty much a beginner with T-SQL (learning something new every day as a result of this project!) Can someone explain how these statements work? For instance, nr's:

select t1.* from
(
SELECT DISTINCT(SubSSN), PTDate, DateDiff(Day, PTDate, '5/31/04') AS AgingDays FROM Commissions
) t1
where PTDate = (select max(c.PTDate) from Commissions c where c.SubSSN = t1.SubSSN

is a little perplexing to me. It works, but I just don't know why.

If someone could take a minute to break this down and explain what this is doing, I'd be very appreciative.

Thanks everyone!

Mark

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-06-15 : 10:16:09

OK, the middle section

quote:

(SELECT DISTINCT(SubSSN), PTDate, DateDiff(Day, PTDate, '5/31/04') AS AgingDays FROM Commissions) t1

is called a derived table, and basically creates a pseudo-table called t1 that the rest of your query can use.

quote:

where PTDate = (select max(c.PTDate) from Commissions c where c.SubSSN = t1.SubSSN

is a correlated subquery. The SELECT MAX... portion runs once for each t1.SubSSN in the main query.

SO basically, the query...
Gets all the records from the Commissions table that you want and 'stores' them in 'table' t1.
Then, for each record in t1, it runs the subquery to see if the PTDate is equal to the maximum PTDate in the Commissions table that is linked to that SubSSN in T1, if so, it returns the row.

Does that make sense ?

mlam
Starting Member

5 Posts

Posted - 2004-06-15 : 10:42:34

Jason,

OK, things are becomming more clear. The "correlated subquery" is the piece I was missing - that's a new term to me and something I'll need to read up on. I take it then that a correlated subquery forces SQL into doing the line-by-line comparison I wanted it to do, but not because I (or more appropriately, nr in this case) explicitly told SQL to use it, but rather because of the way the query was structured. I wish I had known that - it would have saved me a few days of headaches.

Seeing as I'm teaching myself T-SQL as I go, does anyone have an opinion on good references for the T-SQL language and how to use it (something that goes beyond the basic SELECT * FROM table WHERE... - that's the part I'm starting to get a good handle on)? Something that talks about things like correlated subqueries? This site is pretty good, but I'd hate to bother all of you with endless questions, and like any good beginner programmer, I like having hardcopy references on my desk.

I do look at the SQL BOL, which is a great reference if you know what you're looking for. In this case, I didn't know I was looking for a correlated subquery, so I never looked for it.

Thanks Jason!

Mark

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-06-15 : 10:57:48

quote:

I take it then that a correlated subquery forces SQL into doing the line-by-line comparison

Yep, if you look at the WHERE clause of the sub-query, you see it links the table in the subquery to the table (t1) in the outer query, that's where the correlation comes in.
I leave others to recommend good books - I had the good fortune to work with a SQL expert some time ago and brain-drained him!

mlam
Starting Member

5 Posts

Posted - 2004-06-15 : 11:06:49

Jason,

I've now looked up correlated subqueries in the BOL and sure enough, I now see the light!

I was simply never aware of this before. I knew that there had to be a way in T-SQL to do this, but I kept searching for things like FOR...EACH loops, loops in general, WHILE loops, etc. I found a lot of entries that hinted at the solution, but I never stumbled across this.

Thanks for the help! Still waiting for good references!

Mark

Kristen
Test

22859 Posts

Posted - 2004-06-15 : 12:19:15

quote:
Originally posted by mlam
Seeing as I'm teaching myself T-SQL as I go, does anyone have an opinion on good references for the T-SQL language and how to use it

Have a look at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35652

Kristen

gates_micro
Starting Member

29 Posts

Posted - 2004-06-15 : 14:47:31

well mlam,

After running the query in the sql analyser using the data which you have I send this query.Its working fine.

SELECT t.SubSSN,b.PTDate, t.AgingDays from (SELECT SubSSN,
min(AgingDays) as AgingDays from Commissions group by SubSSN)t,(SELECT PTDate,subssn,Agingdays from Commissions) b
where b.SubSSN=t.SubSSN and b.Agingdays=t.Agingdays

If any other sql masters see any error with this query do send your views. Datatype for all the fields are (nvarchar).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources