Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2003
    Posts
    163

    Unanswered: Need help qith SQL query - How to group sales?

    Hi,

    I currently have a table whose structure is as follows:

    CREATE TABLE [tblSales] (
    [OrderID] [int]
    [SaleDate] [smalldatetime] ,
    [ProductCode] [nvarchar] (255) ,
    [QtySold] [float] ,
    [UnitPrice] [float] ,
    [Discount] [float] ,
    [GrossSaleAmount] NULL ,
    [NetSaleAmount] [float]
    )

    The GrossSaleAmount and NetSaleAmount are calculated fields. But for this post, kindly ignore why I am storing calcuated fields...


    QUESTION:
    What I want to do is to populate another table (the DDL of which is give below) from tblSales in such a manner that the TOTAL sales from each product for each available date is grouped/summed together.

    [blue]CREATE TABLE [tbl_Product_Grouped_Sales] (
    [SaleDate] [smalldatetime] ,
    [ProductCode] [nvarchar] (255) ,
    [TotalQtySold] [float] ,
    [NetSaleAmt] [float]
    )




    Thanks in advance for your help.

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    insert into tbl_product_grouped_sales
    select saledate, productcode , sum(QtySold),sum(netsaleamount)from tblsales group by saledate,productcode order by saledate
    Last edited by harshal_in; 04-13-04 at 02:32.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First of all, it is a bad plan to store summary data like this, other than when copying data to a data warehouse. This is almost always an invitation to disaster!

    That said, I think all you need is a simple:
    PHP Code:
    SELECT Convert(CHAR(10), SaleDate121) AS SaleDate
    ,  ProductCode
    ,  Sum(CAST(QtySold AS INT)) AS TotalQtySold
    ,  Sum(CAST(NetSaleAmount AS DECIMAL(13,2)) AS NetSaleAmount
       FROM tblSales
       GROUP BY ProductCode
    Convert(CHAR(10), SaleDate121
    Unless I missed something, this seems suspiciously easy.

    -PatP

  4. #4
    Join Date
    Oct 2003
    Posts
    163

    Question

    Thanks for the replies. Yes the reason why I am doing this is for a data mart and ofcourse I wouldn't even think of storing data in my OLTP in such a manner

    BTW, you both seem to have slightly different solutions. I'm sure that both will produce the same results but Pat, can you kindly comment on the difference between your's and harshal_in's reply. Your reply me help me understadn and learn more.

    Many thanks in advance and thanks again for the replies.

    Regards.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your DDL showed real (aka float) columns being used for counts (money and units sold). A real should be used for measures (such as linear feet of steel, or the distance to the moon). Any kind of real math involves some degree of "floating point error" in the calculation. This is why 2.0 <> (2.0 / 3.0) * 3.0).

    Converting from real to a more appropriate type before aggregation causes less total error than aggregating the reals then converting to an appropriate type after aggregation.

    -PatP

  6. #6
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i suggest that if you are using this to dump to olap then you might consider creating a "staging" view instead of a table
    the view will be accessed faster by your etl (DTS) and it wont add to your storage space or tlog AND you could use a simple "select * from StageView" to get your data
    i use this technique often instead of creating physical staging tables(as long as i know i wont be capturing error rows. otherwise i would create staging tables).

  7. #7
    Join Date
    Oct 2003
    Posts
    163
    Hello Pat and Ruprect,

    Really appreciated your valuable suggestions which I find of great help as I am learning.....

    Question/Request to Pat:
    Many thanks for the valuable tip and the excellent but simple example... Can you kindly guide which data type should I then use for highlighted fields of my following table so that I get accurate results. Should I use real or int or decimal or??? I just want accuracy upto 2 decimal places.

    CREATE TABLE [tbl_Product_Grouped_Sales] (
    [SaleDate] [smalldatetime] ,
    [ProductCode] [nvarchar] (255) ,
    [TotalQtySold] [float] ,
    [NetSaleAmt] [float]

    )


    Question/Request to Ruprect:
    Can you kindly guide me a little about how to use this "staging" view concept. This is new to me and sounds like a good approach (coming from an experienced person)... Here's what I am currently doing and maybe you can guide me how to improve my approach and use the staging view:

    Currently, I have a DTS which performs the following steps:
    [list=1][*]Drops the fact table and then creates it[*]Run another DTS which brings in data from the OLTP table and populates the data mart table[*]Links my fact table with a TimeDimension table (but that's not being discussed over here)[/list=1]

    Many thanks in advance.

    Regards.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without knowing a good deal more about your business requirements, I'd stick with my first guesses. QtySold is probably a count of whole units sold, so INT is appropriate. NetSaleAmount is probably a monetary unit, but I just guessed that you only needed two decimal places (which you seem to have confirmed). There may be business reasons for other datatypes, but until I know those reasons I'm comfortable staying with my first guess.

    -PatP

  9. #9
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565

    Question/Request to Ruprect:
    Can you kindly guide me a little about how to use this "staging" view concept. This is new to me and sounds like a good approach (coming from an experienced person)... Here's what I am currently doing and maybe you can guide me how to improve my approach and use the staging view:

    Currently, I have a DTS which performs the following steps:
    [list=1][*]Drops the fact table and then creates it[*]Run another DTS which brings in data from the OLTP table and populates the data mart table[*]Links my fact table with a TimeDimension table (but that's not being discussed over here)[/list=1]

    Many thanks in advance.

    Regards.
    the idea for this to work is that you have a view already created that points to the actual data that you want to bring into the table during your transform data task.
    you would create a select statement for the source that queries the view
    and then you set the destination = to the table that you want to place the data in
    you can do as much of the standardizaton that you need to do in the view code so that you will have as few xforms(transformations) to do during the xform task.

    so basically create a view that displays the data the way you want it to look when you dump it into the table or just before your major transformations
    and just query that instead of the tables directly.

  10. #10
    Join Date
    Oct 2003
    Posts
    163
    Pat and Ruprect,

    Thanks again for your replies

    Ruprect:
    Thanks (many thanks) for the explanation about the staging area concept. I have a good feeling that I am on the right track (even if not FULLY) becuase the DTSs that I have mention include fetching data from a view and dumping it into a table. Regarding the transformations - Can you kindly give me an example of a transformation. Take any example just to give me an idea. I hope this will be my last botheartion to you for this post.

    Again, I'll appreciate your reply.


    Pat:
    Regarding QtySold I get your point and should use int. But will int store the decimal places too? Asking this stupid question becuase when I try to define int as the datatype for a table field, the 'precision' property appears dimmed (disabled) and from this i assumed that for int you can not define decimal places or upto how many decimal places are needed. Can you kindly clarify please.

    I think your reply to my above question will automatically answer what data type I should use for the NetSaleAmount.

    Thanks again to both of you for your help, replies, and time.

    Regards.

  11. #11
    Join Date
    Oct 2003
    Posts
    163
    Pat and Ruprect,

    Thanks again for your replies

    Ruprect:
    Thanks (many thanks) for the explanation about the staging area concept. I have a good feeling that I am on the right track (even if not FULLY) becuase the DTSs that I have mentioned include fetching data from a view and dumping it into a table.

    Regarding the transformations - Can you kindly please give me an example of a transformation. Take any example just to give me an idea. Will be great help. I hope this will be my last botheartion to you for this post.

    Advance thanks for your reply

    Pat:
    Regarding QtySold I get your point and will use 'int' data type. But will 'int' store the decimal places too and if so, to how many places? Asking this stupid question becuase when I try to define 'int' as the datatype for a table field, the 'precision' property appears dimmed (disabled) and from this I assumed that for 'int' one can not define decimal places or upto how many decimal places
    Can you kindly guide and clarify please.

    I think your reply to my above question will automatically answer what data type I should use for the NetSaleAmount.

    Thanks again to both of you for your help, replies, and time.

    Regards.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, the INT datatype deals only with whole numbers, no decimal places at all. Assuming that you can sell 13% of a widgit, then you'll need to use something like DECIMAL (10, 2) in order to track two digits after the decimal place.

    Just an observation, but you can also use the MONEY datatype. It implicitly supports exactly four decimal places, but performs math much more quickly than most processers can using DECIMAL. For small amounts of data this isn't really important, but in a warehouse environment it can be quite significant.

    -PatP

  13. #13
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Originally posted by Joozh
    Ruprect:
    Thanks (many thanks) for the explanation about the staging area concept. I have a good feeling that I am on the right track (even if not FULLY) becuase the DTSs that I have mentioned include fetching data from a view and dumping it into a table.

    Regarding the transformations - Can you kindly please give me an example of a transformation. Take any example just to give me an idea. Will be great help. I hope this will be my last botheartion to you for this post.

    Advance thanks for your reply
    Regards.
    Answer
    a simple transformation that i find that i do often in dts concerns denormalizing data into the dimension tables. For example concatenating the last name and first names into one column
    one solution is to use the activex transformation(xform) In the transform data task

    the vbscript looks something like this
    Function Main()
    DTSDestination("FullName") = DTSSource("LastName")+ ", " + DTSSource("FirstName")
    Main = DTSTransformStat_OK
    End Function
    now the problem with vbscript is that it is not free threaded (Translated, it is slow)
    so you should avoid it unless you cant do your thing another way.
    my preferred solution for all processes is to try to bring the application logic back to SQL in this case creating a view.

    Create view vEmployeeStage
    AS
    Select
    Lastname +', ' + firstname as FullName
    ,Title
    ,City
    ,Region
    ,extension
    ,reportsto
    From Employees


    you can then use the following sql statement as the source in your transform data task

    Select * from VEmployeeStage

    it will run faster and you can use this view as the basis for a Data Driven Query Task as well.

    [Books Online] Transform Data Task

  14. #14
    Join Date
    Oct 2003
    Posts
    163
    Hi Pat and Ruprect,

    I can't thank you enough for your time and valuable guidance. Trust me and coming from a starter, your replies have been of great help.

    Many thanks. Hope to run into you via some other post of mine

    Regards,

    - J

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •