select s.storeid, c.[date]
from   store s
       cross join calendar c
where  c.[date] between '2014-01-01' and '2014-01-07'
and    not exists
       (
           select *
           from   this_table x
           where  x.storeid = s.storeid
           and    x.DateOfBusiness = c.[date]
       )

quote:

---

Originally posted by JAdauto

Is it possible to return data NOT there for a calendar date. I have a table that lists StoreID and DateofBusiness. I need to report any stores that do not have a record. I dont even know how to start reporting data NOT there.

Examples:
StoreID DateOfBusiness
101 01-01-2014
101 01-02-2014
101 01-03-2014
101 01-04-2014
101 01-06-2014
101 01-07-2014

If I were to run a query for store 101 for the first 7 days of January, I would want this to return store 101 and 01-05-2014.

Is this even possible?

Thanks,
JAdauto

---

;With Calendar
AS
(
SELECT MIN(DateOfBusiness) AS Dt
FROM StoreTable
UNION ALL
SELECT DATEADD(dd,1,Start)
FROM Calendar
WHERE DATEADD(dd,1,Start) < = (SELECT MAX(DateOfBusiness) AS STart
FROM StoreTable)
)

SELECT st.StoreID,c.Dt AS MissingDate
FROM Calendar c
CROSS JOIN (SELECT DISTINCT StoreID FROM StoreTable) st
WHERE NOT EXISTS(SELECT 1
FROM  StoreTable st1
WHERE st1.StoreID = st.SToreID
AND st1.DateOfBusiness = c.Dt
)
ORDER BY StoreID,MissingDate