sql

[fmardani]

HI,
There is a folder on the network that has about 300 subfolders. These subfolders are dates going back to last year.
There is supposed to be one file (date) per day except sundays.
I have run a program which inserted these dates into a table.
So now I have the name of these subfolders (dates) in a table.
The question is, how can I find out which days are missing from this list except sundays.
Remember, there should be a file per day.
Thanks

 

[TheJon]

I would use a counter to reel off dates from the minimum date in your table to the maximum. Set datefirst to 7 (which is the default for US installations) which makes datepart return 1 for a sunday.

In this example, myFolders has a column called foldername which is a datetime type. I hope your data is easly converted to datetime type, if not, please post an example of what you have.

create table myFolders(foldername datetime)

insert into myFolders values ('2004-7-16') -- friday
insert into myFolders values ('2004-7-17') -- saturday
insert into myFolders values ('2004-7-20') -- tuesday


set datefirst 7

declare @myDate datetime
declare @maxDate dateTime

select 
  @myDate = min(foldername),
  @maxDate = max(foldername) 
from 
  myfolders

while datediff(d, @mydate, @maxDate) > 0 -- up to final day
begin
  if datepart(dw, @mydate) <> 1 -- not sunday
  begin
    if not exists(select * from myFolders where datediff(d, @myDate, foldername) = 0)
    begin
      print @myDate
    end
  end
  set @myDate = dateadd(d, 1, @myDate)
end

drop table myfolders

Returns this printed text:

Jul 19 2004 12:00AM



[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]

 

[fmardani]

Hi, How can I exclude mondays as well please? thanks

 

[fmardani]

got it. it is:
and (datepart(dw, @minDate) <> 2)

Thanks

 

[TheJon]

Yes, or:

 datepart not in (1,2)

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]