Selecting a value of 1 - 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 Programming

Selecting a value of 1

Author

Topic

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-04-09 : 13:01:41

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-09 : 14:24:09

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 14:38:41

As per your explanation it should be
.


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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-04-09 : 14:46:02

Excellent! Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-10 : 00:47:51

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources