Click here to Skip to main content
14,971,877 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i am working in mvc3 here i need some help please

here i have some tables like(BugId)
=============================================================================================================

BugID Title Description ProjectId Version BuildNumber EmployeId CategoryID CreatedDate SeverityID PriorityID ReleasePhaseID TypeID

2 Banking bankingapplication 1 new version new build no 1 1 00:00:00.00 1 1 1 1
==========================================================================================================================================================


(Projects Table)



=======================================================

Projectid ProjectName Description Status

1 Finance This is Finance Project Active
2 Uniformatic This is Finance Project Active
3 ProDuct This is Finance Project InActive
4 Cloud This is Finance Project INActive
5 Banking This is Finance Project Progress
6 Ecommerce This is Finance Project Active
====================================================================

RealesePhase (table)

====================================================================

ReleasePhaseID ReleasePhase

1 DEV
2 QA
3 Alpha
4 Beta
5 Live
===============================================

Tostatus(table)

===================================================

ToStatusId Tostatus

1 New
2 Assigned
3 Fixed
4 Re-Opened
5 Closed
6 Deffered
7 Not a Bug
=====================================================

Bughistory (Table)
==================================================================================


BugHistoryID BugID FixedByID AssignedTo Resolution FromStatus ToStatus

5 2 1 1 this is my banking New New
7 2 1 1 this assignto res km,l

======================================================================================================



here i have these tables now i have to write a query to select ProjectName(dropdown)
And (ReleasePhase) from it (open)(closed)(fixedBy)

in Bugs table Bugs will be log (insert) from it so now we have to select Project Name & ReleasePhase as dropdown if we select the project name i should get the No of Bugs we have for it (counting)

from it like
(view) should like this

=====================================================================

ProjectName RealesePhase openBugs ClosedBugs fixedBy

1 New EmployeName
2 Assigned EmployeName
3 Fixed EmployeName
4 Re-Opened EmployeName

======================================================================


so plz help me to write a query count and show the how many bugs Employee inserted and how many are released and how many closed

Thanks In Advance
Posted

I know what is the logic you will used to identify open bugs and closed bugs. also am not sure how you will get fixed by. When you are getting count of open/closed bugs, its not possible to get fixed by column..

I have provided sample, to get projectname, releasephase and closed bugs count.


SQL
<pre lang="sql">select distinct projectname,releasephase,
(Select  COUNT(1) from Bug where Projectid=a.projectid and releasephaseid=a.releasephaseid and
bugid in (select bugid from bughistory where tostatus='Closed')) as Closedbugs
from bug a
inner join projects p on p.projectid=a.projectid
inner join ReleasePhase Rp on rp.releasephaseid=a.releasephaseid


   
Comments
shakil4u 3-Aug-12 10:51am
   
Thank for Answer Nice Programming It's Working For me Thanks Allot
Will You Don't Mind Plz Provide Me in Same Query For New,Assigned,Fixed,
Re-Opened,Closed,Deffered,(Not a Bug) i don't need of fixed by column

Thank for answer
And Thank in Advance
Santhosh Kumar Jayaraman 3-Aug-12 11:03am
   
I have posted another solution for all defect statuses. If it works, mark it as solved.
SQL
select distinct projectname,releasephase,
(Select  COUNT(1) from Bug where Projectid=a.projectid and releasephaseid=a.releasephaseid and
bugid in (select bugid from bughistory where tostatus='New')) as NewBugs,
(Select  COUNT(1) from Bug where Projectid=a.projectid and releasephaseid=a.releasephaseid and
bugid in (select bugid from bughistory where tostatus='Assigned')) as Assignedbugs,
(Select  COUNT(1) from Bug where Projectid=a.projectid and releasephaseid=a.releasephaseid and
bugid in (select bugid from bughistory where tostatus='Fixed')) as FixedBugs,
(Select  COUNT(1) from Bug where Projectid=a.projectid and releasephaseid=a.releasephaseid and
bugid in (select bugid from bughistory where tostatus='Re-Opened')) as ReopenedBugs,
(Select  COUNT(1) from Bug where Projectid=a.projectid and releasephaseid=a.releasephaseid and
bugid in (select bugid from bughistory where tostatus='Closed')) as Closedbugs,
(Select  COUNT(1) from Bug where Projectid=a.projectid and releasephaseid=a.releasephaseid and
bugid in (select bugid from bughistory where tostatus='Deffered')) as Deferredbugs,
(Select  COUNT(1) from Bug where Projectid=a.projectid and releasephaseid=a.releasephaseid and
bugid in (select bugid from bughistory where tostatus='Not a Bug')) as NotBugs
from bug a
inner join projects p on p.projectid=a.projectid
inner join ReleasePhase Rp on rp.releasephaseid=a.releasephaseid
   
Comments
shakil4u 4-Aug-12 1:50am
   
Thank you friend for Answer Nice programing Thank for help
shakil4u 4-Aug-12 1:51am
   
Thank you Santhosh Kumar J

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900