J'essaye de find la différence de date entre today's date
given date
dans la table. Quelques rangées ont 1 date
tandis que d'autres ont 2 dates
(prennent la date antérieure). J'ai essayé avec la variable dynamic et la table de temp mais toujours ne fonctionnant pas
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 * dans le résultat de (select * parmi # result1 union select * from # result2)
select *from result
La première requête donnera le résultat
Et je veux le même résultat pour la deuxième requête aussi pour deux cas de dates.
Enfin combiner à la fois le résultat dans une table
---------------------------------------------------------------------- -- 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 ie 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
Vous pouvez aussi essayer ceci:
select subssortingng(dbo.TableName.TableColumn, 15, charindex('/',dbo.TableName.TableColumn) - 1) FROM dbo.TableName
alors
select getdate() - cast(subssortingng(dbo.TableName.TableColumn, 15, charindex('/',dbo.TableName.TableColumn) - 1)as datetime) FROM dbo.TableName
Note: Changez le numéro (15) pour pouvoir commencer à partir de l'index que vous voulez