Need help with query

• Greg Snidow

  SSCoach

  Points: 16108

  More actions

  August 8, 2008 at 10:30 am

  #196733

  Greetings all. I have a folder that contains tab delimited .txt files that I use to BCP into my database, and they come from daily reports sent to us by our contractors. I name them with the date + contractor name, like this: 8-1-2008_Mastec. I know that many reports are missing, and I am trying to find an easy way to determine which ones they are, before too much time goes by. Right now I only have them from the end of April to now. I am able to get it down to which days have missing reports, and how many, but I can not figure out how to see exactly which reports are missing. Below is the data, and what I have up to where I got stuck. Any help would be greatly appreciated.

  --Create test table

  IF OBJECT_ID('TempDB..#ReportFiles','u') IS NOT NULL

  DROP TABLE #ReportFiles

  CREATE TABLE #ReportFiles

  (

  ID INT IDENTITY(1,1),

  FileName VARCHAR(100)

  )

  --Populate actual data

  INSERT INTO #ReportFiles

  SELECT '4-22-2008_Danella.txt' UNION ALL

  SELECT '4-22-2008_Ivy.txt' UNION ALL

  SELECT '4-22-2008_Jnet.txt' UNION ALL

  SELECT '4-23-2008_Danella.txt' UNION ALL

  SELECT '4-23-2008_Ivy.txt' UNION ALL

  SELECT '4-23-2008_Mastec.txt' UNION ALL

  SELECT '4-23-2008_S&N.txt' UNION ALL

  SELECT '4-24-2008_Danella.txt' UNION ALL

  SELECT '4-24-2008_Jnet.txt' UNION ALL

  SELECT '4-24-2008_Mastec.txt' UNION ALL

  SELECT '4-24-2008_S&N.txt' UNION ALL

  SELECT '4-25-2008_Ivy.txt' UNION ALL

  SELECT '4-25-2008_Mastec.txt' UNION ALL

  SELECT '4-28-2008_Danella.txt' UNION ALL

  SELECT '4-28-2008_Ivy.txt' UNION ALL

  SELECT '4-28-2008_Jnet.txt' UNION ALL

  SELECT '4-28-2008_Mastec.txt' UNION ALL

  SELECT '4-28-2008_S&N.txt' UNION ALL

  SELECT '4-29-2008_Ivy.txt' UNION ALL

  SELECT '4-29-2008_Mastec.txt' UNION ALL

  SELECT '4-29-2008_S&N.txt' UNION ALL

  SELECT '4-30-2008_Danella.txt' UNION ALL

  SELECT '4-30-2008_Mastec.txt' UNION ALL

  SELECT '4-30-2008_S&N.txt' UNION ALL

  SELECT '5-1-2008_Danella.txt' UNION ALL

  SELECT '5-1-2008_Ivy.txt' UNION ALL

  SELECT '5-1-2008_Mastec.txt' UNION ALL

  SELECT '5-1-2008_S&N.txt' UNION ALL

  SELECT '5-12-2008_Danella.txt' UNION ALL

  SELECT '5-12-2008_Ivy.txt' UNION ALL

  SELECT '5-12-2008_Jnet.txt' UNION ALL

  SELECT '5-12-2008_Mastec.txt' UNION ALL

  SELECT '5-13-2008_Ivy.txt' UNION ALL

  SELECT '5-13-2008_Jnet.txt' UNION ALL

  SELECT '5-13-2008_S&N.txt' UNION ALL

  SELECT '5-14-2008_Danella.txt' UNION ALL

  SELECT '5-14-2008_Ivy.txt' UNION ALL

  SELECT '5-14-2008_Jnet.txt' UNION ALL

  SELECT '5-14-2008_Mastec.txt' UNION ALL

  SELECT '5-14-2008_S&N.txt' UNION ALL

  SELECT '5-15-2008_Danella.txt' UNION ALL

  SELECT '5-15-2008_Ivy.txt' UNION ALL

  SELECT '5-15-2008_Jnet.txt' UNION ALL

  SELECT '5-15-2008_Mastec.txt' UNION ALL

  SELECT '5-15-2008_S&N.txt' UNION ALL

  SELECT '5-16-2008_Danella.txt' UNION ALL

  SELECT '5-16-2008_Ivy.txt' UNION ALL

  SELECT '5-16-2008_Mastec.txt' UNION ALL

  SELECT '5-16-2008_S&N.txt' UNION ALL

  SELECT '5-18-2008_Jnet.txt' UNION ALL

  SELECT '5-19-2008_Danella.txt' UNION ALL

  SELECT '5-19-2008_Ivy.txt' UNION ALL

  SELECT '5-19-2008_Jnet.txt' UNION ALL

  SELECT '5-19-2008_Mastec.txt' UNION ALL

  SELECT '5-19-2008_S&N.txt' UNION ALL

  SELECT '5-2-2008_Danella.txt' UNION ALL

  SELECT '5-2-2008_Ivy.txt' UNION ALL

  SELECT '5-2-2008_Jnet.txt' UNION ALL

  SELECT '5-2-2008_Mastec.txt' UNION ALL

  SELECT '5-20-2008_Danella.txt' UNION ALL

  SELECT '5-20-2008_Ivy.txt' UNION ALL

  SELECT '5-20-2008_Jnet.txt' UNION ALL

  SELECT '5-20-2008_Mastec.txt' UNION ALL

  SELECT '5-20-2008_S&N.txt' UNION ALL

  SELECT '5-21-2008_Danella.txt' UNION ALL

  SELECT '5-21-2008_Ivy.txt' UNION ALL

  SELECT '5-21-2008_Jnet.txt' UNION ALL

  SELECT '5-21-2008_Mastec.txt' UNION ALL

  SELECT '5-21-2008_S&N.txt' UNION ALL

  SELECT '5-22-2008_Danella.txt' UNION ALL

  SELECT '5-22-2008_Ivy.txt' UNION ALL

  SELECT '5-22-2008_Jnet.txt' UNION ALL

  SELECT '5-22-2008_Mastec.txt' UNION ALL

  SELECT '5-22-2008_S&N.txt' UNION ALL

  SELECT '5-23-2008_Danella.txt' UNION ALL

  SELECT '5-23-2008_Ivy.txt' UNION ALL

  SELECT '5-23-2008_Jnet.txt' UNION ALL

  SELECT '5-23-2008_Mastec.txt' UNION ALL

  SELECT '5-23-2008_S&N.txt' UNION ALL

  SELECT '5-27-2008_Danella.txt' UNION ALL

  SELECT '5-27-2008_Ivy.txt' UNION ALL

  SELECT '5-27-2008_Mastec.txt' UNION ALL

  SELECT '5-27-2008_S&N.txt' UNION ALL

  SELECT '5-28-2008_Danella.txt' UNION ALL

  SELECT '5-28-2008_Ivy.txt' UNION ALL

  SELECT '5-28-2008_Jnet.txt' UNION ALL

  SELECT '5-28-2008_Mastec.txt' UNION ALL

  SELECT '5-28-2008_S&N.txt' UNION ALL

  SELECT '5-29-2008_Danella.txt' UNION ALL

  SELECT '5-29-2008_Ivy.txt' UNION ALL

  SELECT '5-29-2008_Jnet.txt' UNION ALL

  SELECT '5-29-2008_Mastec.txt' UNION ALL

  SELECT '5-29-2008_S&N.txt' UNION ALL

  SELECT '5-3-2008_Jnet.txt' UNION ALL

  SELECT '5-30-2008_Danella.txt' UNION ALL

  SELECT '5-30-2008_Ivy.txt' UNION ALL

  SELECT '5-30-2008_Jnet.txt' UNION ALL

  SELECT '5-30-2008_Mastec.txt' UNION ALL

  SELECT '5-30-2008_S&N.txt' UNION ALL

  SELECT '5-5-2008_Danella.txt' UNION ALL

  SELECT '5-5-2008_Mastec.txt' UNION ALL

  SELECT '5-5-2008_S&N.txt' UNION ALL

  SELECT '5-6-2008_Danella.txt' UNION ALL

  SELECT '5-6-2008_Ivy.txt' UNION ALL

  SELECT '5-6-2008_Jnet.txt' UNION ALL

  SELECT '5-6-2008_Mastec.txt' UNION ALL

  SELECT '5-6-2008_S&N.txt' UNION ALL

  SELECT '5-7-2008_Danella.txt' UNION ALL

  SELECT '5-7-2008_Ivy.txt' UNION ALL

  SELECT '5-7-2008_Jnet.txt' UNION ALL

  SELECT '5-7-2008_Mastec.txt' UNION ALL

  SELECT '5-7-2008_S&N.txt' UNION ALL

  SELECT '5-8-2008_Danella.txt' UNION ALL

  SELECT '5-8-2008_Ivy.txt' UNION ALL

  SELECT '5-8-2008_Mastec.txt' UNION ALL

  SELECT '5-8-2008_S&N.txt' UNION ALL

  SELECT '5-9-2008_Danella.txt' UNION ALL

  SELECT '5-9-2008_Ivy.txt' UNION ALL

  SELECT '5-9-2008_S&N.txt' UNION ALL

  SELECT '6-10-2008_Danella.txt' UNION ALL

  SELECT '6-10-2008_Ivy.txt' UNION ALL

  SELECT '6-10-2008_Jnet.txt' UNION ALL

  SELECT '6-10-2008_Mastec.txt' UNION ALL

  SELECT '6-10-2008_S&N.txt' UNION ALL

  SELECT '6-11-2008_Danella.txt' UNION ALL

  SELECT '6-11-2008_Ivy.txt' UNION ALL

  SELECT '6-11-2008_Jnet.txt' UNION ALL

  SELECT '6-11-2008_Mastec.txt' UNION ALL

  SELECT '6-11-2008_S&N.txt' UNION ALL

  SELECT '6-12-2008_Danella.txt' UNION ALL

  SELECT '6-12-2008_Ivy.txt' UNION ALL

  SELECT '6-12-2008_Jnet.txt' UNION ALL

  SELECT '6-12-2008_Mastec.txt' UNION ALL

  SELECT '6-12-2008_S&N.txt' UNION ALL

  SELECT '6-13-2008_Danella.txt' UNION ALL

  SELECT '6-13-2008_Ivy.txt' UNION ALL

  SELECT '6-13-2008_Jnet.txt' UNION ALL

  SELECT '6-13-2008_Mastec.txt' UNION ALL

  SELECT '6-13-2008_S&N.txt' UNION ALL

  SELECT '6-16-2008_Danella.txt' UNION ALL

  SELECT '6-16-2008_Ivy.txt' UNION ALL

  SELECT '6-16-2008_Jnet.txt' UNION ALL

  SELECT '6-16-2008_Mastec.txt' UNION ALL

  SELECT '6-16-2008_S&N.txt' UNION ALL

  SELECT '6-17-2008_Danella.txt' UNION ALL

  SELECT '6-17-2008_Ivy.txt' UNION ALL

  SELECT '6-17-2008_Jnet.txt' UNION ALL

  SELECT '6-17-2008_Mastec.txt' UNION ALL

  SELECT '6-17-2008_S&N.txt' UNION ALL

  SELECT '6-18-2008_Danella.txt' UNION ALL

  SELECT '6-18-2008_Ivy.txt' UNION ALL

  SELECT '6-18-2008_Mastec.txt' UNION ALL

  SELECT '6-18-2008_S&N.txt' UNION ALL

  SELECT '6-19-2008_Danella.txt' UNION ALL

  SELECT '6-19-2008_Ivy.txt' UNION ALL

  SELECT '6-19-2008_Jnet.txt' UNION ALL

  SELECT '6-19-2008_Mastec.txt' UNION ALL

  SELECT '6-19-2008_S&N.txt' UNION ALL

  SELECT '6-2-2008_Danella.txt' UNION ALL

  SELECT '6-2-2008_Ivy.txt' UNION ALL

  SELECT '6-2-2008_Jnet.txt' UNION ALL

  SELECT '6-2-2008_Mastec.txt' UNION ALL

  SELECT '6-2-2008_S&N.txt' UNION ALL

  SELECT '6-20-2008_Danella.txt' UNION ALL

  SELECT '6-20-2008_Ivy.txt' UNION ALL

  SELECT '6-20-2008_Jnet.txt' UNION ALL

  SELECT '6-20-2008_Mastec.txt' UNION ALL

  SELECT '6-20-2008_S&N.txt' UNION ALL

  SELECT '6-23-2008_Danella.txt' UNION ALL

  SELECT '6-23-2008_Ivy.txt' UNION ALL

  SELECT '6-23-2008_Jnet.txt' UNION ALL

  SELECT '6-23-2008_Mastec.txt' UNION ALL

  SELECT '6-23-2008_S&N.txt' UNION ALL

  SELECT '6-24-2008_Danella.txt' UNION ALL

  SELECT '6-24-2008_Ivy.txt' UNION ALL

  SELECT '6-24-2008_Jnet.txt' UNION ALL

  SELECT '6-24-2008_Mastec.txt' UNION ALL

  SELECT '6-24-2008_S&N.txt' UNION ALL

  SELECT '6-25-2008_Danella.txt' UNION ALL

  SELECT '6-25-2008_Ivy.txt' UNION ALL

  SELECT '6-25-2008_Jnet.txt' UNION ALL

  SELECT '6-25-2008_Mastec.txt' UNION ALL

  SELECT '6-25-2008_S&N.txt' UNION ALL

  SELECT '6-26-2008_Danella.txt' UNION ALL

  SELECT '6-26-2008_Ivy.txt' UNION ALL

  SELECT '6-26-2008_Jnet.txt' UNION ALL

  SELECT '6-26-2008_Mastec.txt' UNION ALL

  SELECT '6-26-2008_S&N.txt' UNION ALL

  SELECT '6-27-2008_Danella.txt' UNION ALL

  SELECT '6-27-2008_Ivy.txt' UNION ALL

  SELECT '6-27-2008_Jnet.txt' UNION ALL

  SELECT '6-27-2008_Mastec.txt' UNION ALL

  SELECT '6-27-2008_S&N.txt' UNION ALL

  SELECT '6-3-2008_Danella.txt' UNION ALL

  SELECT '6-3-2008_Ivy.txt' UNION ALL

  SELECT '6-3-2008_Jnet.txt' UNION ALL

  SELECT '6-3-2008_Mastec.txt' UNION ALL

  SELECT '6-3-2008_S&N.txt' UNION ALL

  SELECT '6-30-2008_Danella.txt' UNION ALL

  SELECT '6-30-2008_Ivy.txt' UNION ALL

  SELECT '6-30-2008_Jnet.txt' UNION ALL

  SELECT '6-30-2008_Mastec.txt' UNION ALL

  SELECT '6-30-2008_S&N.txt' UNION ALL

  SELECT '6-4-2008_Jnet.txt' UNION ALL

  SELECT '6-4-2008_Mastec.txt' UNION ALL

  SELECT '6-4-2008_S&N.txt' UNION ALL

  SELECT '6-5-2008_Danella.txt' UNION ALL

  SELECT '6-5-2008_Ivy.txt' UNION ALL

  SELECT '6-5-2008_Jnet.txt' UNION ALL

  SELECT '6-5-2008_Mastec.txt' UNION ALL

  SELECT '6-6-2008_Danella.txt' UNION ALL

  SELECT '6-6-2008_Ivy.txt' UNION ALL

  SELECT '6-6-2008_Jnet.txt' UNION ALL

  SELECT '6-6-2008_Mastec.txt' UNION ALL

  SELECT '6-6-2008_S&N.txt' UNION ALL

  SELECT '6-7-2008_Jnet.txt' UNION ALL

  SELECT '6-8-2008_Jnet.txt' UNION ALL

  SELECT '6-9-2008_Danella.txt' UNION ALL

  SELECT '6-9-2008_Ivy.txt' UNION ALL

  SELECT '6-9-2008_Mastec.txt' UNION ALL

  SELECT '6-9-2008_S&N.txt' UNION ALL

  SELECT '7-1-2008_Danella.txt' UNION ALL

  SELECT '7-1-2008_Ivy.txt' UNION ALL

  SELECT '7-1-2008_Jnet.txt' UNION ALL

  SELECT '7-1-2008_Mastec.txt' UNION ALL

  SELECT '7-1-2008_S&N.txt' UNION ALL

  SELECT '7-10-2008_Danella.txt' UNION ALL

  SELECT '7-10-2008_Ivy.txt' UNION ALL

  SELECT '7-10-2008_Jnet.txt' UNION ALL

  SELECT '7-10-2008_Mastec.txt' UNION ALL

  SELECT '7-10-2008_S&N.txt' UNION ALL

  SELECT '7-11-2008_Danella.txt' UNION ALL

  SELECT '7-11-2008_Ivy.txt' UNION ALL

  SELECT '7-11-2008_Jnet.txt' UNION ALL

  SELECT '7-11-2008_Mastec.txt' UNION ALL

  SELECT '7-11-2008_S&N.txt' UNION ALL

  SELECT '7-14-2008_Danella.txt' UNION ALL

  SELECT '7-14-2008_Ivy.txt' UNION ALL

  SELECT '7-14-2008_Jnet.txt' UNION ALL

  SELECT '7-14-2008_Mastec.txt' UNION ALL

  SELECT '7-14-2008_S&N.txt' UNION ALL

  SELECT '7-15-2008_Danella.txt' UNION ALL

  SELECT '7-15-2008_Ivy.txt' UNION ALL

  SELECT '7-15-2008_Jnet.txt' UNION ALL

  SELECT '7-15-2008_Mastec.txt' UNION ALL

  SELECT '7-15-2008_S&N.txt' UNION ALL

  SELECT '7-16-2008_Danella.txt' UNION ALL

  SELECT '7-16-2008_Ivy.txt' UNION ALL

  SELECT '7-16-2008_Jnet.txt' UNION ALL

  SELECT '7-16-2008_Mastec.txt' UNION ALL

  SELECT '7-16-2008_S&N.txt' UNION ALL

  SELECT '7-17-2008_Danella.txt' UNION ALL

  SELECT '7-17-2008_Ivy.txt' UNION ALL

  SELECT '7-17-2008_Jnet.txt' UNION ALL

  SELECT '7-17-2008_Mastec.txt' UNION ALL

  SELECT '7-17-2008_S&N.txt' UNION ALL

  SELECT '7-18-2008_Danella.txt' UNION ALL

  SELECT '7-18-2008_Ivy.txt' UNION ALL

  SELECT '7-18-2008_Jnet.txt' UNION ALL

  SELECT '7-18-2008_Mastec.txt' UNION ALL

  SELECT '7-18-2008_S&N.txt' UNION ALL

  SELECT '7-2-2008_Danella.txt' UNION ALL

  SELECT '7-2-2008_Ivy.txt' UNION ALL

  SELECT '7-2-2008_Jnet.txt' UNION ALL

  SELECT '7-2-2008_Mastec.txt' UNION ALL

  SELECT '7-2-2008_S&N.txt' UNION ALL

  SELECT '7-21-2008_Danella.txt' UNION ALL

  SELECT '7-21-2008_Ivy.txt' UNION ALL

  SELECT '7-21-2008_Jnet.txt' UNION ALL

  SELECT '7-21-2008_Mastec.txt' UNION ALL

  SELECT '7-21-2008_S&N.txt' UNION ALL

  SELECT '7-22-2008_Danella.txt' UNION ALL

  SELECT '7-22-2008_Ivy.txt' UNION ALL

  SELECT '7-22-2008_Jnet.txt' UNION ALL

  SELECT '7-22-2008_Mastec.txt' UNION ALL

  SELECT '7-22-2008_S&N.txt' UNION ALL

  SELECT '7-23-2008_Danella.txt' UNION ALL

  SELECT '7-23-2008_Ivy.txt' UNION ALL

  SELECT '7-23-2008_Jnet.txt' UNION ALL

  SELECT '7-23-2008_Mastec.txt' UNION ALL

  SELECT '7-23-2008_S&N.txt' UNION ALL

  SELECT '7-24-2008_Danella.txt' UNION ALL

  SELECT '7-24-2008_Ivy.txt' UNION ALL

  SELECT '7-24-2008_Mastec.txt' UNION ALL

  SELECT '7-24-2008_S&N.txt' UNION ALL

  SELECT '7-25-2008_Danella.txt' UNION ALL

  SELECT '7-25-2008_Ivy.txt' UNION ALL

  SELECT '7-25-2008_Jnet.txt' UNION ALL

  SELECT '7-25-2008_Mastec.txt' UNION ALL

  SELECT '7-25-2008_S&N.txt' UNION ALL

  SELECT '7-28-2008_Danella.txt' UNION ALL

  SELECT '7-28-2008_Ivy.txt' UNION ALL

  SELECT '7-28-2008_Jnet.txt' UNION ALL

  SELECT '7-28-2008_Mastec.txt' UNION ALL

  SELECT '7-28-2008_S&N.txt' UNION ALL

  SELECT '7-29-2008_Danella.txt' UNION ALL

  SELECT '7-29-2008_Ivy.txt' UNION ALL

  SELECT '7-29-2008_Jnet.txt' UNION ALL

  SELECT '7-29-2008_Mastec.txt' UNION ALL

  SELECT '7-29-2008_S&N.txt' UNION ALL

  SELECT '7-3-2008_Danella.txt' UNION ALL

  SELECT '7-3-2008_Ivy.txt' UNION ALL

  SELECT '7-3-2008_Jnet.txt' UNION ALL

  SELECT '7-3-2008_S&N.txt' UNION ALL

  SELECT '7-30-2008_Danella.txt' UNION ALL

  SELECT '7-30-2008_Ivy.txt' UNION ALL

  SELECT '7-30-2008_Mastec.txt' UNION ALL

  SELECT '7-30-2008_S&N.txt' UNION ALL

  SELECT '7-31-2008_Danella.txt' UNION ALL

  SELECT '7-31-2008_Ivy.txt' UNION ALL

  SELECT '7-31-2008_Jnet.txt' UNION ALL

  SELECT '7-31-2008_Mastec.txt' UNION ALL

  SELECT '7-31-2008_S&N.txt' UNION ALL

  SELECT '7-7-2008_Ivy.txt' UNION ALL

  SELECT '7-7-2008_Jnet.txt' UNION ALL

  SELECT '7-7-2008_S&N.txt' UNION ALL

  SELECT '7-8-2008_Danella.txt' UNION ALL

  SELECT '7-8-2008_Jnet.txt' UNION ALL

  SELECT '7-8-2008_S&N.txt' UNION ALL

  SELECT '7-9-2008_Danella.txt' UNION ALL

  SELECT '7-9-2008_Ivy.txt' UNION ALL

  SELECT '7-9-2008_Mastec.txt' UNION ALL

  SELECT '7-9-2008_S&N.txt' UNION ALL

  SELECT '7-9-2009_Jnet.txt' UNION ALL

  SELECT '8-1-2008_Danella.txt' UNION ALL

  SELECT '8-1-2008_Ivy.txt' UNION ALL

  SELECT '8-1-2008_Jnet.txt' UNION ALL

  SELECT '8-1-2008_Mastec.txt' UNION ALL

  SELECT '8-1-2008_S&N.txt' UNION ALL

  SELECT '8-4-2008_Danella.txt' UNION ALL

  SELECT '8-4-2008_Ivy.txt' UNION ALL

  SELECT '8-4-2008_Jnet.txt' UNION ALL

  SELECT '8-4-2008_Mastec.txt' UNION ALL

  SELECT '8-4-2008_S&N.txt' UNION ALL

  SELECT '8-5-2008_Danella.txt' UNION ALL

  SELECT '8-5-2008_Ivy.txt' UNION ALL

  SELECT '8-5-2008_Jnet.txt' UNION ALL

  SELECT '8-5-2008_Mastec.txt' UNION ALL

  SELECT '8-5-2008_S&N.txt' UNION ALL

  SELECT '8-6-2008_Danella.txt' UNION ALL

  SELECT '8-6-2008_Ivy.txt' UNION ALL

  SELECT '8-6-2008_Jnet.txt' UNION ALL

  SELECT '8-6-2008_Mastec.txt' UNION ALL

  SELECT '8-6-2008_S&N.txt' UNION ALL

  SELECT '8-7-2008_Danella.txt' UNION ALL

  SELECT '8-7-2008_Ivy.txt' UNION ALL

  SELECT '8-7-2008_Jnet.txt' UNION ALL

  SELECT '8-7-2008_Mastec.txt' UNION ALL

  SELECT '8-7-2008_S&N.txt'

  Now the query

  --Show which dates have missing reports. There should be a total

  --of 5 for each Monday through Friday. Any reports on Saturday

  --or Sunday are extra, so are not included in the output. I need to

  --be able to know which contractors are missing reports for each day

  SELECT

  ReportDate,

  NumMissing= 5-(danella+ivy+jnet+mastec+)

  FROM

  (--T2 shows 1 or 0 for each day for each contractor

  SELECT

  ReportDate,

  Danella = SUM(CASE WHEN Contractor LIKE 'Danella%' THEN 1 ELSE 0 END),

  Ivy = SUM(CASE WHEN Contractor LIKE 'Ivy%' THEN 1 ELSE 0 END),

  JNET = SUM(CASE WHEN Contractor LIKE 'JNET%' THEN 1 ELSE 0 END),

  Mastec = SUM(CASE WHEN Contractor LIKE 'Mastec%' THEN 1 ELSE 0 END),

  = SUM(CASE WHEN Contractor LIKE 'S&N%' THEN 1 ELSE 0 END)

  FROM

  (--T1 pulls out the date and contractor name from the filename

  SELECT

  FileName,

  ReportDate = CONVERT(SMALLDATETIME,SUBSTRING(FileName,1,CHARINDEX('_',filename)-1)),

  Contractor = REPLACE(SUBSTRING(FileName,CHARINDEX('_',FileName)+1,20),'.txt','')

  FROM #ReportFiles

  ) t1--Should be end paren before 't1', but the face shows up instead.

  GROUP BY ReportDate

  ) t2

  WHERE 5-(danella+ivy+jnet+mastec+)>0

  AND DATENAME(dw,ReportDate) NOT IN ('Saturday','Sunday')

  Any ideas?

  Greg
  _________________________________________________________________________________________________
  The glass is at one half capacity: nothing more, nothing less.

• Greg Snidow

  SSCoach

  Points: 16108

  More actions

  October 3, 2008 at 6:36 am

  #880425

  Greetings all. I posted this a couple of weeks ago, and did not get any bites, and subsequently it went away anyway, but now the request has come again, and I have not really been working on it, so I am still at the same point. The last time I posted it was also on a Friday, but maybe this time will be better, so I thought I would try to bump it back up. Thank you.

  Greg
  _________________________________________________________________________________________________
  The glass is at one half capacity: nothing more, nothing less.

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  October 3, 2008 at 7:19 am

  #880446

  So many cross joins, so little time!

  ---------- ALTER TABLE TO NORMALIZE DATA ------------------

  ALTER TABLE #ReportFiles

  ADD ReportDate datetime, Company varchar(20)

  UPDATE #ReportFiles

  SET ReportDate = CONVERT(SMALLDATETIME,SUBSTRING(FileName,1,CHARINDEX('_',filename)-1))

  WHERE ReportDate IS NULL

  UPDATE #ReportFiles

  SET Company = SUBSTRING(filename,CHARINDEX('_',filename,1)+1,(CHARINDEX('.',filename,1)-CHARINDEX('_',filename,1))-1)

  WHERE Company IS NULL

  -----------------------------------------------------------

  ---------- DETERMINE WHAT RESULTS SHOULD BE ---------------

  SELECT DISTINCT ReportDate

  INTO #Dates

  FROM #ReportFiles

  WHERE DATENAME(dw,ReportDate) NOT IN ('Saturday','Sunday')

  SELECT DISTINCT Company

  INTO #Companies

  FROM #ReportFiles

  SELECT ReportDate, Company

  INTO #Results

  FROM #Dates CROSS JOIN #Companies

  -----------------------------------------------------------

  --------- FIND ROWS MISSING FROM ORIGINAL DATA ------------

  SELECT DISTINCT R.ReportDate, R.Company

  FROM #Results R LEFT JOIN #ReportFiles RF ON R.ReportDate = RF.ReportDate AND R.Company = RF.Company

  WHERE RF.Company IS NULL

  -----------------------------------------------------------

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• Greg Snidow

  SSCoach

  Points: 16108

  More actions

  October 3, 2008 at 8:03 am

  #880478

  Garadin, your solution is exactly what I need to do. Thank you so much for your quick effort.

  Greg
  _________________________________________________________________________________________________
  The glass is at one half capacity: nothing more, nothing less.

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  October 3, 2008 at 10:13 am

  #880575

  I just realized that my query will fail in the case that there are no text files associated with a particular date. (IE. If all 5 customers are missing their file for that day, the date is never added to the cross join, and therefore never checked.)

  So uh... Oops.

  To circumvent this, I'd use a tally table to generate all weekdays between MIN(ReportDate) and GETDATE(), and use that for the cross join instead.

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]