Untitled

mail@pastecode.io avatarunknown
plain_text
a month ago
1.0 kB
4
Indexable
Never
DECLARE @from_date DATETIME;
DECLARE @to_date DATETIME;
SET @from_date = '<<From Date Eg: 2023-01-01>>';
SET @to_date = DATEADD(DAY, 1, DATEDIFF(DAY, 0, '<<Date To Eg: 2023-01-30>>'));  --'30-01-2023'
IF @from_date = '' OR ISDATE(@from_date) <> 1 
BEGIN
SET @from_date = '01-01-1990'; 
END
IF @from_date <> '' OR ISDATE(@from_date) = 1 
BEGIN
SET @from_date = @from_date;
END
IF @to_date = '' OR ISDATE(@to_date) <> 1 
BEGIN
SET @to_date = '01-01-2200';
END
IF @to_date <> '' OR ISDATE(@to_date) = 1 
BEGIN
SET @to_date = @to_date;
END

select sl.branch_no,
sl.workstation_no as till,
sum(sl.extension) as total_sales,
sum(sl.item_qty) as item_qty,
count(sl.sale_tx_no) as total_transactions,
avg(sl.extension) as avg_spend_trans,
avg(sl.line_id) avg_items_per_trans,
avg(sl.item_qty) as avg_items_qty,
sum(sl.extension-sl.cost) as gp,
avg(sl.extension-sl.cost) as avg_gp_trans
from sales_tx_lines sl
where sl.last_modified between @from_date and @to_date
group by sl.branch_no,sl.workstation_no