Joining two tables with nested queries!

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)

Joining two tables with nested queries!

Author

Topic

scottjamieson
Starting Member

2 Posts

Posted - 2004-04-22 : 06:45:40

Hello!

Your expert help is needed for a newbie to SQL!

The point of this query is to give an hourly breakdown report which counts the reasons in which customers require call backs. I have the following two tables with the relationship being between the customer_id.

Customer Table:
customer_id Int 4
batch_number varchar 5

Callback Table:
customer_id Int 4
date_called date_time 8
reason varchar 50
preferred_time varchar 50
agent varchar 50

I have the following query which seems to work okay but as I need to query only bath_number = 'S001' I need to add the customers table - This is where I get stuck, and am not quite sure how to add it. (I am a beginner!!!!)

Query:

SELECT DISTINCT reason,
(SELECT COUNT(*)
FROM Callback s2
WHERE s2.reason = s1.reason AND (CONVERT(VARCHAR(10), date_called, 112) = CONVERT(VARCHAR(10), GETDATE() - 1, 112)) AND
(CONVERT(VARCHAR(10), date_called, 108) < '11:00')) AS slot1,
(SELECT COUNT(*)
FROM Callback s2
WHERE s2.reason = s1.reason AND (CONVERT(VARCHAR(10), date_called, 112) = CONVERT(VARCHAR(10), GETDATE() - 1, 112)) AND
(CONVERT(VARCHAR(10), date_called, 108) < '11:00') AND (CONVERT(VARCHAR(10), date_called, 108) < '12:00')) AS slot2,
(SELECT COUNT(*)
FROM Callback s2
WHERE s2.reason = s1.reason AND (CONVERT(VARCHAR(10), date_called, 112) = CONVERT(VARCHAR(10), GETDATE() - 1, 112)) AND
(CONVERT(VARCHAR(10), date_called, 108) >= '12:00') AND (CONVERT(VARCHAR(10), date_called, 108) < '13:00')) AS slot3,
(SELECT COUNT(*)
FROM Callback s2
WHERE s2.reason = s1.reason AND (CONVERT(VARCHAR(10), date_called, 112) = CONVERT(VARCHAR(10), GETDATE() - 1, 112)) AND
(CONVERT(VARCHAR(10), date_called, 108) >= '13:00') AND (CONVERT(VARCHAR(10), date_called, 108) < '14:00')) AS slot4,
(SELECT COUNT(*)
FROM Callback s2
WHERE s2.reason = s1.reason AND (CONVERT(VARCHAR(10), date_called, 112) = CONVERT(VARCHAR(10), GETDATE() - 1, 112)) AND
(CONVERT(VARCHAR(10), date_called, 108) >= '14:00') AND (CONVERT(VARCHAR(10), date_called, 108) < '15:00')) AS slot5,
(SELECT COUNT(*)
FROM Callback s2
WHERE s2.reason = s1.reason AND (CONVERT(VARCHAR(10), date_called, 112) = CONVERT(VARCHAR(10), GETDATE() - 1, 112)) AND
(CONVERT(VARCHAR(10), date_called, 108) >= '15:00') AND (CONVERT(VARCHAR(10), date_called, 108) < '16:00')) AS slot6,
(SELECT COUNT(*)
FROM Callback s2
WHERE s2.reason = s1.reason AND (CONVERT(VARCHAR(10), date_called, 112) = CONVERT(VARCHAR(10), GETDATE() - 1, 112)) AND
(CONVERT(VARCHAR(10), date_called, 108) >= '16:00') AND (CONVERT(VARCHAR(10), date_called, 108) < '17:00')) AS slot7,
(SELECT COUNT(*)
FROM Callback s2
WHERE s2.reason = s1.reason AND (CONVERT(VARCHAR(10), date_called, 112) = CONVERT(VARCHAR(10), GETDATE() - 1, 112)) AND
(CONVERT(VARCHAR(10), date_called, 108) >= '17:00') AND (CONVERT(VARCHAR(10), date_called, 108) < '18:00')) AS slot8,
(SELECT COUNT(*)
FROM Callback s2
WHERE s2.reason = s1.reason AND (CONVERT(VARCHAR(10), date_called, 112) = CONVERT(VARCHAR(10), GETDATE() - 1, 112)) AND
(CONVERT(VARCHAR(10), date_called, 108) >= '18:00') AND (CONVERT(VARCHAR(10), date_called, 108) < '19:00')) AS slot9,
(SELECT COUNT(*)
FROM Callback s2
WHERE s2.reason = s1.reason AND (CONVERT(VARCHAR(10), date_called, 112) = CONVERT(VARCHAR(10), GETDATE() - 1, 112)) AND
(CONVERT(VARCHAR(10), date_called, 108) >= '19:00')) AS slot10,
(SELECT COUNT(*)
FROM Callback s2
WHERE s2.reason = s1.reason AND (CONVERT(VARCHAR(10), date_called, 112) = CONVERT(VARCHAR(10), GETDATE() - 1, 112))) AS slot11
FROM dbo.Callback s1
WHERE (CONVERT(VARCHAR(10), date_called, 112) = CONVERT(VARCHAR(10), GETDATE() - 1, 112))

This returns the following results:
Reason Slot 1 Slot 2 Slot 3 Slot 4 Slot 5 Slot 6 Slot7 Slot8 Slot9 Slot10 Slot11
Answer Machine 0 0 60 181 125 179 98 97 50 71 861
Decision maker not available 0 0 0 3 0 0 1 8 5 4 21
Engaged 0 0 4 9 4 6 5 7 13 21 71
No Answer 0 0 54 188 134 180 56 57 38 65 772
Scheduled Callback 0 0 8 18 14 14 21 35 26 38 174

If anyone could offer advice of how to add the 'INNERJOIN' effectively on customer_id so batch_number = 'S001' I would be so grateful!

Thank you in advance.

Scott

mtomeo
Starting Member

30 Posts

Posted - 2004-04-22 : 08:29:55

I'm sure there's an easier way to write your query (using an inline view and a Case statement perhaps?), but I believe this should work...add to your final From/Where:

FROM dbo.Callback s1, dbo.Customer c1
WHERE (CONVERT(VARCHAR(10), date_called, 112) = CONVERT(VARCHAR(10), GETDATE() - 1, 112))
and s1.customer_id = c1.customer_id
and c1.batch_number = 'S001'

scottjamieson
Starting Member

2 Posts

Posted - 2004-05-07 : 09:00:15

Thank you for your help on this. Could you give me any direction on how I could do this where batch_number is a parameter, which would be from a different table.

Table Name: Batches
Field Name:BatchNumber
Values: S001, S002 S003 etc

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources