Customized Round with T-SQL

 
--if roundup (1.43)  to 1.00
--if roundup (1.44)  to 2.00

DECLARE @VALUE1 AS DECIMAL(6,2) = 1.43
DECLARE @VALUE2 AS DECIMAL(6,2) = 1.44


SELECT   Round(@VALUE1+0.06,0)
SELECT   Round(@VALUE2+0.06,0)


 

Split and Combine Values (T-SQL)

 
 create table TB1 (ID int, Name varchar(10))
Insert TB1 values(1,'Prakash'),(2,'Durga')
create table TB2 (ID int, TB1_ID varchar(10))
Insert TB2 values(1,'1,2')
 

 
 
;with mycte as (
select ID, TB1_ID,  substring(TB1_ID, n, charindex(',', TB1_ID + ',', n) - n)  splitID
from TB2
cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)) d(n) 
Where n <= len(TB1_ID) AND substring(',' + TB1_ID, n, 1) = ',' )


,mycte1 
as (Select m.ID,m.TB1_ID,t.Name from mycte m join TB1 t on m.splitID=t.ID)


SELECT t1.ID,t1.TB1_ID,
       Stuff(( SELECT ',' + Cast(t2.Name as varchar(5))
           FROM mycte1 t2
          WHERE t2.ID = t1.ID  
          ORDER BY id
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS Names
  FROM mycte1 t1
 GROUP BY t1.ID,t1.TB1_ID;



 

drop table TB1,TB2
 

Pick up a good practice: Best Practice: Naming Constraints

http://blogs.msdn.com/b/dtjones/archive/2009/10/29/best-practice-naming-constraints.aspx

Instead of using the System Named Constraint, it is good practice to use Programmer Named Constraint.

CREATE TABLE dbo.testdt ( testdt datetime CONSTRAINT DF_defaultDT DEFAULT (getdate()) ) 


 

 --Script from SSMS

 CREATE TABLE [dbo].[testdt](
	[testdt] [datetime] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[testdt] ADD  CONSTRAINT [DF_defaultDT]  DEFAULT (getdate()) FOR [testdt]
GO



 

Aggregate Data By Week in Each Month

 
  create table test (SolvedID int, Type varchar(10), ResolutinDate datetime)
 Insert into test values
 --(40110,'OMS01','2014-05-01 13:28:00.000'),
 -- (40111,'OMS01','2014-05-02 13:28:00.000'),
 --  (40112,'OMS01','2014-05-03 13:28:00.000'),
 --     (40113,'OMS01','2014-05-04 13:28:00.000'),
	--     (40114,'OMS01','2014-05-05 13:28:00.000'),
 (40123,'OMS01','2014-05-29 13:28:00.000'),
 (40124,'OMS02','2014-05-29 13:28:00.000'),
 (40125,'OMS03','2014-05-27 13:28:00.000') 
  --,(40135,'OMS03','2014-05-30 13:28:00.000') ,
  -- (40136,'OMS03','2014-05-31 13:28:00.000') 


 SELECT 'Week'+Cast(DATEPART(WEEK, ResolutinDate)  -
    DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM,0,ResolutinDate), 0))+1 as char(1))+','+Datename(MM,ResolutinDate) as [Week],
	Count(*) as TotalResolved from test
 Group by 'Week'+Cast(DATEPART(WEEK, ResolutinDate)  -
    DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM,0,ResolutinDate), 0))+1 as char(1))+','+Datename(MM,ResolutinDate)


 drop table test

 
 

Bulk Insert Text Files with Powershell

Import-Module -Name 'SQLPS' -DisableNameChecking   
 
$workdir="C:\temp\test\"
$svrname = "MC\MySQL2014" 
 
 Try
{
 
#Change default timeout time from 600 to unlimited
$svr =  new-object ('Microsoft.SqlServer.Management.Smo.Server') $svrname 
$svr.ConnectionContext.StatementTimeout = 0


$table="test1.dbo.myRegions"
 
#remove the filename column in  the target table
$q1 = @"
Use test1;
IF COL_LENGTH('dbo.myRegions','filename') IS NOT NULL
BEGIN
ALTER TABLE test1.dbo.myRegions DROP COLUMN filename;
END
"@
Invoke-Sqlcmd -ServerInstance $svr.Name  -Database  master -Query  $q1



$dt = (get-date).ToString("yyyMMdd") 
$formatfilename="$($table)_$($dt).xml"
$destination_formatfilename ="$($workdir)$($formatfilename)"
 
$cmdformatfile="bcp $table format nul -c -x -f $($destination_formatfilename) -T  -t\t  -S $($svrname) "
Invoke-Expression $cmdformatfile

#Delay 1 second
Start-Sleep -s 1


$q2 = @"
Alter table test1.dbo.myRegions  Add filename varchar(500) Null;
"@
#add the filename column to the target table
Invoke-Sqlcmd -ServerInstance $svr.Name  -Database  master -Query  $q2 



$files = Get-ChildItem $workdir
$items = $files | Where-Object {$_.Extension -eq ".txt"} 
  
for ($i=0; $i -lt $items.Count; $i++) {
 
$strFileName = $items[$i].Name
$strFileNameNoExtension= $items[$i].BaseName
 
$query = @"
BULK INSERT test1.dbo.myRegions from '$($workdir)$($strFileName)' WITH (FIELDTERMINATOR = '\t', FIRSTROW = 2, FORMATFILE =  '$($destination_formatfilename)');
"@ 
 
Invoke-Sqlcmd -ServerInstance $svr.Name  -Database master  -Query  $query  -querytimeout 65534

#Delay 10 second
Start-Sleep -s 10

# Update the filename column
Invoke-Sqlcmd -ServerInstance $svr.Name  -Database master  -querytimeout 65534  -Query  "Update test1.dbo.myRegions SET filename= '$($strFileName)' WHERE filename is null; "

# Move uploaded file to archive
If ((Test-Path "$($workdir)$($strFileName)") -eq $True) { Move-Item -Path  "$($workdir)$($strFileName)" -Destination  "$($workdir)Processed\$($strFileNameNoExtension)_$($dt).txt"}

 }
  }
Catch [Exception]
 {
 write-host "--$strFileName "$_.Exception.Message
 }