SQL help. - 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

Other Forums

MS Access

SQL help.

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-11-27 : 14:19:27

I have 2 tables


LoadInformation
Project | Load | Date
10001   | 1    | 10/10/2003
10001   | 1    | 10/11/2003
10001   | 1    | 10/12/2003
10001   | 1    | 10/14/2003

Details
Project | Level | EstimatedLoads
10001   | 1     | 10
10001   | 2     | 5

I need to produced a report showing the following


###OUTPUT####
Project |  EstLoads | Prev. Sent | Send in this period | TTD
        |           |            | 10/11/03 TO 10/14/03|
-------------------------------------------------------------
10001   |  15       | 1          | 3                   | 4

Now I was able to count the total loads sent and get the loads sent within a specified period but I have no idea on how to sum the estimated loads field in the details table. Any ideas?

What I have so far:


SELECT tblLoadInformation.Project,
       Count([tblLoadInformation].[Load]) AS Expr1, 
       SUM(iif(tblLoadInformation.Date BETWEEN #10/11/2003#
                                       AND #10/14/2003#, 1, 0)) AS Expr2
       FROM tblLoadInformation GROUP BY tblLoadInformation.Project;

Any help would be appreciated!

Mike B

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-28 : 13:37:18

[code]
SELECT
a.Project,
(SELECT SUM(estimatedloads) FROM details d WHERE d.project=a.project) AS EstLoads,
Count(a.Load) AS Expr1,
SUM(iif(a.Date BETWEEN #10/11/2003# AND #10/14/2003#, 1, 0)) AS Expr2
FROM LoadInformation AS a
GROUP BY a.Project;
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources