Dear All,

My one condition is working like if data exist than it should fail only one condition is pending that is "if data exists without qty, than qty should add...

Thanks for everyone. code is working if i use it individually, on the other hand I use temp table in SP it's working too but i need to insert data...

Thanks for your response.

It helps but it's possible for multiple values

;WITH ctevalues (recp_name, ing_name, qty) AS (

select

'pasta pollo' as recp_name, 'paprika' as ing_name, '2 1/2 cup' as qty

--, ('pasta pollo',...

Pls check

create table ing(recp_id int,ing_name_id int,qty text)

This query works. A data set has been created. tnx

select salesrep_id,salesrep_info,customer_name,s2.customer_id,qty_shipped,invoice_date,Month,Year,datediff(MONTH,invoice_date,GETDATE())

,CASE WHEN DATEDIFF(MONTH,invoice_date,GETDATE()) >=2 THEN 'LOST CUSTOMER' END AS Comment

from p21_sales_history_report_view_lost_customer s1

inner join

(select customer_id,max(invoice_date) as MostRecentInvoiceDate from p21_sales_history_report_view_lost_customer

group by customer_id

)...

I wrote a query,

select *,DATEDIFF(MONTH,invoice_date,GETDATE())

,CASE WHEN DATEDIFF(MONTH,invoice_date,GETDATE()) >=2 THEN 'LOST CUSTOMER' END AS Comment

from p21_sales_history_report_view_lost_customer s1

inner join

(select customer_id,max(invoice_date) as MostRecentInvoiceDate from p21_sales_history_report_view_lost_customer

group by customer_id

) S2 on s2.customer_id= s1.customer_id

where salesrep_id='1036'

and DATEDIFF(MONTH,invoice_date,GETDATE())...

Thanks for your response.

as per your query, do I need to create a temp table or it can be created with a normal table?

I created a view.

select * from p21_sales_history_report_view_lost_customer

where

salesrep_id in (@SalesRep)

and columns are

select

customer.salesrep_id,

COALESCE(salesrep_contact.first_name, '')+' '+

COALESCE(salesrep_contact.last_name,'') as salesrep_info,

customer.customer_name,

customer.customer_id,

p21_view_invoice_line.qty_shipped,

invoice_hdr.invoice_date,

DATEPART(mm,invoice_date) as Month,

DATEPART (Year,invoice_hdr.invoice_date) as Year