sql server programming: I have a temp table that stores several aggregate values for data
manipulation. For several elements I need to know what months they are
not found in this table.

I am running into a problem when the months are not all the same
duration. When March is missing , but April and May are present in the
data and I use 30 'd', March is found as missing, but when April is
missing, I have to use 1 'm' as my parameters.

Any thoughts or tricks?

Thanks,

Robert

SELECT
fldDepartmentID,fldElementID,Max(DateAdd("m",-1,fldComplianceDate)) AS
missingMonth
FROM tblComplianceHistory
WHERE DateAdd("m",1,fldComplianceDate)
Not In (select fldComplianceDate from tblComplianceHistory)
group by fldDepartmentID,fldElementID

*** Sent via Developersdex http://www.developersdex.com ***

David,

Thanks for the help. I'll try this out. I I'm glad to see other people
end up working on weekends too :-)

Robert

*** Sent via Developersdex http://www.developersdex.com ***

I'll assume each (flddepartmentid, fldelementid) has at least one row in
tblComplianceHistory. If not then you'll want separate tables to define the
Departments and Elements.

Use a Calendar table with this query:
http://groups.google.com/groups?selm=gtSdnS_P2LLLZOLdRVn-ig%40giganews.com

SELECT D.flddepartmentid, D.fldelementid, YEAR(C.caldate), MONTH(C.caldate)
FROM Calendar AS C
JOIN (SELECT DISTINCT flddepartmentid, fldelementid
FROM tblComplianceHistory) AS D
ON C.caldate BETWEEN '20040101' AND '20041231' /* modify as required */
AND DAY(C.caldate)=1
LEFT JOIN tblComplianceHistory AS H
ON H.fldcompliancedate >= C.caldate
AND H.fldcompliancedate < DATEADD(M,1,C.caldate)
AND H.flddepartmentid = D.flddepartmentid
AND H.fldelementid = D.fldelementid
WHERE H.flddepartmentid IS NULL
GROUP BY D.flddepartmentid, D.fldelementid, YEAR(C.caldate),
MONTH(C.caldate)

--
David Portas
SQL Server MVP
--