Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Crystal report - sql command

Posted on 2011-03-02
14
Medium Priority
?
977 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 101

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 101

Expert Comment

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

mlmcc
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 101

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 101

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 101

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 101

Accepted Solution

by:
mlmcc earned 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

971 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