Help with multiple query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Help with multiple query

Author

Topic

drtduarte
Starting Member

12 Posts

Posted - 2009-07-10 : 13:07:44

Hello to all,

I need to have a query to canculate the percentage of invoiced ammount compared with last year, so I'm trying to use the following query without sucess:

SELECT year(ft.fdata),
January = a.January * 100.0 / b.January
FROM
(select year(ft.fdata),
sum(case when month(ft.fdata)=1
then eivain1+eivain2+eivain3+eivain4+eivain5+eivain6+eivain7+eivain8+eivain9 else 0 end ) as January
from ft (nolock) where year(ft.fdata)=2009 group by year(ft.fdata)
)a
(select year(ft.fdata),
sum(case when month(ft.fdata)=1
then eivain1+eivain2+eivain3+eivain4+eivain5+eivain6+eivain7+eivain8+eivain9 else 0 end ) as January
from ft (nolock) where year(ft.fdata)=2008 group by year(ft.fdata)
)b

Any idea on what could be wrong?

Thanks
David

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-10 : 13:20:12

[code]
SELECT SUM(CASE WHEN month(ft.fdata)=1 and year(ft.fdata)=year(GETDATE()) THEN eivain1+eivain2+eivain3+eivain4+eivain5+eivain6+eivain7+eivain8+eivain9 else 0 end ) *100.0/SUM(CASE WHEN month(ft.fdata)=1 and year(ft.fdata)<>year(GETDATE()) THEN eivain1+eivain2+eivain3+eivain4+eivain5+eivain6+eivain7+eivain8+eivain9 else 0 end ) AS Jan,
SUM(CASE WHEN month(ft.fdata)=2 and year(ft.fdata)=year(GETDATE()) THEN eivain1+eivain2+eivain3+eivain4+eivain5+eivain6+eivain7+eivain8+eivain9 else 0 end ) *100.0/SUM(CASE WHEN month(ft.fdata)=2 and year(ft.fdata)<>year(GETDATE()) THEN eivain1+eivain2+eivain3+eivain4+eivain5+eivain6+eivain7+eivain8+eivain9 else 0 end ) AS Feb,
....
SUM(CASE WHEN month(ft.fdata)=12 and year(ft.fdata)=year(GETDATE()) THEN eivain1+eivain2+eivain3+eivain4+eivain5+eivain6+eivain7+eivain8+eivain9 else 0 end ) *100.0/SUM(CASE WHEN month(ft.fdata)=12 and year(ft.fdata)<>year(GETDATE()) THEN eivain1+eivain2+eivain3+eivain4+eivain5+eivain6+eivain7+eivain8+eivain9 else 0 end ) AS Dec
FROM YouRatble
WHERE ft.fdata >= DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)
AND ft.fdata < DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
[/code]

drtduarte
Starting Member

12 Posts

Posted - 2009-07-13 : 11:55:44

Hello visakh16,

This query is very nice, to work with only two years...
In my case I need to build a report for all years since 2007, to compare the invoiced amount with previous year...

2006 vs 2007
2007 vs 2008
2008 vs 2009
2009 vs 2010

So I need to output something like this:
January February March April May June July August September November December
2010
2009
2008
2007
2006

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-13 : 13:55:25

you can very well extend my solution to bring data more than 2 years by grouping on year value. make a try. If you face any problem, i will help you out

drtduarte
Starting Member

12 Posts

Posted - 2009-07-14 : 03:31:35

Hello my friend,

I tried that yesterday (group by year(ft.fdata)) before posting with no result,
I get the error "Divide by zero error encountered." and if I turn off warnings, I get the following:

1- 0.000000 0.000000 0.000000
2- NULL NULL NULL

Any idea on how to group by year without this issue...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-14 : 14:04:33

[code]
DECLARE @Start int,@End int
SELECT @Start=2007,@End=2010

SELECT YEAR(fdata) AS YearVal,
SUM(CASE WHEN month(ft.fdata)=1 THEN eivain1+eivain2+eivain3+eivain4+eivain5+eivain6+eivain7+eivain8+eivain9 else 0 end ) AS Jan,
SUM(CASE WHEN month(ft.fdata)=2 THEN eivain1+eivain2+eivain3+eivain4+eivain5+eivain6+eivain7+eivain8+eivain9 else 0 end ) AS Feb,
....
SUM(CASE WHEN month(ft.fdata)=12 THEN eivain1+eivain2+eivain3+eivain4+eivain5+eivain6+eivain7+eivain8+eivain9 else 0 end ) AS Dec INTO #Temp
FROM YouRatble
WHERE ft.fdata >= DATEADD(yy,@Start-1901,0)
AND ft.fdata < DATEADD(yy,@End-1899,0)
GROUP BY YEAR(fdata)

SELECT t.YearVal,
t.Jan*100.0/NULLIF(t1.Jan,0) AS Jan,
t.Feb*100.0/NULLIF(t1.Feb,0) AS Feb,
t.Mar*100.0/NULLIF(t1.Jan,0) AS Mar,
...
t.Dec*100.0/NULLIF(t1.Dec,0) AS Dec
FROM #Temp t
OUTER APPLY (SELECT * FROM #Temp WHERE YearVal=t.YearVal-1)t1
WHERE t.YearVal BETWEEN @Start AND @End

DROP TABLE #Temp
[/code]

drtduarte
Starting Member

12 Posts

Posted - 2009-07-16 : 02:56:05

A huge thank you visakh16,

You are great ;) That is really what I wanted...

I never used temp tables before, but it seems a pretty good way to separate data and manage it according with our needs.

Take care,
David Duarte

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources