Hello,
I am working on moving one of our SQL Queries to Domo. This query looks for users who have made a purchase in a month and no purchases in any month before or after. I was able to move this query but I am getting slightly different results. Since dates are used differently in both queries I wonder if I made a mistake here.
This is the query I use in MSSQL
select distinct Customer from(
select Customer, count(distinct SalesOrder) as Purchases from ArTrnDetail where InvoiceDate between '2022-01-01' and '2022-01-31'
and OrderType = 'ST'
and Customer NOT IN ( select Customer from ArTrnDetail where InvoiceDate not between '2022-01-01' and '2022-01-31')
and ProductClass NOT IN('_FRT','_TAX','TAX2')
and LineType <> '4'
and Salesperson IN ('EPI','MGN','PH')
Group By Customer having count(distinct SalesOrder)=1
) b
This is the query I used to create the sql dataflow for the same query. I use Select * for the output dataset
select `InvoiceDate`, `Customer` from(
select `InvoiceDate`, Customer, count(distinct SalesOrder) as Purchases from `raw_syspro_artrndetail` where InvoiceDate between DATE_SUB(`InvoiceDate`, INTERVAL (DAYOFMONTH(`InvoiceDate`) - 1) DAY) and LAST_DAY(`InvoiceDate`)
and OrderType = 'ST'
and Customer NOT IN ( select Customer from `raw_syspro_artrndetail` where InvoiceDate not between DATE_SUB(`InvoiceDate`, INTERVAL (DAYOFMONTH(`InvoiceDate`) - 1) DAY) and LAST_DAY(`InvoiceDate`))
and ProductClass NOT IN('_FRT','_TAX','TAX2')
and LineType NOT IN ('4')
and Salesperson IN ('EPI','MGN','PH')
Group By Customer having count(distinct SalesOrder)=1
) b
Any help is appreciated.
Thanks!
Vik