?
Solved

Convert Visual Foxpro to SQL query

Posted on 2010-01-12
4
Medium Priority
?
643 Views
Last Modified: 2012-05-08
Hi,

I'm trying to convert the query below to SQL and could do with a little help. Anyone good with Visual Foxpro to SQL coding? :)

*** Calculate (NEW) CPH
*** The allarea file might be very big. We need to do some tests to work out if we should split it up for calculations

INDEX ON TTOD(time) TAG cphmaster
SET ORDER TO cphmaster
SELECT TTOD(time) as date FROM allarea GROUP BY 1 INTO CURSOR cphdates READWRITE
SELECT cphdates
COUNT FOR date<=DATE() TO ln_numberofdates
ln_datecount = 0
SCAN
      *** Update the progres bar and info
      ln_datecount = ln_datecount + 1
      thisform.txtInfo.Caption = "Calculating Shift ID's for "+ALLTRIM(DTOC(cphdates.date))
      thisform.shape3.Width = (ln_datecount/ln_numberofdates)*264
      thisform.shape4.Width = 120+((ln_datecount/ln_numberofdates)*264)/2.2
     
      SELECT allarea
      SELECT * from allarea WHERE TTOD(time) = cphdates.date INTO CURSOR datearea READWRITE
     
      *** Calculate which shift ID applies to each record
      SCAN
            lc_callgroup = IIF('_aa'$caller,'CRD',IIF('_'$caller,'BTN','')) && Work out which calling group this record is in
            lc_dow = DOW(TTOD(datearea.time)) && The day of week for this record
            lt_thistime = CTOT('01/01/2000 '+SUBSTR(TTOC(datearea.time),AT(' ',TTOC(datearea.time))+1)) && The time of this record
            *** Assign each record it's correct shiftid from new_shifttime
            SELECT new_shifttime
            LOCATE FOR lc_callgroup = new_shifttime.callgroup AND lt_thistime >= new_shifttime.shiftstart AND lc_dow = new_shifttime.dow
            IF FOUND()
                  REPLACE datearea.shiftid WITH new_shifttime.shiftid
            ELSE
                  && This call was made outside of normal shift times, don't calculate
            ENDIF
      ENDSCAN
     
      *** Index the file for calculating CPH
      SELECT datearea
      INDEX ON ALLTRIM(UPPER(caller))+ALLTRIM(STR(shiftid))+ALLTRIM(TTOC(time)) TAG cphindexer
      SET DECIMALS TO 5

      *** Create a list of individual shifts
      SELECT caller, shiftid, TTOD(time) as cdate FROM datearea GROUP BY 1,2,3 INTO CURSOR cphshifts
      lc_cphscanconds = IIF(ll_cumulativecph,'shiftid>0',IIF(ll_7daycph,'shiftid>0 AND cdate >= DATE()-7','cdate>DATE()'))
      SELECT cphshifts
      *** Scan through the caller/shifts file (only scan for shifts in the last 7 days if ll_7daycps is set)
      SCAN FOR &lc_cphscanconds
            thisform.txtInfo.Caption = "Calculating CPH for "+ALLTRIM(DTOC(cphdates.date))+" ("+ALLTRIM(STR(ROUND((RECNO()/RECCOUNT())*100,0)))+"%)"
            SELECT datearea
            SELECT caller, time, 00000.00000 as cphtime, shiftid FROM datearea WHERE datearea.caller = cphshifts.caller AND datearea.shiftid = cphshifts.shiftid AND TTOD(datearea.time) = cphshifts.cdate ORDER BY 2 INTO CURSOR cphcaller READWRITE
            SELECT new_shifttime
            LOCATE FOR cphcaller.shiftid = new_shifttime.shiftid && Find this so we can refrence for the first and last records
           
            *** Decide when we should be calculating times from / to
            IF CTOT("01/01/2000 "+lc_reportstarttime) > new_shifttime.shiftstart AND (ll_runwithtimerange OR ll_runwithdaterange)
                  lt_calcmindate = CTOT("01/01/2000 "+lc_reportstarttime)
            ELSE
                  lt_calcmindate = new_shifttime.shiftstart
            ENDIF
            IF CTOT("01/01/2000 "+lc_reportendtime) < new_shifttime.shiftend AND (ll_runwithtimerange OR ll_runwithdaterange)
                  lt_calcmaxdate = CTOT("01/01/2000 "+lc_reportendtime)
            ELSE
                  lt_calcmaxdate = new_shifttime.shiftend
            ENDIF
           
           
            *** Decide if break times will apply for this shift
            IF CTOT("01/01/2000 "+lc_reportstarttime) < new_shifttime.breakstart AND CTOT("01/01/2000 "+lc_reportendtime) > new_shifttime.breakend AND (ll_runwithtimerange OR ll_runwithdaterange)
                  ll_includebreak=.t.
                  lt_breakmindate = new_shifttime.breakstart
                  lt_breakmaxdate = new_shifttime.breakend
            ELSE
                  ll_includebreak=.f.
            ENDIF
           
           
            SELECT cphcaller
            INDEX ON ALLTRIM(UPPER(caller))+ALLTRIM(STR(shiftid))+ALLTRIM(TTOC(time)) TAG cphindexer
            SCAN
                  SELECT datearea
                  SEEK ALLTRIM(UPPER(cphcaller.caller))+ALLTRIM(STR(cphcaller.shiftid))+ALLTRIM(TTOC(cphcaller.time))
                  IF FOUND()
                        SELECT cphcaller
                        lt_thistime = CTOT('01/01/2000 '+SUBSTR(TTOC(cphcaller.time),AT(' ',TTOC(cphcaller.time))+1))
                        DO CASE && This is where the CPH for the individual record will be calculated
                              CASE RECNO()=1 && Exception for the first record
                                    IF lt_thistime <= lt_calcmindate+(ln_cphthreshold*60)
                                          ln_thiscph = lt_thistime - lt_calcmindate && Calculate from the start of the shift
                                    ELSE
                                          ln_thiscph = 0 && We cannot determine an appropiate CPH time for this record
                                    ENDIF
                                   
                              CASE RECNO()=RECCOUNT() && Exception for the last resord
                                    IF lt_thistime >= lt_calcmaxdate-(ln_cphthreshold*60)
                                          ln_thiscph = (lt_calcmaxdate - lt_thistime) + (lt_thistime - lt_lasttime) && Calculate to the end of the shift
                                    ELSE
                                          ln_thiscph = 0 && Do the normal thing
                                    ENDIF
                                   
                              OTHERWISE && For all other records
                                    ln_thiscph = lt_thistime - lt_lasttime
                                    IF ln_thiscph>(ln_cphthreshold*60) && The CPH has exceled the allowed amount of time, check if there has been a break!
                                          IF ll_includebreak AND lt_lasttime<(lt_breakmindate+500) AND lt_thistime>(lt_breakmindate-500) AND ((lt_thistime-lt_lasttime)-(lt_breakmaxdate-lt_breakmindate))<(ln_cphthreshold*60)
                                                && This is the first call after a break, OK to have high CPH
                                          ELSE
                                                ln_thiscph=0 && The caller has been off the phone for longer than the threshold. Discard this amount of time
                                          ENDIF
                                    ENDIF
                        ENDCASE
                        lt_lasttime = lt_thistime && Keep track of the last calculated time so we can use it for the next record
                        *** ALSO DO SUMMINK WITH THE BREAKTIME FIELDS TO ALLOW THE CPH TO BE CARRIED OVER
                        &&CTOT("01/01/2010 10:13:09") - CTOT("01/01/2010 10:00:00")
                 
                        SELECT datearea
                        REPLACE datearea.cphtime WITH IIF(ln_thiscph>0,ln_thiscph / 3600,datearea.calltime)
                  ENDIF
                  SELECT cphcaller
            ENDSCAN
      ENDSCAN
      *** Remove the data we have just calculated for and replace with the updates data
      *** This will be faster than updating the existing data
      SELECT allarea
      DELETE FOR TTOD(TIME) = cphdates.date
      APPEND FROM DBF('datearea')
      SELECT datearea
      USE
ENDSCAN
0
Comment
Question by:Betamax
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 16

Expert Comment

by:Auric1983
ID: 26303151
Betamax,

Is the new table in a SQL Database?  If you can provide a sample excel spreadsheet with 1. Your column layout in the table, 2. description of calculations done. and 3. what you want the end result to be I can probably do something.

My Foxpro is pretty rusty, but it looks like you are looping through a dataset to check if breaks apply, what shift ID it is etc.  

0
 

Author Comment

by:Betamax
ID: 26306206
Thanks Auric1983,

Yep I should've included much more info than I did! This query calculates how much time has been spent calling a campaign (that raises money for charities) by each caller working on that campaign. It uses an allarea.dbf that some other foxpro code puts together. I'll try and get an example for you of that.
Any help you can give me is greatly appreciated!
0
 

Author Comment

by:Betamax
ID: 26347988
Rather than convert the code above I think I've got a better way of doing the calculation I require.

What I need is to populate a new table with data to do calculations off. The new table is called CPHcalc and has the columns CampaignID, UserName, Date_time, CPHtime.
The data to populate CPHcalc needs to come from a set of tables named telcom001 - telcom999. The CampaignID is the last 3 digits of each telcom table. Each individual table has the Username of each campaign caller, ana a dat_ column and a time_column. I'd like the dat_and time_ columns merged into a single Date_time column in the CPHcalc table.
CPHtime is the difficult one as I need to work out how much time each user has been on the phone for each campaign. For example if on 20/04/2009 Ben has been working on telcom333 and between 12:30 and12:45 he was on the phone, then I need the entry in CPHcalc to be something like:
CampaignID - 333
UserName - Ben
Date_time - 20/04/2009 12:30
CPHtime - 00:15

And then Ben is on the phone to someone else between 12:45 and 14:05 then it will be something like this.
CampaignID - 333
UserName - Ben
Date_time - 20/04/2009 12:45
CPHtime - 00:20

The code copied here returns the following information.

A list of all calls made by 'dianew_pt258'  when working on telcom333 between 17:00 and 21:00

24 - the total of contacts. (CPH = contacts per hour)

So in 4 hours she made 24 contacts meaning her CPH for campaign 333 is 6!

Hope that makes sense and someone can help me. Please. :)



--One user for the 17:00 to 21:00 shift
select * from sysdba.telcom333 where username = 'dianew_pt258' --and customerstatus = 'contact'
and dat_ = '2009-04-20 00:00:00.000'
and cast(left(time_, 2) as int) >= 17 and cast(left(time_, 2) as int) <= 21
order by dat_, time_

select count(customerstatus) from sysdba.telcom333 where username = 'dianew_pt258' and customerstatus = 'contact'
and dat_ = '2009-04-20 00:00:00.000'
and cast(left(time_, 2) as int) >= 17 and cast(left(time_, 2) as int) <= 21

Open in new window

0
 

Accepted Solution

by:
Betamax earned 0 total points
ID: 26524173
Got it working!
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question