asked
Nested Query
This is likely a "simple" query, but I am a "simple" SQL user. Table CompanyID CID char(5) not null Table DatesWithCalls CID char(5) not null CallDate date not null NumberOfCalls int not null (and not 0) Some companies have no calls. Others have a call on every date. Others have calls on some dates and not on others. My job is to produce list of CID, Date when there are no calls (no entry in the Calls table) for all dates between @StartDate and @EndDate. I can do that for a specific date but not for the entire date range. I don't know how to loop through all the dates. I've read about "tally" tables and created one but still can't get a working query. This works for one date: DECLARE @DateStart DATE DECLARE @DateEnd DATE set @DateStart = '2010-09-22' set @DateEnd = GETDATE () select c.CID,'2010-09-25' from CompanyID c left outer join DatesWithCalls d on (c.CID = d.CID and d.CallDate = '2010-09-25') where ISNULL ( d.numberofcalls, 0 ) = 0 All help appreciated Carl
I got an e-mail saying Cyborg had answered my question. The e-mail included the answer, but when I go online it says ZERO answers. Anyway, the answer does not work. The answer was: DECLARE @DateStart DATE, @DateEnd DATE set @DateStart = '2010-09-22' set @DateEnd = GETDATE () SELECT C.CID,D.CallDate FROM CompanyID C LEFT JOIN DatesWithCalls D ON C.CID = D.CID AND CallDate BETWEEN @FromDate and @ToDate WHERE D.CID IS NULL That returns the CID and NULL for CallDate but not the date on which there were no calls. I expect a table in which the CID is repeated multiple times, each time with a different date for which there were no calls. Carl
DECLARE @FromDate DATE,
@ToDate DATE
SELECT @FromDate = '2010-09-22',@ToDate = GETDATE()
;WITH CTE(cCallDate)
AS
(SELECT @FromDate
UNION ALL
SELECT DATEADD(dd,1,cCallDate)
FROM CTE
WHERE DATEADD(dd,1,cCallDate)
I think this is an example of what you need: USE [adventureworks] go CREATE TABLE #numbers ( N INT NOT NULL , PRIMARY KEY CLUSTERED ( N ) , ADate DATE ) INSERT INTO #numbers SELECT TOP ( 60 ) ROW_NUMBER() OVER ( ORDER BY c1.column_id ) , NULL FROM master.sys.all_columns c1 CROSS JOIN master.sys.all_columns c2 UPDATE [#numbers] SET [ADate] = DATEADD(d, [n].[N], '20010625')--'20010701' + n.[N] FROM [#numbers] AS n SELECT COUNT([soh].[AccountNumber]) , [n].[ADate] FROM [Sales].[SalesOrderHeader] AS soh RIGHT JOIN [#numbers] AS n ON [soh].[OrderDate] = [n].[ADate] GROUP BY adate ORDER BY [n].[ADate] DROP TABLE [#numbers]
