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.
| Author |
Topic |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2002-05-28 : 16:11:41
|
| Hiya!How do I select 1 row per customer from the Orders table? Top 1 will actually only return 1 row, while what I'd like is 1 row per customer.(But not a summary row- No Group By!) Sort of like Line 1 from each order, except some orders might not have a line 1 as it may have been deleted, with only lines 2-5 left.Sarah Berger MCSD |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-05-28 : 16:14:48
|
what's the defining attribute of the one row you want per customer per order? min(line #)?quote:
Hiya!How do I select 1 row per customer from the Orders table? Top 1 will actually only return 1 row, while what I'd like is 1 row per customer.(But not a summary row- No Group By!) Sort of like Line 1 from each order, except some orders might not have a line 1 as it may have been deleted, with only lines 2-5 left.Sarah Berger MCSD
setBasedIsTheTruepath<O> |
 |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-05-28 : 17:05:49
|
quote:
what's the defining attribute of the one row you want per customer per order? min(line #)?
Yes. But does it make much sense to have a subquery in the WHERE clause "WHERE Line = (SELECT Min(Line) FROM .....)"? Or is there a better way to do this?Sarah Berger MCSD |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-05-28 : 19:07:11
|
SarahThere is an answer that setbased looks like he is going to give you, you just have to answer his question first  Damian |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-05-29 : 10:48:54
|
quote:
SarahThere is an answer that setbased looks like he is going to give you, you just have to answer his question first Damian
I did answer yes. Would you like a different answer, or I should just plain shut up and not ask more questions in the same breath as my answer?Sarah Berger MCSD |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-05-29 : 11:05:10
|
Relax ...  I don't believe Merkin was trying to antagonize you, I certainly wasn't. What I was trying to do was ask a question that would lead you down the right path when answered. He picked up on that too I think.You can write the query with an IN, or a join, or a correlated sub-query with a HAVING. Any of those ways will work. Let me know if you want me to elaborate on that at all. If so post the DDL.setBasedIsTheTruepath<O> |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-05-29 : 12:06:10
|
Hi, SetBased,Have no fear, I'm not antagonized, and I was just kidding. I wanted to edit my post and insert a smiley face to show it, but the Edit Post icon seems to have run away. I did realized that your question had a clue in it, which is why I answered as I did. Here's the select statement:SELECT (CASE WHEN GroupName IS NULL THEN 'Dr. ' + RTRIM (D.FirstName) + ' ' + D.LastName ELSE GroupName END) AS DrName, D.Address AS DrAddress, (RTRIM (D.City) + ', ' + D.State + ' ' + D.ZipCode) AS DrCSZ, D.Phone AS DrPhone, P.Account, (RTRIM (P.FirstName) + ' ' + P.LastName) AS PtName, P.Address, (RTRIM (P.City) + ', ' + P.State + ' ' + P.ZipCode) AS CSZ, V.VoucherNo, VD.ServiceDate, VD.ProcCode, (SELECT TOP 1 Description FROM CPTCodes WHERE Code = VD.ProcCode) AS CPTDesc, VD.Charge, VD.AmountPaid, VD.AdjAmount, VD.SingleBalance, (CASE WHEN DATEDIFF (dd, VD.ServiceDate,getdate()) < 30 THEN SingleBalance ELSE 0 END) AS '029', (CASE WHEN DATEDIFF (dd, VD.ServiceDate,getdate()) BETWEEN 30 AND 59 THEN SingleBalance ELSE 0 END) AS '3059', (CASE WHEN DATEDIFF (dd, VD.ServiceDate,getdate()) BETWEEN 60 AND 89 THEN SingleBalance ELSE 0 END) AS '6089', (CASE WHEN DATEDIFF (dd, VD.ServiceDate,getdate()) BETWEEN 90 AND 120 THEN SingleBalance ELSE 0 END) AS '90119', (CASE WHEN DATEDIFF (dd, VD.ServiceDate,getdate()) > 120 THEN SingleBalance ELSE 0 END) AS '120', getdate() AS Today,(SELECT COUNT(Voucherno) FROM Submissions WHERE VoucherNo = V.VoucherNo AND Submittype = 'Statement' AND EXISTS (SELECT VoucherNo FROM Vouchers WHERE VoucherNo = V.VoucherNo AND TotBal > 0)) AS NoticeNo, PtNote = 'Your Account Is ' + CASE WHEN DATEDIFF(dd,VD.ServiceDate,getdate()) > 120 THEN '120+ Days' ELSE (CASE WHEN DATEDIFF(dd,VD.ServiceDate,getdate()) BETWEEN 90 AND 119 THEN '90 Days' ELSE (CASE WHEN DATEDIFF(dd,VD.ServiceDate,getdate()) BETWEEN 60 AND 89 THEn '60 Days' ELSE (CASE WHEN DATEDIFF(dd,VD.ServiceDate,getdate()) BETWEEN 30 AND 59 THEN '30 Days' ELSE 'Less Than 30 Days' END)END)END)END + ' Overdue. Please Pay Up.' FROM Patients P INNER JOIN (Doctors D INNER JOIN (Vouchers V INNER JOIN VouchersDetail VD ON VD.Voucher = V.VoucherNo AND VD.ServiceDate BETWEEN '1/1/1900' AND getdate() AND V.Printed = 1 AND VD.SingleBalance > 0) ON V.DrCode = D.Code) ON P.ID = VD.PatID AND P.Account BETWEEN 0 AND 999999999 ORDER BY P.Lastname, P.Firstname, VoucherNo,LinenumberThis returns all lines for a patient, which is fine sometimes. Sometimes I need only 1 line, with all the fields in this query. quote:
You can write the query with an IN, or a join, or a correlated sub-query with a HAVING.
Dunno about an IN - There's really no way to know what the linenumbers are, as aforementioned. There may be an order whose first n lines were deleted, and linenumbers do not reorder. How could a JOIN be used here? HAVING also sounds good, but how to use it without GROUP BY?If you'd like the table structure DDL, I can post it, but there's alot of unnecessary fields in there.Sarah Berger MCSD |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-05-29 : 13:17:44
|
| wow ... that's quite a select.I'll admit to not having fully digested it all, but it looks (?) like what you need to do join to the first line for a patient in VoucherDetails, rather than the whole table.setBasedIsTheTruepath<O> |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-05-29 : 13:40:37
|
Its bark is worse than its bite...The long CASE statements only calculate patient balance aging twice, once for summing, and once for a message. You can ignore that junk. Otherwise, the select is quite benign, only 4 tables. quote:
...FROM Patients P INNER JOIN (Doctors D INNER JOIN (Vouchers V INNER JOIN VouchersDetail VD ON VD.Voucher = V.VoucherNo AND VD.ServiceDate BETWEEN '1/1/1900' AND getdate() AND V.Printed = 1 AND VD.SingleBalance > 0) ON V.DrCode = D.Code) ON P.ID = VD.PatID AND P.Account BETWEEN 0 AND 999999999...
Are you suggesting I should add a correlated subquery to the innermost join "(Vouchers V INNER JOIN VouchersDetail VD ON VD.Voucher = V.VoucherNo AND VD.ServiceDate BETWEEN '1/1/1900' AND getdate() AND V.Printed = 1 AND VD.SingleBalance > 0 AND VD.LineNumber = (SELECT MIN(Linenumber) FROM VouchersDetail WHERE Voucher = V.VoucherNo))", because otherwise I see no way to join to a single line only,since linenumber exists only in the Vouchersdetail table, not the others.Sarah Berger MCSD |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-05-29 : 13:41:32
|
| that sounds right.setBasedIsTheTruepath<O> |
|
|
|
|
|
|
|
|
Top 1 row per customer