SQL Query

Hi, I hope someone can help me. I have nop ver 3.40 and try to query a sales report that will also include Attribute combinations if possible. We have a product that different colors can be selected for purchase. When I run a sales report, only the general name of the product shows but not the different colors.





Thanks in advance,

Hi,

Are you using some specific tool to generate the sales report? If you require only an SQL query to get you the orders, products and attribute combinations mapped to the current order item, it should be something like the following:

Select oi.OrderId, oi.ProductId, oi.AttributesXml from [Order] o
inner join [OrderItem] oi
on o.Id = oi.OrderId
where oi.AttributesXml != ''

For the sake of the example I have restricted the fields only to order id, product id and the attribute combinations selected for the current order item.

Is that what you need?

Yes, it is what I am looking for. I am using only the SQL query.
How about if I want to generate sales report to include only for specific dates on all items/orders.




Thank you

...
  AND o.PaidDateUtc BETWEEN CONVERT(DATETIME, '2015-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2015-12-31 00:00:00', 102))


(or, you can use CreatedOnUtc.  Note, that the Nop-Templates query above does not take into account any 'status' like Cancelled orders)

newbhere wrote:

Hi,

The New York solution would do the trick, but keep in mind that it will give you only the paid orders between the two dates. If you want to have all the orders you will have to use the CreatedOnUtc field instead of PaidDateUtc.

Select oi.OrderId, oi.ProductId, oi.AttributesXml from [Order] o
inner join [OrderItem] oi
on o.Id = oi.OrderId
where oi.AttributesXml != ''
and o.CreatedOnUtc between convert(datetime, '2016-01-01 00:00:00', 102) and convert(datetime, '2016-12-31 00:00:00', 102)

Note for my query: It will exclude the order products that do not have Attributes. If you want to include them, the query will look like this:

Select oi.OrderId, oi.ProductId, oi.AttributesXml from [Order] o
left join [OrderItem] oi
on o.Id = oi.OrderId
where o.CreatedOnUtc between convert(datetime, '2016-01-01 00:00:00', 102) and convert(datetime, '2016-12-31 00:00:00', 102)

About the order statuses, you can include them very easily. Just add the following expression to the where clause:

Select oi.OrderId, oi.ProductId, oi.AttributesXml from [Order] o
left join [OrderItem] oi
on o.Id = oi.OrderId
where 
oi.AttributesXml != '' 
and o.CreatedOnUtc between convert(datetime, '2016-01-01 00:00:00', 102) and convert(datetime, '2016-12-31 00:00:00', 102)
and o.OrderStatusId = 30

This will give you only the Complete orders.