Translating Crystal Syntax to SQL difficulties.

• lovelevel60

  SSC Rookie

  Points: 48

  More actions

  October 16, 2018 at 9:05 am

  #379645

  Hey All,
  Hoping to draw on some of the expertise on here!
  Currently struggling to translate the following Crystal Reports syntax into SQL.

  Here is an example of the data:
  
  As you can (hopefully) see the quantity text column (renamed) contains numbers and text , I tried just delimiting the quantity_text column to give me the correct quantity that I can use in pivots etc but this came with its own issue in that if you look at the first row we have an issue of 2x10pack ampoules and if you then look at the fourth row we have an issue of 10xampoules. This represents the issuer issuing packs or single ampoules (occurs a lot).
  If I delimit this column I get:
  2
  1
  10
  10
  Which totals at 23 ampoules , when infact it is 50ampoules (the 2 and 1 are packs of 10)
  In Crystal syntax this is the solution, create a containers and a dose units field:
  

  The following two formulas convert the quantity field into doseunits or containers and net the value of any returns.

  Net quantity in containers (xdailyissues)

  NumberVar qty;
  if instr({xdailyissues.quantity},{xdailyissues.drug_packsize})>0 thenqty:=val({xdailyissues.quantity}) else
  qty:=val({xdailyissues.quantity})/{xdailyissues.doseunit_per_pack};
  qty-{xdailyissues.container_returns}-({xdailyissues.doseunit_returns}/{xdailyissues.doseunit_per_pack})

  Net quantity in dose units (xdailyissues)

  NumberVar qty;
  if instr({xdailyissues.quantity},{xdailyissues.drug_packsize})=0 thenqty:=val({xdailyissues.quantity}) else
  qty:=val({xdailyissues.quantity})*{xdailyissues.doseunit_per_pack};
  qty-({xdailyissues.doseunit_per_pack}*{xdailyissues.container_returns})-{xdailyissues.doseunit_returns}

  
  This is how the tables/fields look in SQL:
  Select

  [quantity] AS [quantity_text]

  ,[doseunit_per_pack]

  ,[drug_packsize]

  ,[drug_name]

  FROM xdailyissues

  I guess the basic principle of the formula is to look at xdailyissues_quantity , minus the xdailyissues.drug_packsize text (thus grabbing the value) then if the remaining value is over 0 it returns the value. Otherwise the result is xdailyissues_quantity divided by the xdailyissues.doseunit_per_pack which would give us the dose units (albeit in a decimal - but then this dose make it easily distinguishable)

  Any help would be greatly appreciated - have a few good people really stuck on translating this to SQL.

  Cheers,

• Ken McKelvey

  SSCoach

  Points: 18863

  More actions

  October 16, 2018 at 10:13 am

  #2009912

  I am not quite sure what you are doing but the following may help.
  
-- *** Test Data ***
CREATE TABLE #t
(
    quantity_text varchar(255) NOT NULL
);
INSERT INTO #t
VALUES ('2 x 10 X 1 ml ampoule Carton')
    ,('1 x 10 X 1 ml ampoule Carton')
    ,('10 X 1 ml ampoule');
-- *** End Test Data ***

  SELECT T.quantity_text
    ,REVERSE(RTRIM(LEFT(R.Rquantity_text, X1.Pos - 1))) AS drug_doseunit
    ,R.Rquantity_text
    ,CASE
        WHEN X2.Pos = 0
        THEN T.quantity_text
        ELSE REVERSE(RTRIM(LEFT(R.Rquantity_text, X2.Pos - 1)))
    END AS drug_packetsize
    ,V.packsize
    ,V.packno
    ,V.packsize * V.packno AS ampoules
FROM #t T
    CROSS APPLY (VALUES(REVERSE(T.quantity_text))) R(Rquantity_text)
    CROSS APPLY (VALUES(CHARINDEX('X', R.Rquantity_text))) X1(Pos)
    CROSS APPLY (VALUES(CHARINDEX('X', R.Rquantity_text, X1.Pos + 1))) X2(Pos)
    CROSS APPLY
    (
        VALUES
        (
            CAST(REVERSE(SUBSTRING(R.Rquantity_text, X1.Pos + 1, CASE X2.Pos WHEN 0 THEN 255 ELSE X2.Pos END - X1.Pos - 1)) AS int)
            ,CASE
                WHEN X2.Pos = 0
                THEN 1
                ELSE CAST(REVERSE(SUBSTRING(R.Rquantity_text, X2.Pos + 1, 255)) AS int)
            END
        )
    ) V (packsize, packno);