Sum of samples received, sum of samples received 7 days ago, difference between two sums with total at bottom of column

I would like to create the following:

Location Name   Monday  Last Monday Delta
Location 2        90          92      -2
Location 4        76          71      5
Location 1        20          23      -3
Location 3        15          13      2
Total             201         199     2

From a table OrderInfo

ID    LocationID    DateReceived  
1     1             2016-05-23
2     3             2016-05-23
...
100   1             2016-05-16
101   4             2016-05-15

With a table LocationInfo

ID    LocationName
1     Location 1
2     Location 2
3     Location 3
4     Location 4

Code I have so far:

select 
case 
when grouping([LocationName]) = 1 
then 'Total' 
else [LocationName] 
end [LocationName], 
count(OrderInfo.LocationID) as Monday 
from OrderInfo 
left join LocationInfo 
on OrderInfo.LocationID = LocationInfo.LocationID
where DateReceived = '2016-05-23' 
group by rollup(LocationInfo.LocationName) 
order by count(OrderInfo.LocationID) desc

which gives:

LocationName    Monday
Total           201
Location 2      90
Location 4      76
Location 1      20
Location 3      15

I can repeat the code replacing '2016-05-23' with '2016-05-16' but can't figure out how to join the two queries, get the difference between the two queries and order the results with the total at the bottom.

I'd also like create results with the same format but replace Monday and Last Monday with week-to-date and last week-to-date and one for month-to-date and last month-to-date.

Any help would be greatly appreciated.

Solution

Here are different queries that may help you. I assume that data exists for all locations across different time periods.

1] Data for Monday and Last Monday

SELECT
    T1.[LocationName],
    Monday,
    LastMonday,
    LastMonday-Monday AS [Difference]
FROM
(
SELECT 
    CASE 
    WHEN GROUPING([LocationName]) = 1 
    THEN 'Total' 
    ELSE [LocationName]

    END [LocationName], 

    COUNT(OI.LocationID) as Monday 

FROM OrderInfo AS OI
LEFT JOIN LocationInfo AS LI
ON OI.LocationID = LI.LocationID
WHERE DateReceived = '2016-05-23' 
GROUP BY ROLLUP(LI.LocationName) 
) AS T1
LEFT JOIN
(
SELECT 
    CASE 
    WHEN GROUPING([LocationName]) = 1 
    THEN 'Total' 
    ELSE [LocationName]

    END [LocationName], 

    COUNT(OI.LocationID) as LastMonday 

FROM OrderInfo AS OI
LEFT JOIN LocationInfo AS LI
ON OI.LocationID = LI.LocationID
WHERE DateReceived = '2016-05-16' 
GROUP BY ROLLUP(LI.LocationName) 
) AS T2
ON T1.LocationName=T2.LocationName
ORDER By T1.[LocationName]

2] Data for Week to Date and Last Week to Date

DECLARE @LastWeekMonday DATE =DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)
DECLARE @ThisWeekMonday DATE = DATEADD(week, DATEDIFF(day, 0, getdate())/7, 0) 
DECLARE @CurrentDate DATE =GETDATE() 

--SELECT @LastWeekMonday,@ThisWeekMonday, @CurrentDate 


SELECT
    T1.[LocationName],
    WeekToDate,
    LastWeekToDate,
    LastWeekToDate-WeekToDate AS [Difference]
FROM
(
SELECT 
    CASE 
    WHEN GROUPING([LocationName]) = 1 
    THEN 'Total' 
    ELSE [LocationName]

    END [LocationName], 

    COUNT(OI.LocationID) as WeekToDate 

FROM OrderInfo AS OI
LEFT JOIN LocationInfo AS LI
ON OI.LocationID = LI.LocationID
WHERE DateReceived BETWEEN  @ThisWeekMonday AND @CurrentDate
GROUP BY ROLLUP(LI.LocationName) 
) AS T1
LEFT JOIN
(
SELECT 
    CASE 
    WHEN GROUPING([LocationName]) = 1 
    THEN 'Total' 
    ELSE [LocationName]

    END [LocationName], 

    COUNT(OI.LocationID) as LastWeekToDate 

FROM OrderInfo AS OI
LEFT JOIN LocationInfo AS LI
ON OI.LocationID = LI.LocationID
WHERE DateReceived BETWEEN  @LastWeekMonday AND @CurrentDate
GROUP BY ROLLUP(LI.LocationName) 
) AS T2
ON T1.LocationName=T2.LocationName
ORDER By T1.[LocationName]

3] Data for Month to Date and Last Month to Date

DECLARE @LastMonthStart DATE =DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
DECLARE @CurrentMonthStart DATE = DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
DECLARE @CurrentDate DATE =GETDATE() 

SELECT @LastMonthStart,@CurrentMonthStart, @CurrentDate 


SELECT
    T1.[LocationName],
    MonthToDate,
    LastMonthToDate,
    LastMonthToDate-MonthToDate AS [Difference]
FROM
(
SELECT 
    CASE 
    WHEN GROUPING([LocationName]) = 1 
    THEN 'Total' 
    ELSE [LocationName]

    END [LocationName], 

    COUNT(OI.LocationID) as MonthToDate 

FROM OrderInfo AS OI
LEFT JOIN LocationInfo AS LI
ON OI.LocationID = LI.LocationID
WHERE DateReceived BETWEEN  @CurrentMonthStart AND @CurrentDate
GROUP BY ROLLUP(LI.LocationName) 
) AS T1
LEFT JOIN
(
SELECT 
    CASE 
    WHEN GROUPING([LocationName]) = 1 
    THEN 'Total' 
    ELSE [LocationName]

    END [LocationName], 

    COUNT(OI.LocationID) as LastMonthToDate 

FROM OrderInfo AS OI
LEFT JOIN LocationInfo AS LI
ON OI.LocationID = LI.LocationID
WHERE DateReceived BETWEEN  @LastMonthStart AND @CurrentDate
GROUP BY ROLLUP(LI.LocationName) 
) AS T2
ON T1.LocationName=T2.LocationName
ORDER By T1.[LocationName]