views:

35

answers:

1

I have the following 2 tables:

1) Companies

ID      CompanyName      Abbreviation     Notes  
1        CompanyA             CA          ...  
2        CompanyB             CB          ...  
3        CompanyC             CC          ...

2) PlannedDeployments

ID      CompanyID      TypeID      DepDate      NumDeployed  
1          1             2         09/2010          5  
2          1             2         10/2010          5
3          1             3         09/2010          3
4          1             3         10/2010          3
5          1             4         10/2010          4
6          2             2         12/2010          10
7          2             4         10/2010          1
8          3             2         11/2010          6

Note that TypeID is a number between 1 and 5 describing what type of person is being deployed. For the purposes of this query, I'm interested in Type2 employees for each company and then the sum of Types 3 & 4 for each date. What I eventually want to end up with is a crosstab that looks like the following:

Crosstab

Date/Company    CompanyA    CompanyB    CompanyC     SumOfTypes3and4
09/2010            5                                        3
10/2010            5                                        8
11/2010                                    6                
12/2010                        10

The problem is that final column - the sum of Type 3 and Type 4 employees. The current crosstab that I have includes everything except that sum column and looks like the following:

TRANSFORM Sum(PlannedDeployments.NumDeployed) AS ["NumDeployed"]
SELECT PlannedDeployments.DepDate
FROM PlannedDeployments LEFT JOIN Companies ON Companies.ID=PlannedDeployments.CompanyID
WHERE PlannedDeployments.TypeID=2 AND (PlannedDeployments.DepDate Between FormFieldValue("Form", "Control") AND FormFieldValue("Form", "Control"))
GROUP BY PlannedDeployments.DepDate
PIVOT Companies.CompanyName;

The second part of that WHERE clause is just limiting the data by some form controls. Anyway - I'm having a lot of trouble getting that final column. Anyone have any ideas?

Edit: Building on the solution provided by Remou below, here's what the final query ended up looking like:

TRANSFORM Sum(PlannedDeployments.NumDeployed) AS ["NumDeployed"]
SELECT PlannedDeployments.DepDate, q.SumOfNumDeployed
FROM (SELECT PlannedDeployments.DepDate, Sum(PlannedDeployments.NumDeployed) AS SumOfNumDeployed
FROM PlannedDeployments
WHERE (((PlannedDeployments.[TypeID]) In (3,4)))
GROUP BY PlannedDeployments.DepDate) AS q 
RIGHT JOIN (PlannedDeployments 
INNER JOIN Companies ON PlannedDeployments.CompanyID = Companies.ID) 
ON q.DepDate = PlannedDeployments.DepDate
WHERE PlannedDeployments.TypeID=2 
   AND (PlannedDeployments.DepDate Between FormFieldValue("Form", "Control") 
   AND FormFieldValue("Form", "Control"))
GROUP BY PlannedDeployments.DepDate, q.SumOfNumDeployed
PIVOT Companies.CompanyName;
+1  A: 

You can use a subquery:

TRANSFORM Sum(PlannedDeployments.NumDeployed) AS ["NumDeployed"]
SELECT PlannedDeployments.DepDate, Sum(q.SumOfNumDeployed) AS SumOfSumOfNumDeployed
FROM (SELECT PlannedDeployments.DepDate, Sum(PlannedDeployments.NumDeployed) AS SumOfNumDeployed
FROM PlannedDeployments
WHERE (((PlannedDeployments.[TypeID]) In (3,4)))
GROUP BY PlannedDeployments.DepDate) AS q 
RIGHT JOIN (PlannedDeployments 
INNER JOIN Companies ON PlannedDeployments.CompanyID = Companies.ID) 
ON q.DepDate = PlannedDeployments.DepDate
WHERE PlannedDeployments.TypeID=2 
   AND (PlannedDeployments.DepDate Between FormFieldValue("Form", "Control") 
   AND FormFieldValue("Form", "Control"))
GROUP BY PlannedDeployments.DepDate
PIVOT Companies.CompanyName;
Remou
That gets me real close, but it's not quite correct. What I'm looking for in that last column is a sum-by-date, which isn't quite what I'm seeing with the above query. Given my actual data, I'm expecting something like: 1/2010: 2, 2/2010: 2, 3/2010: 4, 4/2010: 6And What I'm getting instead is: 1/2010: 2, 2/2010: 4, 3/2010: 12, 4/2010: 24.
btollett
You need the subquery limited by the dates on the form as well? If so, you can add a where statement to the subquery.
Remou
Nope - all I ended up having to do was remove the Sum() from the second line there and then adding a 'q.SumOfNumDeployed' to the GROUP BY clause on the second to last line and the data looks perfect. Thanks for your help.
btollett