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.
Author |
Topic |
deanglen
Yak Posting Veteran
65 Posts |
Posted - 2013-04-30 : 09:39:13
|
HiThis script below works fine but if I wanted to add another column which is OrderDate from the orders table in the results how would I do this?select c.CustomerID, C.Email, c.FirstName + ' ' + c.LastName AS CustomerName, count(o.OrderNumber) As NoOrdersfrom customer c join dbo.Orders o with (NOLOCK) on o.CustomerID = c.CustomerID and c.IsRegistered = 1 and o.TransactionState = 'CAPTURED' and c.OkToEmail = 1where o.OrderDate >=dateadd(day,datediff(day,0,GetDate())- 365,0)GROUP BY c.CustomerID, c.FirstName + ' ' + c.LastName, c.Emailhaving count(o.OrderNumber) = 1order by c.CustomerIDThanks |
|
mmkrishna1919
Yak Posting Veteran
95 Posts |
Posted - 2013-04-30 : 09:52:31
|
Add that column in the select list and group by list.Thanks.M.MURALI kRISHNA |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-04-30 : 10:15:45
|
select c.CustomerID, C.Email, c.FirstName + ' ' + c.LastName AS CustomerName, count(o.OrderNumber) As NoOrders,o.orderdatefrom customer cjoin dbo.Orders o with (NOLOCK) on o.CustomerID = c.CustomerIDand c.IsRegistered = 1and o.TransactionState = 'CAPTURED'and c.OkToEmail = 1whereo.OrderDate >=dateadd(day,datediff(day,0,GetDate())- 365,0)GROUP BY c.CustomerID, c.FirstName + ' ' + c.LastName, c.Emailhavingcount(o.OrderNumber) = 1order by c.CustomerID,o.orderdatemohammad.javeed.ahmed@gmail.com |
|
|
deanglen
Yak Posting Veteran
65 Posts |
Posted - 2013-04-30 : 10:22:51
|
Great! Thanks |
|
|
|
|
|
|
|