Select - 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 2000 Forums

Transact-SQL (2000)

Select - Help

Author

Topic

cindylee
Yak Posting Veteran

55 Posts

Posted - 2005-01-27 : 01:45:53

Hello PPl,
I have 2 tables
table A
--------
CustomerID (PK)

1200
2400
3444

Table B
--------
CustomerId (FK) itemID Date status

1200 30200 20/11/04 checked out
1200 30200 30/12/04 Due Date
1200 30200 1/01/05 Billsent
1200 30201 20/11/04 checked out
1200 30201 30/12/04 Due Date
1200 30201 1/01/05 Billsent

I need a output like this

CustomerID itemId CheckedoutDate DueDate BillsentDate

1200 30200 20/11/04 30/12/05 1/01/05
1200 30201 20/11/04 30/12/05 1/01/05

any help greatly appreciated

Cindy

cindylee
Yak Posting Veteran

55 Posts

Posted - 2005-01-27 : 01:50:32

Do i need to use a CASE

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-27 : 03:25:33

No, Cindy -- CASE is not your case (i'm a famous linguist, btw);

select CustomerID, itemId,

(select Date from B as t where
t.CustomerID=B.CustomerID and t.itemId=B.itemId and t.status='checked out')
AS CheckedoutDate,

(select Date from B as t where
t.CustomerID=B.CustomerID and t.itemId=B.itemId and t.status='Due Date')
AS DueDate,

(select Date from B as t where
t.CustomerID=B.CustomerID and t.itemId=B.itemId and t.status='Billsent')
AS BillsentDate

from B

cindylee
Yak Posting Veteran

55 Posts

Posted - 2005-01-27 : 18:06:17

thanks stoad
Cindy

cindylee
Yak Posting Veteran

55 Posts

Posted - 2005-01-27 : 19:54:02

Hi Stoad,
i tried the query and the ouput is:

CustomerID ItemID CheckedOutDate DueDate BillSent
---------- -------- ------------- -------- ---------
1200 30200 20/11/04 30/12/05 1/01/05
1200 30200 20/11/04 30/12/05 1/01/05
1200 30200 20/11/04 30/12/05 1/01/05

It duplicates 3 times, but when i use distinct its ok.
Also I only want for the customers who are present in Table A.

Ty
cindy

jhermiz

3564 Posts

Posted - 2005-01-27 : 23:06:52

Try this:


SELECT B.CustomerID, 
       B.ItemID,
       (SELECT D FROM B AS t WHERE
        t.CustomerID=B.CustomerID AND 
        t.ItemID=B.ItemID AND 
        t.Status='Checked Out')AS CheckedOut,
        (SELECT D FROM B AS t WHERE
        t.CustomerID=B.CustomerID AND
        t.ItemID=B.ItemID AND 
        t.Status='Due Date') AS Due,
        (SELECT D FROM B AS t WHERE
        t.CustomerID=B.CustomerID AND 
        t.ItemID=B.ItemID AND 
        t.Status='Bill Sent') AS BillSent
FROM B 
INNER JOIN A ON B.CustomerID=A.CustomerID
GROUP BY B.CustomerID, 
         B.ItemID

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-01-27 : 23:19:33

quote:
Originally posted by Stoad

(i'm a famous linguist, btw);

A cunning one at that!

Damian

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-28 : 03:02:01

Cindy;
oops! Of course, my query triples each record. Try jon's suggestion.

lol, Damian... i'm forced to be cunning - being at work, it is not easy,
sometimes, to concentrate on (or over?) a forum's question.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-31 : 19:26:04

quote:
Originally posted by Stoad

No, Cindy -- CASE is not your case (i'm a famous linguist, btw);

Might this be a case for CASE

select 
	b.CustomerID,
	b.itemid,
	max(case when b.status = 'checked out' then date end) as CheckedOutDate,
	max(case when b.status = 'Due Date'    then date end) as DueDate,
	max(case when b.status = 'Billsent'    then date end) as BillSentDate
from 
	tableB b
inner join 
	tableA a on a.CustomerID = b.CustomerID
group by 
	customerid,itemid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources