Exclude a set of data - to get record that miss

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

SQL Server 2008 Forums

Transact-SQL (2008)

Exclude a set of data - to get record that miss

Author

Topic

geossl
Yak Posting Veteran

85 Posts

Posted - 2011-05-24 : 09:01:53

Dear All,
The is a table for recording the annual report submitted by different societies.

ReportSubmit
ReportSubmitID <int> PrimaryKey
SocietyID <int>
SubmitDate <datetime>

Every society has to submit an annual report.

I would like to get a list of societies from a date range (such as 2003 - 2009), which did not submit annual report at any year.

The result should look like:

<SocietyID> <Yearof No report>

How to achieve this?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-24 : 09:19:17

You will need to have a list of societies somewhere. For example, if you had a table called societyTable that had this information, then you could do the following:


SELECT societyId, YEAR
FROM societyTable st
WHERE NOT EXISTS 
(
   SELECT * 
   FROM reportsSubmitted  rs 
   WHERE rs.year = st.year AND rs.societyId = st.societyId
)

Alternatively, although less complete, you could make up a list of all socieity id's that have submitted a report in any year, and then use that as the basis for determining if any of them have not submitted reports every year.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-24 : 09:36:18

CREATE TABLE #ReportSubmit
(ReportSubmitID int ,
SocietyID int,
SubmitDate datetime)

SET DATEFORMAT YMD
INSERT INTO #ReportSubmit
SELECT 1,1,'2001-01-01'
UNION ALL
SELECT 2,1,'2003-01-01'
UNION ALL
SELECT 3,2,'2002-01-01'
UNION ALL
SELECT 4,2,'2003-01-01'
UNION ALL
SELECT 5,3,'2001-01-01'
UNION ALL
SELECT 6,3,'2002-01-01'
UNION ALL
SELECT 7,3,'2003-01-01'

SELECT DISTINCT ID.SocietyID,YL.[YEAR] AS MissingYear
FROM #ReportSubmit ID
CROSS APPLY (SELECT YEAR(SUBMITDATE) AS [year] FROM #ReportSubmit) YL
LEFT OUTER JOIN
#ReportSubmit RS
ON RS.SocietyID = ID.SocietyID
AND YL.year = YEAR(RS.SubmitDate)
WHERE
RS.SubmitDate IS NULL

DROP TABLE #ReportSubmit

--------------------------
http://connectsql.blogspot.com/

geossl
Yak Posting Veteran

85 Posts

Posted - 2011-05-24 : 21:30:10

Thanks.

If I want to concat all the missing Year in a field, such as,

<SocietyID> <MissingYear>
1 2007, 2009
2 2008, 2010

Is it possible to do this?

quote:
Originally posted by lionofdezert

CREATE TABLE #ReportSubmit
(ReportSubmitID int ,
SocietyID int,
SubmitDate datetime)

SET DATEFORMAT YMD
INSERT INTO #ReportSubmit
SELECT 1,1,'2001-01-01'
UNION ALL
SELECT 2,1,'2003-01-01'
UNION ALL
SELECT 3,2,'2002-01-01'
UNION ALL
SELECT 4,2,'2003-01-01'
UNION ALL
SELECT 5,3,'2001-01-01'
UNION ALL
SELECT 6,3,'2002-01-01'
UNION ALL
SELECT 7,3,'2003-01-01'

SELECT DISTINCT ID.SocietyID,YL.[YEAR] AS MissingYear
FROM #ReportSubmit ID
CROSS APPLY (SELECT YEAR(SUBMITDATE) AS [year] FROM #ReportSubmit) YL
LEFT OUTER JOIN
#ReportSubmit RS
ON RS.SocietyID = ID.SocietyID
AND YL.year = YEAR(RS.SubmitDate)
WHERE
RS.SubmitDate IS NULL

DROP TABLE #ReportSubmit

--------------------------
http://connectsql.blogspot.com/

geossl
Yak Posting Veteran

85 Posts

Posted - 2011-05-24 : 21:32:04

Furthermore, it is a SQL Server 2000.

quote:
Originally posted by lionofdezert

CREATE TABLE #ReportSubmit
(ReportSubmitID int ,
SocietyID int,
SubmitDate datetime)

SET DATEFORMAT YMD
INSERT INTO #ReportSubmit
SELECT 1,1,'2001-01-01'
UNION ALL
SELECT 2,1,'2003-01-01'
UNION ALL
SELECT 3,2,'2002-01-01'
UNION ALL
SELECT 4,2,'2003-01-01'
UNION ALL
SELECT 5,3,'2001-01-01'
UNION ALL
SELECT 6,3,'2002-01-01'
UNION ALL
SELECT 7,3,'2003-01-01'

SELECT DISTINCT ID.SocietyID,YL.[YEAR] AS MissingYear
FROM #ReportSubmit ID
CROSS APPLY (SELECT YEAR(SUBMITDATE) AS [year] FROM #ReportSubmit) YL
LEFT OUTER JOIN
#ReportSubmit RS
ON RS.SocietyID = ID.SocietyID
AND YL.year = YEAR(RS.SubmitDate)
WHERE
RS.SubmitDate IS NULL

DROP TABLE #ReportSubmit

--------------------------
http://connectsql.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources