Date Difference Finder

Question

I am trying to finding date difference between today's date and given date in the table.Some rows has 1 date while others has 2 dates (take earlier date). I tried with dynamic variable and temp table but still not working

    declare @t nvarchar(1000)
    set @t = ' select a.Availability from [ScrappedData_Regina] a'
    if(len(@t) < = 55  )  -- for single date case
    begin
    SELECT * INTO #result1
     FROM 
     ( 
    select distinct [Product Name],[SKU],DATEDIFF(day,cast(right([Availability],10) as date),cast(GETDATE() as date)) as [Delivery Date] 
    from [ScrappedData_Regina]        
    where [Availability] like '%none%'     -- total 158 products

    )               
    end     
else                        -- for two date case
    begin 
    SELECT * INTO #result2
     FROM 
     ( 

      declare @t date
      set @t = '
      select SUBSTRING(Availability, PATINDEX('% [0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%', [Availability]), 11) as [Date Part] from [ScrappedData_Regina] '

    -- extracts date part from a column

      select  distinct [Product Name],[SKU],DATEDIFF(day,cast(@t as date),cast(GETDATE() as date)) as [Delivery Date]  from ScrappedData_Regina
    )  -- i want to pass the date part in this select statement

select *into result from ( select * from #result1 union select * from #result2 )

select *from result

The first query will give result as

And I want the same result for second query too for two dates case.

Finally combine both the result in a table

Answer 1

You can also try this:

select substring(dbo.TableName.TableColumn, 15, charindex('/',dbo.TableName.TableColumn) - 1) FROM dbo.TableName

Then

select getdate() - cast(substring(dbo.TableName.TableColumn, 15, charindex('/',dbo.TableName.TableColumn) - 1)as datetime) FROM dbo.TableName

Note : Change the (15) number so that It can start from the index you want it to start from

Answer 2

    ----------------------------------------------------------------------
--  Calculating Date Difference between today's date and given date -- 
-----------------------------------------------------------------------

---- Total Products : 1505 ----


    IF OBJECT_ID('#RESULT') IS NOT NULL
    DROP TABLE #RESULT
    GO



     SELECT *INTO #RESULT
         FROM
         (

    select  distinct sku,category,subcategory,[Product Name],summary ,DATEDIFF(day,cast(right([Availability],10) as date),cast(GETDATE() as date)) as [Delivery Date] 
    from ScrappedData_Regina       
    where [Availability] like '%none%'  AND DATEDIFF(day,cast(right([Availability],10) as date),cast(GETDATE() as date)) < 0    -- gives only delivery date that has to be delievered in future i.e in minus
                                                                                                                                -- total 74 Products
   union

    select  distinct sku,category,subcategory,[Product Name],summary,'' as [Delivery Date] from ScrappedData_Regina where  summary like '%in stock%'

   union

   select  distinct sku,category,subcategory,[Product Name],summary,'' as [Delivery Date] from ScrappedData_Regina where  summary like '%call for availability%'

       ) a

       select * from #RESULT


 select * into input_scrapregina
 from
 (

 SELECT *, StockInformation = case 
                        when (summary = 'in stock') then 'Any Stock'
                        when (summary = 'call for availability') then 'Call for Availability'
                        when (a.[Delivery Date] < -1 and a.[Delivery Date] >= -30) then 'Out of Stock expected within 30 days'
                        when (a.[Delivery Date] <= -30 and a.[Delivery Date] >= -60) then 'Out of Stock expected within 60 days' 
                        when (a.[Delivery Date] <= 60 and a.[Delivery Date] >= -90) then 'Out of Stock expected within 90 days' 
                        when (a.[Delivery Date] <= 60 and a.[Delivery Date] >= -90) then 'Out of Stock,not expected' 
                        else 'Call for Availability' end from #RESULT a 
)c


---------------------------------
---------  Final Result --------- 
---------------------------------

---- Total Products : 1505 ----

select *from input_scrapregina