Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Mar 2004
    Posts
    162

    Unanswered: minute count query

    I need a query that gives me the sum of every rows (time column) with lower 'rownr'

    the result:
    rownr time timesum
    1 10 0
    2 10 10
    3 10 20
    4 10 30
    5 10 40
    6 10 50
    7 10 60
    8 10 70

    current table looks like this:
    rownr time
    1 10
    2 10
    3 10
    4 10
    5 10
    6 10
    7 10
    8 10

    and i want the 'timesum' column to be in format hhhh:mm
    current format is rownr=int, time=datetime

    thx for all help

    //Mr

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do you have the URL or a PDF for this assignment?

    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    162
    no im making a database to store my divelogs in and this is the accumulated time im trying to calculate...

  4. #4
    Join Date
    Jul 2007
    Posts
    96
    I have no idea if this is what you are looking for. Nevertheless, to try and guess what the solution to your problem might be I went ahead and created a test database called MiscTests.

    Now, I created a table similar to yours but instead of having the time column as datetime I've changed it to an INT. Here's the table code:
    Code:
    USE [MiscTests]
    GO
    /****** Object:  Table [dbo].[Times]    Script Date: 08/22/2007 17:50:51 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Times](
    	[rownr] [int] IDENTITY(1,1) NOT NULL,
    	[time] [int] NOT NULL CONSTRAINT [DF_Times_time]  DEFAULT ((0)),
     CONSTRAINT [PK_Times] PRIMARY KEY CLUSTERED 
    (
    	[rownr] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    With this table in place I've made a small scalar-valued function to parse the time column. The code of the SVF is as follows:
    Code:
    USE [MiscTests]
    GO
    /****** Object:  UserDefinedFunction [dbo].[parseTime]    Script Date: 08/22/2007 17:53:53 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE FUNCTION [dbo].[parseTime]
    (
    	@Time AS Int
    )
    RETURNS VarChar(10)
    AS
    BEGIN
    	DECLARE   @hours AS Int
    			, @parsedTime AS VarChar(10);
    
    	SET @hours = 0;
    
    	WHILE (@Time >= 60)
    		BEGIN
    			SET @hours = (@hours + 1);
    
    			SET @Time = (@Time - 60);
    		END
    
    	IF (@hours = 0)
    		BEGIN
    			SET @parsedTime = '0';
    		END
    	ELSE
    		BEGIN
    			SET @parsedTime = CAST(@hours AS VarChar(7));
    		END
    
    	SET @parsedTime = @parsedTime + ':';
    
    	IF (@Time < 10)
    		BEGIN
    			SET @parsedTime = @parsedTime + '0'
    		END
    
    	SET @parsedTime = @parsedTime + CAST(@Time AS VarChar(2));
    
    	return @parsedTime;
    END
    GO
    With this done I went ahead and filled the dbo.Times table with a couple of datarows. After that I ran a simple query to return the desired output. Here's the query SQL

    Code:
    SELECT
    	  t.rownr AS rownr
    	, t.time AS [time]
    	, dbo.parseTime((
    						SELECT
    							sum(st.time)
    						FROM
    							dbo.Times st
    						WHERE
    							st.rownr <= t.rownr
    				   )) as timesum
    FROM
    	dbo.Times t
    ORDER BY
    	t.rownr ASC;
    The resultset should be something like this:
    1 0 0:00
    2 10 0:10
    3 10 0:20
    4 10 0:30
    5 10 0:40
    6 10 0:50
    7 10 1:00
    8 10 1:10
    9 10 1:20
    10 10 1:30
    11 10 1:40
    12 10 1:50
    13 10 2:00
    14 10 2:10
    15 10 2:20

    Hope this helps
    Last edited by Diabolic; 08-22-07 at 14:01.

  5. #5
    Join Date
    Apr 2007
    Posts
    183

    Smile

    Code:
    declare @sample table (rownr int, time int)
    
    insert	@sample
    select	1, 10 union all
    select	2, 10 union all
    select	3, 10 union all
    select	4, 10 union all
    select	5, 10 union all
    select	6, 10 union all
    select	7, 10 union all
    select	8, 10
    
    select		s1.rownr,
    		s1.time,
    		convert(varchar(5), dateadd(minute, sum(coalesce(s2.time, 0)), 0), 108) as timesum
    from		@sample as s1
    left join	@sample as s2 on s2.rownr < s1.rownr
    group by	s1.rownr,
    		s1.time

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Peso, your solution is insufficiently complex. How do you ever expect to make a living at this with such concise solutions?
    Obfuscate! Obfuscate! Obfuscate!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Mar 2004
    Posts
    162
    thanks alot for all the effort you guys put into this, but i can't solve this. Its my experience that fails here... i really tryed Diabolic's solutions which im sure works but again, i can't apply that solutiuons at my database....

    is it possible to make a query that gives me the result i want? or do i need a temp table or similar to accomplish my goal?

    //Mr

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It appears that outer theta joins are too elementary for blindman now....

    Not to mention the use of temporal functions which, according to Pat are rather risky, but he's under NDA so can't tell us why....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    MRPCGuy, Peso's method is the standard solution to this common problem, and should work fine.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Apr 2007
    Posts
    183
    declare @sample table (rownr int, time int)

    insert @sample
    select 1, 10 union all
    select 2, 10 union all
    select 3, 10 union all
    select 4, 500 union all
    select 5, 10 union all
    select 6, 10 union all
    select 7, 4000000 union all
    select 8, 10

    select s1.rownr,
    s1.time,
    convert(varchar, sum(coalesce(s2.time, 0)) / 60) + ':' + RIGHT('00' + cast(sum(coalesce(s2.time, 0)) % 60 as varchar(2)), 2)
    from @sample as s1
    left join @sample as s2 on s2.rownr < s1.rownr
    group by s1.rownr,
    s1.time
    order by s1.rownr,
    s1.time
    Last edited by Peso; 08-22-07 at 16:57. Reason: Better sample data to illustrate OP intent for thousands of hours...

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    would somebody -- and i volunteer mrpcguy for this -- please post the DDL to create a table using rownr=int, time=datetime as specified in post #1

    it's fine and dandy to set up test cases using ints, but mrpcguy says he's using datetimes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Mar 2004
    Posts
    162
    im learning, im leraning, alright? Thx Peso for your help...

  13. #13
    Join Date
    Mar 2004
    Posts
    162
    don't get this.... if i have 100000 rows i want to count accumulated time for each row, how can i achive that?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mrpcguy
    don't get this.... if i have 100000 rows i want to count accumulated time for each row, how can i achive that?
    with a query, similar to the ones you've seen in this thread

    any chance you could give us some real data, not fake data? i.e. data with datetimes, not integers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Mar 2004
    Posts
    162
    thing is that i dont have any real data yet, im building this database and gona fill it with divelogs later on. But this function to be able to count accumulated time is important. i only have fake data.
    My idea was to have datetime at that columt because logg gonna look like "00:30" for 30min. Maybe its better to use int and put in "30" for 30min...
    How can i achive what i wont with my first post and use int instead of datetime?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •