Better code for this?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

this is my query..but in this query TID is repeating..i dont want to repeat TID but want to calculate TID like example
TID    Date          ExecutionTime
Test1  10/2/2012         5
Test1  10/2/2012         5
Test1  10/4/2012         5
Test2  10/5/2012         10

think this s my sql table.. and i want to calculate Total time for every TID and TID should not be repeat..

SELECT DISTINCT Row_number() OVER (Order BY TID)LID,BuildNo,TID,Status,StarDate,EndDate,Sum(ExecutionTime)
AS ExecutionTime
from tblTestersWorkStatus
Group BY TID,LID,BuildNo,Status,StarDate,EndDate

but in this query m getty output like
for example
TID    Date        ExecutionTime

C#

Test1&nbsp;&nbsp;10/2/2012&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10

Test1&nbsp;&nbsp;10/4/2012&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5
Test2&nbsp;&nbsp;10/5/2012&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10

but i want calculation like
example:
TID      Date        ExecutionTime
Test1  10/2/2012   15

Test2  10/5/2012  10

Please ask if u have any doubt.??

Posted 21-Feb-12 17:43pm

ythisbug

Updated 21-Feb-12 17:50pm

v3

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)

Tanveer A · Answer 1 · 2012-02-21T17:50:00

Solution 1

do not use row_number() with distinct

SELECT DISTINCT (TID) as TID , BuildNo,,Status,StarDate,EndDate,Sum(ExecutionTime)
AS ExecutionTime
from tblTestersWorkStatus
Group BY TID,LID,BuildNo,Status,StarDate,EndDate

Posted 21-Feb-12 17:50pm

Tanveer A

Comments

ythisbug 21-Feb-12 23:54pm

but also its repeating bro..i tried with only DISTINCT also.see i updated question

Varun Sareen · Answer 2 · 2012-02-21T17:51:00

Solution 2

try this:-

select TID,Date,sum(ExecutionTime) [ET] from tblTestersWorkStatus group by TID,Date

[updated] As question is updated.

select TID,sum(ExecutionTime) [ET] from tblTestersWorkStatus group by TID

Posted 21-Feb-12 17:51pm

Varun Sareen

Updated 21-Feb-12 18:26pm

v2

Comments

ythisbug 21-Feb-12 23:59pm

will get same out for your solution.as i gave query..query is different but output s same.thanks.any other solution?

Varun Sareen 22-Feb-12 0:27am

I have updated the solution please see

ythisbug 23-Feb-12 6:11am

bro can i get some other solution for this.in my sql table Status column is der in that values as In Progress ,Not Started,Completed

and there are many records for In Progress and Completed here calculating for that query as In Progress and Completing in Different

Varun Sareen 22-Feb-12 0:23am

Friend, Don't fetch the column Date in your query and then group by on the TID.. you will get the results you require.

ythisbug 22-Feb-12 0:36am

what u mean by [ET]?

ythisbug 23-Feb-12 6:46am

but i want date also .wat to do for that?

Nilesh Patil Kolhapur 22-Feb-12 0:25am

Hi Varun,
Your answers are really great this make me think.
best luck for your bright future

Varun Sareen 22-Feb-12 0:26am

thanks patil :)