Dear Members!
I Want to calculate expiry date in sql Server my given Quary work best for only one product but i need for whole table
What I have tried:
BEGIN
DECLARE @TempDate Datetime ,
@ExpiryDate Datetime,
@year int ,
@month int ,
@day int
Set @ExpiryDate = (SELECT [ExpiryDate] from [dbo].[Purchases] where [ProductId] = 1 )
SELECT @TempDate = @ExpiryDate
SELECT @year = DATEDIFF(YEAR,@TempDate ,GETDATE()) -
CASE
WHEN (MONTH(@ExpiryDate) > MONTH(GETDATE())) OR
(MONTH(@ExpiryDate) = MONTH(GETDATE()) AND DAY(@ExpiryDate) > DAY(GETDATE()))
THEN 1 ELSE 0
END
SELECT @TempDate = DATEADD(YEAR , @year ,@TempDate)
SELECT @month = DATEDIFF(MONTH,@TempDate , GETDATE()) -
CASE
WHEN DAY(@ExpiryDate) > DAY(GETDATE())
THEN 1 ELSE 0
END
SELECT @TempDate = DATEADD(MONTH,@month,@TempDate)
SELECT @day = DATEDIFF(DAY ,@TempDate,GETDATE())
SELECT @year * -12 + @month
SELECT p.[ProductName] , s.[Quantity],s.[ExpiryDate],s.[Date],s.Price
FROM [dbo].[Purchases] s
JOIN [dbo].[Product] p ON s.ProductId = p.ProductID
SELECT 'Expire in ' + convert(varchar(10), @year * -1) + ' years '+ convert(varchar(10), @month) + ' months'
END
this work fine but i need
SELECT p.[ProductName] , s.[Quantity],s.[ExpiryDate],s.[Date],s.Price
FROM [dbo].[Purchases] s
JOIN [dbo].[Product] p ON s.ProductId = p.ProductID
like this that give me result of all product of the tables