Fill Date Range Gaps and Values

  

 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

Advertisements

XML Split and Pivot

  
 
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)

  

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


“Scrubbing Names via SQL Query/Batch” ( Simple Solution to Use CTEs in One Run)

Here is one solution posted on stackexchange:

https://dba.stackexchange.com/questions/11719/scrubbing-names-via-sql-query-batch

But you can use the two CTEs to update the table without using a temp table.

  

ALTER TABLE [mytable] DISABLE TRIGGER ALL
;with mycte as (

SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ID,
 [mytableid],[firstname],[lastname]	      
  FROM  [dbo].[mytable] e  
   WHERE  ([lastname] is not null  or [lastname]='')  and ([firstname] is not null or [firstname]='')
  )
  ,mycte1 as 
  ( 
SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ID,
 [mytableid] ,[firstname] ,[lastname]  FROM  [dbo].[mytable] 
 WHERE   ([lastname] is not null  or [lastname]='')  and ([firstname] is not null or [firstname]='')
  ) 
  Merge mycte1 e using mycte m on e.ID=m.ID
  WHEN  matched then 
  update
Set  e.FirstName = m.FirstName, 
e.LastName = m.LastName ;

ALTER TABLE [mytable] ENABLE TRIGGER ALL

 

Get Stored Procedure Result to A Table

  

--USE yourDB 
--GO
----Get Stored procedure result into a table

;with mycte as (
SELECT distinct p.name, r.column_ordinal,r.name colName,r.system_type_name,r.is_nullable 
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
WHERE p.name not like'sp_%' --no system sp
and p.name ='getYourSpName'  
)

 

  SELECT   N'If Object_id('''+QUOTENAME('Show_SpResult_'+m1.name)+''',''U'') is not null DROP Table '
  + QUOTENAME('Show_SpResult_'+m1.name) 
  + ' Create table '
  + QUOTENAME('Show_SpResult_'+m1.name) 
  +'('+ STUFF((SELECT  ', ' + QUOTENAME(Colname) + ' ' 
  + system_type_name + Case when is_nullable=1 then  ' NULL' Else ' Not NULL' END
	  FROM mycte AS m2
	  WHERE m1.name=m2.name
	  ORDER BY m2.column_ordinal
	  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') 
	  +');  INSERT INTO '+QUOTENAME('Show_SpResult_'+m1.name)+ ' EXEC '+ Name 
	  + ';  SELECT * FROM '+QUOTENAME('Show_SpResult_'+m1.name) +';' as QueryToBeExecuted
 FROM mycte AS m1
 Group by name

 --Copy the result and execute to get the result (you need to pass in parameters' values if your sp has them )
 --We can make this script to run dynamically

 
 --More information about sys.dm_exec_describe_first_result_set_for_object 
 -- https://msdn.microsoft.com/en-us/library/ff878236%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396


 

Sample Query for a Question: “Select IN and OUT time based on flag”

  

create table #temp (EmpID varchar(20),CheckedTime datetime,CheckedType varchar(1))
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 09:00:10.000','I')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 12:55:00.305','O')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 13:40:20.111','I')
--insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 13:44:10.224','I')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 13:45:15.224','I')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 14:58:00.003','O')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 15:10:30.030','I')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:40:40.156','O')
--insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:44:00.601','O')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:45:00.601','O') 
 

;with mycte as (
select *, row_number() Over(Partition by EmpID Order by CheckedTime)
-row_number() Over(Partition by EmpID, CheckedType Order by CheckedTime) grp
 from #temp)
 ,mycte1 as (
 Select *
 , row_number() Over(Partition by EmpID, CheckedType, grp Order by CheckedTime ASC) rnFirst
 , row_number() Over(Partition by EmpID, CheckedType, grp Order by CheckedTime DESC) rnLast
   from mycte)

 Select EmpID,CheckedTime, CheckedType
 ,Case when rnFirst=1 and rnLast&gt;1 then 'First_'
 when rnFirst&gt;1 and rnLast=1 then 'Last_'
 End +CheckedType as checkCol 
 from mycte1
  Order by CheckedTime

drop table #temp
 

&nbsp;

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c9ca548b-af34-4726-9ffe-92351369c981/select-in-and-out-time-based-on-flag?forum=transactsql


A Sample to Transform JSON File from barchart.com to SQL Server Table (SQL Server 2016)

  
--Sample to download json file with your own apikey (paid subscription )
--http://ondemand.websol.barchart.com/getGrainBids.json?apikey=1234yourapikey789&zipCode=00000&maxDistance=100&getAllLocations=1&getAllBids=1

---a table to hold json file data
Drop  table  if exists dbo.RawData_JsonFileTemp

Declare @sql nvarchar(max);
Declare @ColumnHeaders VARCHAR(MAX) 

declare @json1 varchar(max) 
--
SELECT @json1=BulkColumn--,isjson(BulkColumn)
  FROM OPENROWSET (BULK 'C:\temp\JSON\getGrainBids.json', SINGLE_CLOB) t

;with mycteSrc as (
 SELECT  c.[key],  c.[value] 
 FROM OPENJSON(@json1, '$.results') as c
 
)

,myctebids as (
Select  d.value
from mycteSrc
cross apply  OPENJSON ( value,  '$.bids' )   d
)
,myColumnList as (
select 1 as seq, e.[key] ,   'JSON_Value (b.value, ''$.'+e.[key]+ ''') as '+ quotename(e.[key])   col
from myctebids
cross apply   OPENJSON ( value) e
UNION 
Select 2 as seq,  d.[key] ,   'JSON_Value (c.value, ''$.'+d.[key]+ ''') as '+ quotename(d.[key])  
from mycteSrc
cross apply   OPENJSON ( value ) d
)


Select @ColumnHeaders = STUFF( (SELECT  ','   + col  
  FROM myColumnList
 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
 
 --Print @ColumnHeaders
 Select @sql='SELECT '+@ColumnHeaders+ '
  into RawData_JsonFileTemp from OPENJSON('''+ @json1+'''  , ''$.results'') as c
 CROSS APPLY OPENJSON (c.value, ''$.bids'')  b
 '
 --print @sql
EXEC sp_executesql @sql;


--Result
 SELECT *  FROM dbo.RawData_JsonFileTemp