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

sql quantity sold per day Expand / Collapse
Author
Message
Posted Wednesday, July 03, 2013 2:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 4:02 AM
Points: 672, Visits: 2,625
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
Post #1469911
Posted Wednesday, July 03, 2013 2:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:06 AM
Points: 2,404, Visits: 7,311
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 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


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/) for the best way to ask a question on a technical forum.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1469919
Posted Wednesday, July 03, 2013 3:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 29, 2013 2:56 AM
Points: 16, Visits: 20
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
Post #1469935
Posted Wednesday, July 03, 2013 3:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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?



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1469943
Posted Wednesday, July 03, 2013 3:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 29, 2013 2:56 AM
Points: 16, Visits: 20
the script that will enable you to create the new values for each of the measures
Post #1469951
Posted Wednesday, July 03, 2013 4:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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?



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1469967
Posted Wednesday, July 03, 2013 3:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 35,969, Visits: 30,261
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1470250
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse