Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

daily sum aggregation please help Expand / Collapse
Author
Message
Posted Tuesday, July 2, 2013 5:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 29, 2013 2:56 AM
Points: 16, Visits: 20
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
Post #1469421
Posted Tuesday, July 2, 2013 7:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:50 AM
Points: 13,082, Visits: 12,547
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1469501
Posted Wednesday, July 3, 2013 1:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 29, 2013 2:56 AM
Points: 16, Visits: 20
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

Post #1469887
Posted Wednesday, July 3, 2013 6:43 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:16 PM
Points: 839, Visits: 5,422
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.

Post #1470277
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse