compare data current with past year

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

compare data current with past year

Author

Topic

Neutje
Starting Member

8 Posts

Posted - 2006-10-16 : 05:24:26

Hi,

I'm working for a few days on this and can't figure it out anymore. Nothing seems to work so I would be glad with some help.

I need to get the data from the current year and compare it with data from the past year
The data is in the same table. I tried to join the tables and tried with a subquery but both don't work, with the subquery I get a lot of errors and I think it isn't possible with a subquery so I continued with the join query

This is my join version with give me only the good results when there is only one row found, more rows do return wrong results:

SELECT Table1.nr1, Table1.nr2, Table1.name, Table1.date, Table1.count, Table1.price, Table1.country, [Table1.count]*[Table1.price] AS Expr1,
Table2.price, Table2.count, Table2.nr2, Table2.date, [Table2.price]*[Table2.count] AS Expr2
FROM mytable AS Table1
INNER JOIN mytable AS Table2
ON Table1.name=Table2.name
GROUP BY Table1.nr1, Table1.nr2, Table1.name, Table1.date, Table1.count, Table1.price, Table1.country, [Table1.count]*[Table1.price],
Table2.nr1, Table2.nr2, Table2.name, Table2.date, Table2.count, Table2.price, Table2.country, [Table2.count]*[Table2.price]
HAVING ((Table1.nr2<>"110000") And (Table1.date>=DateAdd('yyyy',-1,2005-11-11) And Table1.date<=DateAdd('yyyy',-1,2005-12-11))) AND
((Table2.nr2<>"110000") And (Table2.date>=2005-11-11 And Table2.date<=2005-11-11))
ORDER BY Table1.name,Table2.name;

does anyone has a idea or a way to get the right results

Best Regards,
Neut David
Online Mania
www.onlinemania.be

Kristen
Test

22859 Posts

Posted - 2006-10-16 : 05:33:08

DateAdd('yyyy',-1,2005-11-11)

should be

DateAdd(yyyy, -1, '20051111')

but I expect that is just a typo in your example here. Same for:

Table2.date>=2005-11-11
should be
Table2.date >= '20051111'

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-16 : 05:33:11

first, you need to enclose the date values in single quotes...

second, try to build the query from 1 to n fields until you've satisfied your requirement...

third, it'll help if you can provide sample data and expected result, plus some ddl to build the tables

--------------------
keeping it simple...

Neutje
Starting Member

8 Posts

Posted - 2006-10-16 : 05:48:50

Tnx for you input
The query without the join works fine and gives me the results of the current year, now i want to add the results of the past year to be able to compare them

Here is some sample data - mytable:

nr1, nr2, name , date , count, price, country
1 , 11 , name1,2005-11-19, 5 , 3 , BE
1 , 12 , name2,2005-11-20, 4 , 4 , BE
2 , 13 , name1,2004-11-18, 2 , 3 , BE
3 , 14 , name3,2004-11-17, 1 , 5 , NL

Result I want to see is :
name , 2005 , 2004
name1 15 6
name2 16 0
name3 0 5

all results should be calculed together when they have the same nr1,nr2 or name
I would like to see the results for each name in the current and the last year

Hope this helps

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-16 : 06:07:40

[code]
declare @t1 table(nr1 int, nr2 int, [name] varchar(10),[date] datetime, ncount int, price int, country varchar(5))

insert into @t1(nr1,nr2,[name],[date],ncount,price, country)
select 1 , 11 , 'name1','2005-11-19', 5 , 3 , 'BE'
union select
1 , 12 , 'name2','2005-11-20', 4 , 4 , 'BE'
union select
2 , 13 , 'name1','2004-11-18', 2 , 3 , 'BE'
union select
3 , 14 , 'name3','2004-11-17', 1 , 5 , 'NL'

select [name],max([2004]) as '2004' ,max([2005]) as '2005'
from (
select [name],case when year([date])='2004' then ncount*price else 0 end as '2004',
case when year([date])='2005' then ncount*price else 0 end as '2005'
from @t1
) s
group by [name]
[/code]

--------------------
keeping it simple...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-16 : 06:56:03

[code]-- Prepare test data
DECLARE @MyTable TABLE (nr1 TINYINT, nr2 TINYINT, name VARCHAR(5), date DATETIME, count TINYINT, price TINYINT, country VARCHAR(2))

INSERT @MyTable
SELECT 1, 11, 'name1', '2005-11-19', 5, 3, 'BE' UNION ALL
SELECT 1, 12, 'name2', '2005-11-20', 4, 4, 'BE' UNION ALL
SELECT 2, 13, 'name1', '2004-11-18', 2, 3, 'BE' UNION ALL
SELECT 3, 14, 'name3', '2004-11-17', 1, 5, 'NL'

-- Do the work. Use sum in case there are several data for same year and name.
SELECT name,
SUM(CASE WHEN YEAR(date) = 2005 THEN count * price ELSE 0 END) '2005',
SUM(CASE WHEN YEAR(date) = 2004 THEN count * price ELSE 0 END) '2004'
FROM @MyTable
GROUP BY name[/code]
Peter Larsson
Helsingborg, Sweden

Neutje
Starting Member

8 Posts

Posted - 2006-10-16 : 07:38:12

I did some testing and didn't get the correct results with jen his answer
With the code of peso I do get the correct results with this modified query (need to be able to get the results within a date range so I added the where clause to the query)

In the query analyser I get the correct results and the query works fine and fast but
When I copy the code to my access program I get a error when I try to save it:
syntax error, operator missing in the query-expression sum(case when year(date) = 2005 then count * price else 0 end) '2005'
Why does the query doesn't work in access ? Do I have to change something?

select name,
sum(case when year(date) = 2005 then count * price else 0 end) '2005',
sum(case when year(date) = 2004 then count * price else 0 end) '2004'

from mytable
where (date>='2005-11-11' and date<='2005-12-11') OR (date>='2004-11-11' and date<='2004-12-11')
group by name
order by name

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-16 : 07:46:14

It is the CASE statement that errors in ACCESS. You should have put this topic in the ACCESS forum.
However, try this

SELECT		Name,
		SUM(IIF(YEAR(Date) = 2005, Count * Price, 0)) AS '2005',
		SUM(IIF(YEAR(Date) = 2004, Count * Price, 0)) AS '2004'
FROM		MyTable
where		(Date >= #2005-11-11# AND Date <= #2005-12-11#)
		OR (Date >= #2004-11-11# AND Date <= #2004-12-11#)
GROUP BY	Name
ORDER  BY	Name

There are guides for these sorts of thing in ACCESS. Look in Help file for PIVOT and TRANSFORM keywords.

Peter Larsson
Helsingborg, Sweden

Neutje
Starting Member

8 Posts

Posted - 2006-10-17 : 03:43:17

tnx 4 your help, I got it working now
greetz

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 03:47:52

Thank you for the feedback.

For helping others with similar problem, it is a good practice to share your solution here. Post the solution so that other can learn from it too.

Peter Larsson
Helsingborg, Sweden

Neutje
Starting Member

8 Posts

Posted - 2006-10-17 : 04:14:40

SELECT Name,
SUM(IIF(YEAR(Date) = 2005, Count * Price, 0)) AS '2005',
SUM(IIF(YEAR(Date) = 2004, Count * Price, 0)) AS '2004'
FROM MyTable
where (Date >= #2005-11-11# AND Date <= #2005-12-11#)
OR (Date >= #2004-11-11# AND Date <= #2004-12-11#)
GROUP BY Name
ORDER BY Name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources