# Thread: Wrong results from sum

1. Registered User
Join Date
Jul 2011
Posts
30

## Unanswered: Wrong results from sum

hello to everyone!
i have two tables.
the first one have the columns quantity and date.
the second have the columns firstday and lastday,it is a week range.
so, i want to sum the quantity for each day which is between firstday and lastday,but the sum returns wrong results.
i use the same conditions with an other query which counts the numbers of the days between the firstday and the lastday, but unfortunately i have wrong results.
can someone to help me??

the code of the "count" (correct results):

SELECT count(distinct[Date]),firstday,lastday
FROM [test].[dbo].[WeekRange],[test].[dbo].[DocHdSal]
where convert(varchar,[Date],103)>=firstday and convert(varchar,[Date],103)<=lastday and month(convert(datetime,firstday,103))=month(Date)a nd month(convert(datetime,lastday,103))=month(Date)
group by firstday,lastday
order by month(convert(datetime,firstday,103)),day(convert( datetime,firstday,103))

the code of the "sum"(wrong results):

SELECT sum([quantity])
FROM [test].[dbo].[DocHdSal],[test].[dbo].[WeekRange]
where convert(varchar,[Date],103)>=firstday and convert(varchar,[Date],103)<=lastday and month(convert(datetime,firstday,103))=month(Date)a nd month(convert(datetime,lastday,103))=month(Date)
group by firstday,lastday
order by month(convert(datetime,firstday,103)),day(convert( datetime,firstday,103))

thank you very much!!!

2. Registered User
Join Date
Mar 2007
Location
Holmestrand, Norway
Posts
332
If you're using SQL Server 2008 or newer, I would suggest casting to the datatype date instead of varchar. If you need to use varchar, you have to use a date format like 'yyyy[sep]MM[sep]dd' for string comparison (>,<,>=,<=,order by) to work as intended, otherwise you will get wrong grouping/sorting/comparison. Personally, I tend to use date format 112, which is yyyyMMdd without any (annoying) separators. Your code will only work as intended for firstday=lastday.

You may also have a look at the ANSI SQL-92 Join Syntax
Last edited by roac; 07-04-11 at 09:17.

3. Registered User
Join Date
Jun 2011
Posts
6

## Try this Query

SELECT sum([quantity])
FROM [test].[dbo].[DocHdSal]
INNER JOIN [test].[dbo].[WeekRange] ON month(convert(datetime,firstday,103))=month(Date) AND month(convert(datetime,lastday,103))=month(Date)
where convert(varchar,[Date],103) between firstday AND lastday
order by month(convert(datetime,firstday,103)),day(convert( datetime,firstday,103))

4. Registered User
Join Date
Jul 2011
Posts
30
Originally Posted by roac
If you're using SQL Server 2008 or newer, I would suggest casting to the datatype date instead of varchar. If you need to use varchar, you have to use a date format like 'yyyy[sep]MM[sep]dd' for string comparison (>,<,>=,<=,order by) to work as intended, otherwise you will get wrong grouping/sorting/comparison. Personally, I tend to use date format 112, which is yyyyMMdd without any (annoying) separators. Your code will only work as intended for firstday=lastday.

You may also have a look at the ANSI SQL-92 Join Syntax
i use sql server 2000.
i prefer 103 data format because is more common in my country.
also,i don't do comparisons with a "stable" date but with the Dates from the table DocHdSal.
the strange thing is that with the same comparisons i have the correct results for days counting.on the other hand, the sum function doesn't "work".

i don't use between clause because i had problems with it in other query which was for the same project.
Last edited by nightmare207; 07-04-11 at 11:05.

5. Registered User
Join Date
Jul 2011
Posts
30
Originally Posted by greenx
SELECT sum([quantity])
FROM [test].[dbo].[DocHdSal]
INNER JOIN [test].[dbo].[WeekRange] ON month(convert(datetime,firstday,103))=month(Date) AND month(convert(datetime,lastday,103))=month(Date)
where convert(varchar,[Date],103) between firstday AND lastday
order by month(convert(datetime,firstday,103)),day(convert( datetime,firstday,103))
thank you very much for your respond.
i will try immediately your solution and i will say to you what happen.

6. Registered User
Join Date
Jul 2011
Posts
30
Originally Posted by greenx
SELECT sum([quantity])
FROM [test].[dbo].[DocHdSal]
INNER JOIN [test].[dbo].[WeekRange] ON month(convert(datetime,firstday,103))=month(Date) AND month(convert(datetime,lastday,103))=month(Date)
where convert(varchar,[Date],103) between firstday AND lastday
order by month(convert(datetime,firstday,103)),day(convert( datetime,firstday,103))
i tried your solution and if i use "group by" i have the same wrong results.
on the other hand,without "group by" i have error. (msg 8120,level 16,state 1,line 1 Column 'test.dbo.WeekRange.firstday' is invalid in the select list because it is not contained in either an aggregate function or the group by clause.)

7. Registered User
Join Date
Mar 2007
Location
Holmestrand, Norway
Posts
332
You may very well use 103 for displaying data, but comparing date strings of format 103 wont yield correct result. Keep in mind, you are comparing text data, not dates. Run the code below, and see which format is being printed in correct order:

create table #a (
dt varchar(20)
)

insert into #a values (CONVERT(varchar,getdate(),103))
insert into #a values (CONVERT(varchar,getdate()+1,103))
insert into #a values (CONVERT(varchar,getdate()-1,103))
go

select * from #a order by dt
go

truncate table #a
go

insert into #a values (CONVERT(varchar,getdate(),112))
insert into #a values (CONVERT(varchar,getdate()+1,112))
insert into #a values (CONVERT(varchar,getdate()-1,112))
go

select * from #a order by dt
go

drop table #a
Last edited by roac; 07-04-11 at 14:13.

8. Registered User
Join Date
Jul 2011
Posts
30
my friend roac,the result is:

04/07/2011
05/06/2011
05/07/2010
05/07/2011
05/07/2012
05/08/2011
06/07/2011

20100705
20110605
20110704
20110705
20110706
20110805
20120705

9. Registered User
Join Date
Jul 2011
Posts
30
i tried to convert the firstday-lastday(varchar), from the table Week range,to varchar,112 but i didn't have different results.it was the same.
on the other side,i did the same converting for the Date (datetime) from the table DocHdSal and i had the correct result,varchar(112).

10. Registered User
Join Date
Jul 2011
Posts
30
i find the solution.
it wants a "distinct" in sum function.
i can't understand the reason but i have the correct results with the distinct.

11. Registered User
Join Date
Mar 2007
Location
Holmestrand, Norway
Posts
332
The join is causing duplicate rows. Most likely because of at least one of the following two:
* Incorrect string comparison
* Multiple rows in WeekRange for each month

Distinct in a sum makes normally very little sense, don't you allow two sales with the same quantity?

A piece of working code:
Code:
```use tempdb;
go

create table DocHdSal (
quantity int,
date datetime
);

create table WeekRange (
firstday varchar(10),
lastday varchar(10)
);

insert into DocHdSal values (1,'2011-01-01');
insert into DocHdSal values (2,'2011-01-01');
insert into DocHdSal values (1,'2011-01-07');
insert into DocHdSal values (1,'2011-01-01');
insert into DocHdSal values (2,'2011-01-06');
go

insert into WeekRange values ('31/12/2010','03/01/2011');
insert into WeekRange values ('04/01/2011','10/01/2011');
go

SELECT sum([quantity]),firstday,lastday
FROM [DocHdSal]
INNER JOIN [WeekRange] ON
convert(varchar,[Date],112)>=convert(varchar,convert(datetime,firstday,103),112) and
convert(varchar,[Date],112)<=convert(varchar,convert(datetime,lastday,103),112)
group by
firstday,
lastday
order by
year(convert(datetime,firstday,103)),
month(convert(datetime,firstday,103)),
day(convert( datetime,firstday,103));

drop table DocHdSal;
drop table WeekRange;```
Please note, you can get wrong quantity if you happen to have overlapping periods in the WeekRange table. You may also be interrested in seing that I use format 112 string comparison of dates, and I've eliminated the need of the distinct, so you hay actually have two rows in the same period with the same quantity.
Last edited by roac; 07-06-11 at 07:28.

12. Registered User
Join Date
Jul 2011
Posts
30
exactly,i have multiple rows in WeekRange for each month,also for each week.
this happen because i create the firstday and the lastday for the WeekRange from the table DocHdSal where there are many Days.

you are right.i maybe have the same quantity.i have not thought it.

i will run your code immediately.

what do you mean with the term overlapping periods??

THANK YOU VERY MUCH!!!

13. Registered User
Join Date
Jul 2011
Posts
30
i post sample data from my tables.

from table DocHdSal
2334 02/01/2009 12:00
2432 05/01/2009 12:00
2455 06/01/2009 12:00
655 07/01/2009 12:00
454 08/01/2009 12:00
3345 09/01/2009 12:00
234 12/01/2009 12:00
566 13/01/2009 12:00

we must have on our minds that we can have many sales in the same day.

from WeekRange
02/01/2009 09/01/2009
02/01/2009 09/01/2009
02/01/2009 09/01/2009
02/01/2009 09/01/2009
02/01/2009 09/01/2009
02/01/2009 09/01/2009
12/01/2009 16/01/2009
12/01/2009 16/01/2009
12/01/2009 16/01/2009
12/01/2009 16/01/2009
12/01/2009 16/01/2009
19/01/2009 23/01/2009

some extra information.
I insert the data into the table WeekRange with an query which creates the firstday and the lastday from the Days of the DocHdSal.

14. Registered User
Join Date
Jul 2011
Posts
30
my friend roac,
i run your code in my db and i have wrong results.
i create another test table with quantities and dates.in this table i put many sales in a day.the results are wrong for both codes,me and yours.

do you believe that a solution is to have a row for each week range??
i will try to do someting like that.
i don't know how but i will try.

if you have any suggestions,please tell to me!

thanks again!

15. Registered User
Join Date
Mar 2007
Location
Holmestrand, Norway
Posts
332
A few questions:
* Why are there overlapping records in WeekRange?
* Can you please post complete schema and sample data for DocHdSal and WeekRange?

You need some extra join criteria between WeekRange and DocHdSal, otherwise it is simply not possible to accomplish what you want.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•