Click here to Skip to main content
14,171,916 members
Rate this:
 
Please Sign up or sign in to vote.
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 :

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

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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/[^]
   
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01 | 2.8.190524.2 | Last Updated 15 May 2015
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100