Merge Same Structure Text Files with Powershell


$dir = "C:\test\myfolder"
$outFile = Join-Path $dir "FileCombined.txt" 
$fileList = Get-ChildItem -Path $dir\* -Include *.txt -Exclude FileCombined.txt -File
 Get-Content $fileList[0] | select -First 2 | Out-File -FilePath $outfile -Encoding ascii
 
 foreach ($file in $filelist)
{
    Get-Content $file | Select-Object -Skip 2 | Out-File -FilePath $outfile -Encoding ascii -Append
}




--Copy and rename file with appended timestamp

gci -Path 'c:\temp\test\src\' | % {copy $_.FullName "c:\temp\test\dest\$($_.BaseName+$(Get-Date -f yyy-MM-dd-hh-mm)+$_.Extension)"}  
 

http://powershell.com/cs/forums/p/20268/43748.aspx#43748


Split QueryString Parameter Value Pair and Assign to Variables with T-SQL XML


--

declare @test table (id int, Col  varchar(2000))
Insert into @test values
(1, 'teststring1=testdata1;teststring2=testdata2;teststring3=testdata3;')


DECLARE @teststring1 as VARCHAR(10)=null
DECLARE @teststring2 as VARCHAR(10)=null
DECLARE @teststring3 as VARCHAR(10)=null
DECLARE @teststring4 as VARCHAR(500)=null
DECLARE @teststring5 as VARCHAR(500)=null

;With mycte as (
SELECT id, Col,  Cast (N'<H><r ' + Replace(Replace(Replace(Col, ';','" '),'=','="')  + '" /></H>' ,'" "','"' ) AS XML) AS [vals]
FROM   @test
)
,mycte1 as (
SELECT id, ROW_NUMBER() OVER (ORDER BY S.a.value('count(.)', 'tinyint')) rn,
S.a.value('@teststring1', 'varchar(50)') as teststring1, 
S.a.value('@teststring2', 'varchar(50)') as teststring2,
S.a.value('@teststring3', 'varchar(50)') as teststring3, 
S.a.value('@teststring4', 'varchar(50)') as teststring4,
S.a.value('@teststring5', 'varchar(50)') as teststring5  
   
FROM mycte d CROSS APPLY d.[vals].nodes('/H/r') S(a)  ) 
 --select * from mycte1
SELECT  
@teststring1=teststring1,
@teststring2=teststring2,
@teststring3=teststring3,
@teststring4=teststring4,
@teststring5=teststring5

from mycte1 WHERE id =1

Print '@teststring1 value ---' + @teststring1;
Print '@teststring2 value ---' + @teststring2;
Print '@teststring3 value ---' + @teststring3;
Print '@teststring4 value ---' + @teststring4;
Print '@teststring5 value ---' + @teststring5;



 

Compute 10! using Recursive WITH clause


-- 
; with refc as (

select 1 as n, 1 as running_factorial
union all
select r.n+1, running_factorial*b.num from refc r 
inner join  (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) b(num)  on r.n+1=b.num )

 
select n, running_factorial from refc


--******RunningProduct

SELECT n, ROUND(EXP(SUM(LOG(n)) OVER  (ORDER BY n)), 0)  as runningProduct
FROM (Values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) d(n)

--*******This one is clower
 

Custom Date and Time formats — a sample and link


select datename(WEEKDAY, current_timestamp) WeekdayName
--SQL Server 2012 or 2014
,FORMAT(current_timestamp,'dddd') AS WeekdayName1
,FORMAT(current_timestamp,'ddd') AS ShortWeekdayName2

, FORMAT(getdate(), 'dd-MMM-yy hh.mm.ss.fffffff tt')

,format(getdate(),'HHmmssfff')

http://msdn.microsoft.com/en-us/library/ee634398.aspx

http://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx


Nested Ranking function to Get an Answer


create table #Data
(
  Date date,
  Balance money
)

insert into #Data values('2014-01-01', 1000)
insert into #Data values('2014-01-02', 1000)
insert into #Data values('2014-01-03', 2000)
insert into #Data values('2014-01-04', 2000)
insert into #Data values('2014-01-05', 1000)
insert into #Data values('2014-01-06', 2000)
insert into #Data values('2014-01-07', 500)


select Date, Balance,RN, dense_rank() over (partition by RNS order by date) Seq, RNS 
from ( select *,  row_number() over (partition by Balance, rn2 order by date) RNS 
from ( select *,  row_number() over ( order by date) RN
 , row_number() over ( order by date) - row_number() over (partition by  balance order by date) rn2  
 from #Data) t1   ) t2
Order by [Date]

 

drop table #Data

/*
Date	Balance	RN	Seq	RNS
2014-01-01	1000.00	1	1	1
2014-01-02	1000.00	2	1	2
2014-01-03	2000.00	3	2	1
2014-01-04	2000.00	4	2	2
2014-01-05	1000.00	5	3	1
2014-01-06	2000.00	6	4	1
2014-01-07	500.00	7	5	1
*/


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b39cc65a-a83b-4302-8288-a0ceaa0f637b/detecting-change-in-values-of-a-column?forum=transactsql


Mark a Date as the Start or End of Week, Month, Quarter and Year


create table MyDates(MyDate  date, MyFlag  Varchar(10) )
insert MyDates(MyDate) Values
('2014-01-01'),   -- ys
('2014-01-31'),   -- me
('2014-04-01'),   -- ms, qs
('2014-03-31'),   -- me, qe
('2014-01-06'),   -- ws
('2014-01-12'),   -- we
('2014-12-31'),  -- ye
('2014-01-04');  -- nothing special, so NULL


SELECT MyDate, Reverse(Stuff(Reverse(
 Case WHen Datepart(weekday, MyDate)=2 Then'ws,' 
 WHEN Datepart(weekday, MyDate)=1 Then'we,' Else'' End
  + 
 CASE When Datediff(day,DATEADD(mm, DATEDIFF(mm, 0, MyDate) , 0), MyDate )=0 Then'ms,' 
 When Datediff(day,DATEADD(mm, DATEDIFF(mm, 0, MyDate)+1 , 0), cast(MyDate as datetime)+1 )=0 Then'me,'
 Else'' End
 + 
 CASE When Datediff(day,DATEADD(Q, DATEDIFF(Q, 0, MyDate) , 0), MyDate )=0 Then'qs,' 
 When Datediff(day,DATEADD(Q, DATEDIFF(Q, 0, MyDate)+1 , 0), cast(MyDate as datetime)+1 )=0 Then'qe,' Else'' End 
   +
 Case When Datediff(day, DATEADD(yy, DATEDIFF(yy,0,MyDate),0), MyDate )=0 Then'ys,'
  When Datediff(day, DATEADD(yy, DATEDIFF(yy,0,MyDate)+1,0),  cast(MyDate as datetime)+1 )=0 Then'ye,' Else'' End  
 ),1,1,'')) as MyFlag 
  
FROM  MyDates
 
Drop Table MyDates;


 



 

Create a Table Daily From Another Table with Date Appended to Table Name


DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @TableName AS NVARCHAR(500)='dbo.test1'+ convert(varchar(10),getdate(),112)
DECLARE @SQLQuery2 AS NVARCHAR(500)
SET @SQLQuery2 ='IF OBJECT_ID ('''+@TableName+ ''',N''U'') IS NOT NULL drop table ' +@TableName

EXEC (@SQLQuery2 )


SET @SQLQuery = 'SELECT * into ' + @TableName + ' FROM  dbo.test1' 

EXEC(@SQLQuery)

EXEC ('select * from '+ @TableName )