Fill Null From Previous Non Null Value

Posted: July 28, 2020 | Author: | Filed under: Uncategorized | Leave a comment

https://social.msdn.microsoft.com/Forums/en-US/f53839e6-493d-4206-8b67-f59e9615b27d/how-to-insert-missing-records-for-each-monthyear-with-the-previous-months-value-if-they-dont?forum=transactsql


Create Table test
( year int, month int,CustomerID int, AccountNum int, Amount int)
GO
Insert test
values
(2019,11,14,1,200),
(2019,11,20,1,500),
(2019,11,20,2,25),
(2019,12,20,1,670),
(2019,12,20,2,50),
(2020,1,20,2,700),
(2020,2,20,2,746),
(2020,3,20,1,550),
(2020,3,20,2,600),
(2020,4,20,2,720) ;
;with mycte as (
select year,month,CustomerID,AccountNum
from (values(2019),(2020)) a(Year)
, (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) ) b(month),
(select distinct CustomerID from test) c,
(select distinct AccountNum from test) d
)
,mycte2 as (select
isnull(t.year,m.year) year,
isnull(t.month,m.month) month,
isnull(t.CustomerID,m.CustomerID) CustomerID,
isnull(t.AccountNum,m.AccountNum) AccountNum
, Amount
from mycte m left join test t on
m.year=t.year and m.month=t.month
and m.CustomerID=t.CustomerID and m.AccountNum=t.AccountNum
)
select m.year,m.month,m.CustomerID,m.AccountNum, isnull(m.Amount, t1.Amount) Amount
from mycte2 m OUTER APPLY (select top (1) Amount from mycte2 t
where m.CustomerID=t.CustomerID and m.AccountNum=t.AccountNum
and datefromparts(m.year,m.month,1)> datefromparts(t.year,t.month,1)
and t.Amount IS NOT NULL
ORDER BY datefromparts(t.year,t.month,1) desc ) t1
WHERE isnull(m.Amount, t1.Amount) is not null
and datefromparts(m.year,m.month,1)<= (select Max(datefromparts(year,month,1)) from test)
order by Year, month,CustomerID, AccountNum
Drop Table test
/*
year month CustomerID AccountNum Amount
2019 11 14 1 200
2019 11 20 1 500
2019 11 20 2 25
2019 12 14 1 200
2019 12 20 1 670
2019 12 20 2 50
2020 1 14 1 200
2020 1 20 1 670
2020 1 20 2 700
2020 2 14 1 200
2020 2 20 1 670
2020 2 20 2 746
2020 3 14 1 200
2020 3 20 1 550
2020 3 20 2 600
2020 4 14 1 200
2020 4 20 1 550
2020 4 20 2 720
*/

view raw

Fill Null Value with T-SQL.sql

hosted with ❤ by GitHub

A UDF from Robyn Page’s SQL Server DATE/TIME Workbench

Posted: July 27, 2020 | Author: | Filed under: Uncategorized | Leave a comment

A question from MSDN:
https://social.msdn.microsoft.com/Forums/en-US/999d188a-244e-47c5-88ea-50337823b316/need-help-in-sql-query-to-get-date-from-month-number-week-number-and-week-day?forum=transactsql

There is a UDF created by Robyn Page many years ago can solve the problem accurately.
I copied the code for easy reference in the future.


IF OBJECT_ID (N'NthDayOfWeekOfMonth') IS NOT NULL
DROP FUNCTION NthDayOfWeekOfMonth
GO
CREATE FUNCTION NthDayOfWeekOfMonth (
@TheYear CHAR(4), –the year as four characters (e.g. '2014')
@TheMonth CHAR(3), –in english (Sorry to our EU collegues) e.g. Jun, Jul, Aug
@TheDayOfWeek CHAR(3), — one of Mon, Tue, Wed, Thu, Fri, Sat, Sun
@Nth INT) –1 for the first date, 2 for the second occurence, 3 for the third
RETURNS DATETIME
WITH EXECUTE AS CALLER
AS
BEGIN
RETURN DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(date,'1 '+@TheMonth+' '+@TheYear,113)), 0)+ (7*@Nth)-1
-(DATEPART (Weekday, DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(date,'1 '+@TheMonth+' '+@TheYear,113)), 0))
+@@DateFirst+(CHARINDEX(@TheDayOfWeek,'FriThuWedTueMonSunSat')-1)/3)%7
END
–https://www.red-gate.com/simple-talk/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/

view raw

gistfile1.txt

hosted with ❤ by GitHub

XML Split And Recursive Concat

Posted: July 17, 2020 | Author: | Filed under: Uncategorized | Leave a comment


declare @s varchar(max) = 'Hi my name is M and I have two sister 1) M and 2) K'
;with mycte as (
select Cast(N'<H><r>'+replace(@s ,' ', '</r><r>') + '</r></H>' AS XML) vals
)
,mycte2 as (
SELECT ROW_NUMBER() OVER (ORDER BY S.a.value('count(.)', 'tinyint')) rn
,S.a.value('.', 'VARCHAR(100)') AS splitVal
FROM mycte d
CROSS APPLY d.[vals].nodes('/H/r') S(a)
where S.a.value('.', 'VARCHAR(100)') not like '%[0-9])%'
)
, rCTE as (
select rn, splitVal, splitVal as mergedCol from mycte2
where rn=1
union all
select m.rn, m.splitVal
, Cast(r.mergedCol +' '+ m.splitVal as varchar(100))
from mycte2 m join rCTE r on m.rn=r.rn+1
)
select top 1 mergedCol from rCTE
order by rn desc

view raw

XMLSplitAndRecursiveConcat.sql

hosted with ❤ by GitHub

https://social.msdn.microsoft.com/Forums/en-US/e1538ff7-5618-484a-ae0c-0ab56d2ec671/how-to-replace-all-digits-followed-by-bracket-in-string?forum=transactsql

Remove Items withJSON (T-SQL)

Posted: July 16, 2020 | Author: | Filed under: Uncategorized | Leave a comment


declare @s varchar(max) = 'Hi my name is M and I have two sister 111) M and 2111) K'—, 3333) W';
select string_agg([value],' ') newString
from openjson('["'+replace(@s ,' ', '","') + '"]')
where [Value] not like '%[0-9])%'

view raw

Remove Items with JSON (T-SQL).sql

hosted with ❤ by GitHub

https://social.msdn.microsoft.com/Forums/en-US/e1538ff7-5618-484a-ae0c-0ab56d2ec671/how-to-replace-all-digits-followed-by-bracket-in-string?forum=transactsql

TSQL Unpivot with JSON Data

Posted: July 5, 2020 | Author: | Filed under: Uncategorized | Leave a comment


CREATE TABLE #tmpTable (
Friday varchar(50),
Monday varchar(50),
Saturday varchar(50),
Sunday varchar(50),
Thursday varchar(50),
Tuesday varchar(50),
Wednesday varchar(50),
fk_incomes int,
fk_life_stages int,
fk_met_areas int,
fk_neighbourhoods int,
local_date date)
INSERT #tmpTable VALUES
('[73,40,66,68,48,67]','[63,57,52,117,27,35]','[50,54,48,84,30,44]','[40,179,62,49,71,25]','[156,241,91,63,21,18]','[33,57,58,48,64,69]','[51,103,113,72,82,66]',3,3,0,6719,CONVERT(DATETIME, '2020-04-01', 120))
,('[0,0,0,0,0,0]','[0,0,2,0,0,0]','[0,0,0,0,0,0]','[0,0,0,2,0,0]','[0,0,0,0,0,0]','[0,0,0,0,0,0]','[14,0,0,0,0,0]',4,2,0,25160,CONVERT(DATETIME, '2020-04-01', 120))
,('[0,0,0,2,0,2]','[0,0,1,0,0,0]','[0,4,4,4,0,1]','[0,0,112,2,0,0]','[0,0,0,1,1,0]','[0,1,2,0,0,0]','[0,0,1,0,0,0]',5,2,0,26254,CONVERT(DATETIME, '2020-04-01', 120)),
('[0,2,18,77,103,0]','[10,47,47,99,4,0]','[3,7,32,29,42,2]','[0,3,0,5,0,1]','[2,55,51,16,42,0]','[3,0,32,36,52,32]','[2,41,68,61,3,0]',1,3,0,193189,CONVERT(DATETIME, '2020-04-01', 120)),
('[2,11,0,12,11,0]','[1,4,0,1,2,0]','[0,23,9,4,9,4]','[26,14,7,15,0,0]','[5,4,13,17,1,5]','[12,45,0,7,0,12]','[13,6,38,4,3,7]',3,3,0,193526,CONVERT(DATETIME, '2020-04-01', 120)),
('[24,23,48,51,31,11]','[25,32,19,23,11,11]','[9,31,76,73,32,11]','[8,15,30,29,14,12]','[27,42,114,50,13,25]','[43,35,8,215,11,10]','[48,127,43,73,23,32]',2,3,0,193640,CONVERT(DATETIME, '2020-04-01', 120)),
('[89,139,227,169,169,42]','[56,114,99,94,59,55]','[97,102,215,120,50,120]','[65,104,71,54,107,41]','[157,102,142,188,94,158]','[55,128,119,229,147,43]','[158,163,216,155,199,85]',1,2,0,194379,CONVERT(DATETIME, '2020-04-01', 120))
;with mysource as (
SELECT fk_neighbourhoods,local_date,
Friday ,
Monday ,
Saturday ,
Sunday ,
Thursday ,
Tuesday ,
Wednesday
, row_number() OVER (ORDER BY fk_neighbourhoods,local_date ) rn
FROM #tmpTable
)
,mycte (TheKey, TheValue) as (
SELECT [KEY], Value from OpenJson((
select * from mysource FOR JSON AUTO ))
WHERE type = 5)
,mycteFriday as (
SELECT
JSON_VALUE(src.TheValue,'$.fk_neighbourhoods') fk_neighbourhoods,
JSON_VALUE(src.TheValue,'$.local_date') local_date
— JSON_VALUE(src.TheValue,'$.Friday') Friday
–,JSON_VALUE(src.TheValue,'$.Monday') Monday
–,JSON_VALUE(src.TheValue,'$.Saturday') Saturday
–…
,unpvt.Value grp
,unpvt1.[key], unpvt1.[value] Friday
FROM mycte AS src
CROSS APPLY OpenJson(src.TheValue) AS unpvt
cross apply OpenJson(JSON_VALUE(src.TheValue,'$.Friday')) AS unpvt1
WHERE unpvt.Type !=5 and
unpvt.[Key] not in (
'Friday'
,'Monday'
,'Saturday'
,'Sunday'
,'Thursday'
,'Tuesday'
,'Wednesday'
,'fk_neighbourhoods'
,'local_date')
)
,mycteMonday as (
SELECT
unpvt.Value grp
,unpvt1.[key], unpvt1.[value] as Monday
FROM mycte AS src
CROSS APPLY OpenJson(src.TheValue) AS unpvt
cross apply OpenJson(JSON_VALUE(src.TheValue,'$.Monday')) AS unpvt1
WHERE unpvt.Type !=5 and
unpvt.[Key] not in (
'Friday'
,'Monday'
,'Saturday'
,'Sunday'
,'Thursday'
,'Tuesday'
,'Wednesday'
,'fk_neighbourhoods'
,'local_date')
)
,mycteSaturday as (
SELECT
unpvt.Value grp
,unpvt1.[key], unpvt1.[value] as Saturday
FROM mycte AS src
CROSS APPLY OpenJson(src.TheValue) AS unpvt
cross apply OpenJson(JSON_VALUE(src.TheValue,'$.Saturday')) AS unpvt1
WHERE unpvt.Type !=5 and
unpvt.[Key] not in (
'Friday'
,'Monday'
,'Saturday'
,'Sunday'
,'Thursday'
,'Tuesday'
,'Wednesday'
,'fk_neighbourhoods'
,'local_date')
)
,mycteSunday as (
SELECT
unpvt.Value grp
,unpvt1.[key], unpvt1.[value] as Sunday
FROM mycte AS src
CROSS APPLY OpenJson(src.TheValue) AS unpvt
cross apply OpenJson(JSON_VALUE(src.TheValue,'$.Sunday')) AS unpvt1
WHERE unpvt.Type !=5 and
unpvt.[Key] not in (
'Friday'
,'Monday'
,'Saturday'
,'Sunday'
,'Thursday'
,'Tuesday'
,'Wednesday'
,'fk_neighbourhoods'
,'local_date')
)
,mycteThursday as (
SELECT
unpvt.Value grp
,unpvt1.[key], unpvt1.[value] as Thursday
FROM mycte AS src
CROSS APPLY OpenJson(src.TheValue) AS unpvt
cross apply OpenJson(JSON_VALUE(src.TheValue,'$.Thursday')) AS unpvt1
WHERE unpvt.Type !=5 and
unpvt.[Key] not in (
'Friday'
,'Monday'
,'Saturday'
,'Sunday'
,'Thursday'
,'Tuesday'
,'Wednesday'
,'fk_neighbourhoods'
,'local_date')
)
,mycteTuesday as (
SELECT
unpvt.Value grp
,unpvt1.[key], unpvt1.[value] as Tuesday
FROM mycte AS src
CROSS APPLY OpenJson(src.TheValue) AS unpvt
cross apply OpenJson(JSON_VALUE(src.TheValue,'$.Tuesday')) AS unpvt1
WHERE unpvt.Type !=5 and
unpvt.[Key] not in (
'Friday'
,'Monday'
,'Saturday'
,'Sunday'
,'Thursday'
,'Tuesday'
,'Wednesday'
,'fk_neighbourhoods'
,'local_date')
)
,mycteWednesday as (
SELECT
unpvt.Value grp
,unpvt1.[key], unpvt1.[value] as Wednesday
FROM mycte AS src
CROSS APPLY OpenJson(src.TheValue) AS unpvt
cross apply OpenJson(JSON_VALUE(src.TheValue,'$.Wednesday')) AS unpvt1
WHERE unpvt.Type !=5 and
unpvt.[Key] not in (
'Friday'
,'Monday'
,'Saturday'
,'Sunday'
,'Thursday'
,'Tuesday'
,'Wednesday'
,'fk_neighbourhoods'
,'local_date')
)
select t1.fk_neighbourhoods,
t1.local_date
–,t1.grp
,t1.Friday
,t2.Monday
,t3.Saturday
,t4.Sunday
,t5.Thursday
,t6.Tuesday
,t7.Wednesday
from mycteFriday t1
join mycteMonday t2 on t1.grp=t2.grp and t1.[key]=t2.[key]
join mycteSaturday t3 on t1.grp=t3.grp and t1.[key]=t3.[key]
join mycteSunday t4 on t1.grp=t4.grp and t1.[key]=t4.[key]
join mycteThursday t5 on t1.grp=t5.grp and t1.[key]=t5.[key]
join mycteTuesday t6 on t1.grp=t6.grp and t1.[key]=t6.[key]
join mycteWednesday t7 on t1.grp=t7.grp and t1.[key]=t7.[key]
drop table #tmpTable

view raw

T-SQL Unpivot with JSON.sql

hosted with ❤ by GitHub

https://social.msdn.microsoft.com/Forums/en-US/c73560d6-535a-455d-8707-38de0341ea5d/how-to-deal-with-or-convert-arrays-in-sql?forum=transactsql

A Query Sample

Posted: July 2, 2020 | Author: | Filed under: Uncategorized | Leave a comment

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7c1f2cf8-b7e9-4994-94cd-7c59d550219d/sql-server-help?forum=transactsql

https://gist.github.com/jingyang-li/a8c1ea0bfeb5801cca90a6c77a2bb3cc#file-querysample2-sql

CREATE TABLE [dbo].[productdetails](
[productid] [int] NULL,
[Productrstartdate] [date] NULL,
[Productenddate] [date] NULL,
[EMIInstallment] [int] NULL
)
GO
INSERT [dbo].[productdetails] ([productid], [Productrstartdate], [Productenddate], [EMIInstallment])
VALUES (1, CAST(N'2020-10-02' AS Date), CAST(N'2024-10-02' AS Date), 5)
,(2, CAST(N'2020-02-10' AS Date), CAST(N'2021-02-10' AS Date), 2)
,(3, CAST(N'2019-01-10' AS Date), CAST(N'2019-01-10' AS Date), 1)
,(4, CAST(N'2019-01-18' AS Date), CAST(N'2021-01-18' AS Date), 3)
GO
—Cross Apply with a number table
— Create Numbers cte
;WITH Num1 (num) AS (
SELECT 1 as num
UNION ALL SELECT num+1
FROM Num1 Where num<101),
Num2 (num) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (num) AS (SELECT ROW_NUMBER() OVER(ORDER BY num) FROM Num2)
Select a.productid
, DATEADD(YEAR,num-1,a.[Productrstartdate] ) CalendarDate
, num as noofinstallmentcount
from [productdetails] a
cross apply (select num from nums Where num<60) d
Where DATEADD(YEAR,num-1,a.[Productrstartdate] )<= a.[Productenddate]
Order by a.productid,num
—Cross JOIN with a number table
— Create Numbers cte
;WITH Num1 (num) AS (
SELECT 1 as num
UNION ALL SELECT num+1
FROM Num1 Where num<101),
Num2 (num) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (num) AS (SELECT ROW_NUMBER() OVER(ORDER BY num) FROM Num2)
Select a.productid
, DATEADD(YEAR,num-1,a.[Productrstartdate] ) CalendarDate
, num as noofinstallmentcount
from [productdetails] a, nums
Where num<60 and DATEADD(YEAR,num-1,a.[Productrstartdate] )<= a.[Productenddate]
Order by a.productid,num
–Recursive
;WITH ABC
AS
(
SELECT productid ,[Productrstartdate] CalendarDate ,[Productenddate],
1 as lvl from [dbo].[productdetails]
UNION ALL
SELECT a.productid ,DATEADD(YEAR,1,b.CalendarDate ) CalendarDate, b.[Productenddate], lvl + 1
FROM [dbo].[productdetails] a join ABC b on a.productid=b.productid
WHERE b.CalendarDate <a.[Productenddate] )
SELECT productid, CalendarDate, lvl as noofinstallmentcount
FROM ABC
order by productid
drop table [productdetails]
view raw querySample2.sql hosted with ❤ by GitHub

https://gist.github.com/jingyang-li/a8c1ea0bfeb5801cca90a6c77a2bb3cc#file-querysample2-sql

https://gist.github.com/jingyang-li/a8c1ea0bfeb5801cca90a6c77a2bb3cc#file-querysample2-sql

Pivot and YTD Sample

Posted: July 1, 2020 | Author: | Filed under: Uncategorized | Leave a comment

https://social.msdn.microsoft.com/Forums/en-US/872463e4-5afc-4721-9eb9-bafb077fb978/show-year-to-date-totals?forum=transactsql

Create Table #empSales
(
employeename varchar(100)
,saleamt decimal(10,2)
,saleMonth varchar(100)
)
Insert Into #empSales VALUES
('James', '1.00', 'January')
,('Richard', '3.28', 'January')
,('Barb', '4.13', 'January')
,('James', '2.00', 'February')
,('Richard', '1.28', 'February')
,('Barb', '4.00', 'February')
,('James', '21.00', 'March')
,('Richard', '13.28', 'March')
,('Barb', '2.13', 'March')
;with MonthAgg as (
select ISNULL(employeename,'Total') employeename
, Sum(Case when saleMonth='January' then saleAmt else null end ) JanuaryAmt
, Sum(Case when saleMonth='February' then saleAmt else null end ) FebruaryAmt
, Sum(Case when saleMonth='March' then saleAmt else null end ) MarchAmt
–….
FROM #empSales
GROUP BY employeeName WITH ROLLUP)
, mycteSum as (
select saleMonth , Sum(saleAmt) Amt
FROM #empSales
GROUP BY saleMonth
)
,mycteYTD as (
select saleMonth , sum(Amt) Over(order by Cast(saleMonth+ ' 01, 1900' as date) ) YTD
from mycteSum)
Select employeeName,JanuaryAmt, FebruaryAmt,MarchAmt from MonthAgg
UNION ALL
Select 'YTD–' as employeeName,
max(Case when saleMonth='January' then YTD else null end ) JanuaryYTD
, max(Case when saleMonth='February' then YTD else null end ) FebruaryYTD
, max(Case when saleMonth='March' then YTD else null end ) MarchYTD
–…
from mycteYTD
drop Table #empSales
view raw gistfile1.sql hosted with ❤ by GitHub