Need help reviewing sql query used to create domo sql dataflow

vikrao · May 2022

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

MarkSnodgrass · May 2022

I think you might be able to do it by doing this:

select Customer, count(distinct LAST_DAY(`InvoiceDate`)) as Purchases 
from `raw_syspro_artrndetail`
where OrderType = 'ST'
and ProductClass NOT IN('_FRT','_TAX','TAX2')
and LineType NOT IN ('4')
and Salesperson IN ('EPI','MGN','PH') 
Group By Customer having count(distinct LAST_DAY(`InvoiceDate`))=1

If their count is one, then they only ordered in one month. If their count is more than one, then they ordered in multiple months.

vikrao · May 2022

Thanks for the quick reply @MarkSnodgrass !

I will try this out and reply to this thread shortly.

Vik

vikrao · May 2022

I got similar results to before. The data is showing about 100-125 customers more than when I pull the same data from mssql.

I am trying to find out why I have more users than mssql showing with this query now.

Maybe something to do with dates when I used Domo's inbuilt date functions? I used domo's inbuilt date functions when I was moving the query to Domo.

MarkSnodgrass · May 2022

I noticed in your MSSQL example, you have some hard coded dates entered and you are using a dynamic date range in your MySQL example. Could that be why you are not seeing the same thing?

vikrao · May 2022

You may be right. I tried to convert those to Dynamic Date ranges when I moved it to Domo since I want the data to automatically update.

Maybe I should create a query with dynamic date in MSSQL to cross check?

MarkSnodgrass · May 2022

Yes, you could use basically the same SQL statement I gave you. The only thing you should need to change would be to swap out the LAST_DAY function for the EOMonth function in MSSQL.

Assuming the source data is the same in both places, you should get the same results by not changing anything else.

vikrao · May 2022

I am using an older version of mssql that does not have EOMonth built into it. Any other way I can check this?

MarkSnodgrass · May 2022

try this, but replace GETDATE() with your actual date field:

SELECT CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) as DATE)

vikrao · May 2022

Something like this then?

select Customer,InvoiceDate, Count (CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,InvoiceDate)+1,0)) as DATE)) as OrderNum
from ArTrnDetail 
where OrderType = 'ST'
and ProductClass NOT IN('_FRT','_TAX','TAX2')
and LineType <> '4'
and Salesperson IN ('EPI','MGN','PH') 
Group By InvoiceDate, Customer having Count (CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,InvoiceDate)+1,0)) as DATE)) = 1

MarkSnodgrass · May 2022

Close. You need the count distinct like this:

select Customer,InvoiceDate, Count(DISTINCT (CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,InvoiceDate)+1,0)) as DATE))) as OrderNum
from ArTrnDetail 
where OrderType = 'ST'
and ProductClass NOT IN('_FRT','_TAX','TAX2')
and LineType <> '4'
and Salesperson IN ('EPI','MGN','PH') 
Group By InvoiceDate, Customer having Count(DISTINCT (CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,InvoiceDate)+1,0)) as DATE))) = 1

vikrao · May 2022

I got a very large number of results from this query.

Do I also need to add a distinct customer to this query?

MarkSnodgrass · May 2022

Sorry, I missed that you had included InvoiceDate in the select statement and group by. That is why you are getting so many records. It should look like this:

select Customer, Count(DISTINCT (CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,InvoiceDate)+1,0)) as DATE))) as OrderNum
from ArTrnDetail 
where OrderType = 'ST'
and ProductClass NOT IN('_FRT','_TAX','TAX2')
and LineType <> '4'
and Salesperson IN ('EPI','MGN','PH') 
Group By Customer having Count(DISTINCT (CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,InvoiceDate)+1,0)) as DATE))) = 1

vikrao · May 2022

Doesnt the invoice date need to be there though? I cannot differentiate between months if that is not there.

Thanks,

Vik

MarkSnodgrass · May 2022

As I understood your question, you wanted to find out which customers ordered in one month and never again. This does that by having the invoice date in the count distinct clause.

jaeW_at_Onyx · May 2022

in the kindest way possible ... if you are having difficulties writing SQL maybe you should consider implementing your dataflow as Magic 2.0 which doesn't require you to code in SQL.

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 query would be trivial in Magic 2.

break your two sub queries into two streams of Data.

Attach the appropriate FILTER tile.

JOIN the two datasets together where you have CUSTOMER NOT IN.

After the JOIN apply your GROUP BY clause and filter on having count_disticnt.

Although honestly I would recommend that you do your aggregation in Analyzer.

Need help reviewing sql query used to create domo sql dataflow

Answers

Categories