July 2, 2013 at 5:57 am
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
July 2, 2013 at 7:41 am
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/
July 3, 2013 at 1:50 am
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
July 3, 2013 at 6:43 pm
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