Sales per Day of Week
@@SalesPerDay
(SQL in Automation > Scripts)
declare @StartDate datetime = convert(varchar(25),'{Start}',126)
declare @EndDate datetime = convert(varchar(25),'{End}',126)
declare @StartDay varchar(10) = @1 -- set start day of week or use @1 parameter
/************************************/
declare @Day varchar(10) = ''
declare @TotalSales money = 0.00
declare @DayCount int = 1
declare @tbl_Days table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Day] varchar(5)
)
declare @tbl_Sales table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Day] varchar(5)
,[Amount] decimal(9,2)
,[Tickets] int
,[AvgTx] decimal(5,2)
,[Percent] decimal(5,2)
)
IF @StartDay='Sun' INSERT INTO @tbl_Days ([Day]) VALUES ('Sun'),('Mon'),('Tue'),('Wed'),('Thu'),('Fri'),('Sat')
IF @StartDay='Mon' INSERT INTO @tbl_Days ([Day]) VALUES ('Mon'),('Tue'),('Wed'),('Thu'),('Fri'),('Sat'),('Sun')
IF @StartDay='Tue' INSERT INTO @tbl_Days ([Day]) VALUES ('Tue'),('Wed'),('Thu'),('Fri'),('Sat'),('Sun'),('Mon')
IF @StartDay='Wed' INSERT INTO @tbl_Days ([Day]) VALUES ('Wed'),('Thu'),('Fri'),('Sat'),('Sun'),('Mon'),('Tue')
IF @StartDay='Thu' INSERT INTO @tbl_Days ([Day]) VALUES ('Thu'),('Fri'),('Sat'),('Sun'),('Mon'),('Tue'),('Wed')
IF @StartDay='Fri' INSERT INTO @tbl_Days ([Day]) VALUES ('Fri'),('Sat'),('Sun'),('Mon'),('Tue'),('Wed'),('Thu')
IF @StartDay='Sat' INSERT INTO @tbl_Days ([Day]) VALUES ('Sat'),('Sun'),('Mon'),('Tue'),('Wed'),('Thu'),('Fri')
WHILE @DayCount < 8
BEGIN
SELECT @Day = [Day] FROM @tbl_Days WHERE [ID]=@DayCount
INSERT INTO @tbl_Sales
SELECT
@Day as [Day]
, SUM(CASE WHEN (left(datename(dw,[Date]),3)=@Day)
THEN [TotalAmount]
ELSE 0
END) as [Amount]
, COUNT(CASE WHEN (left(datename(dw,[Date]),3)=@Day)
THEN 1
ELSE null
END) as [Tickets]
,0,0
FROM [dbo].[Tickets]
WHERE [Date]>=@StartDate AND [Date]<=@EndDate
SET @DayCount = @DayCount + 1
END
INSERT INTO @tbl_Sales
SELECT 'TTL', sum([Amount]), sum([Tickets]), 0, 0 FROM @tbl_Sales
set @TotalSales = (SELECT [Amount] FROM @tbl_Sales WHERE [Day]='TTL')
UPDATE @tbl_Sales SET
[AvgTx] = (CASE WHEN [Tickets]=0 THEN 0 ELSE [Amount]/[Tickets] END)
,[Percent] = [Amount]/@TotalSales * 100
SELECT [Day], [Amount], [Tickets], [AvgTx], [Percent] FROM @tbl_Sales
Report:
[Sales Per Day: 15, 25, 15, 20, 20]
>Day|Sales|Tx|AvgTx|%Sales
@@SalesPerDay:'Sun'
Result: