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


sql quantity sold per day


sql quantity sold per day

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
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
Andy Hyslop
Andy Hyslop
Say Hey Kid
Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)

Group: General Forum Members
Points: 706 Visits: 3023
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
Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2598 Visits: 8437
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.


Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
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
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
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3046 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
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
the script that will enable you to create the new values for each of the measures
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3046 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45456 Visits: 39946
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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