Solved

Crystal report - sql command

Posted on 2011-03-02
14
956 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:qbjgqbjg
[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
  • 7
  • 6
14 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 35021369
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
0
 

Author Comment

by:qbjgqbjg
ID: 35021990
Please explain.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35022606
What do you need the report to show?

mlmcc
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 35

Expert Comment

by:James0628
ID: 35025738
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
0
 

Author Comment

by:qbjgqbjg
ID: 35027676
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.
0
 

Author Comment

by:qbjgqbjg
ID: 35029149
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35029264
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
0
 

Author Comment

by:qbjgqbjg
ID: 35030171
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.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35032588
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
0
 

Author Comment

by:qbjgqbjg
ID: 35036825
Yes
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35037923
Are you using a command to get the data or linking tables?

Are there multiple records for a product in a warehouse?

mlmcc
0
 

Author Comment

by:qbjgqbjg
ID: 35038090
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.
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 35038190
Try a command like

Select BeginFile.Warehouse, BeginFile.Product, BeginFile.Amount, BeginFile.Price,
             "Begin" as file
FROM BeginFile
UNION ALL
Select EndFile.Warehouse, EndFile.Product, EndFile.Amount, EndFile.Price,
             "END" as file
FROM EndFile

In the report
group by warehouse
group by product

Use formulas to build the begin and end lists and display in the group footer

mlmcc
0
 

Author Comment

by:qbjgqbjg
ID: 35039709
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard 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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

756 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