Need help with a tricky query 
Author Message
 Need help with a tricky query

I have a table that has the following layout:

rowid, customerid, worknote, entrydate

Each customerid can have multiple worknotes.  What I need
to do is get the 2 most current worknotes  for each
customer and put them into 2 separate columns in a view
(e.g., customerid, worknote1, worknote2).  Some customerid
may only have a single worknote.

I can build a correlated subquery to get the 2 most current
results per customerid but I can't figure out how to then
display them into multiple columns without cursoring, using
temp tables, etc.

This is the query that gets me the 2 most current records:

select * from CustomerWorknotes t1 where rowid in (select
top 2 t2.rowid from CustomerWorknotes

t2 where t2.customerid = t1.customerid order by entrydate
desc)

Any help would be appreciated.

Thanks.

-John



Sat, 13 Aug 2005 21:48:33 GMT
 Need help with a tricky query

Assuming one customer does not have 2 worknotes with the same entrydate:

select customer_id,
(select TOP 1 worknote from CustomerWorknotes where customer_id
=cw.customer_id order by entrydate) AS worknote1,
(select TOP 1 worknote from CustomerWorknotes where customer_id
=cw.customer_id
AND entrydate < (SELECT MAX(cwc.entrydate) from CustomerWorknotes cwc where
cwc.customer_id = cw.customer_id)
order by entrydate) AS worknote2
FROM CustomerWorknotes cw

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


Quote:
> I have a table that has the following layout:

> rowid, customerid, worknote, entrydate

> Each customerid can have multiple worknotes.  What I need
> to do is get the 2 most current worknotes  for each
> customer and put them into 2 separate columns in a view
> (e.g., customerid, worknote1, worknote2).  Some customerid
> may only have a single worknote.

> I can build a correlated subquery to get the 2 most current
> results per customerid but I can't figure out how to then
> display them into multiple columns without cursoring, using
> temp tables, etc.

> This is the query that gets me the 2 most current records:

> select * from CustomerWorknotes t1 where rowid in (select
> top 2 t2.rowid from CustomerWorknotes

> t2 where t2.customerid = t1.customerid order by entrydate
> desc)

> Any help would be appreciated.

> Thanks.

> -John



Sat, 13 Aug 2005 22:16:57 GMT
 Need help with a tricky query
For such problems, please post your table DDLs , sample data  & expected
results. Here is a guess:

SELECT *
  FROM tbl
 WHERE keycol IN (SELECT TOP 2 t1.keycol
                    FROM tbl t1
                   WHERE t1.customerid = tbl.customerid
                   ORDER BY t1.entrydate DESC)

--
- Anith
(Please respond only to newsgroups)



Sat, 13 Aug 2005 22:29:01 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Help with a tricky SQL query

2. Tricky Query help

3. A Tuff Tricky One - Please Help with Queries

4. tricky query - please help

5. HELP: Tricky query....

6. Help with tricky SQL query

7. Need help on tricky SQL

8. Help. A tricky SQL guru needed

9. Tricky Update statement need help please...

10. Need help with (tricky?) select

11. Need your help with tricky TSQL

12. Need help with a Tricky Update statement


 
Powered by phpBB® Forum Software