daily sum aggregation please help

  • Write an SQL statement that will perform a daily sum aggregation on the field cost. It must only use costs that are associated with the follow items: Z001, Z002. The cost must also only come from division 1. Below is the example table that you will be retrieving data from:

    Costs

    * Receipt

    * Date

    * Item

    * Reason

    * Division

    * Cost

  • engstevo (7/2/2013)


    Write an SQL statement that will perform a daily sum aggregation on the field cost. It must only use costs that are associated with the follow items: Z001, Z002. The cost must also only come from division 1. Below is the example table that you will be retrieving data from:

    Costs

    * Receipt

    * Date

    * Item

    * Reason

    * Division

    * Cost

    Much like your two other posts. This is obviously homework. What have you tried?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I tried first this but I failed :

    Question 1:

    Write an SQL statement that will perform a daily sum aggregation on the field cost. It must only use costs that are associated with the follow items: Z001, Z002. The cost must also only come from division 1. Below is the example table that you will be retrieving data from:

    Costs

    •Receipt

    •Date

    •Item

    •Reason

    •Division

    •Cost

    R/

    use STEVENTEST

    go

    CREATE FUNCTION Daily_Cost1

    (

    @item varchar , @Division nchar

    )

    RETURNS money

    AS

    BEGIN

    DECLARE @daily_cost money

    DECLARE @Cost_date date

    set @Cost_date = GETDATE ()

    if

    @item = 'Z001' OR @item ='Z002'

    SELECT @daily_cost = SUM(cost) from Cost

    where @Cost_date =@Cost_date

    RETURN @daily_cost

    END

    GO

    2.You are only given the transaction date in your transactions table and your client requires you to be able to show them their data by Financial Year, Quarter, Month, Week and Week Day

    Write the script that will enable you to create the new values for each of the measures listed above. The financial year end is 28 February 2011

    Then confirm the Financial Year, Quarter, Month, Week and Weekday.

    CREATE PROCEDURE Fin_Year1

    @Cost_date date = '2011-02-15 00:00:00.0000000'

    AS

    BEGIN

    DECLARE @Fin_year int

    DECLARE @Fin_quarter int

    DECLARE @fin_Month int

    DECLARE @Fin_Week int

    DECLARE @Weekday Varchar (25)

    set @Fin_year = DATEDIFF(year, '2010-02-28 23:59:59.9999999'

    , '2011-02-28 00:00:00.0000000');

    set @Fin_quarter = DATEDIFF(quarter, '2010-02-28 23:59:59.9999999'

    , '2011-02-28 00:00:00.0000000');

    set @fin_Month = DATEDIFF(MONTH, '2010-02-28 23:59:59.9999999'

    , '2011-02-28 00:00:00.0000000');

    set @Fin_Week = DATEDIFF(WEEK, '2010-02-28 23:59:59.9999999'

    , '2011-02-28 00:00:00.0000000');

    set @Weekday = DATEDIFF(WEEKDAY, '2010-02-28 23:59:59.9999999'

    , '2011-02-28 00:00:00.0000000');

    SELECT @Fin_year as FinancialYr ,@Fin_quarter as FinancialQtr , @fin_Month as FinancialMnth , @Weekday as Wekkeday

    END

    GO

    Question 4

    Write a SQL statement that will return all the Sales Orders for the Salespersons’ with the name starting with ‘John’.

    R/

    CREATE VIEW [dbo].[SalesOrder]

    AS

    SELECT dbo.Trnasction.Salesorder, dbo.SalesPerson.SalesPersonName

    FROM dbo.Trnasction CROSS JOIN

    dbo.SalesPerson

    WHERE (dbo.SalesPerson.SalesPersonName = N'John')

    Below are the example tables that you will be retrieving data from:

    Transactions:

    •Sales Order

    •Order Quantity

    •Order Total

    •Order Date

    •SalesPersonID

    •ProductID

    SalesPerson

    •SalesPersonID

    •SalesPersonName

    Product

    •ProductID

    •ProductName

    Question 4

    Write a SQL statement from the above example tables that will return the Product Name and Quantity with the maximum quantity sold per day.

    SELECT e.BuProductName,s.sum(OrderQuantity) as Sold per day

    FROM dbo.Product AS e

    INNER JOIN dbo.Transact AS s

    ON e. productID= s. productID

  • Could be me, but I think you're making this infinitely harder than it really needs to be. If you think in terms of SETS and use pure SQL, you can do it easily.

    Question 1:

    Write an SQL statement that will perform a daily sum aggregation on the field cost. It must only use costs that are associated with the follow items: Z001, Z002. The cost must also only come from division 1. Below is the example table that you will be retrieving data from:

    It's a SUM and a couple of filters... sorry, but you gotta stretch a little. That's what homework is for.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply