Value Pair Query

Posted: February 28, 2018 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

DECLARE @TypeMax TABLE
(
MaxCol nvarchar(max)
)

INSERT INTO @TypeMax
VALUES (‘{“ID”:”075405435453.doc”,”Name”:”Scenario1″,”Date”:”2016-12-19 23:01:03″,”Type”:”SL”}’)

, (‘{“ID”:”55453450480003.wav”,”Name”:”Change43″,”Date”:”2016-12-13 21:03:23″,”Type”:”AL”}’)

–SELECT * FROM @TypeMax

–SQL Server 2016 or 2017
SELECT isJSON(MaxCol),
JSON_VALUE(MaxCol, ‘$.ID’) Id,
JSON_VALUE(MaxCol, ‘$.Name’) Name,
JSON_VALUE(MaxCol, ‘$.Date’) [Date],
JSON_VALUE(MaxCol, ‘$.Type’) Type
FROM @TypeMax

–Before JSON functions were introduced to SQL SERVER
—Use XML shredding

;With mycte as (
SELECT Cast(N'<H><r ‘ + Replace(Replace(Replace(Replace(Replace( MaxCol , ‘{“‘,”), ‘}’,”), ‘”,”‘,'” ‘),'”:”‘,’=”‘) + ‘” /></H>’ ,'””‘,'”‘ ) as xml) AS [vals]
FROM @TypeMax
)

,mycte1 as (
SELECT ROW_NUMBER() OVER (ORDER BY S.a.value(‘count(.)’, ‘tinyint’)) rn,
S.a.value(‘@ID’, ‘varchar(50)’) as [ID],
S.a.value(‘@Name’, ‘varchar(50)’) as [Name],
S.a.value(‘@Date’, ‘varchar(50)’) as [Date],
S.a.value(‘@Type’, ‘varchar(50)’) as [Type]

FROM mycte d CROSS APPLY d.[vals].nodes(‘/H/r’) S(a) )

select ID,Name,[Date],[Type] from mycte1
/*
ID Name Date Type
075405435453.doc Scenario1 2016-12-19 23:01:03 SL
55453450480003.wav Change43 2016-12-13 21:03:23 AL
*/

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4f229634-d6bb-4096-809b-544895bb0529/sql-help-one-column-spilt-into-multiple-columns?forum=transactsql

Generating Sorted List Becomes Easy with SQL Server 2017’s New String Function: String_AGG

Posted: February 27, 2018 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

  

 create table #temp
(id int,Name1 varchar(30),
Name2 varchar(30),
Name3 varchar(30) 
)

insert into #temp
values(1,'Ashley','Zebo','Terry'),
(2,'John','Adam','Bert') 

;with mycte as 
(select * from #temp
cross apply(Values(Name1),(Name2),(Name3)) d(Names) 
)

SELECT id, String_AGG(Names,',') WITHIN GROUP ( ORDER BY Names)  AS Result
FROM mycte
Group by id

drop table #temp

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql

Fill Date Range Gaps and Values

Posted: February 21, 2018 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

  

 create table #forum (Country varchar(20), City varchar (20) , Market varchar(10),  NDate date, NOpen int, NClose int)

Insert into #forum values 
('Japan','Tokyo','Fish', '2017-01-01', 100, 150),  ('Japan','Tokyo','Fish', '2017-02-01', 150, 180), ('Japan','Tokyo','Fish', '2017-04-01', 180, 160)
,('France','Paris','Coffee', '2017-01-01', 1100, 950), ('France','Paris','Coffee', '2017-02-01', 950, 1080), ('France','Paris','Coffee', '2017-06-01', 1080, 1260),
('France','Paris','Fruit', '2017-01-01', 100, 110), ('France','Paris','Fruit', '2017-03-01', 110, 130),
('Argentina','Buenos Aires','Coffee', '2017-01-01', 300, 250),('Argentina','Buenos Aires','Coffee', '2017-02-01', 250, 50),('Argentina','Buenos Aires','Coffee', '2017-03-01', 50, 450),
('Argentina','Buenos Aires','Coffee', '2017-04-01', 450, 160),('Argentina','Buenos Aires','Coffee', '2017-05-01', 160, 320),('Argentina','Buenos Aires','Coffee', '2017-06-01', 320, 200)

--Expected Result
create table #forumResult (Country varchar(20), City varchar (20) 
, Market varchar(10),  NDate date, NOpen int, NClose int)

Insert into #forumResult values 
('Japan','Tokyo','Fish', '2017-01-01', 100, 150),  ('Japan','Tokyo','Fish', '2017-02-01', 150, 180), ('Japan','Tokyo','Fish', '2017-03-01', 180, 180),
('Japan','Tokyo','Fish', '2017-04-01', 180, 160),
('France','Paris','Coffee', '2017-01-01', 1100, 950), ('France','Paris','Coffee', '2017-02-01', 950, 1080), ('France','Paris','Coffee', '2017-03-01', 1080, 1080),
('France','Paris','Coffee', '2017-04-01', 1080, 1080), ('France','Paris','Coffee', '2017-05-01', 1080, 1080),('France','Paris','Coffee', '2017-06-01', 1080, 1260),
('France','Paris','Fruit', '2017-01-01', 100, 110), ('France','Paris','Fruit', '2017-02-01', 110, 110), ('France','Paris','Fruit', '2017-03-01', 110, 130),

('Argentina','Buenos Aires','Coffee', '2017-01-01', 300, 250),('Argentina','Buenos Aires','Coffee', '2017-02-01', 250, 50),('Argentina','Buenos Aires','Coffee', '2017-03-01', 50, 450),
('Argentina','Buenos Aires','Coffee', '2017-04-01', 450, 160),('Argentina','Buenos Aires','Coffee', '2017-05-01', 160, 320),('Argentina','Buenos Aires','Coffee', '2017-06-01', 320, 200)

select * from #forumResult

declare @startdate date 
declare @enddate date   

Select @startdate = min(NDate),@enddate = max(NDate) from #forum 

--****  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)

 --Country, City, Market and Dates  
,myCountryCityMarket as
(
select Country, City, Market, dateadd(month,n-1,@startdate) dt from Nums 
Cross join (Select Distinct Country, City, Market From  #forum ) a    
)

,mycte2 as (
Select cck.Country,cck.City,cck.Market, 
f.NOpen,f.NClose,dt, f.NDate  

FROM myCountryCityMarket cck
LEFT JOIN    #forum f ON  f.NDate =cck.dt  
and f.Country=cck.Country and f.City =cck.City and f.Market= cck.Market  
WHERE cck.dt<=@enddate --last date
 )

 select Country,City,Market,dt NDate  
 , ISNULL(NOpen, NCloseFill) NOpen
 , ISNULL(NClose, NCloseFill) NClose  
 from mycte2 m
 Outer apply (select top 1 NClose from mycte2 m2 
WHERE m2.Country=m.Country and m2.City =m.City and m2.Market= m.Market
 and m2.dt<=m.dt and NClose is not null 
Order by dt DESC) d1(NCloseFill)
 Outer apply (select top 1 NClose from mycte2 m2 
WHERE m2.Country=m.Country and m2.City =m.City and m2.Market= m.Market
 and m2.dt>=m.dt and NClose is not null 
Order by dt DESC) d2(NCloseFill2)
WHERE NCloseFill2 is not null
Order by 1,3,4

drop table  #forumResult, #forum

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e5a9bf94-8f89-414c-8c42-a0bada141971/filling-the-gap-in-the-calendar-having-open-and-close-value?forum=transactsql

XML Split and Pivot

Posted: February 15, 2018 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

  
 
create table rating_label
(
id_pk int,
label_message varchar(500)
)


insert into rating_label values(105,'Look and Feel');
insert into rating_label values(107,'Performance And Speed');
insert into rating_label values(106,'Ease of Use');
insert into rating_label values(109,'Good');
insert into rating_label values(108,'Performance');

create table query_info
(
cust_mob varchar(20),
rating_id varchar(200),
Rated_Date date default getdate()
)

INSERT query_info (cust_mob, rating_id, Rated_Date) VALUES (N'6556949494', N'109*1#108*1#107*3#106*0#105*4#', CAST(N'2018-02-15' AS Date))
 
--INSERT into query_info (cust_mob,rating_id, Rated_Date) VALUES (N'7756946507', N'109*4#108*0#107*5#106*1#105*5#', CAST(N'2018-02-12' AS Date))
--, (N'6565676879', N'109*3#108*4#107*2#106*3#105*4#', CAST(N'2018-02-12' AS Date))
--, (N'3434544657', N'109*2#108*1#107*0#106*2#105*0#', CAST(N'2018-02-12' AS Date))
--insert into query_info (cust_mob,rating_id)values('6556949494','109*1#108*1#107*3#106*0#105*4#'),('7556949486','109*1#108*2#107*2#106*0#105*0#'),('8547125934','109*5#108*0#107*4#106*0#105*4#')
;with mycte as (
select * ,  
CAST (N'<H><r>' +  REPLACE(rating_id,  '#', '</r><r>')+ '</r></H>' AS XML) vals
from query_info
WHERE Rated_Date='2018-02-15' )
, mycte1 as
 ( 
 SELECT  cust_mob,  S.a.value('.', 'VARCHAR(100)') AS splitVal1 
 FROM mycte d
 CROSS APPLY d.[vals].nodes('/H/r') S(a)
 )
 , mycte2 as
 ( 
Select *, CAST (N'<H><r>' + Replace( splitVal1, '*','</r><r>') + '</r></H>' AS XML) [vals] from mycte1
 )
 ,mycte3 as (

 Select DISTINCT cust_mob, 
 S.a.value('(/H/r)[1]', 'VARCHAR(100)') cat,  S.a.value('(/H/r)[2]', 'VARCHAR(100)') val  
 FROM mycte2 d   
 CROSS APPLY d.[vals].nodes('/H/r') S(a)  
 )

 Select rl.label_message, sum(Case when val=1 then 1 else 0 end) [1]
 , sum(Case when val=2 then 1 else 0 end) [2]
 , sum(Case when val=3 then 1 else 0 end) [3]
 , sum(Case when val=4 then 1 else 0 end) [4]
 , sum(Case when val=5 then 1 else 0 end) [5]
 from mycte3 m join rating_label rl on m.cat=rl.id_pk
 WHERE cat<>''
 Group by cat,rl.label_message
 Order by cat
  
 

 

drop table query_info , rating_label

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0e6ddccd-5e94-4478-bd4b-8ca7412cb915/split-row-query?forum=transactsql

Calculating Time Interval (Recursive)

Posted: February 6, 2018 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

  

CREATE TABLE example(
       [ID] [varchar](9) NULL,
       [DTTM] [datetime] NULL,
       [RN0] Int null,
       [COUNTFLAG] int
) ON [PRIMARY] 

Insert into [example] (ID,DTTM) values 
('123456789','2017-10-05 08:00:00.000'), -- Expected Result
('123456789','2017-10-05 08:05:00.000'), 
('123456789','2017-10-05 08:07:00.000'),
('123456789','2017-10-05 08:15:00.000'), -- Expected Result
('123456789','2017-10-05 08:25:00.000'),
('123456789','2017-10-05 10:12:00.000') , -- Expected Result
('123456789','2017-10-05 10:26:00.000'),
('123456789','2017-10-05 10:32:00.000'),  -- Expected Result
('123456789','2017-10-05 10:33:00.000'),
('123456789','2017-10-05 10:34:00.000'),
('123456789','2017-10-05 10:35:00.000'),
('123456789','2017-10-05 10:36:00.000'),
('123456789','2017-10-05 10:37:00.000'),
('123456789','2017-10-05 10:38:00.000'),
('123456789','2017-10-05 10:39:00.000'),
('123456789','2017-10-05 10:40:00.000'),
('123456789','2017-10-05 10:41:00.000'),
('123456789','2017-10-05 10:42:00.000'),
('123456789','2017-10-05 10:43:00.000'),
('123456789','2017-10-05 10:44:00.000'),
('123456789','2017-10-05 10:45:00.000'),
('123456789','2017-10-05 10:46:00.000'),
('123456789','2017-10-05 10:47:00.000'),  -- Expected Result
('123456789','2017-10-05 10:48:00.000')

;with src as (
Select row_number() Over(Partition by ID Order by DTTM) rn
, * from [dbo].[example]
)
--resursive cte
,mycte  as (
Select rn, ID, DTTM, DTTM as DTTM2  from src where rn=1
union all
Select s.rn, s.ID, s.DTTM
, Case when datediff(minute,m.DTTM2, s.DTTM)&gt;=15 then s.DTTM else m .DTTM2 end   

from mycte  m  join src s on s.DTTM&gt;m .DTTM and s.rn=m.rn+1
)
,mycte2 as (
Select ID, DTTM, 
Case when row_number() Over(Partition by ID, DTTM2 Order by DTTM)=1 
then 1 else 0 end CountFlag
from mycte 
)

Select * from mycte2
Where CountFlag=1

drop table [dbo].[example]

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/42fd69c1-3b2b-48a8-ab57-bce03d611f43/calculating-time-interval?forum=transactsql#ceb08dc3-7f4a-4cf0-8f75-096381722660
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dcfeb87f-ee4c-4bfe-b8ed-963f88a610a3/time-interval?forum=transactsql

Jingyang Li

SQL Server

Value Pair Query

Generating Sorted List Becomes Easy with SQL Server 2017’s New String Function: String_AGG

Fill Date Range Gaps and Values

XML Split and Pivot

Calculating Time Interval (Recursive)

Recent Posts

Archives

Categories

Meta