Translate Ruby Script to SQL Query?

5 pts.
Tags:
Microsoft SQL Server 2000
Ruby
SQL queries
SQL Query
SQL scripts
I need help translating a ruby script to SQL query. I'm a SQL noob. Help please.

[em]here is the script.[/em]

require "rubygems" require "ruport"

SQL = <<-EOS SELECT     tv.ApptDate, tv.PatName, tv.PatFirst, tv.PatLast, tv.Phone, tv.ApptTime, tv.Provlast, tv.ProvFirst, tv.ProvTitle, tv.Clinic, tv.Age, tv.Notes FROM (

 /* SECOND LEVEL, this brings out cliniccode from the main query so we can test for new patients against it in the case statements here */

 SELECT     dt.ApptDate, dt.PATNAME,

  (case    when firstname IS NULL THEN substring(patname, charindex(',', patname) + 2, 99)    else firstname END) AS PatFirst,

  (case   when lastname IS NULL THEN substring(patname, 1, charindex(',', patname) - 1)                                        else lastname END) AS PatLast,

  dt.PHONE, dt.ApptTime, dt.ProvLast, dt.ProvFirst, dt.ProvTitle, dt.Clinic,  pat.MEDALERT,

  (case    when birthdate < GETDATE()-23725 then '2' Else '1' End) As Age,

  (case    when exists    (select patid, adacode, clinicappliedto     from dbo.ddb_proc_log pl1 inner join dbo.ddb_proc_code pc1 on pl1.proc_codeid = pc1.proc_codeid and pl1.proc_codedb = pc1.proc_codedb     where dt.patid = pl1.patid and substring(pc1.adacode,1,1)='D' and pl1.clinicappliedto = dt.cliniccode) then ' '     when exists    (select patid, adacode     from dbo.ddb_proc_log pl1 inner join dbo.ddb_proc_code pc1 on pl1.proc_codeid = pc1.proc_codeid and pl1.proc_codedb = pc1.proc_codedb     where dt.patid = pl1.patid and substring(pc1.adacode,1,1)='D' ) then 'NClin'    Else 'New' END) As Notes

    FROM           /* Core of the query is here. */   (SELECT     CONVERT(char(10), a.APPTDATE, 101) AS ApptDate, a.PATNAME, a.PHONE,          CAST(a.TIME_HOUR AS varchar(2))  + ':' + RIGHT('00' + CAST(a.TIME_MINUTE AS varchar(2)), 2) AS ApptTime,          dbo.DDB_RSC_BASE.NAME_LAST AS ProvLast,  dbo.DDB_RSC_BASE.NAME_FIRST AS ProvFirst,          dbo.DDB_RSC_BASE.NAME_TITLE AS ProvTitle, patid,                                                   (SELECT     rscid                                                     FROM          dbo.ddb_rsc_base                                                     WHERE      rsctype = 0 AND urscid =                                                                                (SELECT     defaultclinic                                                                                  FROM          dbo.ddb_rsc_base                                                                                  WHERE      urscid = a.opid AND rsctype = 3)) AS Clinic,                                                   (SELECT     defaultclinic                                                     FROM          dbo.ddb_rsc_base                                                     WHERE      urscid = a.opid AND rsctype = 3) AS ClinicCode   FROM          dbo.DDB_APPT a INNER JOIN dbo.DDB_RSC_BASE ON a.PRPROVID = dbo.DDB_RSC_BASE.URSCID                           WHERE      (a.APPTDATE =CONVERT(varchar, GETDATE()+2, 101) )) dt   /* end of core query -- derived table is dt*/

  INNER JOIN dbo.DDB_PAT pat ON pat.PATID = dt.patid) tv

  /* end of second level, using cliniccode from Core query with case statement to make note field; we also join ddb_pat here and bring in firstname, lastname and medalert */   WHERE  not (provlast=patlast and provfirst = patfirst)  and not (provlast = patlast and (patfirst = 'Dr. '+ substring (patfirst,5,len(patfirst)))) and not (provlast+ ' D.D.S' = patlast and patfirst = provfirst) and not (len(isnull(phone,' '))<7) and not (clinic = 'KB')

EOS

Ruport::Config.source :default, :dsn => "dbi:odbc:dentrix", :user => "enterprise", :password => "d123"

Ruport::Report.new.instance_eval do   @report = Ruport::Query.new(SQL).result.to_csv   @file   = ARGV[0] || "televox.csv"   generate_report end

 

 

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Well, that seems to be a script that executes a SQL query. What exactly do you need help with ?
    69,410 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following