November 8, 2007 at 6:51 am
Hello all,
Not sure if this is the best sub-forum to ask this, admin’s feel free to move it if not.
My background is accountancy, but I'm gradually learning about SQL (be gentle, please!), as I'm having to write a lot of Reporting Services reports. I'm coming a bit unstuck with this problem. I work for a communications company. We have 2 unrelated databases (SQL2000): one contains billing information we receive from 3rd party providers, process and bill/pass on; the other contains records for emails (like sender, recipient, size, etc.) sent by our systems. They are basically unrelated, discreet databases.
I need to find a way to match the call records from the billing table (or my view of it) to a view of the mailbox records. They have some ‘commonality’. Both views have fields for company ID and user ID. That is where it stops though. The only other means of matching the records is by date and time. The billing records contains the date and start time that the device dialled our server (it is a satellite modem). The mailbox record gives the date/time the time our server finished ‘moving’ the data.
There are occasions when the unit dials up but no data is moved, so I have a billing record but no mailbox record to match.
Best short example I can give:
BILLING RECORDS MAILBOX RECORDS
CUST USER DATETIME CUSTOMER USER DATE/TIME
ABC01 U001 01/10/2007 15:00 = ABC01 U001 01/10/2007 15:01
ABC01 U001 01/10/2007 15:10 = ABC01 U001 01/10/2007 15:12
ABC01 U001 01/10/2007 15:20 = Nothing sent
ABC01 U001 01/10/2007 15:30 = ABC01 U001 01/10/2007 15:35
Effectively, the only basis I have to match the tables is the where record in the mailbox table matches the billing record when it is the ‘nearest’ time after the billing record.
Anybody got any suggestions?
Thanks!
November 8, 2007 at 7:18 am
See if this logic fits - it sounds like you know the call-in frequency (in the case below - 10 minutes) and/or the maximum time of a call. Either way - you should only match up to a MAILBOX record that is dated after the current billing datetime and the "next" billing time.
So - assuming you have a BILLING table and a MAILBOX table (you should provide some "real" table and field names if you want specific SQL), it could look something like
Declare @CallInFrequency int
set @CallInFrequency=10 --in minutes
select BILLING.CUST, Billing.USER,Billing.DateTimeMarker, MAILBOX .CUST, MAILBOX .USER,MAILBOX .DateTimeMarker
from
BILLING Left outer join MAILBOX
on Billing.CUST=MAILBOX.CUST and BILLING.USER=MAILBOX.USER
where MAILBOX.DateTimeMarker BETWEEN BILLING.DATETIMEMARKER and dateadd(mi,@CallInFrequency,BILLING.DATETIMEMARKER) /*match based on those records falling within the range*/
or MAILBOX.DateTimeMarker is null
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 8, 2007 at 7:37 am
Hi Matt,
Sorry, I just made those times up. There is frequency no rule for dialing in. The calls times are random. The mail users are at sea using a satellite terminal connected to a PC. They dial up maybe 10 or 20 times a day, generally when they have something to send out (the emails can go in a batch); if there is anything waiting shore-side, that is picked up and delivered in at the same time. Fact is, they're probably dialling in hoping to see something. They're stuck on a ship after all.
However, it is important to the customer (the shipmanagement company) to see what emails are sent/received per call. The satellite circuit is very expensive - think of dial-up internet at $1.00/minute or more...:blink:
Is there a way to paste table/view content easily on these boards?
November 8, 2007 at 8:11 am
your best bet would likely be to put your data or a sampling in a code block. in the create reply screen, click on IFCODE up top, and pick the CODE markup. Put some thing in there - it helps a LITTLE (Steve and co. are still working out the kinks.)
There are a few strategies which could be used. For example - if you had another field on your BILLING table for the "period end" which we could update with the insertion time from the NEXT BILLING record for that cust/user combination, then the logic below would still work.
Post some specifics, and see if the above helps.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 8, 2007 at 10:08 am
I've tried to upload a spreadsheet with an example of what I mean. I can match the call records 'manually' with the application of common sense. I'm just not sure SQL can cope with it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply