Import Excel with Openrowset
Posted: July 29, 2015 Filed under: Uncategorized Leave a comment
if object_id('[aNewTable20150729]') IS NULL
Begin
CREATE TABLE [dbo].[aNewTable20150729](
[accountnumber] [nvarchar](255) NULL,
[COl1] [nvarchar](2550) NULL
)
End
Insert into aNewTable20150729
SELECT * FROM OPENROWSET('MSDASQL','DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);
DBQ=c:\temp\test8.xlsx','SELECT * FROM [Sheet1$]')
select *, len(COL1) from aNewTable20150729
truncate table aNewTable20150729
Import and export between Excel file and SQL Server table by using Powershell script
Posted: July 29, 2015 Filed under: Uncategorized Leave a commentSome sample codes from Simple Talk website to show how to import and export between Excel file and SQL Server table.
Here is the link:
Dense_Rank with Gaps and Islands Problem–Another Solution
Posted: July 29, 2015 Filed under: Uncategorized Leave a commentCREATE TABLE #CustomerOffer ( id INT, qid INT, Referralid INT, Offerid INT, OfferAmount INT) INSERT INTO #CustomerOffer VALUES ( 1,421,513452,101,10000) INSERT INTO #CustomerOffer VALUES ( 2,421,513452,102,10001) INSERT INTO #CustomerOffer VALUES ( 3,421,513452,103,10002) INSERT INTO #CustomerOffer VALUES ( 4,421,513452,104,10003) INSERT INTO #CustomerOffer VALUES ( 5,421,513452,105,10004) INSERT INTO #CustomerOffer VALUES ( 6,421,5290373,3234,10010) INSERT INTO #CustomerOffer VALUES ( 7,421,5290373,3284,100011) INSERT INTO #CustomerOffer VALUES ( 8,421,5290373,3923,100022) INSERT INTO #CustomerOffer VALUES ( 9,422,513454,1111,100101) INSERT INTO #CustomerOffer VALUES ( 10,422,513454,1112,1000111) INSERT INTO #CustomerOffer VALUES ( 11,422,513454,1113,1000222) select Id, qid,ReferralId, OfferId, OfferAmount, dense_rank() over(order by Cast(qid as binary(4))+ Cast(ReferralId as binary(4))) as ReferralRnk ,ROW_NUMBER() over (partition by qid, referralId order by OfferId) as PgmNbr From #CustomerOffer order by Id drop table #CustomerOffer
Run Large SQL script for multiple Inserts with Sqlcmd
Posted: July 24, 2015 Filed under: Uncategorized Leave a commentYou can use sqlcmd utility to run T-SQL statements or sql scripts. When you have difficulty to run large sql statements in SSMS, you can try to run the script through a command window or Powershell window.
The sqlcmd Utility’s official documentation page:
https://msdn.microsoft.com/en-us/library/ms162773.aspx?f=255&MSPPError=-2147217396
The file size of datascript.sql is about 100M and it has multiple rows of insert command generated through Visual Studio Data Comparison result.
Some of the Inserts content in my SQL script file have the value $, so I need to use the disable variable substitution flag -x to disable the variable swap.
The final script looks like this:
sqlcmd -S MC\MSSQL2014 -i "C:\temp\datascript.sql" -x -o "C:\temp\outputfile.txt"
The script can be run through Powershell or cmd window.
An easy way to run this script is from Visual Studio to use teh Update Target command, but I need a script to share with other developers for these inserts.
Week start for the past 5 weeks with custom start date (Monday)
Posted: July 13, 2015 Filed under: Uncategorized Leave a commentSET DATEFIRST 1 declare @enddate date=getdate() declare @startdate date=DATEADD(week, DATEDIFF(week,0,dateadd(week,-5,@enddate)),-1) ;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) ,dates as ( Select datepart(week, dateadd(day,n-1,@startdate) ) wk, dateadd(day,n-1,@startdate) dt from Nums WHERE dateadd(day,n-2,@startdate)< @enddate ) Select wk, Max(Case when datename(dw,dt)='Monday' Then dt End) weekstart, Max(Case when datename(dw,dt)='Sunday' Then dt End) weekend from dates Group by wk Having( Max(Case when datename(dw,dt)='Monday' Then dt End) is not null and Max(Case when datename(dw,dt)='Sunday' Then dt End) is not null) SET DATEFIRST 7
Find out unique counts based on consecutive days
Posted: July 10, 2015 Filed under: Uncategorized Leave a commentDeclare @Test TABLE (id int,[Date] date) INSERT INTO @Test Values (100,'07/01/2015') ,(100,'07/02/2015') ,(100,'07/03/2015') ,(100,'08/01/2015') ,(100,'08/02/2015') ,(100,'08/15/2015') ;with mycte as ( select DATEADD(DAY, - ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [Date]) ,[Date]) grp from @Test) Select Count(Distinct grp) cnt from mycte --3
NTILE with a variable sample
Posted: July 10, 2015 Filed under: Uncategorized Leave a comment
-- Create Sample Table
Declare @GroupRelation_Test TABLE (
MD_Group [varchar](20),
CustomerNumber [varchar](20) ,
AmountBilled [varchar](20) ,
MinAmounttBilled [varchar](20)
)
-- Test data
INSERT INTO @GroupRelation_Test
( CustomerNumber, AmountBilled,MinAmounttBilled)
SELECT '12', '15243' ,'' UNION ALL
SELECT '1231234', '15243' ,'' UNION ALL
SELECT '463', '15243' ,'' UNION ALL
SELECT '442', '15243' ,'' UNION ALL
SELECT '4324', '15243' ,'' UNION ALL
SELECT '3123', '15243' ,'' UNION ALL
SELECT '67123', '15243' ,'' UNION ALL
SELECT '109988', '15243' ,'' UNION ALL
SELECT '74665', '15243' ,'' UNION ALL
SELECT '23323', '15243' ,'' UNION ALL
SELECT '9866', '15243' ,''
Declare @intForNtile int
Set @intForNtile = Ceiling((Select count(*) from @GroupRelation_Test)/ 3.0)
--Make 3 as decimal
select ntile(@intForNtile) Over(Order by newid()) MD_Group
, * from @GroupRelation_Test
Running Total With Group based on business logic (T-sql sample)
Posted: July 7, 2015 Filed under: Uncategorized Leave a comment
declare @t table
(
ID int,
Patid char(10),
DrugID char(10),
DateVal char(10),
NextDate char(10),
Duration char(10),
qty char(10),
--FinishDate datetime,
Remaining int
)
insert @t
values
--ob ID PatId DrugID DateVal NextDate Duration qty Remaining cB lq
( 1 ,'Pat2', 'Drug1', '2012-10-25', '2013-02-28', '126 ', '120', -6 ) ,
( 2 ,'Pat2', 'Drug1', '2013-02-28', '2013-06-20', '112 ', '112', 0 ) ,
( 3 ,'Pat2', 'Drug1', '2013-06-20', '2013-10-08', '110 ', '120', 10 ) ,
( 4 ,'Pat2', 'Drug1', '2013-10-08', '2014-01-13', '97 ', '120 ', 23 ) ,
( 5 ,'Pat2', 'Drug1', '2014-01-13', '2014-01-28', '15 ', NULL, -15 ) ,
( 6 ,'Pat2', 'Drug1', '2014-01-28', '2014-02-03', '6 ', '120 ', 114 ) ,
( 7 ,'Pat2', 'Drug1', '2014-02-03', '2014-02-07', '4 ', '14 ', 10 ) ,
( 8 ,'Pat2', 'Drug1', '2014-02-07', '2014-08-12', '186 ', NULL, -186 ) ,
( 9 ,'Pat2', 'Drug1', '2014-08-12', '2014-12-02', '112 ', '120 ', 8 ) ,
( 10 ,'Pat2', 'Drug1', '2014-12-02', '2015-03-22', '110 ', '120 ', 10 ) ,
( 11 ,'Pat2', 'Drug1', '2015-03-22', '2015-03-26', '4 ', '14 ', 10 ) ,
( 12 ,'Pat2', 'Drug1', '2015-03-26', '2015-04-17', '22 ', '14 ', -8 ) ,
( 13 ,'Pat2', 'Drug1', '2015-04-17', NULL, NULL, '120 ', 120 ) ,
( 1 ,'Pat2', 'Drug2', '2011-02-02', '2011-02-04', '2 ', '1 ', -1 ) ,
( 2 ,'Pat2', 'Drug2', '2011-02-04', '2011-06-20', '136 ', NULL, -136 ) ,
( 3 ,'Pat2', 'Drug2', '2011-06-20', '2012-11-19', '518 ', NULL, -518 ) ,
( 4 ,'Pat2', 'Drug2', '2012-11-19', '2012-11-27', '8 ', '7 ', -1 ) ,
( 5 ,'Pat2', 'Drug2', '2012-11-27', '2014-01-10', '409 ', '90 ', -319 ) ,
( 6 ,'Pat2', 'Drug2', '2014-01-10', '2014-01-11', '1 ', '14 ', 13 ) ,
( 7 ,'Pat2', 'Drug2', '2014-01-11', '2014-02-03', '23 ', '14 ', -9 ) ,
( 8 ,'Pat2', 'Drug2', '2014-02-03', '2014-02-07', '4 ', '14 ', 10 ) ,
( 9 ,'Pat2', 'Drug2', '2014-02-07', '2015-03-21', '407 ', NULL, -407 ) ,
( 10 ,'Pat2', 'Drug2', '2015-03-21', '2015-03-22', '1 ', '14 ', 13 ) ,
( 11 ,'Pat2', 'Drug2', '2015-03-22', '2015-03-26', '4 ', '14 ', 10 ) ,
( 12 ,'Pat2', 'Drug2', '2015-03-26', NULL, NULL, '14 ', 14 ) ,
( 1 ,'Pat1', 'Drug3', '2012-06-27', '2012-08-08', '42 ', '60 ', 18 ) ,
( 2 ,'Pat1', 'Drug3', '2012-08-08', '2012-08-29', '21 ', NULL, -21 ) ,
( 3 ,'Pat1', 'Drug3', '2012-08-29', '2012-09-24', '26 ', '30 ', 4 ) ,
( 4 ,'Pat1', 'Drug3', '2012-09-24', '2012-12-02', '69 ', '60 ', -9 ) ,
( 5 ,'Pat1', 'Drug3', '2012-12-02', '2012-12-19', '17 ', '30 ', 13 ) ,
( 6 ,'Pat1', 'Drug3', '2012-12-19', '2013-01-30', '42 ', '120 ', 78 ) ,
( 7 ,'Pat1', 'Drug3', '2013-01-30', '2013-02-27', '28 ', NULL, -28 ) ,
( 8 ,'Pat1', 'Drug3', '2013-02-27', '2013-04-24', '56 ', '30 ', -26 ) ,
( 9 ,'Pat1', 'Drug3', '2013-04-24', '2013-08-14', '112 ', '90 ', -22 ) ,
( 10 ,'Pat1', 'Drug3', '2013-08-14', '2014-01-23', '162 ', '120 ', -42 ) ,
( 11 ,'Pat1', 'Drug3', '2014-01-23', '2014-04-02', '69 ', '120 ', 51 ) ,
( 12 ,'Pat1', 'Drug3', '2014-04-02', '2014-08-18', '138 ', '120 ', -18 ) ,
( 13 ,'Pat1', 'Drug3', '2014-08-18', '2015-01-15', '150 ', '90 ', -60 ) ,
( 14 ,'Pat1', 'Drug3', '2015-01-15', '2015-05-07', '112 ', '120 ', 8 ) ,
( 15 ,'Pat1', 'Drug3', '2015-05-07', NULL, NULL, '120 ', 120 )
;WITH mytest as (
Select *, row_number() Over(partition by Patid, Drugid Order by ID) rn from @t
)
, mycte1 AS
(
SELECT rn, ID, Patid, Drugid, DateVal,NextDate, Duration,qty, Remaining, AcumulativeTotal = Case When Remaining<0 then 0 else Remaining end
FROM mytest
WHERE rn = 1
UNION ALL
SELECT t.rn, t.ID, t.Patid, t.Drugid,t.DateVal,t.NextDate,t.Duration,t.qty, t.Remaining, Case when m.AcumulativeTotal + t.Remaining<= 0 Then 0 Else m.AcumulativeTotal + t.Remaining End
FROM mycte1 m
INNER JOIN mytest AS t
ON t.rn = m.rn + 1 and t.Patid=m.Patid and t.Drugid=m.Drugid
)
SELECT ID, Patid, Drugid, DateVal,NextDate, Duration,qty, Remaining, AcumulativeTotal
FROM mycte1
ORDER BY Patid, Drugid,ID
OPTION (MAXRECURSION 200);
Jingyang Li 