Sum Datetime Value

  

 create table test(total_hours_worked datetime)
insert into test values 
 ('02:09:33')
,('01:30:00')
,('03:35:00')
select  
 CAST(cast(sum((cast(total_hours_worked as float(53)))) as datetime ) as time(0)) [hh:mm:ss] --precision 15 digits 25-53
--,cast(sum(cast(total_hours_worked as float(24))) as datetime)   --precision 7 digits 1-24 -- or real
from test

--total hours is under 24 hours

drop table test

  

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e40f34b2-6a7d-494a-913a-2a593f114003/sum-a-datetime-field?forum=transactsql

https://docs.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-ver15


A sample: Unpivot with JSON in SQL Server

  


create table ##tempMSRCache (CompanyName varchar(50)
, Standard_A_Status varchar(50)
,Standard_A_Solution varchar(50)
, Standard_B_Status varchar(50)
, Standard_B_Solution varchar(50)
)
 
Insert into ##tempMSRCache values
('CompanyA','in spec','has backups','out of spec','pop mail'),
('CompanyB ','in spec','has backups','inspec','pop mail') 
 
 ;with mycte   (TheKey, TheValue) as (
SELECT [KEY], Value from OpenJson((
select * from ##tempMSRCache  FOR JSON AUTO ))
WHERE type = 5)
 
  
 SELECT
 --columns not in unpivot list
 JSON_VALUE(src.TheValue,'$.CompanyName') CompanyName
--JSON_VALUE(src.TheValue,'$.emp_name') emp_name
-- ,src.TheKey +1 as colOrder, 
 ,unpvt.[Key], unpvt.Value 
 FROM mycte AS src
 CROSS APPLY OpenJson(src.TheValue) AS unpvt  
 WHERE unpvt.Type!=5  and
 --columns not in unpivot list
 unpvt.[Key] not in ('CompanyName')
  
 
 Select * from ##tempMSRCache 
 
drop table ##tempMSRCache

--https://jingyangli.wordpress.com/2016/03/15/dynamic-unpivot-gloabal-temp-table-t-sql/

  

Unpivot with JSON in SQL Server

  

 --SQL Server 2016, 2017,2019 
create table test (emp_id int
,emp_name varchar(30) 
,[17-Sep] decimal(6,2) 
,[17-Oct] decimal(6,2) 
,[17-Nov] decimal(6,2) 
,[17-Dec] decimal(6,2)
,[18-Jan] decimal(6,2) 
,[18-Feb] decimal(6,2) 
,[18-Mar] decimal(6,2) 
,[18-Apr] decimal(6,2) 
,[18-May] decimal(6,2) 
,[18-Jun] decimal(6,2)
,IsActive bit,note varchar(max))
 
 Insert into test 
 values(597,'Bill', 1,2,3,84.01, 0,0,0,95.13,0,0,0,'Regular')
 ,(999,'Joe', 9,8,6,54.01,0,0,0, 95.13,null,0,1,'VIP')

;with mycte   (TheKey, TheValue) as (
SELECT [KEY], Value from OpenJson((
select * from test  FOR JSON AUTO ))
WHERE type = 5)

 
 SELECT
 --columns not in unpivot list
 JSON_VALUE(src.TheValue,'$.emp_id') empid
,JSON_VALUE(src.TheValue,'$.emp_name') emp_name
-- ,src.TheKey +1 as colOrder, 
 ,unpvt.[Key], unpvt.Value 
 FROM mycte AS src
 CROSS APPLY OpenJson(src.TheValue) AS unpvt  
 WHERE unpvt.Type !=5  and 
 --columns not in unpivot list
 unpvt.[Key] not in ('emp_id','emp_name')
 
drop table test 

  

Transpose Matrix with JSON in SQL Server

There is an article to introduce an easy way to use JSON to transpose a matrix with TSQL.(SQL Server 2016 or above)
The article posted a Generic TransposedJSONMatrix Function to demonstrate how to transpose matrix with JSON.

An Easier Way of Pivoting Data in SQL Server


Improved Error message: String or binary data would be truncated.(SQL Server 2019)

Before SQL 2019, it is really frustrated with this error message because you cannot find which column is the offending column.
The error message is informative now in SQL Server 2019, which tells you which column is causing the issue.
Here is an example:

  
 
create table test (id int, col1 varchar(10),
col2 varchar(10),
col3 varchar(10))

insert into test values(1,'abc','cde','fgh')

insert into test
select id, col1,col2,col3+col3+col3+col3 
from test


select * from test
drop table test 

  

/*
Msg 2628, Level 16, State 1, Line 7
String or binary data would be truncated in table ‘test1.dbo.test’,
column ‘col3’. Truncated value: ‘fghfghfghf’.
The statement has been terminated.

*/

If you run the same setup in previous SQL instances, you will get the below message:
/*

(1 row affected)
Msg 8152, Level 16, State 14, Line 7
String or binary data would be truncated.
The statement has been terminated.

*/

Let’s migrate up to SQL Server 2019 to welcome this improvement.

String or Binary data would be truncated: replacing the infamous error 8152


Dynamic Pivot with Case Expression(using for xml or string _agg)

  
 
CREATE TABLE [dbo].[ShoppingDetail](
	[CustomerName] [varchar](256) NOT NULL,
	[ProductCategory] [varchar](256) NOT NULL,
	[TotalAmount] [numeric](18, 2) NOT NULL,
	[Date] [date] NULL
)

INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'Roy Martin', N'Clothing', CAST(2967.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
,(N'Roy Martin', N'Accessories', CAST(2458.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
, (N'Roy Martin', N'Bikes', CAST(52478.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
, (N'Caitlin C Watson', N'Clothing', CAST(3289.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
, (N'Caitlin C Watson', N'Bikes', CAST(75769.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
,(N'Taylor Torres', N'Clothing', CAST(2875.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
,(N'Taylor Torres', N'Accessories', CAST(6567.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
,(N'Taylor Torres', N'Bikes', CAST(46897.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
, (N'Taylor Torres', N'Food Products', CAST(15783.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))

declare @ColumnHeaders NVARCHAR(4000) ;
declare @ColumnHeaders2 NVARCHAR(4000) ;
declare  @sql NVARCHAR(4000);
declare @Param int=7  --day
-- --===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
WHere n<= @Param ) ---Control the total columns list
,dates as (
Select n, DATEADD(day, -n+1, (Select max([date]) from ShoppingDetail) ) dt  
from(Select n from nums) D(n)
)
 
Select @ColumnHeaders = STUFF( (SELECT ',' + 'SUM (Case when [Date]='+ quotename( Convert(char(8),dt,112),'''') +' then  TotalAmount  else null end) as ' + Quotename(Convert(char(10),dt,120),'[')  + char(10)+char(13)
FROM  dates   
Order by n desc
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')   
,
@ColumnHeaders2 = STUFF( (SELECT '+' + 'isnull(SUM (Case when [Date]='+ quotename( Convert(char(8),dt,112),'''') +' then  TotalAmount  else null end),0) '   + char(10)+char(13)
FROM  dates   
Order by n desc
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')   
 
 From dates t1


  
SET @sql = N'Select  Case when [CustomerName] is null then ''Total'' else [CustomerName] end [CustomerName], '
+ @ColumnHeaders + ','+@ColumnHeaders2+' as [total] '
+' from [ShoppingDetail]
Group by grouping sets(CustomerName,())  '
  
--print @sql
 EXEC sp_executesql @sql;
     

drop table [ShoppingDetail]


--SQL Server 2017 or 2019



CREATE TABLE [dbo].[ShoppingDetail](
	[CustomerName] [varchar](256) NOT NULL,
	[ProductCategory] [varchar](256) NOT NULL,
	[TotalAmount] [numeric](18, 2) NOT NULL,
	[Date] [date] NULL
)

INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'Roy Martin', N'Clothing', CAST(2967.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
,(N'Roy Martin', N'Accessories', CAST(2458.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
, (N'Roy Martin', N'Bikes', CAST(52478.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
, (N'Caitlin C Watson', N'Clothing', CAST(3289.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
, (N'Caitlin C Watson', N'Bikes', CAST(75769.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
,(N'Taylor Torres', N'Clothing', CAST(2875.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
,(N'Taylor Torres', N'Accessories', CAST(6567.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
,(N'Taylor Torres', N'Bikes', CAST(46897.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
, (N'Taylor Torres', N'Food Products', CAST(15783.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))

declare @ColumnHeaders NVARCHAR(4000) ;
declare @ColumnHeaders2 NVARCHAR(4000) ;
declare  @sql NVARCHAR(4000);
declare @Param int=7  --day
-- --===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
WHere n<= @Param ) ---Control the total columns list
,dates as (
Select n, DATEADD(day, -n+1, (Select max([date]) from ShoppingDetail) ) dt  
from(Select n from nums) D(n)
)
 
Select @ColumnHeaders = string_agg('SUM (Case when [Date]='+ quotename( Convert(char(8),dt,112),'''') +' then  TotalAmount  else null end) as ' + Quotename(Convert(char(10),dt,120),'[')  , ','+char(10)+char(13))  WITHIN GROUP ( ORDER BY dt)
,@ColumnHeaders2 = string_agg('isnull(SUM (Case when [Date]='+ quotename( Convert(char(8),dt,112),'''') +' then  TotalAmount  else null end),0) ' , '+'+char(10)+char(13))  WITHIN GROUP ( ORDER BY dt)
From dates  

SET @sql = N'Select  Case when [CustomerName] is null then ''Total'' else [CustomerName] end [CustomerName], '
+ @ColumnHeaders + ','+@ColumnHeaders2+' as [total] '
+' from [ShoppingDetail]
Group by grouping sets(CustomerName,())  '
  
 EXEC sp_executesql @sql;
     

drop table [ShoppingDetail]

  

https://forums.asp.net/p/2162731/6289179.aspx?p=True&t=637130305269355006


Split String Based on a Pattern

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1748ac46-6654-40e9-85b9-d3c812532173/split-text-in-query?forum=transactsql

  
 
create table #test(Interaction int,InteractionLog varchar(8000))


insert into #test values(10456
,'12:26:47: Initializing 12:26:47: Offering 12:26:47: ANI:  1111111111 12:26:47: DNIS:  1713 12:26:47: Call answered 12:26:47: Company IVR 12:26:47: Default Schedule 12:26:48: CompanyIVR 12:26:48: Company IVR 12:26:56: PhoneNumEntered:1111111111 12:27:29: AuthViaANI=Success 12:28:24: Entered Workgroup Customer_Service 12:28:24: Offering 12:28:50: Assigned Skill: Customer_Service 12:28:50: ACD - Wait Agent 12:28:50: ACD call waiting for agent 12:29:21: ACD interaction assigned to AnAgent 12:29:21: ACD - Alerting: AnAgent 12:29:22: Sent to user AnAgent 12:29:22: Offering 12:29:23: Sent to station S-STATION-NUM 12:29:23: Connected 12:29:23: ACD interaction connected to AnAgent 12:29:23: ACD - Assigned: AnAgent 12:33:12: Disconnected [Remote Disconnect:Normal, Specified (ISDN Cause Code 31)]')
 
 declare  @search_expression NVARCHAR(4000) = '%_[0-9][0-9]:[0-9][0-9]:[0-9][0-9]:%' 
;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)
    

,positions as 
(
select distinct n = subIdx + n 
, InteractionLog,Interaction	 
 from #test
	CROSS APPLY (select n FROM Nums ) d
        CROSS APPLY (SELECT numRev = LEN(InteractionLog) - n + 1) x
        CROSS APPLY (SELECT subExp = RIGHT(InteractionLog, numRev)) y
        CROSS APPLY (SELECT subIdx = PATINDEX(@search_expression, subExp)) z
    WHERE 
        n BETWEEN 1 AND LEN(InteractionLog)
 AND SubIdx  0

		)
 , 
 finalset as (
Select 
 Interaction, n,
 substring(InteractionLog,isnull(lag(n) over(order by n),1), n-isnull(lag(n) over(order by n) ,0)) InteractionLog 
from positions
union  
Select top 1 Interaction,n,substring(InteractionLog,max(n)Over(Partition by Interaction), len(InteractionLog)) Interaction
from positions
Order by n desc
)


select Interaction,InteractionLog, 
row_number() over(partition by Interaction  order by n ) InteractionSeq
 from finalset

drop table if exists #test

--https://dba.stackexchange.com/questions/41961/how-to-find-all-positions-of-a-string-within-another-string