Convert month name to the first day of Month

  


CREATE TABLE test (mnth  VARCHAR(10))
 

INSERT INTO test VALUES ('January'),('February'),('March')
 

   SELECT  FORMAT( CAST(concat( mnth,' 1 ', Year(Getdate()) ) as date), 'dd-MM-yy')
   FROM  test 


drop table test
 

Missing Dates -TSQL Sample

  




create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth) 
select 10, '2007-11-01' Union all
select 10, '2007-10-01' Union all
select 10, '2008-03-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'




;with mycte0 as (
 SELECT ID_No ,min(OccurMonth) dt  
 FROM #InputTable GROUP BY ID_No 
 )

,mycte1 as
(
SELECT ID_No,dt  FROM mycte0  
UNION ALL
SELECT ID_No, Dateadd (month, 1, dt ) FROM mycte1 b
WHERE EXISTS
(SELECT 1 FROM #InputTable it
WHERE it.ID_No = b.ID_No
AND it.OccurMonth > b.dt)
)
 
 
 
 
SELECT m.ID_No, m.dt
FROM   mycte1 m left join  #InputTable it
               ON it.ID_No = m.ID_No
                  AND it.OccurMonth = m.dt
WHERE  it.ID_No IS NULL
 


drop table #InputTable
 

Fill Null Values Beased on Previous Non-Null Value and Asssign Sequence

  


 
DECLARE @TBL TABLE (RowNum INT, DataId int, RowOrder DECIMAL(18,2) NULL)
INSERT INTO @TBL VALUES
(1,    105508,       1.00),
(2,    105717,       NULL),
(3,    105718,       NULL),
(4,    105509,       2.00),
(5,    105510,       3.00),
(6,    105514,       NULL),
(7,    105513,       4.00),
(8,    105719,       NULL),
(9,    105718,       NULL),
(10,105718,   NULL)

;WITH mycte0 AS
(SELECT RowNum ,DataId,RowOrder
   FROM @TBL
  WHERE RowOrder IS NOT NULL )
  ,mycte as (
  Select RowNum, DataId, Coalesce(RowOrder, (SELECT MAX(m.RowOrder)
                FROM mycte0 m
               WHERE m.RowNum <= s.RowNum))  RowOrder
 FROM @TBL AS s)

 ,mycte2 as (
SELECT  RowNum, DataId, RowOrder
 ,RowOrder + Row_number() Over(Partition by RowOrder  Order by  RowNum) / 100.0 newRowOrder

FROM mycte  )

Merge @TBL as t
Using mycte2 as src on t.RowNum = src.RowNum 
When matched then 
Update Set 
RowOrder=src.newRowOrder;

 Select * from @TBL;

--Option 2
DECLARE @TBL2 TABLE (RowNum INT, DataId int, RowOrder DECIMAL(18,2) NULL)
INSERT INTO @TBL2 VALUES
(1,    105508,       1.00),
(2,    105717,       NULL),
(3,    105718,       NULL),
(4,    105509,       2.00),
(5,    105510,       3.00),
(6,    105514,       NULL),
(7,    105513,       4.00),
(8,    105719,       NULL),
(9,    105718,       NULL),
(10,105718,   NULL)
 
 ;with mycte as (
SELECT RowNum,DataId,RowOrder,
CAST(SUBSTRING(MAX( CAST(DataId AS BINARY(4)) + CAST(RowOrder AS BINARY(8)) )
OVER( ORDER BY RowNum ASC ROWS UNBOUNDED PRECEDING ),5,8) AS Decimal(18,2) ) lastNonNullval
 
  FROM @TBL2

   ) 
,mycte2 as (

Select RowNum,DataId,RowOrder
,lastNonNullval+(row_number()Over(Partition by lastNonNullval Order by RowNum )-1)/100.0 as newRowOrder 
From mycte
 )
  Update mycte2 
 set RowOrder=newRowOrder;


 Select * from @TBL2
 

Insert Data Into a Wide Table with COLUMN_SET

A nonwide table in SQL Server can have 1024 columns and a wide table can have 30000 columns with Column_SET and SPARSE column design.
Here is a working sample to demonstrate how to insert data into a wide table.

  

 if object_id('dbo.widetabletest','U') is not null
 drop table widetabletest ;

 CREATE TABLE [dbo].[widetabletest](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[col0] [varchar](50) NULL,
	[c1col1] [varchar](50) SPARSE  NULL,
	[c2col1] [varchar](50) SPARSE  NULL,
	[c3col1] [varchar](50) SPARSE  NULL,
	[c15000col1] [varchar](50) SPARSE  NULL,
	[c15000Xmlcol1] [xml] COLUMN_SET FOR ALL_SPARSE_COLUMNS  NULL)
 
 
 INSERT widetabletest (col0, [c15000Xmlcol1])
VALUES ('row1', '<c1col1>test1</c1col1><c3col1>abc</c3col1><c15000col1>Something1</c15000col1>');
INSERT widetabletest (col0, [c15000Xmlcol1])
VALUES ('row2', '<c1col1>test2</c1col1><c2col1>test2!!!!</c2col1><c15000col1>Something1</c15000col1>');


 --create a format file DECLARE @cmd VARCHAR(4000) 
 DECLARE @cmd NVARCHAR(4000) 
set @cmd ='bcp   [test1].[dbo].[widetabletest] format nul -c -x -f  C:\temp\myFormatFileTest.xml -T  -t\t -S'+ @@servername;
exec master..xp_cmdshell @cmd
 
 Go

---generate a test file
DECLARE @cmd6 NVARCHAR(4000) 
set @cmd6 = 'BCP [test1].[dbo].[widetabletest] OUT "C:\temp\myDataWidetable.txt"   -f  "C:\temp\myFormatFile2.xml" -T  -t\^|  -S'+ @@servername
exec master..xp_cmdshell @cmd6

Go
/*
Use any of the below methods:  
bcp;
Bulk insert ;
Insert with OPENROWSET

*/
 ----bcp in with all data
 DECLARE @cmd8 NVARCHAR(4000) 
set @cmd8 = 'BCP  [test1].[dbo].[widetabletest] IN "C:\temp\myDataWidetable.txt"  -f  "C:\temp\myFormatFile2.xml"  -T -S '+ @@servername
exec master..xp_cmdshell @cmd8
 
go

BULK INSERT [test1].[dbo].[widetabletest] 
   FROM 'C:\temp\myDataWidetable.txt'
    WITH (FORMATFILE = 'C:\temp\myFormatFile2.xml');


--Bulk insert
  INSERT INTO [test1].[dbo].[widetabletest] ([col0],[c15000Xmlcol1])
    SELECT [col0],[c15000Xmlcol1]
      FROM  OPENROWSET(BULK  'C:\temp\myDataWidetable.txt',
      FORMATFILE='C:\temp\myFormatFile2.xml'  
      ) as t1 ;
 

 --Check
 Select * from widetabletest
 

SSRS Report Not Working Correctly with IE11 (Update .net Framwork to NET 4.5.1 )

http://www.msigman.com/ssrs-print-icon-not-appearing-in-internet-explorer-11-not-able-to-print-reports-from-ie11/

http://blogs.msdn.com/b/selvar/archive/2013/12/20/reportviewer-2012-control-not-showing-print-icon-when-viewed-from-ie-11-desktop-mode.aspx

(Solved) IE11 Install Problem: Neutral package installation failed (exit code = 0x00003715 (14101)) and Setup exit code: 0x00009C59 (40025)

I ran into an issue with the installation of IE 11 on Win 7. It took me many hours searching solutions from the internet.
Finally, I got the issue resolved from this link:
http://answers.microsoft.com/en-us/windows/forum/all/i-get-error-9c59-when-updating-internet-explorer/03d4473a-867b-44a5-9a37-913e9a5879cf

I copied the steps from Oz (Open cmd window with administrator right):
”
Step 0. ‘mklink /D c:\Windows\SysNative c:\Windows\system32’ in an elevated Command Prompt as you advised. But I had to do a few more bits for it to work for me.
Step 1. Download a copy of IE11 from Microsoft and Run the IE11 installer and wait until it gets to the ‘Install Internet Explorer 11’ prompt. Do not close this window.
Step 2. Go into ‘C:\Windows\temp’ and look for the most recently created folder with ‘IE’ at the beginning of it’s name. E.g. ‘IE1CF18.tmp’.
Step 3. Go into that folder and verify that you can see the following two .cab files; ‘IE11-neutral.Downloaded.cab’ and ‘IE11-neutral.Extracted.cab’. If you do, then carry onto the next step. If you don’t, then go back a level and try one of the other similarly named folders.
Step 4. Use that folder name to create the path for the .cab files to be installed from. E.g.: C:\Windows\temp\IE1CF18.tmp
Step 5. Now, substituting my example path with the one you determined in the last step, run an elevated Command Prompt and enter the following commands in the order listed:
dism.exe /online /add-package /packagepath:C:\Windows\temp\IE1CF18.tmp\IE11-neutral.Extracted.cab /quiet /norestart
dism.exe /online /add-package /packagepath:C:\Windows\temp\IE1CF18.tmp\IE11-neutral.Downloaded.cab /quiet /norestart
Step 6. When done, return to the ‘Install Internet Explorer 11’ prompt and click ‘Install’. With a bit of luck it should successfully install.

“

Get Start and End Dates for a Month : day 1-14 or day 15 through the end of month (for date range filter)

  



declare @dt datetime=current_timestamp  

,@startdate date
,@enddate date


Set @startdate = Case when day(@dt)>=15 then  dateadd(month,datediff(month,0,@dt),0) else Dateadd(Day,14,dateadd(month,datediff(month,0,@dt)-1,0) )  END

Set @enddate = Case when day(@dt)>=15 then  Dateadd(day,14,dateadd(month,datediff(month,0,@dt),0)) else  dateadd(month,datediff(month,0,@dt),0)  End

print @startdate
print @enddate
 

Add Sequence a Dataset with Datetime Columns

  

 --Create table #MonthProcess  (ID numeric, MonthStartDate  datetime, LastMonthDate  datetime, Month_Code numeric, Process_Code char(2), Remarks char(2))

Create table #MonthProcess  (ID numeric, MonthStartDate  datetime
, LastMonthDate  datetime, Month_Code int, Process_Code char(2), Remarks char(4))

Declare @MonthStartDate datetime = '01/01/2016'
Declare @MonthEnd datetime = '01/01/2017'

Declare @StartID numeric = 80


;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)-1 FROM Num2)

 Insert into #MonthProcess (ID,MonthStartDate,LastMonthDate, Month_Code,Process_Code,Remarks)

Select  n+@StartID ID, dateadd(month,n,@MonthStartDate) MonthStartDate
, EOMONTH(dateadd(month,n,@MonthStartDate)) LastMonthDate
--,Dateadd(day,-1,dateadd(month,n+1,@MonthStartDate))  LastMonthDate
,12 - n%12 as Month_Code
, 'PR' as Process_Code, 'STRD' as Remarks  
  
FROM  Nums
WHERE dateadd(month,n-1,@MonthStartDate)<@MonthEnd


--check data
Select * from #MonthProcess

--clean up
drop table #MonthProcess
 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8287987e-116b-4e3a-a184-4bec8a6b16e4/help-in-increment-query?forum=transactsql