Hello everyone, i need us help i m unablt to make A SP thats i used for reporting i want to get the Total Projects, Planned Current year, Completed Projects , UnderConstruction projects, for this i have these tables Table : Projects ProjectID int ProjectCode char(13) (impp 9 digits are used as 1 digit for Province, 1 for District, 1 for Tehsil, 2 for UnionCouncils, 2 for Villages, 1 for sector, 1 for subsector and other 4 are used for 0000 for 1 code 0001 for 2nd so on but 9 are fixed used for these.) PartWorkPlan char(9) (used to store Planned year i.e. 2006-2007) InitialCost Now 2nd table: Activities ActivityID int ActivityName varchar(150) 3rd table: ActivitiesProgress ProjectID ActivityID StartDate EndDate now the problem is i want to show the TotalProjects in the Database and the Completed and Undersonstruction Projects but my queries not shows the correct result as if i have 1 complete proj then my query returns the 1 as total projects also . Plz help me who can i write or modify my queries to get the required result. plz i m in trouble and still not ablt to write correct query plz help me.Thanx in Advance. my query : select Count(Projects.ProjectID)as Total, PartWorkPlan as 'Planned [current Year]',Count(Projects.projectID) as Completed FROM dbo.Projects INNER JOIN dbo.ActivityProgress ON dbo.Projects.ProjectID = dbo.ActivityProgress.ProjectID INNER JOIN dbo.Activities ON dbo.ActivityProgress.ActivityID = dbo.Activities.ActivityID where substring(ProjectCode,2,1)=1 and substring(ProjectCode,3,1)=1 and substring(ProjectCode,4,2)=02 and substring(ProjectCode,8,1)='A' and Activities.ActivityID=30 group by PartWorkPlan here is some data of my tables also : Activities Table data AvtivityIDActivityName 10Project Identified 20Implementing Agency Confirmed and Funds Committed 30PC1 Prepared 160Project Final Audit and Closure ActivityProgress table data ProjectID ActivityID StartDate EndDate 27596 10 2007-04-24 10:02:42.090NULL 27596 20 2007-04-24 10:02:56.107NULL 27596 30 2007-04-24 10:03:00.623NULL 20695 40 2007-04-25 00:00:00.0002007-04-26 00:00:00.000 20695 50 2007-04-25 00:00:00.0002007-04-26 00:00:00.000 Projects table data ProjectID ProjectNameProjectCodeInitialCostPartWorkPlan 27596 GPS 1103000BB00011.262006-2007 27598 XYZ1112400BC00021.02007-2008 27599 ABC1210700BA00030.52007-2008 27600 DEF1210800BA00041.12007-2008 isa
