SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Selecting a value of 1
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deanglen
Yak Posting Veteran

64 Posts

Posted - 04/09/2013 :  13:01:41  Show Profile  Reply with Quote
Hi

The script below gives me an extract of how many orders each customer has placed. Within the Customer table there is a field called RegisteredDate

How can I change the script so it only shows customers from a specific date and only show those customers who have an order total of 1?

select c.CustomerID, c.FirstName + ' ' + c.LastName AS CustomerName, count(o.OrderNumber) As NoOrders
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
join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber
and c.IsRegistered = 1
and p.SKU like '%'
GROUP BY c.CustomerID, c.FirstName + ' ' + c.LastName
order by c.CustomerID

James K
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 04/09/2013 :  14:24:09  Show Profile  Reply with Quote
This?
select 
	c.CustomerID, c.FirstName + ' ' + c.LastName AS CustomerName, count(o.OrderNumber) As NoOrders
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
	join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber
		and c.IsRegistered = 1
		and p.SKU like '%'
where
	o.OrderDate >= '20130101'
GROUP BY 
	c.CustomerID, c.FirstName + ' ' + c.LastName
having
	count(o.OrderNumber) = 1
order by 
	c.CustomerID
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/09/2013 :  14:38:41  Show Profile  Reply with Quote
As per your explanation it should be
.

...
WHERE c.RegisteredDate > = @YourDatevalue
...




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

deanglen
Yak Posting Veteran

64 Posts

Posted - 04/09/2013 :  14:46:02  Show Profile  Reply with Quote
Excellent! Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/10/2013 :  00:47:51  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000