To JOIN, UNION, or what? - 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 2005 Forums

Other SQL Server Topics (2005)

To JOIN, UNION, or what?

Author

Topic

Jay C
Starting Member

6 Posts

Posted - 2009-02-26 : 22:05:19

Guys, I have an interesting, and to me at least, very challenging situation.

There are two tables in my situation. The first table is the dialer table. When a phone call is made, a row is inserted on whether of not the call reached anyone (no this isn't a telemarketing thing LOL). This is an automated reminder system. If the dialer reaches a voice or answering machine, it inserts a row into another table (transfer_table). It then requests a route for the the call to my script to play the appointment time. The inserts another row in the transfer table. These two rows in the transfer table are keyed to the dialer table via a foreign key (dialer_fk). Where it gets hard is as it goes through my script is inserts another row that shows script status, and a final row when the call is disconnected.

Each row in the transfer table has a duration column along with a PK, and a FK for 3 of the four rows related to a single call. The first row inserted is null for this FK (it's not a true foriegn key). I need to get the dialer detail columns, and the durations and call dispositions from the transfer_table to "see" the whole call.

So can anyone offer a direction I should explore? I plan to extract the data from these two tables into a third for reporting purposes. I'd like to do it in one statement but my fallback is to use some PHP arrays and multiple queries ... I'd rather a clever solution though :D

I appreciate your input,
Jay

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-02-27 : 03:52:58

Can you post some DDL and sample data? You'll get a more thorough answer that way.

Jay C
Starting Member

6 Posts

Posted - 2009-02-27 : 12:57:46

[code]
SELECT RecoveryKey, TimeZone, CustomerTimeZone, DbDateTime,
CallResult, Phone,PCallKey, AccountNumber
FROM Dialer_table
WHERE (DateTime > '2/26/2009');

RecoveryKey TZ CTZ Date.Time Result Phone PCallKey Acct #
============ === === ==================== ===== ========= ======== =======
516878856122 360 360 2/26/2009 1:43:58 PM 10 9034335555 16780495 1|4|1215

SELECT RecoveryKey, RCallKeyDay, RCallKey, DateTime,
PCallKey, Disposition, Time, V2, V3, ICRCallKey,
ICRCallKeyParent, ICRCallKeyChild
FROM Call_script_table
WHERE (DateTime > '2/26/2009')

...
...
RecoveryKey RCKD RCK Date Time PCallKey Disp Time V2 V3 ICRCK ICRP ICRC
============ ===== === ==================== ======== ==== ==== ==== ==== ===== ==== ====
516878856452 0 0 2/26/2009 1:41:59 PM 16780495 28 20 NULL NULL 48732 NULL 48733
516878856453 149075 217 2/26/2009 1:41:59 PM 16780495 7 0 NULL NULL 48733 48732 NULL
516878856454 149075 217 2/26/2009 1:42:04 PM 331 13 24 RRG NULL 48734 NULL NULL
516878856455 149075 217 2/26/2009 1:42:04 PM 332 13 27 NULL NULL 48735 NULL NULL
...
...
[/code]
I want to sum the times in Call_script_table.

Jay

Jay C
Starting Member

6 Posts

Posted - 2009-02-27 : 21:56:10

What I'd like to get is:


Key     Date      StartTime   Disp  Dur  V2   V3  Result   Phone       Acct # 
======  ========= =========== ==== ==== ==== ==== =====  ==========  =========
5001    2/26/2009 1:41:59 PM   13   47  RRG  NULL  10    9034335555  1|4|1215

The Key is just a autonumber sequence. The duration is the sum of the first and last rows (20 + 27) in the Call_script_table. The last 3 columns come from the Dialer_table. V3 shows NULL, it should be V4 but rather than edit the previous emails I'll leave it. V2 is optional and just shows me the last audio file played. What's important to me is is they heard the day date and time which is the value I place in V4.

Any takers?

Jay

Jay C
Starting Member

6 Posts

Posted - 2009-02-28 : 00:50:46

Is there anything else I need to supply to help explain/show the issue? Would I be better off using a inner join between the Dialer_detail, and the Call_script table. Then following it up with another query to get the other 2 rows related to the RCKD RCK pair.

Thanks for looking,
Jay

Jay C
Starting Member

6 Posts

Posted - 2009-03-02 : 15:35:46

Just an update since I have 65 reads and 1 relpy :)

I am planing to use PHP and the php_sqlsrv dll. I have it connecting now (a lot has changed from the mssql driver BTW). I'll update again when I have it doing what I want.

Jay

Jay C
Starting Member

6 Posts

Posted - 2009-03-05 : 17:58:32

As promised here is the update. I used 3 nested queries to get the data as I needed.

FWIW,
Jay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources