Stumped on a SQL Statement

[Corey McGlone]

Hi all,

I'm trying to write a SQL statement (or series of statements) in a stored procedure and I'm having trouble working out the logic.

I have 3 tables that look something like this:

Customers
+--------------------+
| Name | Customer_ID | 
+--------------------+
 
Service Types
+---------+
| Type_ID |
+---------+
 
Services Rendered
+----------------------------------------+
| Customer_ID | Type_ID | Date Performed |
+----------------------------------------+

Now, the idea here is that we have multiple services that can be offered. What I want to get from the data is when the last time was each service was performed. I can do that but, in the case that a specific service was never performed (as might be the case for a new customer), we'd want to know that.

So, let's say I have data that looks like this:

Customers
+------------------+
| Mickey Mouse | 1 | 
| Donald Duck  | 2 |
+------------------+
 
Service Types
+-------+
| Type1 |
| Type2 |
| Type3 |
+-------+
 
Services Rendered
+-----------------------+
| 1 | Type1 | 1/1/2005  |
| 1 | Type1 | 1/15/2005 |
| 1 | Type2 | 1/17/2005 |
| 1 | Type3 | 1/25/2005 |
| 2 | Type1 | 1/3/2005  |
+-----------------------+

So, if you look at that, Mickey Mouse has had all 3 services performed but Donald Duck (customer 2) has only had 1 service performed. Now, let's say we want to see when the last time our customers have had Service Type 3 done, I'd like to get a result set that looks like this:

+-----------------------+
| 1 | Type3 | 1/25/2005 |
| 2 | Type3 |    NULL   |
+-----------------------+

It's important that we know that Customer 2 has never had this done - because we might want to contact that customer about it.

Boy I hope that made sense. I'm having a really hard time wrapping my head around this SQL statement. If anyone out there has any ideas, I'd love to hear them.

Thanks.

[Jayesh Lalwani]

Did you try using Outer Join
[ April 15, 2005: Message edited by: Jayesh Lalwani ]

[Shailesh Chandra]

Corey,

I think this query will give you desired result
This query is ORACLE compatible

SELECT
CUSTOMER_ID, TYPE, SERVICE_DATE FROM
(
SELECT 
CUSTOMERS.CUSTOMER_ID,
SERVICES_RENDERED.TYPE,
SERVICES_RENDERED.SERVICE_DATE,
DENSE_RANK() OVER (ORDER BY SERVICE_DATE DESC nulls last) RN 
FROM
CUSTOMERS , SERVICES_RENDERED
WHERE
CUSTOMERS.Customer_ID = SERVICES_RENDERED.Customer_ID(+)
)
WHERE RN = 1

you have posted a similar problem few days back here ,I was just wondering if you referred my reply in same post. Only a outer join need to be added

Hope this solve your problem

Shailesh
[ April 16, 2005: Message edited by: Shailesh Chandra ]

[Corey McGlone]

Originally posted by Jayesh Lalwani:
Did you try using Outer Join

I thought all I needed to add was an Outer Join, but I wasn't sure if that was the case and, even if it was, I wasn't sure about the proper syntax. I'm trying to join all 3 tables at once and I seem to be having problems with that.

I was thinking that the simplest way to do this might be to perform an outer join on two tables and then a left join on the third. So, my thought was to do an outer join on Customers and Service Types, first, giving me something like this:

Temp Table - Outer Join of Customers and Service Types
 
+--------------------------+
| Mickey Mouse | 1 | Type1 |
| Mickey Mouse | 1 | Type2 |
| Mickey Mouse | 1 | Type3 |
| Donald Duck  | 2 | Type1 |
| Donald Duck  | 2 | Type2 |
| Donald Duck  | 2 | Type3 |
+--------------------------+

Once I've got that table, I should be able to do a left join between that and my Services_Rendered table. Does that make sense?

My problem is that I can't seem to work out the syntax for that outer join. As far as I can tell, the syntax for an outer join in SQL Server is:

SELECT * FROM Customers
FULL OUTER JOIN Service_Types
ON [Criteria]

However, in this case, I have no idea what to put in for criteria. The two tables have nothing at all in common. None of their columns match. I really just want every row in the first table to be matched with every row from the second table. What's the syntax for that?

Thanks.
[ April 18, 2005: Message edited by: Corey McGlone ]

[Corey McGlone]

Originally posted by Shailesh Chandra:
you have posted a similar problem few days back...

Shailesh,

Thanks for the feedback - you've got a good memory - this question stems from the exact same stored procedure. Now, the only difference is that the requirements have changed a little bit. Unfortunately, I'm using SQL Server instead of Oracle so I'm not sure how usable your query would be for me.

[Shailesh Chandra]

Originally posted by Corey McGlone:

Unfortunately, I'm using SQL Server instead of Oracle so I'm not sure how usable your query would be for me.

Even I was looking at previous post but I could not find database information.

Any way If you have solved previous problem only a outer join would suffice to solve problem. but you have already mentioned your problem to use outer join
That is what I can think as of now.I will get back if any other solution come in my mind

Thanks
Shailesh
[ April 18, 2005: Message edited by: Shailesh Chandra ]

[Corey McGlone]

Well, I've gotten a little further, but I'm still stuck - now it's just on a different part of the procedure. My solution to the previous problem was to use a CROSS JOIN to get the two tables linked together and then do a LEFT JOIN with the remaining table. That seems to have done the trick, but now I'm left with a little cleanup to do, at the end.

Is there a way to delete a number of rows based on the result of another query? For example, looking at my "nearly final" data, I want to do a query, which is going to give me a number of Patient IDs and Service Types, in pairs. Those are the records I want to delete. Is there a way to easily do that with a DELETE statement?

I'm not sure if I said that very clearly or not, so here's a quick example:

+--------------------------------------+
| Mickey Mouse | 1 | Type1 | 1/12/2005 |
| Mickey Mouse | 1 | Type2 | 1/15/2005 |
| Mickey Mouse | 1 | Type3 |   NULL    |
| Donald Duck  | 2 | Type1 | 1/18/2005 |
| Donald Duck  | 2 | Type2 |   NULL    |
| Donald Duck  | 2 | Type3 |   NULL    |
+--------------------------------------+

Now, I might do a query on this that returns this:

+-----------+
| 1 | Type3 |
| 2 | Type3 |
+-----------+

So, I'd like to quickly delete those rows and be left with something like this:

+--------------------------------------+
| Mickey Mouse | 1 | Type1 | 1/12/2005 |
| Mickey Mouse | 1 | Type2 | 1/15/2005 |
| Donald Duck  | 2 | Type1 | 1/18/2005 |
| Donald Duck  | 2 | Type2 |   NULL    |
+--------------------------------------+

Anyone know how to do that?

Thanks.

[Jayesh Lalwani]

There are couple of ways you can do this. I think the best way would be to use a NOT IN clause (It depends on the size of your tables. But if your second query returns only a couple of rows then NOT IN should work ok). But I don't know how to do NOT IN clause for multiple columns.

So, you might want to try using NOT EXISTS with a subquery

Not IN might work like this.

Select c.name, c.customer_id, st.type_id, sr.Date_Performed
from customers c inner join service_types st 
left outer join services_rendered sr on c.customerid = sr.customer_id and st.type_id = sr.type_id
where c.customer_id, st.type_id not in 
(
    Select customer_id, type_id
    from ......
)

NOT exists will work like this

Select c.name, c.customer_id, st.type_id, sr.Date_Performed
from customers c inner join service_types st 
left outer join services_rendered sr on c.customerid = sr.customer_id and st.type_id = sr.type_id
where not exists 
(
    Select customer_id , type_id 
    from ......
    where customer_id = c.customer_id and type_id = c.type_id
)

I think that should work. Although the subquery will run for every row in your main query

[Shailesh Chandra]

Hi Corey,

Jayesh has given you the suugestion, I want to add only on more option


let say you have made a query which result in data given below and assuming that you have query something like

 
select customer,code,service_type,service_date from .......where condition

and output is

+--------------------------------------+
| Mickey Mouse | 1 | Type1 | 1/12/2005 |
| Mickey Mouse | 1 | Type2 | 1/15/2005 |
| Mickey Mouse | 1 | Type3 |   NULL    |
| Donald Duck  | 2 | Type1 | 1/18/2005 |
| Donald Duck  | 2 | Type2 |   NULL    |
| Donald Duck  | 2 | Type3 |   NULL    |
+--------------------------------------+

now keeping one thing in mind that you need data with latest Date

you can make a query like

SELECT CUSTOMER,CODE,SERVICE_TYPE,MAX(SERVICE_DATE) FROM  
(SELECT CUSTOMER,CODE,SERVICE_TYPE,SERVICE_DATE FROM .......WHERE CONDITION)
GROUP BY CUSTOMER,CODE,SERVICE_TYPE

thanks
[ April 18, 2005: Message edited by: Shailesh Chandra ]