CREATE VIEW dbo.KARGOLAR_GONDERIM_LISTESI
AS
SELECT 
CONCAT(CONCAT(CONCAT(CONCAT(CASE 
WHEN LEN(CAST(CLCREF AS VARCHAR))=1 THEN CONCAT('000',CAST(CLCREF AS VARCHAR)) 
WHEN LEN(CAST(CLCREF AS VARCHAR))=2 THEN CONCAT('00',CAST(CLCREF AS VARCHAR)) 
WHEN LEN(CAST(CLCREF AS VARCHAR))=3 THEN CONCAT('0',CAST(CLCREF AS VARCHAR)) 
ELSE CAST(CLCREF AS VARCHAR) END, CASE 
WHEN LEN(CAST(DATEPART(ms,CAST(GETDATE() AS TIME)) AS VARCHAR))=1 THEN CONCAT('00',CAST(DATEPART(ms,CAST(GETDATE() AS TIME)) AS VARCHAR)) 
WHEN LEN(CAST(DATEPART(ms,CAST(GETDATE() AS TIME)) AS VARCHAR))=2 THEN CONCAT('0',CAST(DATEPART(ms,CAST(GETDATE() AS TIME)) AS VARCHAR))
ELSE CAST(DATEPART(ms,CAST(GETDATE() AS TIME)) AS VARCHAR) END), CASE 
WHEN DATEDIFF(DAY,CAST(DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AS DATE),CAST(GETDATE() AS DATE))<10 THEN CONCAT('00',DATEDIFF(DAY,CAST(DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AS DATE),CAST(GETDATE() AS DATE)))
WHEN DATEDIFF(DAY,CAST(DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AS DATE),CAST(GETDATE() AS DATE))<100 THEN CONCAT('0',DATEDIFF(DAY,CAST(DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AS DATE),CAST(GETDATE() AS DATE)))
ELSE DATEDIFF(DAY,CAST(DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AS DATE),CAST(GETDATE() AS DATE)) END),
DATEDIFF(YEAR, '2023-01-01',CAST(GETDATE() AS DATE))), CASE 
WHEN ABS(DATEDIFF(MINUTE, DATEADD(HOUR, 8, CAST(CAST(GETDATE() AS DATE) AS DATETIME)), GETDATE()))<10 THEN CONCAT('00',ABS(DATEDIFF(MINUTE, DATEADD(HOUR, 8, CAST(CAST(GETDATE() AS DATE) AS DATETIME)), GETDATE())))
WHEN ABS(DATEDIFF(MINUTE, DATEADD(HOUR, 8, CAST(CAST(GETDATE() AS DATE) AS DATETIME)), GETDATE()))<100 THEN CONCAT('0',ABS(DATEDIFF(MINUTE, DATEADD(HOUR, 8, CAST(CAST(GETDATE() AS DATE) AS DATETIME)), GETDATE())))
ELSE ABS(DATEDIFF(MINUTE, DATEADD(HOUR, 8, CAST(CAST(GETDATE() AS DATE) AS DATETIME)), GETDATE())) END) AS ENT,
DEFINITION_, ADRES, CITY, TOWN, TELNRS1 FROM dbo.DEPO_FIS_KARGO_ISLEMLERI