question

chopkin avatar image
chopkin 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
t-sqljoinstally
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
Sorry Chopkin! The notification was correct but meantime i deleted my answer thats why you found zero answer here
0 Likes 0 ·
Cyborg avatar image
Cyborg answered


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)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
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]
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.