Count the number of people that fall into a 30, 60, 90 day window

[Philippe Perrault]

I am trying to make a query that looks at the [Date Loss]-Date() and counts
the number that are <=30, Between 31 And 60, Between 61 And 90.

Here is the SQL:
SELECT [CSM Report HHB].PMOS, [CSM Report HHB].GRADE, [CSM Report
HHB].SumOfAUTH_STR, [CSM Report HHB].CountOfSSN, ([Loss Date]-Date()) AS Days
FROM [CSM Report HHB] LEFT JOIN [HHB 214 FIB Alpha Roster] ON ([CSM Report
HHB].PMOS = [HHB 214 FIB Alpha Roster].PMOS) AND ([CSM Report HHB].GRADE =
[HHB 214 FIB Alpha Roster].Rank);

The date loss comes from the HHB 214 FIB Alpha Roster. Everyone on this
roster is grouped by Rank and PMOS and counted then summed for a total number
by Rank by MOS by the time this query is run.

 

[Philippe Perrault]

I am trying to make a query that looks at the [Date Loss]-Date() and counts
the number that are <=30, Between 31 And 60, Between 61 And 90.

Here is the SQL:
SELECT [CSM Report HHB].PMOS, [CSM Report HHB].GRADE, [CSM Report
HHB].SumOfAUTH_STR, [CSM Report HHB].CountOfSSN, ([Loss Date]-Date()) AS Days
FROM [CSM Report HHB] LEFT JOIN [HHB 214 FIB Alpha Roster] ON ([CSM Report
HHB].PMOS = [HHB 214 FIB Alpha Roster].PMOS) AND ([CSM Report HHB].GRADE =
[HHB 214 FIB Alpha Roster].Rank);

The date loss comes from the HHB 214 FIB Alpha Roster. Everyone on this
roster is grouped by Rank and PMOS and counted then summed for a total number
by Rank by MOS by the time this query is run.

I have been poking around the forum and found that using the following
expression Sum(Abs([Loss Date]-Date()<=30)) will give me the rusult I am
looking for however it has one unwanted result. My data displays like this

PMOS Rank Auth Asg 30 Days
13D1O E3 9 1 1
13D1O E3 9 1 1

I need it to display like this
PMOS Rank Auth Asg 30 Days
13D1O E3 9 2 2

Any suggestions on how to accomplish this would be greatly appricated?

 

[Michael Gramelspacher]

(e-mail address removed)>,
(e-mail address removed) says...

I am trying to make a query that looks at the [Date Loss]-Date() and counts
the number that are <=30, Between 31 And 60, Between 61 And 90.

Here is the SQL:
SELECT [CSM Report HHB].PMOS, [CSM Report HHB].GRADE, [CSM Report
HHB].SumOfAUTH_STR, [CSM Report HHB].CountOfSSN, ([Loss Date]-Date()) AS Days
FROM [CSM Report HHB] LEFT JOIN [HHB 214 FIB Alpha Roster] ON ([CSM Report
HHB].PMOS = [HHB 214 FIB Alpha Roster].PMOS) AND ([CSM Report HHB].GRADE =
[HHB 214 FIB Alpha Roster].Rank);

The date loss comes from the HHB 214 FIB Alpha Roster. Everyone on this
roster is grouped by Rank and PMOS and counted then summed for a total number
by Rank by MOS by the time this query is run.

I have been poking around the forum and found that using the following
expression Sum(Abs([Loss Date]-Date()<=30)) will give me the rusult I am
looking for however it has one unwanted result. My data displays like this

PMOS Rank Auth Asg 30 Days
13D1O E3 9 1 1
13D1O E3 9 1 1

I need it to display like this
PMOS Rank Auth Asg 30 Days
13D1O E3 9 2 2

Any suggestions on how to accomplish this would be greatly appricated?

This seems to works with Northwind. Maybe you can try
something similiar. Watch line wrapping.

SELECT Orders.CustomerId,
Customers.CompanyName,
(SELECT SUM(Iif(Datediff("d",[a.OrderDate],[a.ShippedDate])
< 30, +1,0))
FROM Orders AS a
WHERE Orders.CustomerId = a.CustomerId) AS [Less Than
30 Days],
(SELECT SUM(Iif(Datediff("d",[a.OrderDate],[a.ShippedDate])
BETWEEN 31 AND 60,+1,0))
FROM Orders AS a
WHERE Orders.CustomerId = a.CustomerId) AS [31 - 60
Days],
(SELECT SUM(Iif(Datediff("d",[OrderDate],[ShippedDate])
BETWEEN 61 AND 90,+1,0))
FROM Orders AS a
WHERE Orders.CustomerId = a.CustomerId) AS [61 - 90
Days],
(SELECT SUM(Iif(Datediff("d",[OrderDate],[ShippedDate]) >
90,+1,0))
FROM Orders AS a
WHERE Orders.CustomerId = a.CustomerId) AS [Greater
Than 90 Days]
FROM Customers
INNER JOIN Orders ON Customers.CustomerId =
Orders.CustomerId
GROUP BY Orders.CustomerId,Customers.CompanyName;

 

[Philippe Perrault]

I already had the following:
GROUP BY [HHB Authorized].PMOS, [HHB Authorized].Rank, [HHB
Authorized].SumOfAUTH_STR, [HHB 214 FIB Alpha Roster].[Loss Date];

And I think I didn't give some pertinent information: The 30 Days Field is
an expression so I could not Group By without getting an error. But your
post did give me an idea to build another query that runs against this query
and sums the Auth Field and 30 Days field and groups all as shown above in
the SQL and I got the data in the format I was looking for. If you know of a
more elegant solution then a second query I'm all ears but I do have a
solution that works.

Thanks for the help

(e-mail address removed)>,
(e-mail address removed) says...

I am trying to make a query that looks at the [Date Loss]-Date() and counts
the number that are <=30, Between 31 And 60, Between 61 And 90.

Here is the SQL:
SELECT [CSM Report HHB].PMOS, [CSM Report HHB].GRADE, [CSM Report
HHB].SumOfAUTH_STR, [CSM Report HHB].CountOfSSN, ([Loss Date]-Date()) AS Days
FROM [CSM Report HHB] LEFT JOIN [HHB 214 FIB Alpha Roster] ON ([CSM Report
HHB].PMOS = [HHB 214 FIB Alpha Roster].PMOS) AND ([CSM Report HHB].GRADE =
[HHB 214 FIB Alpha Roster].Rank);

The date loss comes from the HHB 214 FIB Alpha Roster. Everyone on this
roster is grouped by Rank and PMOS and counted then summed for a total number
by Rank by MOS by the time this query is run.

I have been poking around the forum and found that using the following
expression Sum(Abs([Loss Date]-Date()<=30)) will give me the rusult I am
looking for however it has one unwanted result. My data displays like this

PMOS Rank Auth Asg 30 Days
13D1O E3 9 1 1
13D1O E3 9 1 1

I need it to display like this
PMOS Rank Auth Asg 30 Days
13D1O E3 9 2 2

Any suggestions on how to accomplish this would be greatly appricated?

This seems to works with Northwind. Maybe you can try
something similiar. Watch line wrapping.

SELECT Orders.CustomerId,
Customers.CompanyName,
(SELECT SUM(Iif(Datediff("d",[a.OrderDate],[a.ShippedDate])
< 30, +1,0))
FROM Orders AS a
WHERE Orders.CustomerId = a.CustomerId) AS [Less Than
30 Days],
(SELECT SUM(Iif(Datediff("d",[a.OrderDate],[a.ShippedDate])
BETWEEN 31 AND 60,+1,0))
FROM Orders AS a
WHERE Orders.CustomerId = a.CustomerId) AS [31 - 60
Days],
(SELECT SUM(Iif(Datediff("d",[OrderDate],[ShippedDate])
BETWEEN 61 AND 90,+1,0))
FROM Orders AS a
WHERE Orders.CustomerId = a.CustomerId) AS [61 - 90
Days],
(SELECT SUM(Iif(Datediff("d",[OrderDate],[ShippedDate]) >
90,+1,0))
FROM Orders AS a
WHERE Orders.CustomerId = a.CustomerId) AS [Greater
Than 90 Days]
FROM Customers
INNER JOIN Orders ON Customers.CustomerId =
Orders.CustomerId
GROUP BY Orders.CustomerId,Customers.CompanyName;