SQL Server Performance

Query Problem

Discussion in 'General Developer Questions' started by isa, Apr 28, 2007.

  1. isa New Member

    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

Share This Page