Clean Double Quoted Text to Another Table

  

   create table testtablewithOneColumn (col0 varchar(4000))
Insert into testtablewithOneColumn values('"Some data        ","3800&","827","         ","         ","       ","51345","R","S","          10.00006" ')
,('"         ","3800","827","         ","         ","       ","51345","R","S","          10.00008" ')
,('"row3         ","3800","827","         ","         ","       ","51345","R","S","          10.00009" ')

CREATE TABLE testtablewithTargetColumns (
SalaryCode VARCHAR(50)
,SalaryID VARCHAR(50)
,Department VARCHAR(50)
,Region VARCHAR(50)
,Area VARCHAR(50)
,theMisingCol VARCHAR(50)
,TypeCode VARCHAR(50)
,ShortCode VARCHAR(50)
,ManagerCode VARCHAR(50)
,Units VARCHAR(50)) 

DECLARE @cmd1 NVARCHAR(4000) 
set @cmd1 = 'BCP [mydb1].[dbo].testtablewithOneColumn OUT  "C:\temp\myTableData.txt" -c -r \"\n  -T -S'+ @@servername
exec master..xp_cmdshell @cmd1, no_output 
-- Export to a text file

BULK INSERT  mydb1.[dbo].testtablewithTargetColumns
  FROM 'C:\temp\myTableData.txt'
  WITH
     (
        FIELDTERMINATOR ='","',
        ROWTERMINATOR ='" "\n',
        FirstRow=1,
		DATAFILETYPE = 'char'
     );

---remove the first  double quote
Update  mydb1.[dbo].testtablewithTargetColumns
SET
SalaryCode=Replace(SalaryCode,'"', '')  

  --Check the target table
select SalaryCode
	 , SalaryID, Department, Region, Area, theMisingCol, TypeCode, ShortCode, ManagerCode, Units
 from  mydb1.[dbo].testtablewithTargetColumns

 --clean up
 drop table testtablewithOneColumn,testtablewithTargetColumns
https://social.msdn.microsoft.com/Forums/en-US/6ca6eb19-b73d-4289-8b5a-f2287c8d330f/remove-the-double-quotes?forum=transactsql

 

Format Date with st,nd,rd or th and a way to remove them with TSQL

  

create table test (dt datetime)
--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

Insert into test (dt) 

Select dateadd(day, n, dt)  dt from (
select getdate() as dt )t Cross apply (select n from nums ) d(n)

-- select * from test

SELECT 'Date formated with st,nd,rd or th ----- ' col1,  DATENAME(month, dt) + ' ' + DATENAME(day, dt) + 
	CASE 
		WHEN DATENAME(day, dt) IN ('1', '21', '31') THEN 'st'
		WHEN DATENAME(day, dt) IN ('2', '22') THEN 'nd'
		WHEN DATENAME(day, dt) IN ('3', '23') THEN 'rd'
		ELSE 'th'
	END
	+ ' ' + DATENAME(Year, dt)   dtFormatedwith_st_nd_rd_th

Into testdtFormatedwith_st_nd_rd_th
From test

--Select * from testdtFormatedwith_st_nd_rd_th

--Convert back to date
 Select  dtFormatedwith_st_nd_rd_th
 , try_Convert(datetime, Replace(Replace(Replace(Replace(dtFormatedwith_st_nd_rd_th,'1st','1'),'nd',''),'rd',''),'th','') ) dt_2
 --,Convert(datetime, Replace(Replace(Replace(Replace(dtFormatedwith_st_nd_rd_th,'1st','1'),'nd',''),'rd',''),'th','') )
 from testdtFormatedwith_st_nd_rd_th

drop table testdtFormatedwith_st_nd_rd_th
drop table test

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e389d2d4-63de-4b87-a223-c835674c09ca/convert-datetime?forum=transactsql
 

Mark Last Five Fiscal Years in a Column

  

CREATE TABLE dbo.financial_years (id int identity(1,1),	start_of_fin_year date,	end_of_fin_year date);

-- Load sample financial_years from 2008 to 2017
	INSERT INTO dbo.financial_years (start_of_fin_year, end_of_fin_year)
	SELECT	DATEADD(YEAR, -1*n , start_of_fin_year),	DATEADD(YEAR, -1*n , end_of_fin_year)
	from (select '2018-04-01' as start_of_fin_year, '2019-03-31' as end_of_fin_year) t
			Cross apply(values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) d(n)
--get beginningofCurrentFY
;with mycte as (
select
Case when Month(getdate())>3 then Dateadd(month,3,dateadd(Year, datediff(year,0,getdate()),0))
else Dateadd(month,3,dateadd(Year, datediff(year,0,getdate())-1,0)) end beginningofCurrentFY
)

-- declare @today date ='2016-4-2'
-- ;with mycte as (select
--Case when Month( @today)>3 then Dateadd(month,3,dateadd(Year, datediff(year,0, @today),0))
--else Dateadd(month,3,dateadd(Year, datediff(year,0, @today)-1,0)) end beginningofCurrentFY)

select start_of_fin_year, end_of_fin_year ,
case when  start_of_fin_year<=beginningofCurrentFY
and  start_of_fin_year< (dateadd(Year,-5,beginningofCurrentFY) )
then 'Yes' else 'No' End    from financial_years
cross apply (Select beginningofCurrentFY from mycte) d(beginningofCurrentFY)

drop table financial_years

----https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3d640159-5247-4323-8cf9-63141571ca8e/dynamically-i-want-to-get-last-5-financial-years?forum=transactsql
 

Sort Order with Multiple Levels Using T-SQL

  

  CREATE TABLE [dbo].[test](
	[Lev1] [char](1) NULL,
	[LeV2] [char](2) NULL,
	[Lev3] [int] NULL,
	[Price] [int] NULL
)

INSERT [dbo].[test] ([Lev1], [LeV2], [Lev3], [Price]) VALUES  (N'A', NULL, NULL, 80)
,(N'A', N'1 ', NULL, 30)
,(N'A', N'1 ', 1234, 10)
,(N'A', N'1 ', 1235, 5)
,(N'A', N'1 ', 1236, 15)
,(N'A', N'2 ', NULL, 50)
,(N'A', N'2 ', 1237, 20)
,(N'A', N'2 ', 1238, 5)
,(N'A', N'2 ', 1239, 25)
,(N'B', NULL, NULL, 90)
,(N'B', N'1 ', NULL, 30)
,(N'B', N'1 ', 1240, 30)
,(N'B', N'2 ', NULL, 60)
,(N'B', N'2 ', 1242, 10)
,(N'B', N'2 ', 1243, 50)

;with mycte as (
SELECT[Lev1], [LeV2], [Lev3],[Price]
	  ,dense_rank() Over( Order by [Lev1] desc) rn1
	  ,dense_rank() Over( Order by [Lev1] desc,Case when Lev2 is null then 9999 else Lev2 end  desc) rn2
	  ,dense_rank() Over( Order by [Lev1] desc,Lev2 desc,Case when Lev3 is null then 9999 else Lev3+Price end desc) rn3
  FROM  [dbo].[test]
  )

SELECT [Lev1], [LeV2], [Lev3],[Price]
--, rn1, rn2, rn3
FROM   mycte
ORDER  BY rn1, rn2, rn3 

---https://social.msdn.microsoft.com/Forums/en-US/13b0c16a-1543-4d3c-9fea-60acebc31eff/sql-order-by-query-accross-multi-level-groups?forum=transactsql
 /*
 Lev1	LeV2	Lev3	Price
B	NULL	NULL	90
B	2 	NULL	60
B	2 	1243	50
B	2 	1242	10
B	1 	NULL	30
B	1 	1240	30
A	NULL	NULL	80
A	2 	NULL	50
A	2 	1239	25
A	2 	1237	20
A	2 	1238	5
A	1 	NULL	30
A	1 	1236	15
A	1 	1234	10
A	1 	1235	5
 */

--Another solution
select Lev1,Lev2, Lev3, Price from test
Order by Cast(Lev1 as binary(8)) desc
, Cast(Case when Lev2 is null then 9999 else Lev2 end as binary(8)) desc
, Cast(Case when Lev3 is null then 9999 else Lev3 + Price end as binary(8)) desc

drop table test

Read Last 7 Days Erro Log with xp_ReadErrorLog

declare @dt1 datetime=getdate()-7
declare @dt2 datetime=getdate()

EXEC xp_ReadErrorLog 0, 1, N’error’, N”,@dt1,@dt2

—-Or Failed Login
–EXEC xp_ReadErrorLog 0, 1, “Failed”, “Login”,@dt1,@dt2

—-****** the syntax with double quotes “Failed”

(Script above was  on SQL Server 2014)

System Function msdb.dbo.agent_datetimeMSDB.dbo.sysjobhistory Table and Run_duration Column

 
In the msdb.dbo.sysjobhistory table, the run_duration column is INT type and stored as (H)HHMMSS format from right to left.
If HH is under 24 hours, we can get the during in time format HH:MM:SS by using a system function. For example:
select msdb.dbo.agent_datetime(19000101, 231353) will return 23:13:53 9 23 hours 13 minutes and 53 seconds.

In very long running job, if the hours go over 24 hours, all the numbers in HHH positions are whole number of hours.

We need to tweak a little to make it work:

  
 
  
 SELECT j.NAME AS 'Job',
       run_date,
       run_time,
       msdb.dbo.Agent_datetime(run_date, run_time) [Run_Datetime] 
,Case when run_duration>24*10000 then 
Cast(run_duration%240000/10000 as varchar(3)) +' (day) '
+ Cast(run_duration/240000 as varchar(2))  
+':' + LefT(Right(Cast(run_duration as varchar(8)),4),2)
+':'+ Right(Cast(run_duration as varchar(8)),2) +''
else
Format(msdb.dbo.agent_datetime(19000101,  run_duration) ,'HH:mm:ss')
end [Duriation in HH:MM:SS]
FROM   msdb.dbo.sysjobs j
       INNER JOIN msdb.dbo.sysjobhistory h
               ON j.job_id = h.job_id
WHERE  j.enabled = 1
ORDER  BY run_duration DESC 

 

Using DISTINCT to Generate Combined Column List with Different Order by Column

  
 
  

 Create table Prods (prc_id int, prc_NAME varchar(10), prc_idgrp int)
Insert into Prods values(1,'D',25),(2,'A',25),(3,'C',25),(4,'B',25)
 


 declare @xlist varchar(1000)
 

;with mycte as (
Select distinct prc_ID, prc_NAME from PRODS
)

 SELECT @xlist=Stuff(( SELECT ',' + quotename(Cast(prc_ID as varchar(5)),']')
  FROM mycte
 Order by prc_NAME            
 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')   
 

 

Print @xlist
 drop table Prods
 

 

Running Total with Reset for 1 after a 0

  

/*
create table test (id1 int, id2 int,month int,YEAR int,  Occurrence int)
 insert into test values (500,7,1,2015,1),(500,7,2,2015,1),(500,7,3,2015,1)
,(500,7,4,2015,1),(500,7,5,2015,1),(500,7,6,2015,1)
,(500,7,7,2015,1),(500,7,8,2015,0),(500,7,9,2015,1)
,(500,7,10,2015,0),(500,7,11,2015,0)

*/
create table test (id1 int, id2 int,month int,YEAR int,  Occurrence int)

 insert into test values(123,5,1,2011,1),(123,5,2,2011,1)
 ,(123,5,3,2011,1),(123,5,4,2011,1),(123,5,5,2011,0)
 ,(123,5,6,2011,0),(123,5,7,2011,0),(123,5,8,2011,0)
 ,(123,5,9,2011,0),(123,5,10,2011,0),(123,5,11,2011,0)
 ,(232,4,5,2011,0),(232,4,6,2011,0),(232,4,7,2011,1)
 ,(232,4,8,2011,1),(458,2,2,2011,0),(458,2,3,2011,0)
 ,(458,2,4,2011,0),(458,2,5,2011,1),(458,2,6,2011,1)
 ,(458,2,7,2011,1),(458,2,8,2011,0),(458,2,9,2011,0)
 ,(458,2,10,2011,1),(458,2,11,2011,1),(569,1,1,2010,0)
 ,(569,1,2,2010,0),(569,1,3,2010,0),(569,1,4,2010,1)
 ,(569,1,5,2010,1),(569,1,6,2010,1),(569,1,7,2010,1)
 ,(569,1,8,2010,1),(569,1,9,2010,1),(569,1,10,2010,1)
 ,(569,1,11,2010,0),(569,1,12,2010,0),(569,1,1,2011,0)
 ,(569,1,2,2011,0),(569,1,3,2011,1),(569,1,4,2011,1)
 ,(569,1,5,2011,1),(569,1,6,2011,1)

;with mycte as (
select id1,id2, year, month, occurrence
,row_number()Over( Partition by id1 Order by year, month) rn
from test
)

,mycte1 as
(select id1, id2, year, month, rn,occurrence, occurrence as expectedOutput from mycte
 where rn=1 

union all
select m1.id1, m1.id2, m.year,m.month, m1.rn+1 rn
 ,m.occurrence, case when  m.occurrence=0 then m1.expectedOutput else m.occurrence+m1.expectedOutput*m1.occurrence end
from mycte1 m1 join mycte m on m.rn=m1.rn+1 and m.id1=m1.id1)

Select id1, id2, year, month, occurrence,  expectedOutput from mycte1
order by id1,rn
option (maxrecursion 0)

Drop table test



---Another solution

;with mycte as
(

select id1,id2,month, year,Occurrence,
row_number()Over(Partition by id1 Order by year, month) - row_number()Over(Partition by id1,Occurrence Order by year, month) grp
from test

)
,mycte1 as (
Select id1,id2,month, year,Occurrence ,grp
, Nullif(Sum(Occurrence) Over(Partition by ID1,grp Order by Year, Month),0) expectedOccurrence
from mycte

)

select id1, id2, month, year, Occurrence,
ISNULL(CAST(SUBSTRING(MAX(CAST(year AS BINARY(4)) + CAST(month AS BINARY(4)) + CAST(expectedOccurrence AS BINARY(4)) )
OVER(Partition by ID1 ORDER BY year,month ROWS UNBOUNDED PRECEDING ),9,4) AS int ) ,0) as expectedOccurrence

from mycte1
order by id1,year, month

 

https://social.msdn.microsoft.com/Forums/en-US/2a28042a-e886-420d-bda3-3d0e5c14859c/count-occurances-without-a-break-using-sql?forum=transactsql

Split Two Comma Delimited Columns with T-SQL

  

 create table test (Name varchar(20), value varchar(20) )
insert into test values('A,B,C','1,2,3')

--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,NameCte as (
select    substring(Name, n, charindex(',', Name + ',', n) - n)  splitName
from test
cross apply Nums
Where n <= len(Name) AND substring(',' + Name, n, 1) = ','

)
,ValueCTE as 
(
select      substring(value, n, charindex(',', value + ',', n) - n)  splitVal
from test
cross apply Nums
Where n <= len(Name) AND substring(',' + Name, n, 1) = ','
)

Select splitName+splitVal from NameCte,ValueCTE

drop table test