Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Progressive total

Posted on 2004-10-01
11
341 Views
Last Modified: 2010-08-05
Dear Experts,
I Have a table with the following fields and data
ID    NAME      date          Salary
1     Bob     2004-01-01   10
2     John    2004-01-01   10
3     Bob     2004-02-01   30
4     Helen  2004-02-01    20
5     Helen  2004-03-01    50

I want to see the following by single-query

Bob     2004-01-01   10
John    2004-01-01   10
Helen  2004-01-01    0
Bob     2004-02-01   40
John    2004-02-01   10
Helen  2004-02-01    20
Bob     2004-03-01   40
John    2004-03-01   10
Helen  2004-03-01    70
0
Comment
Question by:DMStar
11 Comments
 
LVL 14

Expert Comment

by:adwiseman
ID: 12199730
I think

Select m.NAME, m.date, sum(m1.Salary)
from MyTable m
LEFT OUTER JOIN MyTable m1 ON m1.date <= m.date
GROUP BY m.name, m.date
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12199777
can you explain the expeted results a bit pls.   I dont understand where the figures come from
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 12199779
sorry, forgot a join on id.  Are the id's in your example correct, or maybe the join is supposed to be on name, not id.

Select m.NAME, m.date, sum(m1.Salary)
from MyTable m
LEFT OUTER JOIN MyTable m1 ON m1.date <= m.date AND m.id = m1.id
GROUP BY m.name, m.date
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 12

Expert Comment

by:monosodiumg
ID: 12199791
For each date that appears in the Salaries table?

BTW, avoid using reserved words like "date" are column names.
Select Name, SDate, Sum(case S.Date <= D.Date then Salary else 0 end)
FROM Salaries S CROSS JOIN Select distinct [date] as SDate from Salaries) D
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12199802
Select Name, SDate, Sum(case S.Date <= D.Date then Salary else 0 end)
FROM Salaries S CROSS JOIN Select distinct [date] as SDate from Salaries) D
GROUP BY Name, SDate
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12199825
Adwiseman's solution will miss out Helen's first record at least. Needs to be a cross product one the dates to ensure each date is represented for each Name.
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 12199861
Here with exactly your output

Select m.NAME, md.date, sum(ISNULL(m1.Salary, 0))
from (Select distinct name from MyTable) m
CROSS JOIN (Select distinct date from MyTable) md
LEFT OUTER JOIN MyTable m1 ON m1.date <= md.date AND m.name = m1.name
GROUP BY m.name, md.date
order by md.date, m.name

I'm selecting a list of names , and cross joining that with the distinct list of dates.  Goving me a list of all names, with all dates.  Then joining that in with the salart and grouping them.  The order is not the same, but the result set is.
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 12199870
--my working code

create table MyTable([id] int, [name] varchar(50), [date] smalldatetime, salary int)
GO
insert into MyTable
Select 1, 'Bob', '2004-01-01', 10
insert into MyTable
Select 2, 'John', '2004-01-01', 10
insert into MyTable
Select 1, 'Bob', '2004-02-01', 30
insert into MyTable
Select 3, 'Helen', '2004-02-01', 20
insert into MyTable
Select 3, 'Helen', '2004-03-01', 50
GO
select * from MyTable
GO
Select m.NAME, md.date, sum(ISNULL(m1.Salary, 0))
from (Select distinct name from MyTable) m
CROSS JOIN (Select distinct date from MyTable) md
LEFT OUTER JOIN MyTable m1 ON m1.date <= md.date AND m.name = m1.name
GROUP BY m.name, md.date
order by md.date, m.name
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 12199906
I would sugest using a different source for the list of all distinct dates.  A function that returns a table given a start and end date, or create a table of all dates say between 01-01-1900 and 01-01-2200
0
 
LVL 14

Accepted Solution

by:
adwiseman earned 25 total points
ID: 12199965
monosodiumg's code, with typo correction works also.

Here, changed to use my test code.

Select Name, SDate, Sum(case WHEN S.Date <= D.SDate then Salary else 0 end)
FROM MyTable S
CROSS JOIN (Select distinct [date] as SDate from MyTable) D
GROUP BY Name, SDate
0
 

Author Comment

by:DMStar
ID: 12200271
2 adwiseman  
Your code works fine
2monosodiumg
doesn't work syntax error

Thanks everybody
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Visual Studios 1 76
Join with a SQL Server STUFF 5 33
Check ALL SP in database make sure there are no errors 17 43
Requesting help with creating an SQL query with 2 tables 6 24
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question