| Author |
 Topic  |
|
|
deanglen
Starting Member
40 Posts |
 Posted - 08/15/2012 : 06:56:01
|
Hi
I am running this report that shows how many products have sold in a date range from a table called dbo.prodcut
select os.OrderedProductSKU, convert(DECIMAL(15,2),SUM(os.OrderedProductPrice)) totalsales
from Orders_ShoppingCart os
join Orders o on o.OrderNumber = os.OrderNumber
where o.OrderDate between '01-Jan-2011' and '01-Jan-2012'
group by os.OrderedProductSKU
order by 2 desc
What I want to do is also include the following
1.Set it so that it only selects a SKU value (not all of them)
2.Includes the customer email address from the Orders table
3.Only shows a value of IsRegistered=1 from a Customer table
So it is joining three tables dbo.Product, dbo.Orders and dbo.Customer
Any ideas how to include those three as well?
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 08/15/2012 : 07:03:42
|
You are grouping by OrderedProductSKU - is that for a single customer?
For 1. are you expectinhg a single row returned?
select os.OrderedProductSKU, convert(DECIMAL(15,2),SUM(os.OrderedProductPrice)) totalsales ,
max(o.email) as email
from Orders_ShoppingCart os
join Orders o on o.OrderNumber = os.OrderNumber
join Customers c on c.whatever = o.whatever
where o.OrderDate between '01-Jan-2011' and '01-Jan-2012'
and os.OrderedProductSKU = @OrderedProductSKU
and c.IsRegistered=1
group by os.OrderedProductSKU
order by totalsales desc
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
deanglen
Starting Member
40 Posts |
Posted - 08/15/2012 : 08:04:12
|
Basically I want a list of all customer emails that has the value of "IsRegistered" of 1 that have ordered a product. The SQL Query will only be specific to one SKU there will need to be a WHERE SKU = somewhere in the script.
I jsut cant work out how to link those. |
|
|
|
deanglen
Starting Member
40 Posts |
Posted - 08/16/2012 : 07:54:46
|
Ok managed to get this far but one last thing. How would I show the OrderDate from the orders.dbo. Tried Join and Innter Join but no luck
select distinct p.SKU, c.CustomerID, c.Email, c.FirstName, c.LastName from customer c
join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID
join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID
where p.SKU like 'A659%'
and c.IsRegistered = 1
order by c.CustomerID
|
|
|
| |
Topic |
|
Joining SQL Script
