Sum coming wrong

Question

Hii to all. 

 i am calculating sum of hours between dates like


SELECT SUM(hours) from Calender where caldate BETWEEN '2013-02-25' AND '2013-02-28'

Output-32


Now suppose i have two dates 
 so i done like this

SELECT SUM(hours) from Calender where caldate BETWEEN '2013-02-25' AND '2013-02-28' or caldate BETWEEN '2013-02-25' AND '2013-02-28'

Output coming again 32

I wants that output should come 64 

Pls help how to do that

---

Niki

Answer 1

SELECT SUM(hours) from Calender where caldate BETWEEN '2013-02-25' AND '2013-02-28' or caldate BETWEEN '2013-02-25' AND '2013-02-28'

Hi Niki,

That is questionable programming.

Just trust SQL Server, never misses adding numbers.

You can double up the rows with UNION ALL.

SELECT SUM(OrderQty) FROM (
SELECT OrderQty FROM Sales.SalesOrderDetail
UNION ALL
SELECT OrderQty FROM Sales.SalesOrderDetail
) X;

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 2

I don't want this thing I do not have two SQL query

Union All Works between two Select Query

I have only one select query

Let me Explain me in Brief

I have a table 

calid            caldate                        XX   YY      Days                 Year   Hours      

4800        2013-02-20 00:00:00     20   51     Wednesday     2013   8.00
4801        2013-02-21 00:00:00     21   52     Thursday        2013   8.00
4802        2013-02-22 00:00:00     22   53     Friday            2013   8.00
4803        2013-02-23 00:00:00     23   54     Saturday       2013   5.00
4804        2013-02-24 00:00:00     24   55     Sunday         2013   0.00
4805        2013-02-25 00:00:00     25   56     Monday        2013   8.00
4806        2013-02-26 00:00:00     26   57     Tuesday        2013   8.00
4807        2013-02-27 00:00:00     27   58     Wednesday    2013   8.00
4808        2013-02-28 00:00:00     28   59     Thursday       2013   8.00

Now in my Ui Code one user is selecting date between 25 to 28  and another also selecting date between 25 to 28

So i have to calculate sum of hours

so thats why i have written like

SELECT SUM(hours) from Calender where caldate BETWEEN '2013-02-25' AND '2013-02-28' 
                          OR caldate BETWEEN '2013-02-25' AND '2013-02-28' 

Output coming 32  as it comes  64

---

Niki

Answer 3

>SELECT SUM(hours) from Calender where

caldate BETWEEN '2013-02-25' AND '2013-02-28'
                          OR
caldate BETWEEN '2013-02-25' AND '2013-02-28'

I see the same predicate twice, OR between. Do I miss something?

That should not give any more rows than a single predicate.

Where are the "users" in the query?

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 4

You have two identical  filters in your where clause so whether you are using OR or AND to combine them, you will get the same result.

Answer 5

A SELECT query without joins, as in your case, will extract data from the table in your FROM clause based on the conditions applied.  Each record will be evaluated to see if it fits the conditions, and in your example you have specified the same condition twice.  Therefore each row returned is returned once, based on it 'fitting' the criteria specified in your query.

If you have separate users specifying the same conditions, and you want the data to appear for each user even if it involves the same records, then you'll need to either run a separate query for each user then combine the results, OR, feed in the users in some way to cause a duplication of the records, e.g.

declare @calendar table
(
	 calid int
	,caldate datetime
	,Hours decimal(4,2)
);

insert into @calendar
(calid,caldate,Hours)
values
(4800,'2013-02-20',8.00),
(4801,'2013-02-21',8.00),
(4802,'2013-02-22',8.00),
(4803,'2013-02-23',5.00),
(4804,'2013-02-24',0.00),
(4805,'2013-02-25',8.00),
(4806,'2013-02-26',8.00),
(4807,'2013-02-27',8.00),
(4808,'2013-02-28',8.00);

select
	 sum(c.Hours)
from @calendar c
inner join
	(select 1 as user_number, '2013-02-25' start_date, '2013-02-28' end_date union all
	 select 2, '2013-02-25', '2013-02-28') users
on  c.caldate between users.start_date and users.end_date;