need help in sql

Question

Hey Guys

I need help here. Please see attached. I want revenue column in my sql query. I am unable to get it . The way to calculate revenue column is retailsales/sum(retailsales). Also the one attached is a derived table from different sources.

My SQL so far which calculates till retail sales is as follows:-

select cast(h.StartDateTime as date) as TxnDate,b.code as BranchCode,b.name as BranchName,p.code as SKU,p.name as SKU_Description, i.LineValue as SalesValue, i.Quantity as Quantity
into #wtd_sales
from Txn.Header h
join dbo.vw_branch b on h.fkBranchId = b.pkBranchID
join txn.ItemLine i on i.fkHeaderId = h.pkHeaderId
join ProductVariant pv on pv.pkProductVariantID = i.fkProductVariantId
join product p on p.pkProductID = pv.fkProductID
where cast(h.startdatetime as date) =cast(getdate()-1 as date) and b.code in(853)

select SKU,SKU_Description,sum(Quantity) as Quantity,sum(SalesValue) as Retailsales
,row_number() over(order by sum(quantity) desc)as r
into #wtd_bd
from #wtd_sales
group by sku,SKU_Description

/Not sure whether this approach is correct to calculate revenue as seen in the screenshot/

select sku,sku_description,Quantity,sum(Retailsales) over(order by r asc) as Total
from #wtd_bd
where r between 1 and 25

any help will be much appreciated

Farhan Jamil

Answer 1

I think you want

Select sku, sku_description, Quantity, Retailsales,
  Retailsales / Sum(RetailSales) Over () As Revenue
From wtd_bd;

Tom