0

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

Pang
  • 9,564
  • 146
  • 81
  • 122
Carlo 1585
  • 1,455
  • 1
  • 22
  • 40
  • Carlo, take the last line and get rid of the last wild card, like this - `and cfv.value like '%' + cast (@today as VARCHAR)`. This ensures you are looking for a string pattern xxx2, instead of xx2xx as you were before. You could even be more specific and so this - `and cfv.value like '%,' + cast (@today as VARCHAR)` adding in a comma after the first wild card. – Steve Mangiameli May 11 '16 at 19:31
  • Hi Steve, Thanks so much for the tips, it could help but my problem is that the number are not every time in order, some time you can find 1,4,2. In this case the day 2 is in the last position in the DB but it's not the last delivery day... some more tips pls? :D – Carlo 1585 May 11 '16 at 19:52
  • Well you'd have to define how you are determining last delivery day in your string. If you have control over that, force it to a determinable position and always look there for it. If you give me a moving target and don't give me a way to predict where it's going to be, you can't expect me to hit it. – Steve Mangiameli May 11 '16 at 20:09
  • yes I know for this reason I have all this problem, the things is that a c# code manage this data based on another field so this are dynamic and can change :S for this reason I was thinking to create a dynamic table with all the future day and make a kind of join or like but logically I can't make a "like" multiple to multiple and I don't know if there is some other solution :( – Carlo 1585 May 11 '16 at 20:42
  • Hi Steve, after some investigation I found the structure of data and how they should be; they use to follow this order: 2,3,4,5,6,0,1. The problem is that the 0 and 1 are at the end and not at the beginning :S some idea please? – Carlo 1585 May 12 '16 at 09:04

3 Answers3

0

Seeing that the data structure is this one: 2,3,4,5,6,0,1, I found a partially solution in this way:

   declare @today int
set @today=DATEPART(dw,GETDATE())-2-2
print @today
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 the day end with the current day it's the last one; but I still have to exception: example: 4,5,0 2,1 etc....

to solve that I tough to do an IF but I receive an error message, someone know how I can do it please?

declare @today int
set @today=DATEPART(dw,GETDATE())-2-2
print @today
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))  and (
        if (@today != 0 or @today!=1)
            (cfv.value not like '%,0'  or cfv.value not like '%,1')
    )

This is the error:

Msg 156, Level 15, State 1, Line 14 Incorrect syntax near the keyword 'if'. Msg 102, Level 15, State 1, Line 15 Incorrect syntax near 'cfv'.

Carlo 1585
  • 1,455
  • 1
  • 22
  • 40
0

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
Steve Mangiameli
  • 688
  • 7
  • 15
0

SOLUTION (maybe not the best but it's working; if it's not let me know pls :S but I tested and looks working):

declare @today int
set @today=DATEPART(dw,GETDATE())-2 /*-2 because the date are managed from a c# code so I need in this way to have the day in the format Monday=0, etc*/
declare @case as CHAR (5)
if (@today=0)(select @case='zero')
if (@today=1)(select @case='one')
if (@today>1)(select @case='other')
select cfv.value, 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
 CASE
    WHEN ((@case='other') AND (cfv.value like '%,' + cast (@today as VARCHAR) or cfv.value like '%' + cast    (@today as VARCHAR) 
    or cfv.value like '%' + cast (@today as VARCHAR)+',0'
    or cfv.value like '%' + cast (@today as VARCHAR)+',0,1'
    or cfv.value like '%' + cast (@today as VARCHAR)+',1'))
    then 1
    WHEN ((@case ='zero') AND(cfv.value='0')) THEN 1
    WHEN ((@case ='one') AND(cfv.value='1' or cfv.value='0,1')) THEN 1
    ELSE 0
END = 1

Thanks so much for the help, your tips helped me a lot ;)

Carlo 1585
  • 1,455
  • 1
  • 22
  • 40