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
 General SQL Server Forums
 New to SQL Server Programming
 Prblem getting Current and Previous Month data

Author  Topic 

RaghaSM
Yak Posting Veteran

52 Posts
Posted - 2009-10-05 : 11:16:32
I am trying to get the training data for the current month as well as the previous months data... but getting the wrong data ....all values not as ex[ected.. its summing all the relevant nos irrespective of dates and displaying

Please help... very urgent

Here is the query that I have written :




select
(DateName(month,'10/01/2009') +' ' + CONVERT(VARCHAR(20),YEAR('10/01/2009'))) AS MONTHYEAR , a.agencyid,a.agencyname,

sum(CASE WHEN t.LevelID = 1 THEN t.T ELSE '' END) as 1T_Sum ,
sum(CASE WHEN t.LevelID = 1 THEN t.C ELSE '' END) AS 1C_Sum,
sum(CASE WHEN t.LevelID = 1 THEN t.N ELSE '' END) AS 1N_Sum,
sum(CASE WHEN t.LevelID = 2 THEN t.T ELSE '' END) AS 7T_Sum,
sum(CASE WHEN t.LevelID = 2 THEN t.C ELSE '' END) AS 7C_Sum,
sum(CASE WHEN t.LevelID = 2 THEN t.N ELSE '' END) AS 7N_Sum,
sum(CASE WHEN t.LevelID = 3 THEN t.T ELSE '' END) AS 2T_Sum,
sum(CASE WHEN t.LevelID = 3 THEN t.C ELSE '' END) AS 2C_Sum,
sum(CASE WHEN t.LevelID = 3 THEN t.N ELSE '' END) AS 2N_Sum,
sum(CASE WHEN t.LevelID = 4 THEN t.T ELSE '' END) AS 8T_Sum,
sum(CASE WHEN t.LevelID = 4 THEN t.C ELSE '' END) AS 8C_Sum,
sum(CASE WHEN t.LevelID = 4 THEN t.N ELSE '' END) AS 8N_Sum,
sum(CASE WHEN t.LevelID = 5 THEN t.T ELSE '' END) AS 3T_sum,
sum(CASE WHEN t.LevelID = 5 THEN t.C ELSE '' END) AS 3C_sum,
sum(CASE WHEN t.LevelID = 5 THEN t.N ELSE '' END) AS 3N_sum ,
sum(CASE WHEN t.LevelID = 7 THEN t.T ELSE '' END) AS 4T_sum,
sum(CASE WHEN t.LevelID = 7 THEN t.C ELSE '' END) AS 4C_sum,
sum(CASE WHEN t.LevelID = 7 THEN t.N ELSE '' END) AS 4N_sum,

sum(CASE WHEN PrevMonth.LevelID = 1 THEN PrevMonth.T ELSE '' END) as 1T_SumPrevMonth ,
sum(CASE WHEN PrevMonth.LevelID = 1 THEN PrevMonth.C ELSE '' END) AS 1C_SumPrevMonth,
sum(CASE WHEN PrevMonth.LevelID = 1 THEN PrevMonth.N ELSE '' END) AS 1N_SumPrevMonth,
sum(CASE WHEN PrevMonth.LevelID = 2 THEN PrevMonth.T ELSE '' END) AS 7T_SumPrevMonth,
sum(CASE WHEN PrevMonth.LevelID = 2 THEN PrevMonth.C ELSE '' END) AS 7C_SumPrevMonth,
sum(CASE WHEN PrevMonth.LevelID = 2 THEN PrevMonth.N ELSE '' END) AS 7N_SumPrevMonth,
sum(CASE WHEN PrevMonth.LevelID = 3 THEN PrevMonth.T ELSE '' END) AS 2T_SumPrevMonth,
sum(CASE WHEN PrevMonth.LevelID = 3 THEN PrevMonth.C ELSE '' END) AS 2C_SumPrevMonth,
sum(CASE WHEN PrevMonth.LevelID = 3 THEN PrevMonth.N ELSE '' END) AS 2N_SumPrevMonth,
sum(CASE WHEN PrevMonth.LevelID = 4 THEN PrevMonth.T ELSE '' END) AS 8T_SumPrevMonth,
sum(CASE WHEN PrevMonth.LevelID = 4 THEN PrevMonth.C ELSE '' END) AS 8C_SumPrevMonth,
sum(CASE WHEN PrevMonth.LevelID = 4 THEN PrevMonth.N ELSE '' END) AS 8N_SumPrevMonth,
sum(CASE WHEN PrevMonth.LevelID = 5 THEN PrevMonth.T ELSE '' END) AS 3T_sumPrevMonth,
sum(CASE WHEN PrevMonth.LevelID = 5 THEN PrevMonth.C ELSE '' END) AS 3C_sumPrevMonth,
sum(CASE WHEN PrevMonth.LevelID = 5 THEN PrevMonth.N ELSE '' END) AS 3N_sumPrevMonth ,
sum(CASE WHEN PrevMonth.LevelID = 7 THEN PrevMonth.T ELSE '' END) AS 4T_sumPrevMonth,
sum(CASE WHEN PrevMonth.LevelID = 7 THEN PrevMonth.C ELSE '' END) AS 4C_sumPrevMonth,
sum(CASE WHEN PrevMonth.LevelID = 7 THEN PrevMonth.N ELSE '' END) AS 4N_sumPrevMonth

FROM
ETT_Agency A

left
outer join (select * from Training where updateddate>='10/01/2009'
and updateddate<='10/30/2009')as t on t.agencyid=a.agencyid

left
outer join (select * from Training where updateddate>='09/01/2009'
and updateddate<='09/30/2009')as PrevMonth on PrevMonth.agencyid=a.agencyid

group
BY a.AgencyID ,a.agencyname
order
by a.agencyname

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2009-10-05 : 12:25:49
There are several way to do this sort of query, here is one way: 
select
	(DateName(month,'10/01/2009') +' ' + CONVERT(VARCHAR(20),YEAR('10/01/2009'))) AS MONTHYEAR , a.agencyid,a.agencyname,

	sum(CASE WHEN MONTH(t.updateddate) = 10 AND t.LevelID = 1 THEN t.T ELSE 0 END) as 1T_Sum , 
	sum(CASE WHEN MONTH(t.updateddate) = 10 AND t.LevelID = 1 THEN t.C ELSE 0 END) AS 1C_Sum, 
	sum(CASE WHEN MONTH(t.updateddate) = 10 AND t.LevelID = 1 THEN t.N ELSE 0 END) AS 1N_Sum, 
	sum(CASE WHEN MONTH(t.updateddate) = 10 AND t.LevelID = 2 THEN t.T ELSE 0 END) AS 7T_Sum, 
	sum(CASE WHEN MONTH(t.updateddate) = 10 AND t.LevelID = 2 THEN t.C ELSE 0 END) AS 7C_Sum, 
	sum(CASE WHEN MONTH(t.updateddate) = 10 AND t.LevelID = 2 THEN t.N ELSE 0 END) AS 7N_Sum, 
	sum(CASE WHEN MONTH(t.updateddate) = 10 AND t.LevelID = 3 THEN t.T ELSE 0 END) AS 2T_Sum, 
	sum(CASE WHEN MONTH(t.updateddate) = 10 AND t.LevelID = 3 THEN t.C ELSE 0 END) AS 2C_Sum, 
	sum(CASE WHEN MONTH(t.updateddate) = 10 AND t.LevelID = 3 THEN t.N ELSE 0 END) AS 2N_Sum, 
	sum(CASE WHEN MONTH(t.updateddate) = 10 AND t.LevelID = 4 THEN t.T ELSE 0 END) AS 8T_Sum, 
	sum(CASE WHEN MONTH(t.updateddate) = 10 AND t.LevelID = 4 THEN t.C ELSE 0 END) AS 8C_Sum, 
	sum(CASE WHEN MONTH(t.updateddate) = 10 AND t.LevelID = 4 THEN t.N ELSE 0 END) AS 8N_Sum, 
	sum(CASE WHEN MONTH(t.updateddate) = 10 AND t.LevelID = 5 THEN t.T ELSE 0 END) AS 3T_sum, 
	sum(CASE WHEN MONTH(t.updateddate) = 10 AND t.LevelID = 5 THEN t.C ELSE 0 END) AS 3C_sum, 
	sum(CASE WHEN MONTH(t.updateddate) = 10 AND t.LevelID = 5 THEN t.N ELSE 0 END) AS 3N_sum ,
	sum(CASE WHEN MONTH(t.updateddate) = 10 AND t.LevelID = 7 THEN t.T ELSE 0 END) AS 4T_sum, 
	sum(CASE WHEN MONTH(t.updateddate) = 10 AND t.LevelID = 7 THEN t.C ELSE 0 END) AS 4C_sum, 
	sum(CASE WHEN MONTH(t.updateddate) = 10 AND t.LevelID = 7 THEN t.N ELSE 0 END) AS 4N_sum,
	
	sum(CASE WHEN MONTH(t.updateddate) = 9 AND t.LevelID = 1 THEN t.T ELSE 0 END) as 1T_SumPrevMonth , 
	sum(CASE WHEN MONTH(t.updateddate) = 9 AND t.LevelID = 1 THEN t.C ELSE 0 END) AS 1C_SumPrevMonth, 
	sum(CASE WHEN MONTH(t.updateddate) = 9 AND t.LevelID = 1 THEN t.N ELSE 0 END) AS 1N_SumPrevMonth, 
	sum(CASE WHEN MONTH(t.updateddate) = 9 AND t.LevelID = 2 THEN t.T ELSE 0 END) AS 7T_SumPrevMonth, 
	sum(CASE WHEN MONTH(t.updateddate) = 9 AND t.LevelID = 2 THEN t.C ELSE 0 END) AS 7C_SumPrevMonth, 
	sum(CASE WHEN MONTH(t.updateddate) = 9 AND t.LevelID = 2 THEN t.N ELSE 0 END) AS 7N_SumPrevMonth, 
	sum(CASE WHEN MONTH(t.updateddate) = 9 AND t.LevelID = 3 THEN t.T ELSE 0 END) AS 2T_SumPrevMonth, 
	sum(CASE WHEN MONTH(t.updateddate) = 9 AND t.LevelID = 3 THEN t.C ELSE 0 END) AS 2C_SumPrevMonth, 
	sum(CASE WHEN MONTH(t.updateddate) = 9 AND t.LevelID = 3 THEN t.N ELSE 0 END) AS 2N_SumPrevMonth, 
	sum(CASE WHEN MONTH(t.updateddate) = 9 AND t.LevelID = 4 THEN t.T ELSE 0 END) AS 8T_SumPrevMonth, 
	sum(CASE WHEN MONTH(t.updateddate) = 9 AND t.LevelID = 4 THEN t.C ELSE 0 END) AS 8C_SumPrevMonth, 
	sum(CASE WHEN MONTH(t.updateddate) = 9 AND t.LevelID = 4 THEN t.N ELSE 0 END) AS 8N_SumPrevMonth, 
	sum(CASE WHEN MONTH(t.updateddate) = 9 AND t.LevelID = 5 THEN t.T ELSE 0 END) AS 3T_sumPrevMonth, 
	sum(CASE WHEN MONTH(t.updateddate) = 9 AND t.LevelID = 5 THEN t.C ELSE 0 END) AS 3C_sumPrevMonth, 
	sum(CASE WHEN MONTH(t.updateddate) = 9 AND t.LevelID = 5 THEN t.N ELSE 0 END) AS 3N_sumPrevMonth ,
	sum(CASE WHEN MONTH(t.updateddate) = 9 AND t.LevelID = 7 THEN t.T ELSE 0 END) AS 4T_sumPrevMonth, 
	sum(CASE WHEN MONTH(t.updateddate) = 9 AND t.LevelID = 7 THEN t.C ELSE 0 END) AS 4C_sumPrevMonth, 
	sum(CASE WHEN MONTH(t.updateddate) = 9 AND t.LevelID = 7 THEN t.N ELSE 0 END) AS 4N_sumPrevMonth
FROM
	ETT_Agency AS A 
LEFT OUTER JOIN 
	Training AS T
	ON t.agencyid = a.agencyid 
	AND t.updateddate >= '09/01/2009'  
	AND t.updateddate <= '10/30/2009'
GROUP BY 
	MONTH(t.updateddate),
	a.AgencyID,
	a.agencyname
Go to Top of Page

RaghaSM
Yak Posting Veteran

52 Posts
Posted - 2009-10-05 : 13:19:33
Its not working for me..... Please help!!

The problem I see is For each agency there are 2 rows one row with current details and previous month details as blank and the 2nd row with current month details as blank and displaying previous month details
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2009-10-05 : 13:28:40
Try chaning this:
LEFT OUTER JOIN 
	Training AS T
	ON t.agencyid = a.agencyid 
	AND t.updateddate >= '09/01/2009'  
	AND t.updateddate <= '10/30/2009'
To this: 
LEFT OUTER JOIN 
	Training AS T
	ON t.agencyid = a.agencyid 
WHERE
	t.updateddate >= '09/01/2009'  
	AND t.updateddate <= '10/30/2009'
Go to Top of Page

RaghaSM
Yak Posting Veteran

52 Posts
Posted - 2009-10-05 : 13:41:29
Nope... Its now showing the agaencies that has data and stll displaying 2 rows fir each agency one with current month and another previous months
Go to Top of Page

rohitkumar
Constraint Violating Yak Guru

472 Posts
Posted - 2009-10-05 : 13:45:40
and what do you want your output to look like?
Go to Top of Page

RaghaSM
Yak Posting Veteran

52 Posts
Posted - 2009-10-05 : 13:49:26
I want all the agencies with data tahts showing the current nmonths and Previous months data in a single row.

I mean each row a differnet agency with its corresponding Current and Previous months data.
Go to Top of Page

rohitkumar
Constraint Violating Yak Guru

472 Posts
Posted - 2009-10-05 : 13:54:33
that means you dont want to group data based on month, remove it from group by
Go to Top of Page

RaghaSM
Yak Posting Veteran

52 Posts
Posted - 2009-10-05 : 14:01:57
Great!!............. I am very bad in SQL and had to depend on others..
Thanks for your help...

Can you please suggest how to learn good sql ?
Really appreciate all your help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-10-05 : 14:03:47
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

rohitkumar
Constraint Violating Yak Guru

472 Posts
Posted - 2009-10-05 : 14:13:15
and then keep visiting this forum and read blogs
Go to Top of Page

RaghaSM
Yak Posting Veteran

52 Posts
Posted - 2009-10-05 : 14:15:45
Appreciate everyone's help!! thank you very much.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site