locked
How To Find Last Sale Date v/s Next Sale Date Using SQL Query RRS feed

  • Question

  • User-807418713 posted

    Hello

    This is my Table 1 Sale Data

    Product Name Sale_Date Sale Qty
    AA 1-May-2019 1
    BB 4-May-2019 5
    AA 5-May-2019 10
    AA 11-May-2019 2
    CC 10-May-2019 3
    AA 19-May-2019 6
    CC 25-May-2019 9

    Result set to get sales duration of each product of last 60 days

    Product Name Last Sale Date Next Sale Date Sale Qty No Sales From No Of Days
    AA 1-May-2019 5-May-2019 1 3
    AA 5-May-2019 11-May-2019 10 5
    AA 11-May-2019 19-May-2019 8 7
    AA 15-May-2019 No Sale Till Date 0 14
    BB 4-May-2019 No Sale Till Date 5 24
    CC 10-May-2019 25-May-2019 3 16
    CC 25-May-2019 No Sale Till Date 9 4
    Tuesday, May 28, 2019 1:00 PM

All replies

  • User765422875 posted

    If you are asking how to get the last/max date of your table you can do the following:

    select max(Sale_Date) from SaleData

    or

    SELECT TOP 1 Sale_Date FROM SaleData ORDER BY Sale_Date DESC

    Tuesday, May 28, 2019 2:08 PM
  • User665608656 posted

    Hi Gopi,

    According to your description and the table you provided, I suggest you use sql's case when grammar to query fields on the basis of the original sala_Table, then use case when judgment to define a field named Next Sale Date.

    First, it is judged that when the Last Sale Date of ProductName is the maximum date, the No Sale Till Date is assigned to Next Sale Date field.

    Conversely, when the Id of the ProductName is larger than the current Id, get the minimum date of the Last Sale Date of the productName and assign that date to Next Sale Date field.

    For more details,you could refer to this sql query:

    SELECT k.Id,k.ProductName,k.sale_Date as 'Last Sale Date' ,
    (case 
    when cast(k.Sale_Date as varchar) in (select max(a.Sale_Date) from sala_Table a where a.ProductName = k.ProductName group by a.ProductName)
    then  'No Sale Till Date'
    else 
    cast((select min(a.Sale_Date) from sala_Table a where a.ProductName = k.ProductName and a.Id > k.Id group by a.ProductName) as varchar)
    end )
    as 'Next Sale Date' 	         
    FROM sala_Table k  order by k.ProductName 

    This is the result of my work demo:

    Best Regards,

    YongQing.

    Wednesday, May 29, 2019 6:21 AM
  • User-807418713 posted
    Hi

    Can It be possible to achieve my exact output.

    Thank you
    Thursday, May 30, 2019 11:40 AM
  • User665608656 posted

    Hi Gopi,

    According to your description, I am not quite clear about your specific requirements.

    Do you want to use SQL to query the values of Sale Qty and No Sales From No Of Days, or do you want the date format 2019-05-01 in the results I provided to be written in the date format 1-May-2019 you provided?

    If you want to show the two fields Sale Qty and No Sales From No Of Days,I'm sorry to tell you that I haven't found out what their rules are.

    Could you please tell me the relationship between the values of these two columns and the values in the first table? That will help us to solve this issue more easierly.

    Best Regards,

    YongQing.

    Friday, May 31, 2019 5:55 AM