declare @fromdate datetime, @todate datetime

set @fromdate = '7 February 2011'
set @todate = DATEADD(dd, 7, @fromdate)

select
    *
    into #tmp
from (
select
    [Date]
    , s.Id as StoreId
    , s.Name as StoreName
    , o.TotalPrice
    , CASE
        WHEN o.OrderStatusId = 2 THEN 'Y'
        ELSE 'N'
      END AS Success
    , o.OrderStatusId
    , CASE WHEN o.IsMobileDevice = 1 THEN 'Y' ELSE 'N' END AS IsMobileDevice
    , u.Fullname as UserName
    , u.MobileNumber as CellNumber
    , u.EmailAddress as EmailAddress
    , CASE WHEN COALESCE(o.Instructions, '') LIKE '%test%' THEN 'Y' ELSE 'N' END AS IsTest
    , o.Instructions
    , o.CardPaymentId
    , o.UserId
from Orders o
LEFT JOIN Stores s on o.StoreId = s.Id
LEFT JOIN UserDetails u on o.UserId = u.UserId
where Date > @fromdate
and Date < @todate
--order by Date Desc
) t
where IsTest = 'N'
AND OrderStatusId <> 3 -- pending card authorisation -> the user did not continue so these should be ignored

SELECT 'Total number of orders: ', COUNT(*) FROM #tmp
UNION
SELECT 'Total number of successful orders: ', COUNT(*) FROM #tmp WHERE Success = 'Y'
UNION
SELECT 'Total number of stores: ', COUNT(DISTINCT StoreName) FROM #tmp
UNION
SELECT 'Stores where errors occurred: ', COUNT(DISTINCT StoreName) FROM #tmp WHERE Success = 'N'
UNION
SELECT 'Credit card orders for the week: ', COUNT(*) FROM #tmp WHERE CardPaymentId is not null
UNION
SELECT 'Number of credit card orders that failed: ', COUNT(*) FROM #tmp WHERE CardPaymentId is not null AND Success = 'N'
UNION
SELECT 'Mobile orders for the week (successful only): ', COUNT(*) FROM #tmp WHERE IsMobileDevice = 'Y' AND Success = 'Y'
UNION
SELECT 'Web site orders for the week (successful only): ', COUNT(*) FROM #tmp WHERE IsMobileDevice = 'N' AND Success = 'Y'
UNION
SELECT 'Total registered users: ', COUNT(*) FROM UserDetails WHERE CreatedOn < @todate
UNION
SELECT 'Users registered this week: ', COUNT(*) FROM UserDetails WHERE CreatedOn > @fromDate and CreatedOn < @todate
UNION
SELECT 'New users who ordered this week: ', COUNT(*) FROM UserDetails WHERE CreatedOn > @fromDate and CreatedOn < @todate and UserId IN (Select UserId from #tmp)
UNION
SELECT 'Total order amount (successful only): ', SUM(TotalPrice) FROM #tmp WHERE Success = 'Y'
UNION
SELECT 'Average order amount (successful only): ', AVG(TotalPrice) FROM #tmp  WHERE Success = 'Y'
UNION
SELECT 'Average order amount (website, successful only): ', AVG(TotalPrice) FROM #tmp WHERE IsMobileDevice = 'N' AND Success = 'Y'
UNION
SELECT 'Average order amount (mobisite, successful only): ', AVG(TotalPrice) FROM #tmp WHERE IsMobileDevice = 'Y' AND Success = 'Y'

-- get the 3 busiest stores for the week
SELECT TOP 3
(select name from Stores where Id = t.StoreId) as StoreName,
COUNT(*) as OrderCount
from #tmp t
WHERE Success = 'Y'
group by StoreId
order by COUNT(*) desc

drop table #tmp