Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


daily sum aggregation please help


daily sum aggregation please help

Author
Message
engstevo
engstevo
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16566 Visits: 17016
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)
engstevo
engstevo
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
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
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2178 Visits: 12518
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search