Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Minute and Hour calcultion from column.

Author  Topic 

sadiqmodan
Starting Member

16 Posts

Posted - 2010-12-01 : 04:47:37
Hello,
I have a data look like below
PERSON FUN JOB NAME QUESTION MINUTES Total Minute
admin C test Q13a 0
admin C test Q13a 1
admin C test Q13a 15
admin C test Q13b 2
admin C test Q13b 3
admin C test Q4b 1
admin C test Qs7 2
admin C test Qs7 0
super C test123 Q13 3
super V test Qs7 1

I need to calculate Total minute for QUESTION=Q13a, person=admin, FUN=C

after that data looks like as below.

PERSON FUN JOB NAME QUESTION MINUTES Total Minute
admin C test Q13a 0
admin C test Q13a 1
admin C test Q13a 15 16
admin C test Q13b 2
admin C test Q13b 3 5
admin C test Q4b 1 1
admin C test Qs7 2
admin C test Qs7 0 2
super C test123 Q13 3 3
super V test Qs7 1 1

Any one have solution please help me.. Thanks in advance.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-01 : 05:10:43
select person, fun, job, question, minutes, total = (select sum(minutes) from tbl t2 where t2.person = t.person and t2.fun = t.fun and t2.job = t.job and t2.question = t.question and t2.minutes <= t.minutes)
from tbl t
order by person, fun, job, question, minutes


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-01 : 05:30:18
Try this -


DECLARE @Table AS TABLE ( PERSON varchar(50), FUN varchar(50), JOBNAME varchar(50), QUESTION varchar(50), MINUTES INT )

INSERT INTO @Table
SELECT 'admin','C','test','Q13a','0'
UNION ALL
SELECT 'admin','C','test','Q13a','1'
UNION ALL
SELECT 'admin','C','test','Q13a','15'
UNION ALL
SELECT 'admin','C','test','Q13b','2'
UNION ALL
SELECT 'admin','C','test','Q13b','3'
UNION ALL
SELECT 'admin','C','test','Q4b','1'
UNION ALL
SELECT 'admin','C','test','Qs7','2'
UNION ALL
SELECT 'admin','C','test','Qs7','0'
UNION ALL
SELECT 'super','C','test123','Q13','3'
UNION ALL
SELECT 'super','V','test','Qs7','1'


SELECT PERSON, FUN, JOBNAME, QUESTION, MINUTES,
CASE WHEN RowNo = 1 THEN [TOTAL MINUTES] ELSE '' END [TOTAL MINUTES]
FROM
(
SELECT A.PERSON, A.FUN, A.JOBNAME, A.QUESTION, A.MINUTES, CONVERT( VARCHAR(20), B.[TOTAL MINUTES]) [TOTAL MINUTES], ROW_NUMBER() OVER( PARTITION BY A.PERSON, A.FUN, A.QUESTION ORDER BY MINUTES DESC ) RowNo
FROM @Table A
INNER JOIN
(
SELECT PERSON, FUN, QUESTION, SUM(MINUTES) [TOTAL MINUTES] FROM @Table
GROUP BY PERSON, FUN, QUESTION
) B
ON A.PERSON = B.PERSON AND A.FUN = B.FUN AND A.QUESTION = B.QUESTION
) A
ORDER BY PERSON, FUN, JOBNAME, QUESTION, MINUTES


Vaibhav T

If I cant go back, I want to go fast...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-01 : 13:08:38
you mean you've show totals only along with last row or each group?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sadiqmodan
Starting Member

16 Posts

Posted - 2010-12-02 : 05:55:04
Thanks nigelrivett, vaibhavktiwari83, visakh16 for your quick response.

nigelrivett your suggestion is perfect and its work for me many many thanks all you my friends.
Go to Top of Page
   

- Advertisement -