I'm using Crystal Reports 16 to generate reports for a customer and then use those reports in a software from another company. I'm not allowed to modify the database structure so I must do all data manipulation inside SQL Server's queries.

Most of the length in those reports are metric, but a few reports need a column with that length converted into imperial at 32th.

Here what I do in queries to convert those lengths

CASE
        WHEN a.PCE_LEN_FRACTION <= 16 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'
        WHEN a.PCE_LEN_FRACTION > 16 AND a.PCE_LEN_FRACTION <= 47 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 1/32'
        WHEN a.PCE_LEN_FRACTION > 47 AND a.PCE_LEN_FRACTION <= 79 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 1/16'
        WHEN a.PCE_LEN_FRACTION > 79 AND a.PCE_LEN_FRACTION < 110 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 3/32'
        WHEN a.PCE_LEN_FRACTION >= 110 AND a.PCE_LEN_FRACTION <= 141 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 1/8'
        WHEN a.PCE_LEN_FRACTION > 141 AND a.PCE_LEN_FRACTION < 172 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 5/32'
        WHEN a.PCE_LEN_FRACTION >= 172 AND a.PCE_LEN_FRACTION <= 204 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 3/16'
        WHEN a.PCE_LEN_FRACTION > 204 AND a.PCE_LEN_FRACTION < 235 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 7/32'
        WHEN a.PCE_LEN_FRACTION >= 235 AND a.PCE_LEN_FRACTION <= 266 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 1/4'
        WHEN a.PCE_LEN_FRACTION > 266 AND a.PCE_LEN_FRACTION < 297 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 9/32'
        WHEN a.PCE_LEN_FRACTION >= 297 AND a.PCE_LEN_FRACTION <= 329 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 5/16'
        WHEN a.PCE_LEN_FRACTION > 329 AND a.PCE_LEN_FRACTION < 360 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 11/32'
        WHEN a.PCE_LEN_FRACTION >= 360 AND a.PCE_LEN_FRACTION <= 391 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 3/8'
        WHEN a.PCE_LEN_FRACTION > 391 AND a.PCE_LEN_FRACTION < 422 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 13/32'
        WHEN a.PCE_LEN_FRACTION >= 422 AND a.PCE_LEN_FRACTION <= 454 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 7/16'
        WHEN a.PCE_LEN_FRACTION > 454 AND a.PCE_LEN_FRACTION < 485 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 15/32'
        WHEN a.PCE_LEN_FRACTION >= 485 AND a.PCE_LEN_FRACTION <= 516 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 1/2'
        WHEN a.PCE_LEN_FRACTION > 516 AND a.PCE_LEN_FRACTION < 547 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 17/32'
        WHEN a.PCE_LEN_FRACTION >= 547 AND a.PCE_LEN_FRACTION <= 579 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 9/16'
        WHEN a.PCE_LEN_FRACTION > 579 AND a.PCE_LEN_FRACTION < 610 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 19/32'
        WHEN a.PCE_LEN_FRACTION >= 610 AND a.PCE_LEN_FRACTION <= 641 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 5/8'
        WHEN a.PCE_LEN_FRACTION > 641 AND a.PCE_LEN_FRACTION < 672 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 21/32'
        WHEN a.PCE_LEN_FRACTION >= 672 AND a.PCE_LEN_FRACTION <= 704 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 11/16'
        WHEN a.PCE_LEN_FRACTION > 704 AND a.PCE_LEN_FRACTION < 735 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 23/32'
        WHEN a.PCE_LEN_FRACTION >= 735 AND a.PCE_LEN_FRACTION <= 766 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 3/4'
        WHEN a.PCE_LEN_FRACTION > 766 AND a.PCE_LEN_FRACTION < 797 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 25/32'
        WHEN a.PCE_LEN_FRACTION >= 797 AND a.PCE_LEN_FRACTION <= 829 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 13/16'
        WHEN a.PCE_LEN_FRACTION > 829 AND a.PCE_LEN_FRACTION < 860 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 27/32'
        WHEN a.PCE_LEN_FRACTION >= 860 AND a.PCE_LEN_FRACTION <= 891 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 7/8'
        WHEN a.PCE_LEN_FRACTION > 891 AND a.PCE_LEN_FRACTION < 922 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 29/32'
        WHEN a.PCE_LEN_FRACTION >= 922 AND a.PCE_LEN_FRACTION <= 954 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 15/16'
        WHEN a.PCE_LEN_FRACTION > 954 AND a.PCE_LEN_FRACTION < 985 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES as varchar)   '"'   ' 31/32'
        WHEN a.PCE_LEN_FRACTION >= 985 AND a.PCE_LEN_INCHES < 11 THEN
            CAST(a.PCE_LEN_FEETS as varchar)   ''' '   CAST(a.PCE_LEN_INCHES   1 as varchar)   '"'
        WHEN a.PCE_LEN_FRACTION >= 985 AND a.PCE_LEN_INCHES = 11 THEN
            CAST(a.PCE_LEN_FEETS  1 as varchar)   ''' 0"'
        ELSE ''
    END

It works ok for the amount of data I get from my query, but I use the same thing 4 times in a specific query.

In Crystal reports, I can't find any build-in founction to convert metric to imperial directly, which may improve performance much not having to do in into my query.

Does anyone know a more efficient way to convert metrics to imperials using SQL or Crystal Reports?

CodePudding user response：

If you first round to the nearest integral multiple of 1/32 inch, you can test for numerator divisible by 32, 16, 8, 4, and 2 to reduce your case statement down to a small handful. Each case would then calculate and format your fraction as wholes, halves, quarters, etc.

Having your fraction already separated from the whole and represented as 1/1000ths complicates things a bit, especially when handling the 32/32 round up case.

Below is an example that demonstrates two versions of a more compact fraction formatter with a range of generated data.

DECLARE @Data TABLE (
    Value Decimal(10,3),
    Inch INT,
    Thousands INT,
    ThirtySeconds INT,
    Carry INT,
    InchFraction VARCHAR(100),
    TotalThirtySeconds INT,
    InchFraction2 VARCHAR(100)
    )

-- Generate data from 0.950 thru 2.050
INSERT @Data (Value)
SELECT TOP 1101 (ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1) / 1000.000   0.950
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) D1(D)
CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) D2(D)
CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) D3(D)
CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) D4(D)

UPDATE @Data SET Inch = FLOOR(Value), Thousands = Value * 1000 % 1000

-- Calcuating from integer thousands to 32nds to fraction
UPDATE @Data SET ThirtySeconds = ROUND(Thousands * 32.0 / 1000, 0)
UPDATE @Data SET Carry = CASE WHEN ThirtySeconds = 32 THEN 1 ELSE 0 END
UPDATE @Data SET InchFraction = CONVERT(VARCHAR(10), Inch   Carry)
      CASE
      WHEN ThirtySeconds 2 = 0 THEN ''
      WHEN ThirtySeconds  = 0 THEN ' '   CONVERT(VARCHAR(10), ThirtySeconds / 16)   '/2'
      WHEN ThirtySeconds %8 = 0 THEN ' '   CONVERT(VARCHAR(10), ThirtySeconds / 8)   '/4'
      WHEN ThirtySeconds %4 = 0 THEN ' '   CONVERT(VARCHAR(10), ThirtySeconds / 4)   '/8'
      WHEN ThirtySeconds %2 = 0 THEN ' '   CONVERT(VARCHAR(10), ThirtySeconds / 2)   '/16'
      ELSE ' '   CONVERT(VARCHAR(10), ThirtySeconds)   '/32'
      END

-- Calculation direct from Value
UPDATE @Data SET TotalThirtySeconds = Round(Value * 32, 0)
UPDATE @Data SET InchFraction2 = CONVERT(VARCHAR(10), TotalThirtySeconds / 32)
      CASE
      WHEN TotalThirtySeconds 2 = 0 THEN ''
      WHEN TotalThirtySeconds  = 0 THEN ' '   CONVERT(VARCHAR(10), TotalThirtySeconds % 32 / 16)   '/2'
      WHEN TotalThirtySeconds %8 = 0 THEN ' '   CONVERT(VARCHAR(10), TotalThirtySeconds % 32 / 8)   '/4'
      WHEN TotalThirtySeconds %4 = 0 THEN ' '   CONVERT(VARCHAR(10), TotalThirtySeconds % 32 / 4)   '/8'
      WHEN TotalThirtySeconds %2 = 0 THEN ' '   CONVERT(VARCHAR(10), TotalThirtySeconds % 32 / 2)   '/16'
      ELSE ' '   CONVERT(VARCHAR(10), TotalThirtySeconds  % 32)   '/32'
      END

-- Show summary
SELECT FromValue = MIN(Value), ThruValue = MAX(Value), InchFraction, InchFraction2
FROM @Data
GROUP BY InchFraction, InchFraction2
ORDER BY MIN(Value)

-- Show details including intermediate values
SELECT *
FROM @Data
ORDER BY Value

Partial Results:

See