sum query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

sum query

Author

Topic

Amateurtje
Starting Member

5 Posts

Posted - 2011-06-07 : 06:17:27

Hello All,

I am new here so let me briefly introduce myself. I do a little VB programming and seem to know some things about Excel. Therefore my boss asked me to make a Access database in which we can store a lot of data that is now all through the company in excel files. Also we want to make some analysis off this data. I have made some SQL queries but now I need one and I can not seem to find it. I run into a lot of problems.

Problem of today:

I have a table (called projects) in which I have a lot of data: oa the columns: startdate, enddate, amount

This means that inbetween those dates the project is active and therefore the amount of people specified in [Amount] is active.

I want to make a graph where inbetween two dates, the total of active people is given.

For that I need to make a table the has in the first column dates. These can be all startdates and enddates of the projects. (at these dates the amount value will change)
In the second column I need the total amounts (sums) of the column [amount] per specified date.

Can anybody help me?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-07 : 08:41:41

Based on my understanding of your description, something like what I am showing below should work. If that does not work or you, can you post some sample data along with table DDLs? Brett's post here may be of help in creating sample data and scripts: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


with Dates(dt) AS  -- create dates on which amounts change.
(
   SELECT
   startDate
   FROM 
      ProjectsTable
   UNION
   SELECT
      endDate
   FROM
      ProjectsTable
)
SELECT -- find the amounts on those dates.
   d.dt,
   SUM(p.amount) AS TotalAmount
FROM
   d
   OUTER APPLY
   (
      SELECT Amount
      FROM ProjectsTable p
      WHERE d.dt BETWEEN p.startdate AND p.enddate
   )p

Amateurtje
Starting Member

5 Posts

Posted - 2011-06-07 : 09:47:51

Thanks for the fast reply.

I will take a look at it because it is a lot more than I was used to uptill now... (pfft). Thanks a lot!!

I have taken a look to the link you posted and I will try to describe it more as stated in this page. While I work with access I do not have the Create table ddl's (as far as I know).

Problem:
Question: How do I make the table as in the endresult.
Input:
Project II StartDate II Enddate II Amount (Amount of people working)
Proj1 II 1 feb II 1 april II 4
Proj2 II 1 feb II 1 May II 2
Proj3 II 1 jan II 1 april II 5

From two comboboxes I get a Graphenddate and an Grapstartdate. This I can include with a "where"and "between" statement. The table below is the table I need to make the graph.

Desired table endresult:
Enddates and startdates (sorted upward)II [red]Total amount [/red](on that date)
1 jan II 5
1 feb II 11
1 apr II 2
1 may II 0

I will try the sql you gave me. I never saw an SQL beginning with "with" (and having several "SELECT"statements) but I am very new at this. The following is what you meant, isn't it?

with Dates(dt) AS  ( SELECT startDate FROM ProjectsTable UNION SELECT endDate FROM ProjectsTable) SELECT d.dt, SUM(p.amount) AS TotalAmount FROM  d OUTER APPLY (SELECT Amount FROM ProjectsTable p WHERE d.dt BETWEEN p.startdate AND p.enddate)p

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-07 : 10:40:46

In this context, the "with" keyword is creating a common table expression (CTE). CTE's are available only in SQL 2005 or later. A query can have more than one "select", as in this example, or in other constructs, for example, when you have a sub-query.

Based on your sample data, I think the query that I posted earlier should work with one change. And that is to change the where condition date as shown below. Instead of


WHERE d.dt BETWEEN p.startdate AND p.enddate

use


WHERE d.dt >= p.startdate AND d.dt < p.enddate

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-07 : 22:21:42

People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

>> .. my boss asked me to make a Access database in which we can store a lot of data that is now all through the company in excel files. <<

And of course they will not pay for training :) Might want to consider looking for a new job ..

CREATE TABLE Projects
(project_nbr INTEGER NOT NULL PRIMARY KEY,
project_start_date DATE NOT NULL,
project_end_date DATE NOT NULL,
staffing_level INTEGER NOT NULL);

I want to make a graph where in between two dates, the total of active people is given.

>> For that I need to make a table the has in the first column dates. These can be all start_dates and end_dates of the projects. (at these dates the amount value will change). In the second column I need the total amounts (sums) of the column [amount] per specified date. <<

There is an SQL programming idiom called a calendar table. This table has calendar information for your enterprise – fiscal calendars, holidays, etc.

SELECT P.project_nbr, C.cal_date, SUM(staffing_level) AS staff_level_tot
FROM Calendar AS C, Projects AS P
WHERE C.cal_date BETWEEN P.project_start_date
AND P.project_end_date
AND C.cal_date BETWEEN @in_report_start_date
AND @in_report_end_date;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Amateurtje
Starting Member

5 Posts

Posted - 2011-06-09 : 07:18:25

thanks for the reply.

I am sorry that I gave some background information. I am not a frequent user of a lot of different forums. I only use 1 other dutch electronics forum, and there people appriciate that (new) users give some background information before explaining their problem. In my second post I tried to be more direct and exact. I am also relatively new to access, queries and sql statements. I try to learn how to make DDL's from a table but msAccess does not give these (I think). When starting this topic, i did not even know what DDL's are, let alone, ISO standards. Sorry for this and the fact that I am not a proffesional and trained programmer.

@sunitabeck, I try to use your SQL statement but I think msAccess has trouble with accepting a with statement at the beginning.

I will try to play with the statements tomorrow when I have a complete day to work on my database and try to get it working. I will leave a message with the progress.

thanks a lot.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-09 : 07:29:58

Ugh! My bad!! I completely missed what you said about this being in Access - I assumed you were on SQL Server. I have very limited (like zero) experience with Access, so I don't know how to write the query for Access.

Can you repost your question (to MS Access forum)? People may not look at this thread because they would see a few replies and think that the question has been answered.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources