Last post May 31, 2019 05:55 AM by Yongqing Yu
May 28, 2019 01:00 PM|Gopi.MCA|LINK
This is my Table 1 Sale Data
Result set to get sales duration of each product of last 60 days
May 28, 2019 02:08 PM|deepalgorithm|LINK
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
SELECT TOP 1 Sale_Date FROM SaleData ORDER BY Sale_Date DESC
May 29, 2019 06:21 AM|Yongqing Yu|LINK
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' ,
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'
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)
as 'Next Sale Date'
FROM sala_Table k order by k.ProductName
This is the result of my work demo:
May 30, 2019 11:40 AM|Gopi.MCA|LINK
May 31, 2019 05:55 AM|Yongqing Yu|LINK
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.