SQLTeam.com | Weblogs | Forums

Complex query

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.

customers.fNAME, customers.lname, products.discription, products.price, orderdetails.quantity, orders.id, (products.price*orderdetails.quantity) as total, orders.placedon

FROM customers, orders, orderdetails, products


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.

Here is the query I have that is doing close to what I need.
orders.id, sum(products.price*orderdetails.quantity) as total, orders.placedon

FROM customers, orders, orderdetails, products

group by orders.id, orders.placedon;

This is now getting me the total on each order, listed by orders.id and orders.placedon (date).

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?" :slight_smile: ) Adding ALIAS names to the tables also helps to reduce the verbosity of the code

FROM	customers AS C
	JOIN orders AS O
	JOIN orderdetails AS OD
	JOIN products AS P
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.

    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

Thank you that got me going so I was able to create multiple different reports off of that.

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)

would be an option

It's an extremely efficient and flexible technique compared to other methods, so "ugliness" be damned!

For example, for a daily total:
DATEADD(DAY, DATEDIFF(DAY, 0, o.placedon), 0)
Or for a yearly total:
DATEADD(YEAR, DATEDIFF(YEAR, 0, o.placedon), 0)

Once you see and understand the technique, the same approach can be applied to many such types of tasks.

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.

Thank you

Yes, fair enough. I still think its a bit hard on a Newbie though ...

ROUND(o.placedon, DAY)
ROUND(o.placedon, YEAR)

much more friendly :sunglasses:

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...:slight_smile:

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.

IMHO, newbie's need to learn the right way to do it right out of the box.

I'm with you. Newbies need to learn the right way to do it right out of the box.

You can squeeze a bit more out of it by not using the DATEADD part in the GROUP BY and using the PlacedOn_Month alias in the ORDER BY.