This is for a class Database is setup with 4 tables of interest. Employees, customers products, Which have basic employee customer and product info, Orders table, which is very simple, it has the order number Primary key, employee who took the order, and the customer the order is for. Then an order details table, that has the order number, product id and quantity of that product ordered and the date it was ordered.
I'm trying to figure out how to do a query to get the gross sales by product by month, but I can't figure it out.
WHERE customers.ID = ORDERS.BYCUSTOMER and ORDERS.ID=ORDERDETAILS.ORDERID and products.id=ORDERDETAILS.PRODUCTID
;
This query lets me get the totals on each line of the orders, which I know is the starting point. From here I need to be able to aggregate it by order number, then hopefully I can figure out the sort by customer/product/date.
Thankyou
Not related to what you want to do, but I strongly recommend that you use JOIN syntax instead of this style. Don;t know if you have been taught that? (If not I would ask "Why not?" ) Adding ALIAS names to the tables also helps to reduce the verbosity of the code
FROM customers AS C
JOIN orders AS O
ON O.BYCUSTOMER = C.ID
JOIN orderdetails AS OD
ON OD.ORDERID = O.ID
JOIN products AS P
ON P.id = OD.PRODUCTID
SELECT SaleMonth, SUM(products.price*orderdetails.quantity) as total
FROM ...
GROUP BY SaleMonth
so you just need to work out how to do Sale Month. I presume that needs to be Year & Month (so that it makes sense if the orders span a year end), but the question doesn't say that.
Month, of a date, is available using MONTH() function, so replace SaleMonth with MONTH(O.placedon)
100% you should use the "new" JOIN syntax to explicitly indicate columns to join on; the old syntax is deprecated and will be going away completely fairly soon.
SELECT
o.id, sum(p.price*od.quantity) as total,
DATEADD(MONTH, DATEDIFF(MONTH, 0, o.placedon), 0) AS placedon_month
FROM orders o
INNER JOIN orderdetails od ON o.ID = od.ORDERID
INNER JOIN products p ON p.id = od.PRODUCTID
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, o.placedon), 0), o.id
ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, 0, o.placedon), 0), o.id
I skated over that because I think its "ugly" for a Newbie ... disappointing that in 2017 we still have that method of "rounding" a date to a whole number of units (months in this case)
I wonder if Oracle / PostgreSQL / et al do it any better these days?
I suppose
SELECT YEAR(O.placedon) AS MyYear
, MONTH(O.placedon) AS MyMonth,
, SUM(products.price*orderdetails.quantity) as total
FROM ...
GROUP BY YEAR(O.placedon), MONTH(O.placedon)
That does make the date much easier to work with. I'm much better at programing in Java/c than in SQL.... Functional languages are hard for me to work with. Normally I just extract the data and work it in the application. That I can understand, but that is also why I am doing an SQL class now.
Any way you look at it you have to remove the time portion of the datetime data type to insure that your beginning/end ranges are appropriate.
If you utilize the date data type then you don't have to worry about that part of the calculation - but you still have to add the number of days/months/years to define your start/end periods.
With that said - we do have a way of 'rounding' that is quite simple:
SELECT DATEFROMPARTS(year(getdate()), 1, 1) -- First of this year
SELECT DATEFROMPARTS(year(getdate()), month(getdate()), 1) -- First of this month
SELECT DATEFROMPARTS(year(getdate()), month(getdate()), day(getdate())) -- Today
We also can get the end of the month with:
SELECT EOMONTH(getdate(), 0) -- End of this month
SELECT EOMONTH(getdate(), -1) -- End of previous month
SELECT EOMONTH(getdate(), 1) -- End of next month
The problem you will run into with the above is that they return a DATE and not a DATETIME. This can cause an implicit conversion in the execution plan but may not be a big issue anymore. I recall seeing something that stated indexes on dates could be utilized in these situations - but to be safe you would want to cast/convert the date to a datetime.
Notice though that you have to change the computation for each time period -- now that is the "ugly" part to me.
And you have the issue of potentially different data types with other methods.
Whereas DATEADD will automatically return the exact data type you passed into the formula. That is:
DATEADD(DAY, <some_calc>, column_name)
will always return the same data type as column_name, whether it's time/date/smalldatetime/etc.
Agreed - and the reason I have that formula all over my code...
Just showing that there are other options but they have some downsides that you have to be aware of...there are uses for each one and I have used them all at some point...any of these work just fine as long as they are only being used to create the variable to be used in a query - once you start putting columns into any of these formulas and using that in the where clause you are going to have lots of issues.
) Adding ALIAS names to the tables also helps to reduce the verbosity of the code