SQL - Day of the week issue on select

Question

I have a problem with a query.... I have a this query:

declare @today int
set @today=DATEPART(dw,GETDATE())-2
select cast (cfv.value as VARCHAR), cfv.companyId
from CompanyFieldvalues cfv
join CompanyFields cf on cf.companyFieldId=cfv.companyFieldId
where cf.name='NextDeliveryDates' and cfv.companyId in(
select cfv.companyId
from CompanyFieldvalues cfv
join Companies c on c.companyId=cfv.companyId
where cfv.value='Retailer' and c.status=1)
/*and cfv.value like '%' + cast (@today as VARCHAR) + '%' */

This give me as result a table like that: Unique Account of a company, Delivery Days

CM001 | 2,4,1
CD04 | 3,3,4
CS7 | 2
CR001 | 4
FG076 | 3,3,5,4 JUH768 | 2,2,2
HG006 | 2
KG040 | 3,2,5

In a few words I'm just saving in @today the value of the actual day of the week (-2 because the system that use this DB manage the days in a different way) and then I just select the the company information and the delivery days from two different tables.

My problem is that I need select just the company that have as last delivery day today.... so if today is day 2 I can have company with last delivery day 1,2 - 0,2 - 0,1,2 etc...

If you see in my code there is the last line commented, if you add this line you get this other result:

CM001 | 2,4,1
CS7 | 2
JUH768 | 2,2,2
HG006 | 2
KG040 | 3,2,5

But in this way, as you can see, I select different companies that don't have as last delivery day the current day.

So I calculate a dynamic table that contain all the future date:

declare @day int
set @day=DATEPART(dw,GETDATE())-1
declare @week int
set @week=7
declare @extra table
(extraday varchar)
while (@day<@week)
begin
insert into @extra (extraday) values (@day)
set @day=@day+1
end

This give me this result: Days of the week future than the current one

3 4 5 6

I try to make different join, difference, like but I don't get to have just the companies with last delivery day as today.

Do you know how can I fix it? or if you have another idea of how I can do please let me know.

Thanks so much, Carlo

Answer 1

It looks like you are trying to implement conditional logic within your WHERE clause, but you are going about it incorrectly. You'll need to either break the statements up or use dynamic string building to create the query and execute it. It should look something like this. Depending on your validation routines for @today, you may want add some protection to guard against SQL injection.

declare @today int
set @today=DATEPART(dw,GETDATE())-2-2
print @today

declare @nsql nvarchar(max)

set @nsql=N'
select 
    cast (cfv.value as VARCHAR)
from 
         CompanyFieldvalues cfv
    join CompanyFields cf on cf.companyFieldId=cfv.companyFieldId
where 
        cf.name=''NextDeliveryDates'' 
    and cfv.companyId in
    (
        select cfv.companyId
        from 
                 CompanyFieldvalues cfv
            join Companies c on c.companyId=cfv.companyId
        where 
            cfv.value=''Retailer'' and c.status=1
    )
    and (  cfv.value like ''%,''' + cast(@today as VARCHAR)+' 
        or cfv.value like ''%''' + cast(@today as VARCHAR)  


if (@today != 0 or @today!=1)
set @nsql=@nsql+N'
        and ((cfv.value not like ''%,0''  or cfv.value not like ''%,1''))'

print @nsql
--exec sp_executesql @nsql