Joining SQL Script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Joining SQL Script

Author

Topic

deanglen
Yak Posting Veteran

65 Posts

Posted - 2012-08-15 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-15 : 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
Yak Posting Veteran

65 Posts

Posted - 2012-08-15 : 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
Yak Posting Veteran

65 Posts

Posted - 2012-08-16 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources