better code for this?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

here is my sql table
SID(pk) LID BuildNo TID  Status      StarDate   EndDate  ExecutionTime DelStatus
212     abc 10.2.3 CBT  In Progress 2/15/2012                    5                  0
236     abc 10.2.3 CBT Completed   2/21/2012 2/22/2012     7                  1
230     abc 10.2.3 CBT In Progress 2/20/2012                    40                 0

and here is my stored procedure query to find out total Executiontime for TID

SQL

CREATE PROCEDURE  [dbo].[usp_TotalExecutionTime1]

AS

BEGIN
 SELECT 
 Distinct (TID),LID,BuildNo,Status,StarDate,EndDate,Sum(ExecutionTime) AS ExecutionTime
 from tblTestersWorkStatus
 Group BY LID,TID,BuildNo,Status,EndDate,StarDate
END

but for this query its calculating for Status like In progress calculating differently and for Completed calculating differently..

so i want to calculate for TID CBT executiontime=52 i want 52 answer..
but am getting answer is like CBT InProgress 45
and CBT Completed 7..

can any one help me to find out the solution..thank you...

Posted 23-Feb-12 1:34am

ythisbug

Updated 23-Feb-12 1:39am

sachin10d

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

sachin10d · Answer 1 · 2012-02-23T01:43:00

Solution 1

Removing Status,EndDate,StarDate
from the select query will give you the output as
CBT executiontime=52

SQL

CREATE PROCEDURE  [dbo].[usp_TotalExecutionTime1]
AS
BEGIN
 SELECT 
 Distinct (TID),LID,BuildNo,Sum(ExecutionTime) AS ExecutionTime
 from tblTestersWorkStatus
 Group BY LID,TID,BuildNo
END

Posted 23-Feb-12 1:43am

sachin10d

Comments

ythisbug 23-Feb-12 7:45am

bro i want startdate EndDate to show in grid..so thats the problem

Manfred Rudolf Bihy · Answer 2 · 2012-02-23T02:22:00

Solution 2

I have no idea what you are trying to achieve, but if you want the start and end date as well as the status you can't do it with GROUP BY clause. You can do this instead:

SQL

SELECT DISTINCT (TID),
                 LID,
                 BuildNo,
                 Status,
                 StarDate,
                 EndDate,
                (
                    SELECT SUM(ExecutionTime)
                    FROM tblTestersWorkStatus t2
                    WHERE t2.LID = tws.LID
                ) AS ExecutionTimeForThisLID
FROM tblTestersWorkStatus tws

Regards,

Manfred

Posted 23-Feb-12 2:22am

Manfred Rudolf Bihy

Comments

ythisbug 23-Feb-12 23:30pm

All Estimates Inbox Ramesh 10.2.3 Not Started 2/15/2012 96
All Estimates Inbox Ramesh 10.2.3 Not Started 2/19/2012 96

bro if i give your query m getting this output..actully execution time is 0 for this test case but m getting 96 as total execution..

can u telmme wats the error??

ythisbug 23-Feb-12 23:53pm

and i dont want to repeat TID and LID too

ythisbug 23-Feb-12 23:57pm

Clip Board Testing shahid 10.2.3 Completed 2/21/2012 2/22/2012 12:00:00 AM 217
Clip Board Testing shahid 10.2.3 In Progress 2/15/2012 217
Clip Board Testing shahid 10.2.3 In Progress 2/18/2012 217
Clip Board Testing shahid 10.2.3 In Progress 2/19/2012 217
Clip Board Testing shahid 10.2.3 In Progress 2/20/2012 217

and see this output also,..here its repeating..total is 217 but it showing in execution column..for every test case..this should be total for all Clip Board Testing

ythisbug 24-Feb-12 0:01am

or can i get qeury that can find status Completed totalExecution time and In Progress Total execution time..can any one suggest me ??