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
//MrDo you have the URL or a PDF for this assignment?
-PatP|||:( no im making a database to store my divelogs in and this is the accumulated time im trying to calculate...|||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:
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:
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
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 ;)|||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|||Peso, your solution is insufficiently complex. How do you ever expect to make a living at this with such concise solutions?
Obfuscate! Obfuscate! Obfuscate!|||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|||It appears that outer theta joins are too elementary for blindman now... :rolleyes:
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...|||MRPCGuy, Peso's method is the standard solution to this common problem, and should work fine.|||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|||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|||im learning, im leraning, alright? Thx Peso for your help...|||don't get this... if i have 100000 rows i want to count accumulated time for each row, how can i achive that?|||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|||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?|||For int you just have to log the number of mins (int), but that will turn innacurate soon enough. Since you're storing the number of minutes in integer you will soon realise that if your dive was 2.5mins you'll have to round it.
To store the time as an int it's prolly better to store the number of seconds.|||your correct about that, but since i always round it up to whole minutes i don't need seconds...|||you should be able to use the solutions presented here, namely peso's one, if you're going to use int.|||Ok,
I got this:
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 this:
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
I run this:
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, 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
What if I got a table with 10000 rows… that’s many select statements
I’m very aware that it’s my skills is the biggest problem here, so plz help…|||no, it's still only one SELECT statement, no matter how many rows|||well in my test table i have 50 rows and the output from above gives me 8 rows?!|||maybe not correct forum but anyway... is this doable in ms access? thinking of put this db in access instead? (don't want to run sql local)|||is this doable in ms access? certainly is|||maybe not correct forum but anyway... is this doable in ms access? thinking of put this db in access instead? (don't want to run sql local)
of for the love of....|||...pancakes?|||well in my test table i have 50 rows and the output from above gives me 8 rows?!
You are getting 8 rows because you are using the wrong query. If I didn't missunderstand your post, you are using the following SQL to retrieve the desired results:
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, 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
Well, it's only natural that you are only getting 8 rows. If you look closely you'll see that you are selecting the results from the temporary table called @.sample. To retrieve your real data you should use this sql statement:
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 <MyTableName> as s1
left join <MyTableName> as s2 on s2.rownr < s1.rownr
group by s1.rownr,s1.time
order by s1.rownr,s1.time
Please, remember to replace <MyTableName> with the actual name of your dive logs table.|||Please, remember to replace <MyTableName> with the actual name of your dive logs table.and you will also have to change this nonsense --convert(varchar, sum(coalesce(s2.time, 0)) / 60)
+ ':' + RIGHT('00' + cast(sum(coalesce(s2.time, 0)) % 60 as varchar(2)), 2)into the equivalent ms access nonsense
:)|||thx alot it works fine... I'l take your query to another forum and get it translated into access when im have gone nuts trying to translate it myself...|||Good Lord.
It's only 12:08 PM and I am now exhausted.
and the thread is only two pages :)|||and the sarcasm and humiliation at this forum is extraordinary... so what's ur problem beside being exhausted?|||and the sarcasm and humiliation at this forum is extraordinary... so what's ur problem beside being exhausted?My problems are numerous and well documented, but are primarily out of scope in the current discussion. One of those I would consider IN scope, however, is my inability to grasp the usefulness of the term "ur" within the realm of an otherwise reasonably well-crafted sentence.
Sarcasm? Guilty as charged.
Humiliation? Nah, I've seen not one virtual wedgie applied here.
Lighten up. If you think this forum is bad, it's the first one you've been to.|||... my inability to grasp the usefulness of the term "ur" within the realm of an otherwise reasonably well-crafted sentence.then you will just have to make another visit to http://icanhascheezburger.com/
it will make sense eventually
:)|||and the sarcasm and humiliation at this forum is extraordinary...
LOL.. Thats why I keep coming back!|||http://icanhascheezburger.com/
lol @. that site :beer:|||then you will just have to make another visit to http://icanhascheezburger.com/
it will make sense eventually
:)
I must admit, I only looked at the first couple pages then got busy with *coff* work *coff* and forwarded the link to myself at home. I will catch up with the rest of the class over the weekend.
) ... but still nothing. Here is what I'm doing: