Post reply

Trying to find the minimum date for 1st fuel and non fuel transaction

  • August 7, 2018 at 1:41 pm

    #356979

    Hi all,

    I have been struggling with this query for a while now. I am trying to bring back A.AccountNumber, MIN(B.SalesDate), A.AccountType, B.CustomerID, B.CustomerInvoiceValueTotalGross, C.IsFuel fields back from 3 different tables, to give me the 1st fuel and and non transaction. I'm expecting to get only one date per account transaction, but keep getting several dates (or the same date) for the same account number.  Here is my query below which is getting me close but not close enough. Any help will be most appreciated.

    SELECT A.Account_Number, MIN(B.Sales_Date), A.Account_Type, B.Customer_ID, B.Customer_InvoiceValueTotal_Gross, C.Is_Fuel
    FROM Table A
    join
    Table B on A.top_level_customerid = B.Customer_ID
    join
    Table C on C.Product_ID = B.Product_ID
    WHERE B.Sales_Date = (SELECT TOP (1) MIN(Sales_Date)
                    FROM Table B
                    WHERE Customer_ID = A.top_level_customerid)
    group by A.Account_Number, A.Account_Type, B.Sales_Date, B.Customer_InvoiceValueTotal_Gross, B.Customer_ID, C.Is_Fuel, C.Product_ID
    order by A.Account_Number

  • August 7, 2018 at 2:35 pm

    #2000710

    You have too many fields in your GROUP BY.  Specifically, you should not have B.Sales_Date.  If that still doesn't help, follow the directions for posting sample data and expected results in the first link in my signature.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 7, 2018 at 2:37 pm

    #2000711

    Hi,

    Can this be of any help?


    SELECT MinDate.Account_Number
         , MinDate.MinimumDate
         , A.Account_Type
         , B.Customer_Id
         , B.Customer_InvoiceValueTotal_Gross
         , C.Is_Fuel
    FROM
    (
    SELECT MIN(B.Sales_Date) MinimumDate, A.Account_Number
    FROM A
         JOIN B ON A.Top_Level_CustomerId = B.Customer_Id
         JOIN C ON B.Product_Id = C.Product_Id
    GROUP BY A.Account_Number
    ) MinDate
    JOIN A ON MinDate.Account_Number = A.Account_Number
    JOIN B ON A.Top_Level_CustomerId = B.Customer_Id
    JOIN C ON B.Product_Id = C.Product_Id

  • August 7, 2018 at 2:45 pm

    #2000712

    debasis.yours - Tuesday, August 7, 2018 2:37 PM

    Hi,

    Can this be of any help?


    SELECT MinDate.Account_Number
         , MinDate.MinimumDate
         , A.Account_Type
         , B.Customer_Id
         , B.Customer_InvoiceValueTotal_Gross
         , C.Is_Fuel
    FROM
    (
    SELECT MIN(B.Sales_Date) MinimumDate, A.Account_Number
    FROM A
         JOIN B ON A.Top_Level_CustomerId = B.Customer_Id
         JOIN C ON B.Product_Id = C.Product_Id
    GROUP BY A.Account_Number
    ) MinDate
    JOIN A ON MinDate.Account_Number = A.Account_Number
    JOIN B ON A.Top_Level_CustomerId = B.Customer_Id
    JOIN C ON B.Product_Id = C.Product_Id

    There should be a way to do this without reading each of the tables twice, but we would need sample data and expected results to be sure.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 7, 2018 at 2:49 pm

    #2000714

    Yes, you are right Drew.

  • August 7, 2018 at 3:31 pm

    #2000715

    Hi all,

    Thanks for the advice, but so far none of the exapmles have worked. Here'ss the sample data below, which you can still brings back multiple transactions per account.

    Account_NumberMinimum_DateAccount_TypeCustomer_IdCustomer_InvoiceValueTotal_GrossIs_Fuel
    AS6013961814/08/2017New14482937.260
    AS6013961814/08/2017New1448290.010
    AS6013961814/08/2017New14482935.410
    AS6013846819/07/2017New14367712.420
    AS6013846819/07/2017New1436779.150
    AS6012928429/09/2016Internal134490118.50
    AS6013846819/07/2017New1436773160
    AS6012928429/09/2016Internal13449040
    AS6013692617/07/2017Internal142133110
    AS6013901826/07/2017New144229340
    AS6013901826/07/2017New144229850
    AS6013901826/07/2017New144229290

    ?xH ?

  • August 7, 2018 at 8:31 pm

    #2000728

    Welcome aboard. 
    What we're asking for is a create table script and some insert statements.... something we can run so we have some data to work with.  Since you're new here, please read this article on Best Practices. - it will show you how to post a question that gets answered. We're happy to help, but we need CREATE TABLE scripts and INSERTS so we have some data to work with.

    Thanks!

  • August 8, 2018 at 7:53 am

    #2000801

    Also, we asked for sample data and expected results.  You've only provided the first half of that.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply