July 3, 2013 at 2:10 am
I have tried this but my head of it department still telling me tht i have failed this is my last chance
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 2:41 am
Hi
Difficult to help with the info given, perhaps this will point you in the right direction?
SELECT
e.BuProductName
,MAX(s.OrderQuantity) as Sold
,CAST(SomeDate AS DATE)
FROM
dbo.Product AS e
INNER JOIN dbo.Transact AS s
ON e. productID= s. productID
GROUP BY
CAST(SomeDate AS DATE)
,e.BuProductName
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 3, 2013 at 2:54 am
engstevo (7/3/2013)
I have tried this but my head of it department still telling me tht i have failed this is my last chanceWrite 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
Syntactically incorrect. Should read: -
SELECT e.BuProductName,
SUM(s.OrderQuantity) AS [Sold per day]
FROM dbo.Product AS e
INNER JOIN dbo.Transact AS s ON e.productID = s.productID
GROUP BY e.BuProductName;
However, that isn't the answer to your question. For that, we need readily consumable sample data and DDL. Take a look at this article (http://www.sqlservercentral.com/articles/Best+Practices/61537/)[/url] for the best way to ask a question on a technical forum.
July 3, 2013 at 3:24 am
Deeply thank for this other query
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.
R/I have tried this :
CREATE PROCEDURE FinYear2012
@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)
SELECT
Mydate
, CASE
WHEN (Mydate) BETWEEN '2010-02-28 ' AND '2010-05-28 ' THEN 'First Quarter'
WHEN (Mydate) BETWEEN '2010-05-28 ' AND '2010-08-28 ' THEN 'Second Quarter'
WHEN (Mydate) BETWEEN '2010-08-28 ' AND '2010-12-28 ' THEN 'Third Quarter'
WHEN (Mydate) BETWEEN '2010-12-28 ' AND '2011-02-28 ' THEN 'Fourth Quarter'
END AS FiscalYear
FROM Transactions
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
July 3, 2013 at 3:30 am
All of these looks like a homework to me.
In the real life this one better to be done using calendar table.
And you would only use the script to populate it...
Also, there are a calendar months and weeks and there are financial months and weeks. What is required in your case?
July 3, 2013 at 3:43 am
the script that will enable you to create the new values for each of the measures
July 3, 2013 at 4:01 am
engstevo (7/3/2013)
the script that will enable you to create the new values for each of the measures
What do you mean by that?
There are build in T-SQL datetime function to determine calendar quarter, month, week and day of the week. Determining financial calendar parts will depend on the set rules used by the company - they are no universal. There is no such thing as standard Financial Year. They do really depend on country you are in and a company. There are many different types of Financial Years based on 4-4-5 weeks.
Check this out: http://en.wikipedia.org/wiki/4%E2%80%934%E2%80%935_calendar
P.S. Are you sure that if you pass this test with all help you will be able successfully perform tasks required by the postition you are trying to fill?
July 3, 2013 at 3:22 pm
Ohhhh, be careful, folks. This looks like a trick question that I might write as an "ace breaker" on an example. If you read the original question, it says to return the "max" quantity sold... not the total. I believe Andy considered that but it all begs an explanation as to what is meant by "max" quanity sold if not taken at face value like Andy did ( and I believe he was correct in doing so).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply