Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 :

SQL
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.
Posted
Updated 15-May-15 16:28pm
v2

1 solution

There are a number of ways, but you could create a small table of hours with an inclusive join and setting null to zero or union all the two tables and group results by hour. You could just add the hour result via code and group.

See http://blog.jooq.org/2013/11/19/how-to-create-a-range-from-1-to-10-in-sql/[^]
 
Share this answer
 
v2
Comments
Ahmad_kelany 16-May-15 0:14am    
thanks very much
have a very nice day

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900