Clausula Where em Consulta Formatada

[former_member1039832]

Boa tarde!

Estou com a dificuldade de inserir um comando na Where, para filtrar o max(T0.[DocDate]) 'Última Compra', a ideia seria colocar uma opção para o usuário digitar um intervalo de tempo para consultar, mas apresenta um erro, sempre que eu insiro o max(T0.[DocDate]) na Where.

Poderiam me ajudar?

Segue consulta abaixo:

Select
me.[Código],
me.[Cliente],
me.[Data de Cadastro],
me.[GroupName] 'Grupo',
mo.[Primeira Compra],
mo.[Última Compra],
mo.[Acumulado],
case when me.ativo = 'y' then 'Ativo' else 'Inativo' end 'Status',
mo.[Nº de Pedidos],
isnull(me.Territorio,mo.territorio) 'Territorio',
isnull(me.Vendedor,mo.vendedor) 'Vendedor',
isnull(me.Representante,mo.representante) 'Representante'


from
(
SELECT
T1.[CardName] 'Cliente',
T4.[GroupName],
T1.[Cardcode] 'Código',
T1.[CreateDate] 'Data de Cadastro',
T2.[SlpName] 'Vendedor',
T3.[AgentName] 'Representante',
T1.validfor 'Ativo',
T11.[descript] 'Territorio'


FROM
OCRD T1
left JOIN ORDR T0 ON T0.[CardCode] = T1.[CardCode]
LEFT JOIN OTER T11 ON T1.[Territory] = T11.[territryID]
LEFT JOIN OSLP T2 ON T1.[SlpCode] = T2.[SlpCode]
LEFT JOIN OAGP T3 ON T1.[AgentCode] = T3.[AgentCode]
LEFT JOIN OCRG T4 ON T1.[GroupCode] = T4.[GroupCode]
Where t1.cardtype = 'c'
group by
T1.[Cardname], T1.[Cardcode], T1.[CreateDate], T2.[SlpName],T3.[AgentName], t1.validfor, T11.[descript], T4.[GroupName]
) me
left join
(
SELECT
T1.[CardName] 'Nome',
T1.[Cardcode] 'Código',
T1.[CreateDate] 'Data de Cadastro',
T2.[SlpName] 'Vendedor',
T3.[AgentName] 'Representante',
max(T0.[DocDate]) 'Última Compra',
min(T0.[DocDate]) 'Primeira Compra',
sum(t0.doctotal)'Acumulado',
count(t0.docentry)'Nº de Pedidos',
T11.[descript] 'Territorio'


FROM
ORDR T0 
INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode]
LEFT JOIN OTER T11 ON T1.[Territory] = T11.[territryID]
LEFT JOIN OSLP T2 ON T1.[SlpCode] = T2.[SlpCode]
LEFT JOIN OAGP T3 ON T1.[AgentCode] = T3.[AgentCode]
LEFT JOIN OCRG T4 ON T1.[GroupCode] = T4.[GroupCode]
Where T0.canceled =  'N'
group by
T1.[Cardname], T1.[Cardcode], T1.[CreateDate],T2.[SlpName],T11.[descript],T3.[AgentName], T4.[GroupName]
) mo


on me.[Código] = mo.[Código]


order by 5 desc

[former_member1039832]

Hello, Good Evening! Sorry for the inconvenience, but I tried to run the command below, and displayed the message:

1). [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The multi-part identifier "T0.DocDate" could not be bound. 2). [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement 'Valores definidos pelo usuário' (CSHS) (s) could not be prepared.

Can you help me run this query?

Select
me.[Código],
me.[Cliente],
me.[Data de Cadastro],
me.[GroupName] 'Grupo',
mo.[Primeira Compra],
mo.[Última Compra],
mo.[Acumulado],
case when me.ativo = 'y' then 'Ativo' else 'Inativo' end 'Status',
mo.[Nº de Pedidos],
isnull(me.Territorio,mo.territorio) 'Territorio',
isnull(me.Vendedor,mo.vendedor) 'Vendedor',
isnull(me.Representante,mo.representante) 'Representante'




from
(
SELECT
T1.[CardName] 'Cliente',
T4.[GroupName],
T1.[Cardcode] 'Código',
T1.[CreateDate] 'Data de Cadastro',
T2.[SlpName] 'Vendedor',
T3.[AgentName] 'Representante',
T1.validfor 'Ativo',
T11.[descript] 'Territorio'




FROM
OCRD T1
left JOIN ORDR T0 ON T0.[CardCode] = T1.[CardCode]
LEFT JOIN OTER T11 ON T1.[Territory] = T11.[territryID]
LEFT JOIN OSLP T2 ON T1.[SlpCode] = T2.[SlpCode]
LEFT JOIN OAGP T3 ON T1.[AgentCode] = T3.[AgentCode]
LEFT JOIN OCRG T4 ON T1.[GroupCode] = T4.[GroupCode]
Where t1.cardtype = 'c'
group by
T1.[Cardname], T1.[Cardcode], T1.[CreateDate], T2.[SlpName],T3.[AgentName], t1.validfor, T11.[descript], T4.[GroupName]
) me
left join
(
SELECT
T1.[CardName] 'Nome',
T1.[Cardcode] 'Código',
T1.[CreateDate] 'Data de Cadastro',
T2.[SlpName] 'Vendedor',
T3.[AgentName] 'Representante',
max(T0.[DocDate]) 'Última Compra',
min(T0.[DocDate]) 'Primeira Compra',
sum(t0.doctotal)'Acumulado',
count(t0.docentry)'Nº de Pedidos',
T11.[descript] 'Territorio'




FROM
ORDR T0 
INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode]
LEFT JOIN OTER T11 ON T1.[Territory] = T11.[territryID]
LEFT JOIN OSLP T2 ON T1.[SlpCode] = T2.[SlpCode]
LEFT JOIN OAGP T3 ON T1.[AgentCode] = T3.[AgentCode]
LEFT JOIN OCRG T4 ON T1.[GroupCode] = T4.[GroupCode]


group by
T1.[Cardname], T1.[Cardcode], T1.[CreateDate],T2.[SlpName],T11.[descript],T3.[AgentName], T4.[GroupName], T0.[DocDate]
) mo


on me.[Código] = mo.[Código]


HAVING max(T0.[DocDate]) <= [%1]




order by 5 desc

[Abhishek_Hazra]

Hi Gabriel,

If I understood your problem correctly with help of google translate, you might want to try syntax : 'having max(T0.[DocDate]) = <input>' instead of 'Where max(T0.[DocDate]) = <input>'.

This should work.

Regards,
Abhishek

[former_member1039832]

Hello, Abhishek! I tried to follow the procedure, but a new error message appeared. It follows attached

The code executed was:

Select
me.[Código],
me.[Cliente],
me.[Data de Cadastro],
me.[GroupName] 'Grupo',
mo.[Primeira Compra],
mo.[Última Compra],
mo.[Acumulado],
case when me.ativo = 'y' then 'Ativo' else 'Inativo' end 'Status',
mo.[Nº de Pedidos],
isnull(me.Territorio,mo.territorio) 'Territorio',
isnull(me.Vendedor,mo.vendedor) 'Vendedor',
isnull(me.Representante,mo.representante) 'Representante'


from
(
SELECT
T1.[CardName] 'Cliente',
T4.[GroupName],
T1.[Cardcode] 'Código',
T1.[CreateDate] 'Data de Cadastro',
T2.[SlpName] 'Vendedor',
T3.[AgentName] 'Representante',
T1.validfor 'Ativo',
T11.[descript] 'Territorio'


FROM
OCRD T1
left JOIN ORDR T0 ON T0.[CardCode] = T1.[CardCode]
LEFT JOIN OTER T11 ON T1.[Territory] = T11.[territryID]
LEFT JOIN OSLP T2 ON T1.[SlpCode] = T2.[SlpCode]
LEFT JOIN OAGP T3 ON T1.[AgentCode] = T3.[AgentCode]
LEFT JOIN OCRG T4 ON T1.[GroupCode] = T4.[GroupCode]
Where t1.cardtype = 'c'
group by
T1.[Cardname], T1.[Cardcode], T1.[CreateDate], T2.[SlpName],T3.[AgentName], t1.validfor, T11.[descript], T4.[GroupName]
) me
left join
(
SELECT
T1.[CardName] 'Nome',
T1.[Cardcode] 'Código',
T1.[CreateDate] 'Data de Cadastro',
T2.[SlpName] 'Vendedor',
T3.[AgentName] 'Representante',
max(T0.[DocDate]) 'Última Compra',
min(T0.[DocDate]) 'Primeira Compra',
sum(t0.doctotal)'Acumulado',
count(t0.docentry)'Nº de Pedidos',
T11.[descript] 'Territorio'


FROM
ORDR T0 
INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode]
LEFT JOIN OTER T11 ON T1.[Territory] = T11.[territryID]
LEFT JOIN OSLP T2 ON T1.[SlpCode] = T2.[SlpCode]
LEFT JOIN OAGP T3 ON T1.[AgentCode] = T3.[AgentCode]
LEFT JOIN OCRG T4 ON T1.[GroupCode] = T4.[GroupCode]


HAVING max(T0.[DocDate]) <= [%1]


group by
T1.[Cardname], T1.[Cardcode], T1.[CreateDate],T2.[SlpName],T11.[descript],T3.[AgentName], T4.[GroupName], T0.[DocDate]
) mo


on me.[Código] = mo.[Código]


order by 5 desc