LFL sales query in SQL

Question

This might seem a bit like a do-my-homework-for-me question (and it is), but I think that the sales results I’m trying to get are fairly generic and will be useful for anyone doing sales reporting.

I have a basic Sales table (fields: Branch, DateOfSale, SalesAmount) in SQL Server 2005 and I need to build a report with the data in that table. I'm after the SQL that will give me the data for that report.

The actual table used BranchID, which I have changed to Branch in this example.

SetUp Script

-- create sales table
CREATE TABLE Sales(
Branch varchar(30) NOT NULL,
DateOfSale smalldatetime NOT NULL,
SalesAmount money NOT NULL)

-- London: same week last year
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('London','20090714',100)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('London','20090715',200)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('London','20090716',300)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('London','20090717',400)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('London','20090718',500)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('London','20090719',600)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('London','20090720',700)

-- London: last 2 weeks sales
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('London','20090706',1000)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('London','20090707',1100)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('London','20090708',1200)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('London','20090709',1300)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('London','20090710',1400)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('London','20090711',1500)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('London','20090712',1600)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('London','20090713',1700)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('London','20090714',1800)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('London','20090715',1900)

-- Cape Town: last 2 weeks sales
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('Cape Town','20090706',2000)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('Cape Town','20090707',2100)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('Cape Town','20090708',2200)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('Cape Town','20090709',2300)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('Cape Town','20090710',2400)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('Cape Town','20090711',2500)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('Cape Town','20090712',2600)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('Cape Town','20090713',2700)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('Cape Town','20090714',2800)
INSERT INTO Sales (Branch, DateOfSale, SalesAmount) VALUES ('Cape Town','20090715',2900)

Assumptions

1. The first day of the week is Monday
2. Today’s date is Wed, 16 July 2009

Required Result

Branch   DailySales   DailyLFL   WTD    WTDLFL  LFL
London       1900.00    300.00  5400.00  600.00      Y
Cape Town    2900.00    2200.00  8400.00  6300.00

Daily Sales Sales from yesterday

DailyLFL: Like-for-like sales. So, sales for this day 1 year ago. Not this day as in 15 July, but rather Wed of the 29th week (which is 16 July). If this branch is less than a year old (like the Cape Town branch), then use last weeks sales (i.e. Wed last week). If no sales last week, then zero.

WTD: Week-to-date. Sales summed starting at Monday this week until yesterday. So, Mon, Tues, Wed in my example.

WTD LFL: Week-to-date like-for-like. The same logic as LFL, but this time with WTD instead of Daily Sales

LFL: A bit flag show if we were able to use LFL (value: 1) or had to use last weeks sales (value: 0)

As these are standard metrics in retail sales, I’m hoping that someone has already written the SQL for them. Maybe not the if-you-do-not-have-last-year-then-use-last-week bit, but definitely LFL and WTD.

If the solution needs a calendar table (e.g. http://tinyurl.com/nt5gck), that's fine.

Answer 1

I got no good answers, so I had to do this myself. For the sake of completeness, here is my answer.

Please note that the answer uses the real table name in my database (Sales_ByDay: ProfitCentreID, DateOfSale, NetSalesAmt), instead of the made-up table I referred to in my question (Sales: Branch, DateOfSale, SalesAmount)

Function: Alerts_DailySales

create function Alerts_DailySales
(
@StartDate smalldatetime, 
@EndDate smalldatetime
)
returns table
as
return
(
select 
r.ProfitCentreID, 
s.DateOfSale, 
sum(s.NetSalesAmt) DailySales
from Sales_ByDay s
inner join RevenueCentres r on s.RevenueCentreID = r.RevenueCentreID
where s.DateOfSale >= @StartDate
and s.DateOfSale <= @EndDate
group by r.ProfitCentreID, s.DateOfSale
)

Function: Alerts_WTDSales

create function Alerts_WTDSales
(
@StartDate smalldatetime, 
@EndDate smalldatetime
)
returns table
as
return
(
select
a.ProfitCentreID,
a.DateOfSale, 
max(a.DailySales) DailySales, 
sum (b.DailySales) WTDSales
from dbo.Alerts_DailySales(@StartDate, @EndDate) a
left outer join dbo.Alerts_DailySales(@StartDate, @EndDate) b on a.ProfitCentreID = b.ProfitCentreID and a.DateOfSale >= b.DateOfSale
group by a.ProfitCentreID, a.DateOfSale
)

Function: Date_GetMonday

create function [dbo].[Date_GetMonday] (@dt smalldatetime)
returns smalldatetime
as
begin
    return dateadd(week, datediff(week, 0, @dt-1), 0)
end

Proc: Alerts_SalesReport

create proc Alerts_SalesReport
as

set nocount on 

declare @StartDate_CW smalldatetime
declare @EndDate_CW smalldatetime
declare @StartDate_LW smalldatetime
declare @EndDate_LW smalldatetime
declare @StartDate_LY smalldatetime
declare @EndDate_LY smalldatetime    

-- sort out dates
set @EndDate_CW = dateadd(day, -1, Util.dbo.Date_RoundToDay(getdate())) -- yesterday
set @StartDate_CW = Util.dbo.Date_GetMonday(@EndDate_CW)
set @StartDate_LW = dateadd(day, -7, @StartDate_CW)
set @EndDate_LW = dateadd(day, -7, @EndDate_CW)
set @StartDate_LY = dateadd(week, -52, @StartDate_CW)
set @EndDate_LY = dateadd(week, -52, @EndDate_CW)

-- get sales
select 
ProfitCentreName Branch,
cw.DailySales, 
coalesce(ly.DailySales, lw.DailySales, 0) DailyLFL, 
cw.DailySales - coalesce(ly.DailySales, lw.DailySales, 0) DailyVar, 
cw.WTDSales, 
coalesce(ly.WTDSales, lw.WTDSales, 0) WTD_LFL, 
cw.WTDSales - coalesce(ly.WTDSales, lw.WTDSales, 0) WTDVar, 
cast(isnull(ly.DailySales, 0) as bit) LFL
from ProfitCentreNames pn 
inner join dbo.Alerts_WTDSales(@StartDate_CW, @EndDate_CW) cw on pn.ProfitCentreID = cw.ProfitCentreID
left outer join dbo.Alerts_WTDSales(@StartDate_LW, @EndDate_LW) lw on cw.ProfitCentreID = lw.ProfitCentreID and dateadd(day, -7, cw.DateOfSale) = lw.DateOfSale
left outer join dbo.Alerts_WTDSales(@StartDate_LY, @EndDate_LY) ly on cw.ProfitCentreID = ly.ProfitCentreID and dateadd(week, -52, cw.DateOfSale) = ly.DateOfSale
where cw.DateOfSale = @EndDate_CW
order by pn.ProfitCentreName, cw.DateOfSale

I'm not sure if that will work straight away if you just copy and paste it, but you should be able to pick up on the logic.