asked on

Crystal report - sql command

I am creating a command to use as input in a Crystal Report. I have some fields that are not defined as numeric that I want to sum. How can I change them to numeric. See code. QTY_ON_HN and AVG_Cost are not defined as numeric. The reason I am doing this command is: Some of the items will move from one warehouse to another, so the begin and end will be different. Those items need to have separate lines on the report for each warehouse. Most will be the same and should only have one line. So this is the only way I could think of to accomplish this. But I am open to suggestion if there is a better way.

SELECT
    ITEM_CODE,
    WHSE_CODE,
     sum (QTY_Begin) as qtybegin
     sum (Avg_Cost_Begin) as Acstbegin 
     sum (QTY_End) as qtyend
     sum (Avg_Cost_end) as Acstend
    
        

FROM
    (
     SELECT 
      ESVITEMR.ITEM_CODE as Item_Code,
      INVBEG2.WHSE_CODE as WHSE_CODE,
      INVBEG2.QTY_ON_HN as QTY_Begin, 
      INVBEG2.AVG_COST as AVG_Cost_Begin,
      0 as QTY_End,
      0 as AVG_Cost_End
      
     FROM 
           EDENLive.dbo.ESVITEMR ESVITEMR,S102E9DC.INVLIB.INVBEG2 INVBEG2
WHERE 

ESVITEMR.ITEM_CODE = INVBEG2.Item_Code

/* ---------------------------------------------------------------------------*/
union all

SELECT 
      ESVITEMR.ITEM_CODE as Item_Code,
      INVEND2.WHSE_CODE as WHSE_CODE,
      0 as QTY_Begin,
      0 as AVG_Cost_Begin,
      INVEND2.QTY_ON_HN as QTY_END, 
      INVEND2.AVG_COST as AVG_Cost_END
      
      
     FROM 
           EDENLive.dbo.ESVITEMR ESVITEMR,S102E9DC.INVLIB.INVEND2 INVEND2
WHERE 

ESVITEMR.ITEM_CODE = INVEND2.Item_Code


   
) x
GROUP BY
    ITEM_CODE,
    WHSE_CODE

Open in new window

Mike McCracken

You will have to convert the non-numeric to numeric.

I don't really understand why the query needs to be so complex
I think you should be ablle to join the 3 tables and get the data you want.

mlmcc

qbjgqbjg

ASKER

Please explain.

Mike McCracken

What do you need the report to show?

mlmcc

James0628

Ignoring the structure of your query for the moment (mlmcc may be right about it being overly complicated. I haven't tried to figure it out :-), you can use CONVERT or CAST in MS SQL to convert a character field to numeric, and then you could use SUM on the converted values. But if those columns could ever contain non-numeric data, like letters, you may get an error. You can use ISNUMERIC to test the values first, before trying to convert them.

James

qbjgqbjg

ASKER

The fields in question always contain numeric data. The purpose of the report is: List the beginning value of inventory (by warehouse) as of a specified start date, and the ending value (by warehouse) as of a specified date. So the data I have is stored in a text file, So all of the fields are character. Most items remain in the same warehouse, so for those there would be a single line with under the warehouse with the begin/end values, But where the warehouse changes, there needs to be separate lines under each of the warehouses. That is the goal.

qbjgqbjg

ASKER

The records need to be grouped by warehouse. If they mach then the beginning and the end fall under the same warehouse, no problem, but if they don't, then the begin should fall under the begin warehouse, and the end should fall under the end warehouse. I don't see a way to do this without the sql. And I don't know the simpler way to join that mlmcc suggested should be possible. If there is a simpler way I would be more than happy to use it. I just need it explained.
Thanks

Mike McCracken

Are you trying to show what is in a warehouse or what you have for each item and then what is in each warehoiuse?

Something like

Warehouse1
Item1 54
Item2 43
Warehouse2
Item1 4
Item3 33

or

Item1
Warehouse1 54
Warehouse2 4
Total 58
Item2
Warehouse1 43
Item3
Warehouse2 33

mlmcc

qbjgqbjg

ASKER

The report is supposed to show the value of what was in the warehouse (detail by item #) at the beginning of a date range and the value of what is in the warehouse at the end of the date range. There are 5 warehouses. I will give an example with 2 warehouses. There also would be totals for the value by warehouse.
Date range: 10/01/2009 - 09/31/2010
Item # BEG QTY BEG COST BEG VALUE END QTY END COST END VALUE
Warehouse 1
11111 20 .10 2.00 5 .15 .75
11112 1 15.00 15.00 3 15.00 45.00
11113 2 1.00 2.00 0 0 0
Warehouse 2
11113 0 0 0 3 1.00 3.00
11114 5 2.00 10.00 4 2.10 8.40

The input is in 2 files that each contain Item#, warehouse, Qty, cost. 1 file for the begin date, 1 file for the end date. Then there is an item detail file that they link to so I can obtain an account # for the item.

As you can see in this example, item 11113 started out in Warehouse 1, but ended in Warehouse 2.

Mike McCracken

So you have a file BeginningInventory with data like
Warehouse1 11111 20 .10
Warehouse1 11112 1 15.00
Warehouse1 11113 2 1.00
Warehouse2 11114 5 2.00

EndInventory
Warehouse1 11111 5. .15
Warehouse1 11112 3 15.00
Warehouse2 11113 3 1.00
Warehouse2 11114 4 2.10

A third file like
11111 ProductA
11112 ProductB
11113 ProductC
11114 ProductD

mlmcc

qbjgqbjg

ASKER

Yes

Mike McCracken

Are you using a command to get the data or linking tables?

Are there multiple records for a product in a warehouse?

mlmcc

qbjgqbjg

ASKER

I am using the command so that I can get a single line when the warehouse stays the same and separate lines when they are different. So, yes to the linking question. There is only one record for each item.

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

qbjgqbjg

ASKER

I converted the files to SQL using SSIS. I am using this command. I decided I could do without the third file. This is almost working. The only Problem I am having now is SSIS is not creating the cost field correctly. It has 5 decimal positions and it changes it to 2.

Use InventoryHistoryData
SELECT
ITEM_CODE,
WHSE_CODE,
sum (QTY_Begin) as qtybegin,
sum (AVG_Cost_Begin) as Acstbegin,
sum (QTY_End) as qtyend,
sum (Avg_Cost_end) as Acstend

FROM
(
SELECT
IN102109_.ITEM_CODE as Item_Code,
IN102109_.WHSE as WHSE_CODE,
IN102109_.QTY as QTY_Begin,
IN102109_.AVGCOST as AVG_Cost_Begin,
0 as QTY_End,
0 as AVG_Cost_End

FROM
dbo.IN102109$ IN102109_

/* ---------------------------------------------------------------------------*/
union all

SELECT
IN092810_.ITEM_CODE as Item_Code,
IN092810_.WHSE as WHSE_CODE,
0 as QTY_Begin,
0 as AVG_Cost_Begin,
IN092810_.QTY as QTY_END,
IN092810_.AVGCOST as AVG_Cost_END

FROM
dbo.IN092810$ IN092810_

) x
GROUP BY
ITEM_CODE,
WHSE_CODE