Solved

Crystal report - sql command

Posted on 2011-03-02
14
931 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
  • 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
 
LVL 34

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now