month report to include zeros

[jamiec1]

I need to display the count of jobs logged by month but include zeros where no jobs have been logged in a mth

SELECT format(DateTimeCreated, "yyyy/mm&quot As Mnth , count(JobRecords.DateTimeCreated) AS Tot
FROM JobRecords
GROUP BY format(DateTimeCreated, "yyyy/mm"

this returns
2002/04 20
2002/05 34

but i need to return

2001/01 0
2001/02 0
2001/03 0
2001/04 20
2001/05 34
etc up to current mth

not sure if it is possible though.
thanks

jamie

 

[blom0344]

Hello Jamie,

Create secondary table with all occurences of the months ("Months&quot with field "Months" and link this table to "Jobrecords".

SELECT Months.Months, Count(IIf(IsNull([JobRecords.DateTimeCreated]),0,[JobRecords.DateTimeCreated])) AS Tot
FROM Jobrecords RIGHT JOIN Months ON Jobrecords.Mnth = Months.Months
GROUP BY Months.Months

 

[jamiec1]

hi blom

the JobRecords.DateTimeCreated field is date and time so without having the month table full of every date and time in each year i dont think this would work .. thanks for your help though..

i posted a cut down version of the query the full one is here


SELECT format(JobRecords.DateTimeCreated, "yyyy/mm&quot AS Mnth, count(JobRecords.DateTimeCreated) AS Tot
FROM ProblemCategory INNER JOIN (CustomerContracts INNER JOIN (CoveredSites INNER JOIN JobRecords ON CoveredSites.SiteID = JobRecords.SiteID) ON CustomerContracts.CustomerID = CoveredSites.CustomerID) ON ProblemCategory.ProbCatID = JobRecords.ProbCatID
WHERE (((CustomerContracts.CustomerID)=1))
GROUP BY format(JobRecords.DateTimeCreated, "yyyy/mm"

note : the jobrecords field is date and time . so i cant joing to another table with months in ..

 

[blom0344]

Hello Jamie,

Sorry, I did not look closely enough at your table structure.
Could your define your original query as part of the query were you join with the additional table?
(I mean link the additional table with the query as you first described) Suppose this query is called "QRY".

So QRY reads as:

SELECT format(DateTimeCreated, "yyyy/mm&quot As Mnth , count(JobRecords.DateTimeCreated) AS Tot
FROM JobRecords
GROUP BY format(DateTimeCreated, "yyyy/mm"


Use this query in a new query where you join with additional table:

SELECT Months.Months, IIf(IsNull[QRY.Tot],0,[QRY.Tot])As Total
FROM
QRY RIGHT JOIN Months ON QRY.Mnth = Months.Months


I stick to the general idea of your problem, the whole idea is to:

aggregate and create new dimension "Month" in a pre-query and use this query within a second query where you use the new dimension to create outer join with the additional table, in the mean time transforming the blanks from the outer join to a real '0' .

Hope this is clear enough.

 

[jamiec1]

Thanks blom


Thats got that working .. i split it into 2 querys

QRY:

SELECT format(JobRecords.DateTimeCreated, "yyyy/mm&quot AS Mnth, count(JobRecords.DateTimeCreated) AS Tot
FROM ProblemCategory INNER JOIN (CustomerContracts INNER JOIN (CoveredSites INNER JOIN JobRecords ON CoveredSites.SiteID = JobRecords.SiteID) ON CustomerContracts.CustomerID = CoveredSites.CustomerID) ON ProblemCategory.ProbCatID = JobRecords.ProbCatID
WHERE (((CustomerContracts.CustomerID)=1))
GROUP BY format(JobRecords.DateTimeCreated, "yyyy/mm"


QRY2:

SELECT format(months.months,"yyyy/mm&quot AS MTH
FROM months;

then did

SELECT QRY2.MTH, IIf(IsNull([QRY.Tot]),0,[QRY.Tot]) AS Total
FROM QRY RIGHT JOIN QRY2 ON QRY.Mnth=QRY2.MTH;

thanks again for your help

Do you think it is possible to keep it all in one query because i am trying to output the record to an asp page and will need to pass in the variable customerID. Not sure if it is going to be possible though as it seems like it has to be split into seperate querys to run .. it is a shame you cant run access like stored proc in sql