Posted: January 18, 2019 | Author: Jingyang Li | Filed under: Uncategorized |
--SQL Server 2016, 2017
create table source (CODE varchar(100))
Insert into Source values('6-1-A-B')
--Insert into yourtable (CODE, CODE1,CODE2,CODE3,CODE4)
Select s.CODE
, Max(Case when rn=1 then value end) CODE1
, Max(Case when rn=2 then value end) CODE2
, Max(Case when rn=3 then value end) CODE3
, Max(Case when rn=4 then value end) CODE4
from Source s
Cross apply (
SELECT ss.[value], ROW_NUMBER() OVER (PARTITION BY s.CODE ORDER BY s.CODE ) AS RN
FROM string_Split(CODE,'-') AS ss
) as d
Group by s.CODE
Drop table source
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/14dbc085-d942-48ba-878a-b83ea590a085/sql-string-split?forum=transactsql
Posted: January 16, 2019 | Author: Jingyang Li | Filed under: Uncategorized |
DECLARE @StartDate DATETIME = '2019-01-01'
DECLARE @EndDate DATETIME = DATEADD(DAY,900,@StartDate)
DECLARE @CustomOrderTable TABLE (DayOfWeek INT, OrderInTable INT)
INSERT @CustomOrderTable VALUES (7,7),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6)
--**** create a Number table
;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)
--
,DateRange as
(
select dateadd(day,n-1,@startdate) dt from Nums
where dateadd(day,n-1,@startdate)<=@EndDate)
,mycte as (
SELECT *,DayName = DATENAME(WEEKDAY,dt), DayOfWeek = DATEPART(DW,dt),Week = DATEPART(WEEK,dt),
Sum(case when DATENAME(WEEKDAY,dt)='Tuesday' then 1 else 0 end) Over(Partition by Year(dt), month(dt) order by dt) grp
FROM DateRange DR)
--INSERT INTO YourTable
Select dt, DayName,
Year(dt) Year
,Case when Day(DateAdd(Month, DateDiff(Month, 0, dt), 0)+6
-(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, dt), 0))
+@@DateFirst+2)%7 --First Wednesday IN the month
)=1 then
DateAdd(Month, DateDiff(Month, 0, dt), 0)+6+14
-(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, dt), 0))
+@@DateFirst+2)%7
Else
DateAdd(Month, DateDiff(Month, 0, dt), 0)+6+7
-(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, dt), 0))
+@@DateFirst+2)%7
END First_Wednesday_after_the_SecondTuesday_In_a_Month
from mycte
Robyn Page’s SQL Server DATE/TIME Workbench
First Wednesday after the Second Tuesday In a Month
Posted: January 15, 2019 | Author: Jingyang Li | Filed under: Uncategorized |
--Thanks for Ronen for the test table mark up
CREATE TABLE TestTbl (
ID INT primary key,
Date DATETIME
)
INSERT INTO TestTbl (ID, Date)
SELECT top 100000
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
DATEADD(DAY, 0, ABS(CHECKSUM(NEWID()))%100000) d -- random values from 1900-01-01 to 2173-10-16
FROM sys.all_objects t1
CROSS JOIN sys.all_objects t2
CREATE NONCLUSTERED INDEX idx_Date
ON [dbo].[TestTbl] ([Date])
Set statistics io on
Set statistics time on
--- Solution 3************************
print 'Jingyang Solution 3'
Select
Case when abs(datediff(dd, getdate(), dt1))<abs(datediff(dd, getdate(), dt2))
then dt1 else dt2 end dt
from (Select 1 as n ) src
cross apply (
Select TOP 1 [Date]
from TestTbl
WHERE [Date] GETDATE()
Order by [Date]) d2(dt2)
/**************************************/
-- Ronen Ariely 1:
print 'Ronen Ariely 1'
;With MyCTE1 as (
Select TOP 1 [Date], RN = row_number() Over(Order by [Date])
from TestTbl
WHERE [Date] < GETDATE()
Order by [Date]
),
MyCTE2 as (
Select TOP 1 [Date], RN = row_number() Over(Order by [Date] DESC)
from TestTbl
WHERE [Date] < GETDATE()
Order by [Date] DESC
),
MyCTE3 as(
SELECT [Date],I = ABS(DATEDIFF(DAY, [Date], GETDATE())) FROM MyCTE1
UNION ALL
SELECT [Date],I = ABS(DATEDIFF(DAY, [Date], GETDATE())) FROM MyCTE2
)
SELECT TOP 1 [Date]
FROM MyCTE3
order by I
GO
---Olaf's
print 'Olaf''s'
SELECT TOP 1 *
FROM TestTbl
ORDER BY ABS(DATEDIFF(day, GETDATE(), Date))
---Jingyang Solution 1
print 'Jingyang Solution 1'
Select ID, Date from (
Select *, row_number() Over(Order by abs(datediff(day,[Date],getdate() ))) rn
from TestTbl
) t
WHERE rn=1
---Solution 2
print 'Jingyang Solution 2'
select distinct Date from (
Select * , (min(abs(datediff(day,[Date],getdate() ))) Over() ) diff, datediff(day,[Date],getdate() ) flg
from TestTbl
cross apply (values( abs(datediff(day,[Date],getdate() )) ) ) d(val)
) t
where diff=val
---Solution 4
print 'Jingyang Solution 4'
;with mycte1 as (
Select max([Date]) dt1
from TestTbl
WHERE [Date] GETDATE()
)
Select
Case when abs(datediff(dd, getdate(), dt1))<abs(datediff(dd, getdate(), dt2))
then dt1 else dt2 end dt
from mycte1,mycte2
--Jingyang Solution 5
print 'Jingyang Solution 5'
Select top 1 dt
from
(Select max([Date]) dt
from TestTbl
WHERE [Date] GETDATE()
) t
Order by abs(datediff(dd, getdate(), dt))
drop table TestTbl
Set statistics io off
Set statistics time off
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/33d32be6-43e2-454f-86ba-880fc39f394a/find-date-closest-to-getdate?forum=transactsql
Posted: January 11, 2019 | Author: Jingyang Li | Filed under: Uncategorized |
DECLARE @FromDate Date='2018-01-01'
DECLARE @ToDate Date='2019-01-11'
create table #temp (_Type VARCHAR(20),_Date date,Qty int,BalQty INT)
insert into #temp (_Type,_Date,Qty,BalQty) values
('IN',@FromDate,10,10) --modified date
,('OUT','2018-01-15',8,2)
,('OUT','2018-02-12',2,0),
('OUT','2018-03-13',2,-2)
,('OUT','2018-04-04',3,-5)
,('IN','2018-05-24',10,5)
,('IN','2018-05-28',6,11)
,('STKADJ','2018-05-28',11,0)
,('OUT','2018-08-29',6,-6)
,('IN','2018-09-15',10,4)
;with mycte as (
select * , Case when BalQty<0 or (_Type='STKADJ' and BalQty=0 ) then 1 else 0 end chk
,row_number() Over(order by _Date)-row_number() Over(partition by Case when BalQty<0 or (_Type='STKADJ' and BalQty=0 ) then 1 else 0 end order by _Date) grp
from (
Select _Type,_Date,Qty,BalQty from #temp
Union all
Select '',@ToDate as _Date, 0 Qty,0 BalQty ) t
)
,mycte2 as (
Select *, row_number() Over(Partition by chk,grp Order by _Date) rn
,Case when row_number() Over(Partition by chk,grp Order by _Date)= 1 and chk=0 then datediff(day,min(_Date) Over(Partition by chk,grp ) , max(_Date) Over(Partition by chk,grp )) +1 else 0 end diff from mycte
)
select _Type,_Date,Qty,BalQty, Sum(diff) dtdiff from mycte2
GROUP BY
GROUPING SETS
(
((_Type),(_Date),(Qty),(BalQty) ),
()
)
Order by ISNULL(_Date, Dateadd(day,1,@ToDate))
drop table #temp
/*
_Type _Date Qty BalQty dtdiff
IN 2018-01-01 10 10 43
OUT 2018-01-15 8 2 0
OUT 2018-02-12 2 0 0
OUT 2018-03-13 2 -2 0
OUT 2018-04-04 3 -5 0
IN 2018-05-24 10 5 5
IN 2018-05-28 6 11 0
STKADJ 2018-05-28 11 0 0
OUT 2018-08-29 6 -6 0
IN 2018-09-15 10 4 119
2019-01-11 0 0 0
NULL NULL NULL NULL 167
*/
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a47bec20-41a1-48c3-a03a-ee078649d44e/reorder-level-report-days-calculation-?forum=transactsql
Posted: January 8, 2019 | Author: Jingyang Li | Filed under: Uncategorized |
CREATE TABLE [DNPA](
[ComputerName] [varchar](20) NULL,
[DistinguishedName] [varchar](256) NULL
);
GO
INSERT INTO [dbo].[DNPA] (ComputerName,DistinguishedName)
VALUES ( 'COMPUTER01', 'CN=COMPUTER01,OU=Computers,OU=Seattle,OU=PNW,DC=usa,DC=int' ) ,
( 'COMPUTER02', 'CN=COMPUTER02,OU=Computers,OU=Seattle,OU=PNW,DC=usa,DC=int' ) ,
( 'COMPUTER03', 'CN=COMPUTER03,OU=Computers,OU=Seattle,OU=PNW,DC=usa,DC=int' ) ,
( 'COMPUTER04', 'CN=COMPUTER04,CN=Computers,DC=spain,DC=int' ) ,
( 'COMPUTER05', 'CN=COMPUTER05,OU=Computers,OU=Terminal Support Services,OU=Barcelona,DC=spain,DC=int' ) ,
( 'COMPUTER06', 'CN=COMPUTER06,OU=Computers,OU=Planning Department,OU=Barcelona,DC=spain,DC=int' ) ,
( 'COMPUTER07', 'CN=COMPUTER07,OU=Computers,OU=Operations,DC=mexico,DC=int' ) ,
( 'COMPUTER08', 'CN=COMPUTER08,OU=Computers,OU=Information Services,OU=Barcelona,DC=spain,DC=int' ) ,
( 'COMPUTER09', 'CN=COMPUTER08,OU=Computers,OU=Security,OU=Barcelona,DC=spain,DC=int' ) ,
( 'COMPUTER10', 'CN=COMPUTER10,OU=Computers,OU=Gate,OU=Barcelona,DC=spain,DC=int' ) ,
( 'COMPUTER11', 'CN=COMPUTER11,OU=Computers,OU=Mexico City,DC=mexico,DC=int' ) ,
( 'COMPUTER12', 'CN=COMPUTER12,OU=Computers,OU=Security,OU=Barcelona,DC=spain,DC=int' ) ,
( 'COMPUTER13', 'CN=COMPUTER13,OU=Computers,OU=Mexico City,DC=mexico,DC=int' ) ,
( 'COMPUTER14', 'CN=COMPUTER14,OU=Computers,OU=Blocked-USB,OU=Barcelona,DC=spain,DC=int' ) ,
( 'COMPUTER15', 'CN=COMPUTER15,OU=Computers,OU=Mexico City,DC=mexico,DC=int' ) ,
( 'COMPUTER16', 'CN=COMPUTER16,OU=Computers,OU=Training,OU=Barcelona,DC=spain,DC=int' ) ,
( 'COMPUTER17', 'CN=COMPUTER17,OU=Computers,OU=Mexico City,DC=mexico,DC=int' ) ,
( 'COMPUTER18', 'CN=COMPUTER18,OU=Computers,OU=Mexico City,DC=mexico,DC=int' ) ,
( 'COMPUTER19', 'CN=COMPUTER19,OU=Computers,OU=Planning Department,OU=Barcelona,DC=spain,DC=int' ) ,
( 'COMPUTER20', 'CN=COMPUTER20,OU=Computers,OU=Mexico City,DC=mexico,DC=int' ) ,
( 'COMPUTER21', 'CN=COMPUTER21,OU=Computers,OU=Planning Department,OU=Barcelona,DC=spain,DC=int' );
;
GO
;with mycte as (
SELECT s.ComputerName, s.DistinguishedName, [x].[value],
ROW_NUMBER() OVER (PARTITION BY s.ComputerName ORDER BY x.rn DESC ) rn2
FROM [DNPA] AS s
CROSS APPLY (
SELECT ss.[value],
ROW_NUMBER() OVER (PARTITION BY s.ComputerName ORDER BY s.ComputerName DESC) AS rn
FROM STRING_SPLIT(s.DistinguishedName, ',') AS [ss]
) AS x
)
---*****************SQL Server 2017 using STRING_AGG function
/*
Select ComputerName,
Stuff(STRING_AGG(stuff([value] ,1,3,'')
, '\' ) WITHIN GROUP ( ORDER BY rn2) ,charindex('\',STRING_AGG(stuff([value] ,1,3,'')
, '\' ) WITHIN GROUP ( ORDER BY rn2)),1,'.') newString
FROM mycte
Group by ComputerName
*/
SELECT m2.ComputerName
, STUFF((
SELECT Case when rn2=2 then '.' else '\' end + CAST(stuff(m.[value] ,1,3,'') AS NVARCHAR(MAX))
FROM mycte m
WHERE m.ComputerName = m2.ComputerName
ORDER BY m.rn2 ASC
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') AS newString
FROM mycte AS m2
GROUP BY ComputerName
Drop TABLE [DNPA]
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ccc281f1-63b2-45a8-a990-66724ac65d67/how-to-use-cte-inline-with-select-clause?forum=transactsql
Posted: January 3, 2019 | Author: Jingyang Li | Filed under: Uncategorized |
DECLARE @StartDate DATETIME = '2019-01-01'
DECLARE @EndDate DATETIME = DATEADD(DAY,900,@StartDate)
DECLARE @CustomOrderTable TABLE (DayOfWeek INT, OrderInTable INT)
INSERT @CustomOrderTable VALUES (7,7),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6)
--**** create a Number table
;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)
--
,DateRange as
(
select dateadd(day,n-1,@startdate) dt from Nums
where dateadd(day,n-1,@startdate)<=@EndDate)
,mycte as (
SELECT *,DayName = DATENAME(WEEKDAY,dt), DayOfWeek = DATEPART(DW,dt),Week = DATEPART(WEEK,dt),
Sum(case when DATENAME(WEEKDAY,dt)='Tuesday' then 1 else 0 end) Over(Partition by Year(dt), month(dt) order by dt) grp
FROM DateRange DR)
--INSERT INTO YourTable
Select dt, DayName, DR.DayOfWeek, Week,
Year(dt) Year
,Max(case when grp=2 and DayName='Wednesday' then dt else null end) Over(Partition by Year(dt), month(dt) ) First_Wednesday_after_the_SecondTuesday_In_a_Month
from mycte DR INNER JOIN @CustomOrderTable CO ON CO.DayOfWeek=DR.DayOfWeek
ORDER BY dt
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d0bbb46c-d632-45a2-b825-4e3b18cb887d/first-wednesday-after-the-second-tuesday?forum=transactsql
Jingyang Li 