Use Basket select convert(varchar(7),Orderdate,120) as YearMonth ,OrderID ,TotalCost into #a from Basket.dbo.BaseOrderTracker(nolock) where OrderDate >='2013-07-01' and OrderDate <='2014-07-31' and OrderTypeID = 15 and OESourceID = 2 and StageID= 10400 select * from #a select YearMonth, COUNT(distinct OrderID), SUM(TotalCost) from #a (nolock) group by YearMonth drop table #a
DECLARE @StartDate datetime = '2014-4-1' DECLARE @EndDate datetime = '2014-5-1' SELECT case when StartDate >= @StartDate then 'Y' else 'N' end as CurrentMonth, COUNT (1 ) as icount FROM ( select distinct PurchasedByContactID as ContactID from BASKET.. BaseOrderTracker(nolock ) where OrderDate>= @StartDate and OrderDate < @EndDate and OrderTypeID =15 and OESourceID = 2 and StageID= 10400 --and FulfillmentLocationID in (705 ,718, 710,711 ) ) co inner join CONTACTS.dbo .Consultants c with (nolock) on c .ContactID = co.ContactID group by case when StartDate >= @StartDate then 'Y' else 'N' end