Export-csv with Tab Delimiter and Remove Double Quotes

  
  
 
 
 
$file='C:\temp\theOutFile.txt'
get-service | export-csv -delimiter "`t" -path $file -notype 
(Get-Content $file) | Foreach-Object {$_ -replace '"', ''}|Out-File $file

notepad $file

 
 
 

 

Check all values in a group (Relational division)

  
  
 
 create table #t1(memid varchar(100),eventcode varchar(100))

insert into #t1 values('mem1','1stdate')
insert into #t1 values('mem1','2ndate')
insert into #t1 values('mem1','3rddate')
insert into #t1 values('mem2','1stdate')
insert into #t1 values('mem2','2ndate')
insert into #t1 values('mem2','3rddate')
insert into #t1 values('mem3','1stdate')
insert into #t1 values('mem3','2ndate')
insert into #t1 values('mem4','3rddate')



--option 1
select memid from #t1 WHERE eventcode='1stdate'
intersect
select memid from #t1 WHERE eventcode='2ndate'
intersect
select memid from #t1 WHERE eventcode='3rddate'


--option 2
select distinct memid from #t1 s 
WHERE Exists (select 1 from   #t1 t WHERE t.eventcode='1stdate' and s.memid=t.memid)
and Exists (select 1 from   #t1 t WHERE t.eventcode='2ndate' and s.memid=t.memid)
and Exists (select 1 from   #t1 t WHERE t.eventcode='3rddate' and s.memid=t.memid)
 

--Option 3
;with mycte as (
Select * 
,SUm(
Case 
when eventcode='1stdate' then POWER(2*1,1) --1stdate
when eventcode='2ndate' then POWER(2*1,2) --2ndate
when eventcode='3rddate' then POWER(2*1,3) --3rddate
--when eventcode='4thdate' then POWER(2*1,4) --4thdate
End) Over(Partition by memid)  bitSUM  
from #t1
)


Select memid,eventcode from mycte
WHERE (2 & bitSUM = 2 ) --1stdate
and  (4 & bitSUM = 4) --2ndate
AND (8 & bitSUM = 8) --3rddate
--AND (16 & bitSUM = 16) --4thdate








---Option 4
--Celko's relational division
--https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

--Celko solution 1
SELECT DISTINCT memid
  FROM #t1 AS t1 
  WHERE NOT EXISTS
       (SELECT *
          FROM  (Select distinct eventcode from #t1 ) t
         WHERE NOT EXISTS
               (SELECT *
                  FROM  #t1 AS t2
                 WHERE (t1.memid = t2.memid)
                   AND (t2.eventcode = t.eventcode)));

 --Celko solution 2
SELECT t1.memid
   FROM #t1 AS t1, (Select distinct eventcode from #t1 ) AS t2
  WHERE t1.eventcode = t2.eventcode
  GROUP BY t1.memid 
  HAVING COUNT(t1.eventcode) = (SELECT COUNT(eventcode) FROM  (Select distinct eventcode from #t1 ) AS t );

--Celko solution 3
SELECT t1.memid
  FROM #t1 AS t1
LEFT OUTER JOIN (Select distinct eventcode from #t1 ) AS t2
       ON t1.eventcode = t2.eventcode 
  GROUP BY t1.memid
HAVING COUNT(t1.eventcode) = (SELECT COUNT(eventcode) FROM (Select distinct eventcode from #t1) t)
   AND COUNT(t2.eventcode) = (SELECT COUNT(eventcode)FROM (Select distinct eventcode from #t1) t);

   
--Celko solution 4

;with mycte as (
Select distinct eventcode from #t1
)

SELECT PS1.memid 
  FROM  #t1 AS PS1    
  LEFT OUTER JOIN     mycte AS H1   ON PS1.eventcode = H1.eventcode
GROUP BY PS1.memid 
Having    COUNT(PS1.eventcode) = (SELECT COUNT(eventcode) FROM mycte)
            AND COUNT(H1.eventcode) = (SELECT COUNT(eventcode) FROM mycte) ;



--SELECT PS1.memid,
--CASE WHEN COUNT(PS1.eventcode) > 
--                              (SELECT COUNT(eventcode) FROM mycte )
--                              AND COUNT(H1.eventcode) = (SELECT COUNT(eventcode)FROM mycte )
--            THEN 'more than all'
--            WHEN COUNT(PS1.eventcode) = (SELECT COUNT(eventcode) FROM mycte)
--                 AND COUNT(H1.eventcode) 
--                     = (SELECT COUNT(eventcode) FROM mycte)
--            THEN 'exactly all '
--            WHEN MIN(H1.eventcode) IS NULL
--            THEN 'none'
--            ELSE 'some' END AS skill_level
--  FROM  #t1 AS PS1    LEFT OUTER JOIN     mycte AS H1   ON PS1.eventcode = H1.eventcode
--  GROUP BY PS1.memid;


drop table #t1

 

https://social.msdn.microsoft.com/Forums/en-US/e676bccf-9fa8-479d-ae5f-d48a39b1ee0a/need-select-if-all-my-values-are-present?forum=transactsql


Find Date Gap Between Start and End

  
  
  --SQL Server 2012 or above
  create table SampleData(ID   int,   start_dt  datetime, end_dt datetime)  
insert into SampleData values
(123,'01-Aug-14','01-Aug-14')
,(123,'01-Aug-14','01-Aug-14')
,(345,'01-Aug-14 ','01-Aug-14')  
,(456,'01-Aug-14 ','02-Aug-14')
,(456,'02-Aug-14','02-Aug-14') 
,(456,'03-Aug-14','03-Aug-14') 
,(456,'03-Aug-14','13-Aug-14') 
,(456,'15-Aug-14','23-Aug-14') 
 
 

;WITH mycte AS
(
	SELECT *
		, CASE 
			WHEN DATEDIFF(day, LAG(end_dt) OVER (PARTITION BY ID ORDER BY start_dt), start_dt) <= 1 THEN 0
			ELSE 1
		END AS [flag]
	FROM SampleData
),
mycte1 AS 
(
	SELECT *, SUM([flag]) OVER (PARTITION BY ID ORDER BY start_dt) AS grp
	FROM mycte
)
SELECT ID, MIN(start_dt) AS start_dt, MAX(end_dt) AS end_dt
FROM mycte1
GROUP BY ID, grp
Order by ID, start_dt

 
 


drop table SampleData

 

Use SFTP to send file with public/private keys along passphrase

WinSCP is a great open source tool to use sftp protocol to send files.
Besides you can use user name and password, you can use public key and private key pair to authenticate to the site.
With newer version (>=5.6), you can automate an agent job to run an SSIS package with Script Task to include passphrase used to generate the key pair.
The following code snippet is an example:

string logname = “E:\\temp\\log\\” + DateTime.Now.ToString(“yyyyMMddHHmmssfff”) + “thislog.xml”;
//// Run hidden WinSCP process
Process winscp = new Process();
winscp.StartInfo.FileName = “c:\\Program Files (x86)\\WinSCP\\winscp.com”;

winscp.StartInfo.Arguments = “/log=\”” + logname + “\””;
winscp.StartInfo.UseShellExecute = false;
winscp.StartInfo.RedirectStandardInput = true;
winscp.StartInfo.RedirectStandardOutput = true;
winscp.StartInfo.CreateNoWindow = true;
winscp.Start();

//// Feed in the scripting commands

winscp.StandardInput.WriteLine(“option batch abort”);
winscp.StandardInput.WriteLine(“option confirm off”);

winscp.StandardInput.WriteLine(“open theuser@155.111.222.101 -privatekey=E:\\keys\\mykeys\\myprivate.ppk -passphrase=MySecretPhrase “);
winscp.StandardInput.WriteLine(“put E:\\output\\exportfile.csv”);

winscp.StandardInput.Close();

string output = winscp.StandardOutput.ReadToEnd();

// Wait until WinSCP finishes
winscp.WaitForExit();
Dts.TaskResult = (int)ScriptResults.Success;