Hello Every One ,
i have a query that extracts the sales per hour in a particular time period
to know in which hour of the day does the business thrive and in which does it struggle
the query goes like this :
select top(24) Datepart(HOUR,inv.invoiceDate) as Hr , sum(iif(inv.invoicetype=1,inv.totalafterdiscount , -inv.totalafterdiscount)) as Amount
from Invoices inv
where (inv.invoicetype = 1 or inv.invoicetype = 3) and inv.store = @storeid and (inv.invoicedate between @StartDate and @EndDate)
group by Datepart(HOUR,inv.invoiceDate)
order by Hr
the problem is that the hours in which no sales happened doesn't appear in result
which is very understandable given the way the query is written
what i want is to make the hours in which no sales took place appear in the result with amount value of zero
how can i achieve that ?
Thanks every one for taking time to read this and for helping me ,
really appreciated.