Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    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. #2
    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 10:17.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    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. #4
    Join Date
    Jul 2011
    Posts
    30
    Quote Originally Posted by roac View Post
    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
    first of all, thanks for your reply.
    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 12:05.

  5. #5
    Join Date
    Jul 2011
    Posts
    30
    Quote Originally Posted by greenx View Post
    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. #6
    Join Date
    Jul 2011
    Posts
    30
    Quote Originally Posted by greenx View Post
    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. #7
    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))
    insert into #a values (CONVERT(varchar,dateadd(year,1,getdate()),103))
    insert into #a values (CONVERT(varchar,dateadd(year,-1,getdate()),103))
    insert into #a values (CONVERT(varchar,dateadd(month,1,getdate()),103))
    insert into #a values (CONVERT(varchar,dateadd(month,-1,getdate()),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))
    insert into #a values (CONVERT(varchar,dateadd(year,1,getdate()),112))
    insert into #a values (CONVERT(varchar,dateadd(year,-1,getdate()),112))
    insert into #a values (CONVERT(varchar,dateadd(month,1,getdate()),112))
    insert into #a values (CONVERT(varchar,dateadd(month,-1,getdate()),112))
    go

    select * from #a order by dt
    go

    drop table #a
    Last edited by roac; 07-04-11 at 15:13.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  8. #8
    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. #9
    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. #10
    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. #11
    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 08:28.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  12. #12
    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??

    your help is very useful!!!

    THANK YOU VERY MUCH!!!

  13. #13
    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. #14
    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. #15
    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.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

Posting Permissions

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