Need help reviewing sql query used to create domo sql dataflow

vikrao
vikrao Member
edited March 2023 in SQL DataFlows

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

Tagged:

Answers

  • 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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • vikrao
    vikrao Member

    Thanks for the quick reply @MarkSnodgrass !

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

    Vik

  • vikrao
    vikrao Member

    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.

  • 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?

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • vikrao
    vikrao Member

    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?

  • 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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • vikrao
    vikrao Member

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

  • 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)
    


    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • vikrao
    vikrao Member

    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 
    
  • 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 
    


    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • vikrao
    vikrao Member

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

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

  • 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 
    


    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • vikrao
    vikrao Member

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


    Thanks,

    Vik

  • 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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • 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.


    1. select distinct Customer from(
    2. select Customer, count(distinct SalesOrder) as Purchases from ArTrnDetail where InvoiceDate between '2022-01-01' and '2022-01-31'
    3. and OrderType = 'ST'
    4. and Customer NOT IN ( select Customer from ArTrnDetail where InvoiceDate not between '2022-01-01' and '2022-01-31')
    5. and ProductClass NOT IN('_FRT','_TAX','TAX2')
    6. and LineType <> '4'
    7. and Salesperson IN ('EPI','MGN','PH') 
    8. Group By Customer having count(distinct SalesOrder)=1
    9. ) 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.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"