sqlserver 存储过程
sp_worklog
USE [GESTOR_TAREAS]
GO
/****** Object: StoredProcedure [dbo].[sp_worklog] Script Date: 06/02/2019 8:44:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Josué Javier
-- Create date: 06/02/2019
-- Description: Extracto de tareas realizadas desde dos fechas
-- =============================================
ALTER PROCEDURE [dbo].[sp_worklog]
@user NVARCHAR(20),
@datefrom DATETIME = NULL,
@dateto DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @iduser int;
-- GET ID USER
SELECT @iduser = IDUsuario
FROM USUARIOS
WHERE UPPER(Usuario) = UPPER(@user)
-- DATES OF CURRENT WEEK
IF (@datefrom IS NULL AND @dateto IS NULL)
BEGIN
SET @datefrom = DATEADD(wk,DATEDIFF(wk,0, GETDATE() -1 ),0);
SET @dateto = DATEADD(wk,DATEDIFF(wk,0, GETDATE() -1 ),4);
END
DECLARE @work TABLE(
Nombre NVARCHAR(50)
,Codigo NVARCHAR(10)
,IDIncidencia INT
,Clave INT
,NombreInc NVARCHAR(255)
,Estado NVARCHAR(50)
,TareasRealizadas NVARCHAR(MAX)
,MinutosRealizados INT
,HorasRealizadas DECIMAL(4,2)
,Fecha DATETIME
);
INSERT INTO @work
(Nombre
,Codigo
,IDIncidencia
,Clave
,NombreInc
,Estado
,TareasRealizadas
,MinutosRealizados
,HorasRealizadas
,Fecha)
SELECT
CLIENTES.Nombre as NombreCliente
,CLIENTES.Codigo
,INCIDENCIAS.IDIncidencia
,INCIDENCIAS.Clave
,INCIDENCIAS.Nombre as NombreInc
,ESTADOS_RESOLUCION_INCIDENCIAS.Nombre as Estado
,WORKLOG.TareasRealizadas
,WORKLOG.MinutosRealizados
,((WORKLOG.MinutosRealizados)/CAST(60 AS DECIMAL(4,2)))
,WORKLOG.Fecha
FROM WORKLOG
INNER JOIN INCIDENCIAS ON WORKLOG.IDIncidencia = INCIDENCIAS.IDIncidencia
INNER JOIN ESTADOS_RESOLUCION_INCIDENCIAS ON INCIDENCIAS.IDEstadoResolucionIncidencia = ESTADOS_RESOLUCION_INCIDENCIAS.IDEstadoResolucionIncidencia
INNER JOIN CLIENTES ON INCIDENCIAS.IDCliente = CLIENTES.IDCliente
WHERE WORKLOG.IDUsuario = @iduser and WORKLOG.Fecha >= @datefrom and WORKLOG.Fecha <= @dateto
ORDER BY WORKLOG.Fecha
-- Worklog
SELECT *
FROM @work
-- Time
SELECT SUM(HorasRealizadas) as Horas
FROM @work
END
sc_worklog
DECLARE @from as DATETIME = CAST('28/01/2019 00:00:00:000' as DATETIME)
DECLARE @to as DATETIME = CAST('01/02/2019 23:59:59:000' as DATETIME)
EXEC sp_worklog
@user = 'jcampos',
@datefrom = @from,
@dateto = @tosqlserver 在CSV文件中搜索
在CSV文件中搜索数据
SearchCVFiles
--IF (@@SERVERNAME =N'PDCPDSTDB001')
--RETURN
IF OBJECT_ID('tempdb..#ARCHIVOTEMP') IS NOT NULL
DROP TABLE #ARCHIVOTEMP
IF OBJECT_ID('tempdb..#Files') IS NOT NULL
DROP TABLE #Files
CREATE TABLE #Files(
subdirectory varchar(100) COLLATE Latin1_General_CI_AS,
depth int,
isfile int
)
INSERT INTO #Files
(
subdirectory,
depth,
isfile
)
EXEC Master.dbo.xp_DirTree 'C:\Temp\Duty manger',1,1
IF OBJECT_ID('tempdb..#FilesCSV') IS NOT NULL
DROP TABLE #FilesCSV
CREATE TABLE #FilesCSV(
ID bigint IDENTITY (1,1),
NameFile varchar(500)
)
INSERT INTO #FilesCSV (NameFile)
SELECT LTRIM(RTRIM(f.subdirectory)) FROM #Files f WHERE f.subdirectory LIKE '%.csv' AND f.isfile =1
IF OBJECT_ID('tempdb..#ValuesToSearch') IS NOT NULL
DROP TABLE #ValuesToSearch
CREATE TABLE #ValuesToSearch(
ID bigint IDENTITY (1,1),
PART VARCHAR(50) COLLATE Latin1_General_CI_AS,
ResponseID bigint
)
INSERT INTO #ValuesToSearch
(
--ID - this column value is auto-generated
PART,
ResponseID
)
VALUES
('781974',1472287),
('781972',1472287),
('724441',1471418),
('608135',1471418),
('1078194',1471952),
('1078183',1471952),
('1059885',1471952),
('1065862',1471452),
('1065863',1471452),
('1065868',1471452),
('1146134',1471452),
('1334933',1471452),
('1065859',1471452),
('1167411',1471150),
('1218289',1471150),
('1250736',1471150),
('1246882',1471150),
('1241504',1471150),
('1240496',1471150),
('1231946',1471150),
('1231133',1471150),
('1231131',1471150),
('1231129',1471150),
('1219460',1471150),
('1206676',1471150),
('1206675',1471150),
('1189397',1471150),
('1189396',1471150),
('1189390',1471150),
('1261504',1471150),
('1175884',1471150),
('1316223',1471150),
('1135797',1471150),
('1098542',1471150),
('1189389',1471150),
('1388034',1471150),
('984471',1471150),
('961162',1471150),
('761162',1471150),
('761060',1471150),
('761055',1471150),
('1531606',1471150),
('1519500',1471150),
('1289650',1471150),
('1388044',1471150),
('1264473',1471150),
('1331897',1471150),
('1320776',1471150),
('1305914',1471150),
('1060465',1471150),
('1282865',1471150),
('1276414',1471150),
('1276185',1471150),
('1276184',1471150),
('1451560',1471150),
('818166',1472505),
('1352283',1472505)
IF OBJECT_ID('tempdb..#ValuesFound') IS NOT NULL
DROP TABLE #ValuesFound
CREATE TABLE #ValuesFound(
ID bigint IDENTITY (1,1),
PART VARCHAR(50) COLLATE Latin1_General_CI_AS,
ResponseID bigint,
NameFile varchar(500)
)
DECLARE
@PATH VARCHAR(500),
@CONTAR BIGINT,
@RECONTAR BIGINT=1,
@ARCHIVO NVARCHAR(500),
@PATHRUTA VARCHAR(500),
@SQl nvarchar(max)
SET @PATH='C:\Temp\Duty manger';
SELECT @CONTAR=COUNT(*) FROM #FilesCSV
WHILE (@RECONTAR <=@CONTAR)
BEGIN
CREATE TABLE #ARCHIVOTEMP(
ID bigint IDENTITY (1,1),
PART VARCHAR(50) COLLATE Latin1_General_CI_AS,
ResponseID bigint
)
SELECT @ARCHIVO=convert(nvarchar(500), NameFile) FROM #FilesCSV WHERE ID=@RECONTAR
--SET @PATHRUTA=@PATH+@ARCHIVO
SET @SQL =N'INSERT INTO #ARCHIVOTEMP(PART, ResponseID)
select * from OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\Temp\Duty manger;'',''select PROD_ID
, RESP_ID
from '+ltrim(rtrim(@ARCHIVO))+''')'
EXEC sp_executesql @SQL
INSERT INTO #ValuesFound
(
--ID - this column value is auto-generated
PART,
ResponseID,
NameFile
)
SELECT a.PART, a.ResponseID, @ARCHIVO FROM #ARCHIVOTEMP a INNER JOIN #ValuesToSearch vts ON a.PART COLLATE SQL_Latin1_General_CP1_CI_AS =vts.PART COLLATE SQL_Latin1_General_CP1_CI_AS AND a.ResponseID=vts.ResponseID
DROP TABLE #ARCHIVOTEMP
SET @RECONTAR=@RECONTAR+1
END
SELECT vts.PART, vts.ResponseID, vf.NameFile FROM #ValuesFound vf RIGHT JOIN #ValuesToSearch vts ON vf.PART=VTS.PART AND VF.ResponseID=VTS.ResponseID
DROP TABLE #Files
DROP TABLE #FilesCSV
DROP TABLE #ValuesToSearch
DROP TABLE #ValuesFound
sqlserver Concat行到字符串
concat row to string
select distinct ST2.SubjectID,
substring(
(
select ',' + ST1.StudentName as [text()]
from dbo.Students ST1
where ST1.SubjectID = ST2.SubjectID
order by ST1.SubjectID
for xml path ('')
), 2, 1000) [Students]
from dbo.Students ST2sqlserver 无需管理员即可获取交易
dbcc
declare @OpenTranStatus as table (
ActiveTransaction varchar(25),
Details sql_variant
)
-- Execute the command, putting the results in the table
insert into @OpenTranStatus
exec ('dbcc opentran() with tableresults, no_infomsgs')
select * from @OpenTranStatussqlserver SQL Server 2016+ JSON
SQL Server 2016+ JSON | <br/>网址：[https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-2017]
For Json Support to SQL Server 2014 database in 2016
ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 130
String vs LAX Mode
-- SOURCE: https://bertwagner.com/2017/03/07/the-ultimate-sql-server-json-cheat-sheet/
-- Lax (default: function will return an error if invalid JSON path specified
--lax is the default, so you don't need to be explicitly state it
SELECT JSON_VALUE('{ "Color" : "Red" }', '$.Shape')
-- Output: NULL
SELECT JSON_VALUE('{ "Color" : "Red" }', 'lax $.Shape')
-- Output: NULL
-- Strict: function will return an error if invalid JSON path specified
SELECT JSON_VALUE('{ "Color" : "Red" }', 'strict $.Shape')
-- Output: Property cannot be found on the specified JSON path.
JSON_QUERY
-- SOURCE: https://bertwagner.com/2017/03/07/the-ultimate-sql-server-json-cheat-sheet/
-- See https://gist.github.com/bertwagner/356bf47732b9e35d2156daa943e049e9 for a formatted version of this JSON
DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }] }'
-- This returns NULL because the values of Cars is an array instead of a simple object
SELECT JSON_VALUE(@garage, '$.Cars')
-- Output: NULL
-- Using JSON_QUERY() however returns the JSON string representation of our array object
SELECT JSON_QUERY(@garage, '$.Cars')
-- Output: [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }]
-- This instance of JSON_VALUE() correctly returns a singular scalar value
SELECT JSON_VALUE(@garage, '$.Cars[0].Make')
-- Output: Volkswagen
-- Using JSON_QUERY will not work for returning scalar values - it only will return JSON strings for complex objects
SELECT JSON_QUERY(@garage, '$.Cars[0].Make')
-- Output: NULL
-- This is useful to help filter an array and then extract values with JSON_VALUE():
-- See https://gist.github.com/bertwagner/356bf47732b9e35d2156daa943e049e9 for a formatted version of this JSON
DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }] }'
-- We use JSON_QUERY to get the JSON representation of the Cars array
SELECT JSON_QUERY(@garage, '$.Cars')
-- Output: [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }]
-- If we combine it with JSON_VALUE we can then pull out specific scalar values
SELECT JSON_VALUE(JSON_QUERY(@garage, '$.Cars') , '$[0].Make')
-- Output: Volkswagen
Open Json
-- SOURCE: https://bertwagner.com/2017/03/07/the-ultimate-sql-server-json-cheat-sheet/
-- See https://gist.github.com/bertwagner/356bf47732b9e35d2156daa943e049e9 for a formatted version of this JSON
DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }] }'
SELECT * FROM OPENJSON(@garage, '$.Cars') -- Displaying the values of our "Cars" array. We additionally get the order of the JSON objects outputted in the "key" column and the JSON object datatype in the "type" column
/* Output:
key value type
------ ------------------------------------------------------------------------------------------------------------------------------------ ----
0 { "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" } 5
1 { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" } 5
*/
SELECT * FROM OPENJSON(@garage, '$.Cars[0]') -- Specifying the first element in our JSON array. JSON arrays are zero-index based
/* Output:
key value type
---------------- ------------------------------------- ----
Make Volkswagen 1
Model { "Base": "Golf", "Trim": "GL" } 5
Year 2003 2
PurchaseDate 2006-10-05T00:00:00.000Z 1
*/
SELECT * FROM OPENJSON(@garage, '$.Cars[0].Model') -- Pulling the Model property from the first element in our Cars array
/* Output:
key value type
------- ------- ----
Base Golf 1
Trim GL 1
*/
-- See https://gist.github.com/bertwagner/356bf47732b9e35d2156daa943e049e9 for a formatted version of this JSON
DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }] }'
-- Here we retrieve the Make of each vehicle in our Cars array
SELECT JSON_VALUE(value, '$.Make') FROM OPENJSON(@garage, '$.Cars')
/* Output:
------------
Volkswagen
Subaru
*/
-- Parsing and converting some JSON dates to SQL DateTime2
SELECT CAST(JSON_VALUE(value, '$.PurchaseDate') as datetime2) FROM OPENJSON(@garage, '$.Cars')
/* Output:
---------------------------
2006-10-05 00:00:00.0000000
2015-08-18 00:00:00.0000000
*/
-- We can also format the output schema of a JSON string using the WITH option. This is especially cool because we can bring up values from sub-arrays (see Model.Base and Model.Trim) to our top-level row result
SELECT * FROM OPENJSON(@garage, '$.Cars')
WITH (Make varchar(20) 'strict $.Make',
ModelBase nvarchar(100) '$.Model.Base',
ModelTrim nvarchar(100) '$.Model.Trim',
Year int '$.Year',
PurchaseDate datetime2 '$.PurchaseDate')
/* Output:
Make ModelBase Year PurchaseDate
-------------- ----------- ----------- ---------------------------
Volkswagen Golf 2003 2006-10-05 00:00:00.0000000
Subaru Impreza 2016 2015-08-18 00:00:00.0000000
*/
Creating Json - JSON Auto
-- SOURCE: https://bertwagner.com/2017/03/07/the-ultimate-sql-server-json-cheat-sheet/
-- Automatically creates a JSON string from a SELECT statement. Quick and dirty.
-- Create our table with test data
DROP TABLE IF EXISTS ##Garage;
CREATE TABLE ##Garage
(
Id int IDENTITY(1,1),
Make varchar(100),
BaseModel varchar(50),
Trim varchar(50),
Year int,
PurchaseDate datetime2
);
INSERT INTO ##Garage VALUES ('Volkswagen', 'Golf', 'GL', 2003, '2006-10-05');
INSERT INTO ##Garage VALUES ('Subaru', 'Impreza', 'Premium', 2016, '2015-08-18');
-- Take a look at our data
SELECT * FROM ##Garage;
-- AUTO will format a result into JSON following the same structure of the result set
SELECT Make, BaseModel, Trim, Year, PurchaseDate
FROM ##Garage
FOR JSON AUTO;
-- Output: [{"Make":"Volkswagen","BaseModel":"Golf","Trim":"GL","Year":2003,"PurchaseDate":"2006-10-05T00:00:00"},{"Make":"Subaru","BaseModel":"Impreza","Trim":"Premium","Year":2016,"PurchaseDate":"2015-08-18T00:00:00"}]
-- Using aliases will rename JSON keys
SELECT Make AS [CarMake]
FROM ##Garage
FOR JSON AUTO;
-- Output: [{"CarMake":"Volkswagen"},{"CarMake":"Subaru"}]
-- Any joined tables will get created as nested JSON objects. The alias of the joined tables becomes the name of the JSON key
SELECT g1.Make, Model.BaseModel as Base, Model.Trim, g1.Year, g1.PurchaseDate
FROM ##Garage g1
INNER JOIN ##Garage Model on g1.Id = Model.Id
FOR JSON AUTO;
-- Output: [{"Make":"Volkswagen","Year":2003,"PurchaseDate":"2006-10-05T00:00:00","Model":[{"Base":"Golf","Trim":"GL"}]},{"Make":"Subaru","Year":2016,"PurchaseDate":"2015-08-18T00:00:00","Model":[{"Base":"Impreza","Trim":"Premium"}]}]
-- Finally we can encapsulate our entire JSON result in a parent element by specifiying the ROOT option
SELECT Make, BaseModel, Trim, Year, PurchaseDate
FROM ##Garage
FOR JSON AUTO, ROOT('Cars');
-- Output: {"Cars":[{"Make":"Volkswagen","BaseModel":"Golf","Trim":"GL","Year":2003,"PurchaseDate":"2006-10-05T00:00:00"},{"Make":"Subaru","BaseModel":"Impreza","Trim":"Premium","Year":2016,"PurchaseDate":"2015-08-18T00:00:00"}]}
Creating Json - JSON Path
-- SOURCE: https://bertwagner.com/2017/03/07/the-ultimate-sql-server-json-cheat-sheet/
-- PATH will format a result using dot syntax in the column aliases. Here's an example with just default column names
SELECT Make, BaseModel, Trim, Year, PurchaseDate
FROM ##Garage
FOR JSON PATH, ROOT('Cars');
-- Output: {"Cars":[{"Make":"Volkswagen","BaseModel":"Golf","Trim":"GL","Year":2003,"PurchaseDate":"2006-10-05T00:00:00"},{"Make":"Subaru","BaseModel":"Impreza","Trim":"Premium","Year":2016,"PurchaseDate":"2015-08-18T00:00:00"}]}
-- And here is the same example, just assigning aliases to define JSON nested structure
SELECT Make, BaseModel as [Model.Base], Trim AS [Model.Trim], Year, PurchaseDate
FROM ##Garage
FOR JSON PATH, ROOT('Cars');
-- Output: {"Cars":[{"Make":"Volkswagen","Model":{"Base":"Golf","Trim":"GL"},"Year":2003,"PurchaseDate":"2006-10-05T00:00:00"},{"Make":"Subaru","Model":{"Base":"Impreza","Trim":"Premium"},"Year":2016,"PurchaseDate":"2015-08-18T00:00:00"}]}
-- We can actually go multiple levels deep with this type of alias dot notation nesting
SELECT Make, BaseModel as [Model.Base], Trim AS [Model.Trim], 'White' AS [Model.Color.Exterior], 'Black' AS [Model.Color.Interior], Year, PurchaseDate
FROM ##Garage
FOR JSON PATH, ROOT('Cars');
-- Output: {"Cars":[{"Make":"Volkswagen","Model":{"Base":"Golf","Trim":"GL","Color":{"Exterior":"White","Interior":"Black"}},"Year":2003,"PurchaseDate":"2006-10-05T00:00:00"},{"Make":"Subaru","Model":{"Base":"Impreza","Trim":"Premium","Color":{"Exterior":"White","Interior":"Black"}},"Year":2016,"PurchaseDate":"2015-08-18T00:00:00"}]}
-- Concatenating data rows with UNION or UNION ALL just adds the row as a new element as part of the JSON array
SELECT Make, BaseModel AS [Model.Base], Trim AS [Model.Trim], Year, PurchaseDate
FROM ##Garage WHERE Id = 1
UNION ALL
SELECT Make, BaseModel, Trim, Year, PurchaseDate
FROM ##Garage WHERE Id = 2
FOR JSON PATH, ROOT('Cars');
-- Output: {"Cars":[{"Make":"Volkswagen","Model":{"Base":"Golf","Trim":"GL"},"Year":2003,"PurchaseDate":"2006-10-05T00:00:00"},{"Make":"Subaru","Model":{"Base":"Impreza","Trim":"Premium"},"Year":2016,"PurchaseDate":"2015-08-18T00:00:00"}]}
-- We can even include our FOR JSON in our SELECT statement to generate JSON strings for each row of our result set
SELECT g1.*, (SELECT Make, BaseModel AS [Model.Base], Trim AS [Model.Trim], Year, PurchaseDate FROM ##Garage g2 WHERE g2.Id = g1.Id FOR JSON PATH, ROOT('Cars')) AS [Json]
FROM ##Garage g1
/* Output:
Id Make BaseModel Trim Year PurchaseDate Json
--- ------------- ------------ --------- ------- --------------------------- --------------------------------------------------------------------------------------------------------------------------
1 Volkswagen Golf GL 2003 2006-10-05 00:00:00.0000000 {"Cars":[{"Make":"Volkswagen","Model":{"Base":"Golf","Trim":"GL"},"Year":2003,"PurchaseDate":"2006-10-05T00:00:00"}]}
2 Subaru Impreza Premium 2016 2015-08-18 00:00:00.0000000 {"Cars":[{"Make":"Subaru","Model":{"Base":"Impreza","Trim":"Premium"},"Year":2016,"PurchaseDate":"2015-08-18T00:00:00"}]}
*/
Modify Json
-- See https://gist.github.com/bertwagner/356bf47732b9e35d2156daa943e049e9 for a formatted version of this JSON
DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }] }'
-- I upgraded some features in my Volkswagen recently, technically making it equivalent to a "GLI" instead of a "GL".
-- Let's update our JSON using JSON_MODIFY:
SET @garage = JSON_MODIFY(@garage, '$.Cars[0].Model.Trim', 'GLI')
SELECT @garage
-- Output: { "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GLI" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }] }
-- ADD
-- See https://gist.github.com/bertwagner/356bf47732b9e35d2156daa943e049e9 for a formatted version of this JSON
DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GLI" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }] }'
-- I decided to sell my Golf. Let's add a new "SellDate" property to the JSON saying when I sold my Volkswagen.
-- If we use strict mode, you'll see we can't add SellDate because the key never existed before
--SELECT JSON_MODIFY(@garage, 'append strict $.Cars[0].SellDate', '2017-02-17T00:00:00.000Z')
-- Output: Property cannot be found on the specified JSON path.
-- However, in lax mode (default), we have no problem adding the SellDate
SELECT JSON_MODIFY(@garage, 'append lax $.Cars[0].SellDate', '2017-02-17T00:00:00.000Z')
-- Output: { "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GLI" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" ,"SellDate":["2017-02-17T00:00:00.000Z"]}, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }] }
-- After selling my Golf, I bought another car a few days later: A new Volkswagen Golf GTI. Let's add it to our garge:
-- Note the use of JSON_QUERY; this is so our string is interpreted as a JSON object instead of a plain old string
SET @garage = JSON_MODIFY(@garage, 'append $.Cars', JSON_QUERY('{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GTI" }, "Year": 2017, "PurchaseDate": "2017-02-19T00:00:00.000Z" }'))
SELECT @garage;
-- Output: { "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GLI" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" },{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GTI" }, "Year": 2017, "PurchaseDate": "2017-02-19T00:00:00.000Z" }] }
-- DELETE
DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GLI" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z", "SellDate" : "2017-02-17T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" },{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GTI" }, "Year": 2017, "PurchaseDate": "2017-02-19T00:00:00.000Z" }] }'
-- Let's remove the PurchaseDate property on my original Volkswagen Golf since it's not relevant anymore:
SET @garage = JSON_MODIFY(@garage, '$.Cars[0].PurchaseDate', NULL)
SELECT @garage
-- Output: { "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GLI" }, "Year": 2003, "SellDate" : "2017-02-17T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" },{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GTI" }, "Year": 2017, "PurchaseDate": "2017-02-19T00:00:00.000Z" }] }
-- DELETE FROM ARRAY
-- https://connect.microsoft.com/SQLServer/feedback/details/3120404/sql-modify-json-null-delete-is-not-consistent-between-properties-and-arrays
DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GLI" }, "Year": 2003, "SellDate" : "2017-02-17T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" },{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GTI" }, "Year": 2017, "PurchaseDate": "2017-02-19T00:00:00.000Z" }] }'
-- I realize it's not worth keeping the original Volkswagen in my @garage data any longer, so let's completely remove it.
-- Note, if we use NULL as per the MSDN documentation, we don't actually remove the first car element of the array - it just gets replaced with NULL
-- This is problematic if we expect the indexes of our array to shift by -1.
SELECT JSON_MODIFY(@garage, '$.Cars[0]', NULL)
-- Output: { "Cars": [null, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" },{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GTI" }, "Year": 2017, "PurchaseDate": "2017-02-19T00:00:00.000Z" }] }
-- To truly delete it (and not have the NULL appear as the first item in the array) we have to convert to a rowset, select everything that's not the first row, aggregate the rows into a string (UGH) and then recreate as JSON.
-- This is incredibly ugly. The STREAM_AGG() function in SQL vNext should make it a little cleaner, but why doesn't the JSON_MODIFY NULL syntax just get rid of the element in the array?
-- I have opened a Microsoft connect issue for this here: https://connect.microsoft.com/SQLServer/feedback/details/3120404
SELECT JSON_QUERY('{ "Cars" : [' +
STUFF((
SELECT ',' + value
FROM OPENJSON(@garage, '$.Cars')
WHERE [key] <> 0
FOR XML PATH('')), 1, 1, '') + '] }')
-- Output: { "Cars" : [{ "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" },{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GTI" }, "Year": 2017, "PurchaseDate": "2017-02-19T00:00:00.000Z" }] }
Json Performace Tuning
-- SOURCE: https://bertwagner.com/2017/03/07/the-ultimate-sql-server-json-cheat-sheet/
-- ADD A COMPUTED COLUMN
-- Car data source: https://github.com/arthurkao/vehicle-make-model-data
IF OBJECT_ID('dbo.Cars') IS NOT NULL
BEGIN
DROP TABLE dbo.Cars;
END
CREATE TABLE dbo.Cars
(
Id INT IDENTITY(1,1),
CarDetails NVARCHAR(MAX)
);
-- See https://gist.github.com/bertwagner/1df2531676112c24cd1ab298fc750eb2 for the full untruncated version of this code
DECLARE @cars nvarchar(max) = '[ {"year":2001,"make":"ACURA","model":"CL"}, {"year":2001,"make":"ACURA","model":"EL"},...]';
INSERT INTO dbo.Cars (CarDetails)
SELECT value FROM OPENJSON(@cars, '$');
SELECT * FROM dbo.Cars;
/*
Output:
Id CarDetails
----------- ----------------------------------------------
1 {"year":2001,"make":"ACURA","model":"CL"}
2 {"year":2001,"make":"ACURA","model":"EL"}
3 {"year":2001,"make":"ACURA","model":"INTEGRA"}
...
*/
-- Remember to turn on "Include Actual Execution Plan" for all of these examples
-- Before we add any computed columns/indexes, let's see our execution plan for our SQL statement with a JSON predicate
SELECT * FROM dbo.Cars WHERE JSON_VALUE(CarDetails, '$.model') = 'Golf'
/*
Output:
Id CarDetails
----------- --------------------------------------------------
1113 {"year":2001,"make":"VOLKSWAGEN","model":"GOLF"}
2410 {"year":2002,"make":"VOLKSWAGEN","model":"GOLF"}
3707 {"year":2003,"make":"VOLKSWAGEN","model":"GOLF"}
...
*/
-- The execution plan shows a Table Scan, not very efficient
-- We can now add a non-persisted computed column for our "model" JSON property.
ALTER TABLE dbo.Cars
ADD CarModel AS JSON_VALUE(CarDetails, '$.model');
-- We add the distinct to avoid parameter sniffing issues.
-- Our execution plan now shows the extra computation that is occuring for every row of the table scan.
SELECT DISTINCT * FROM dbo.Cars WHERE JSON_VALUE(CarDetails, '$.model') = 'Golf'
SELECT DISTINCT * FROM dbo.Cars WHERE CarModel = 'Golf'
-- ADD AN INDEX TO THE COMPUTED COLUMN
-- Add an index onto our computed column
CREATE CLUSTERED INDEX CL_CarModel ON dbo.Cars (CarModel)
-- Check the execution plans again
SELECT DISTINCT * FROM dbo.Cars WHERE JSON_VALUE(CarDetails, '$.model') = 'Golf'
SELECT DISTINCT * FROM dbo.Cars WHERE CarModel = 'Golf'
-- We now get index seeks!
-- Indexed computed column returns results in ~1ms
SELECT * FROM dbo.Cars WHERE CarModel = 'Golf'
-- PERFORMANCE TEST
-- Turn on stats and see how long it takes to parse the ~20k JSON array elements
SET STATISTICS TIME ON
-- Test #1
-- Test how long it takes to parse each property from all ~20k elements from the JSON array
-- SQL returns this query in ~546ms
SELECT JSON_VALUE(value, '$.year') AS [Year], JSON_VALUE(value, '$.make') AS Make, JSON_VALUE(value, '$.model') AS Model FROM OPENJSON(@cars, '$')
-- Test #2
-- Time to deserialize and query just Golfs without computed column + index
-- This takes ~255ms in SQL Server
SELECT * FROM OPENJSON(@cars, '$') WHERE JSON_VALUE(value, '$.model') = 'Golf'
-- Test #3
-- Time it takes to compute the same query for Golf's with a computed column and clustered index
-- This takes ~1ms on SQL Server
SELECT * FROM dbo.Cars WHERE CarModel = 'Golf'
-- Test #4
-- Serializing data on SQL Server takes ~110ms
SELECT * FROM dbo.Cars FOR JSON AUTO
-- What about serializing/deserializing smaller JSON datasets?
-- Let's create our smaller set
DECLARE @carsSmall nvarchar(max) = '[ {"year":2001,"make":"ACURA","model":"CL"}, {"year":2001,"make":"ACURA","model":"EL"}, {"year":2001,"make":"ACURA","model":"INTEGRA"}, {"year":2001,"make":"ACURA","model":"MDX"}, {"year":2001,"make":"ACURA","model":"NSX"}, {"year":2001,"make":"ACURA","model":"RL"}, {"year":2001,"make":"ACURA","model":"TL"}]';
-- Test #5
-- Running our query results in the data becoming deserialized in ~0ms
SELECT JSON_VALUE(value, '$.year') AS [Year], JSON_VALUE(value, '$.make') AS Make, JSON_VALUE(value, '$.model') AS Model FROM OPENJSON(@carsSmall, '$')
--30ms in sql
-- Test #6
-- And serialized in ~0ms
SELECT TOP 7 * FROM dbo.Cars FOR JSON AUTO
Update Specific Object
-- https://stackoverflow.com/questions/49828897/update-an-existing-json-value-inside-a-json-array-in-sql
-- SOURCE: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=b0f6e2e2e7f442cc95adb7f2f8e2730d
DECLARE @T TABLE(i INT, c NVARCHAR(MAX) CHECK (ISJSON(c)> 0 ));
INSERT INTO @T VALUES (1, '[{"id":"101","name":"John"}, {"id":"102","name":"peter"}]');
SELECT * FROM @T;
-- OUTPUT
-- 1 [{"id":"101","name":"John"}, {"id":"102","name":"peter"}]
SELECT * FROM @T
CROSS APPLY OPENJSON(c) s
WHERE i = 1
AND JSON_VALUE(s.value, '$.id')=102;
-- OUTPUT
-- i | c | key | value | type
-- 1 [{"id":"101","name":"John"}, {"id":"102","name":"peter"}] 1 {"id":"102","name":"peter"} 5
WITH CTE AS (
SELECT * FROM @T
CROSS APPLY OPENJSON(c) s
WHERE i = 1
AND JSON_VALUE(s.value, '$.id')=102
)
UPDATE CTE
SET c = JSON_MODIFY(c, '$[' + CTE.[key] + '].name', 'Joe');
SELECT * FROM @T;
-- OUTPUT
-- 1 [{"id":"101","name":"John"}, {"id":"102","name":"Joe"}]sqlserver SSIS工作经历
SSIS Job History
SET NOCOUNT ON;
SELECT DISTINCT sj.name,
sh.run_date,
sh.step_name,
STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(sh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time',
STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS) ',
CASE sh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In Progress'
END 'Status'
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobhistory sh
ON sj.job_id = sh.job_id
WHERE sh.run_status=4
order by run_date desc, run_time DESC
sqlserver SQL备份还原进度
sql-progress
SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE') sqlserver CLICKHOUSE
CLICKHOUSE
GOODS
CREATE TABLE IF NOT EXISTS goods
(
eventdate DateTime DEFAULT now(),
sku UInt64,
pharmacynumber UInt16,
skuguid Nullable(UInt64) DEFAULT CAST(NULL, 'Nullable(UInt64)'),
description Nullable(String) DEFAULT CAST(NULL, 'Nullable(String)'),
producer Nullable(String) DEFAULT CAST(NULL, 'Nullable(String)'),
quantity Nullable(UInt16) DEFAULT CAST(NULL, 'Nullable(UInt16)'),
multiplier Nullable(UInt16) DEFAULT CAST(1, 'Nullable(UInt16)'),
price Nullable(Float32) DEFAULT CAST(NULL, 'Nullable(Float32)'),
ean13 Nullable(Int64) DEFAULT CAST(NULL, 'Nullable(Int64)'),
expiration Nullable(Date) DEFAULT CAST(NULL, 'Nullable(Date)'),
EventDate DateTime ALIAS eventdate,
EVENTDATE DateTime ALIAS eventdate,
event_date DateTime ALIAS eventdate,
EVENT_DATE DateTime ALIAS eventdate,
Sku UInt64 ALIAS sku,
SKU UInt64 ALIAS sku,
pharmacy_number UInt16 ALIAS pharmacynumber,
PHARMACY_NUMBER UInt16 ALIAS pharmacynumber,
PharmacyNumber UInt16 ALIAS pharmacynumber,
SkuGuid Nullable(UInt64) ALIAS skuguid,
SKUGUID Nullable(UInt64) ALIAS skuguid,
sku_guid Nullable(UInt64) ALIAS skuguid,
SKU_GUID Nullable(UInt64) ALIAS skuguid,
Description Nullable(String) ALIAS description,
DESCRIPTION Nullable(String) ALIAS description,
Producer Nullable(String) ALIAS producer,
PRODUCER Nullable(String) ALIAS producer,
Quantity Nullable(UInt16) ALIAS quantity,
QUANTITY Nullable(UInt16) ALIAS quantity,
Multiplier Nullable(UInt16) ALIAS multiplier,
MULTIPLIER Nullable(UInt16) ALIAS multiplier,
Price Nullable(Float32) ALIAS price,
PRICE Nullable(Float32) ALIAS price,
EAN13 Nullable(Int64) ALIAS ean13,
Expiration Nullable(Date) ALIAS expiration,
EXPIRATION Nullable(Date) ALIAS expiration
)
ENGINE = ReplacingMergeTree(eventdate)
PARTITION BY pharmacynumber
ORDER BY (pharmacynumber, sku)
SETTINGS index_granularity = 8192
PRICE
CREATE VIEW IF NOT EXISTS price
(
eventdate DateTime,
sku UInt64,
pharmacynumber UInt16,
skuguid Nullable(UInt64),
`unhex(description)` Nullable(String),
`unhex(producer)` Nullable(String),
quantity Nullable(UInt16),
multiplier Nullable(UInt16),
price Nullable(Float32),
ean13 Nullable(Int64),
expiration Nullable(Date)
) AS
SELECT
eventdate,
sku,
pharmacynumber,
skuguid,
unhex(description),
unhex(producer),
quantity,
multiplier,
price,
ean13,
expiration
FROM goodssqlserver Vbox查询
Vbox Queries
--- Not Recording Sites
SELECT
DISTINCT T0.Vboxid
--T0.*
FROM
lbtVboxAttendance T0
INNER JOIN
lbtSiteMas T1
ON T0.Vboxid = T1.VBoxId
WHERE EventCode = 'RECORD' AND DATEDIFF(n, Attime, GETDATE()) > 10
AND qStatus = 1 AND s_Status = 1 AND BankCode IN ('25', '16')
--SELECT * FROM lbtSiteMas WHERE s_Name LIKE '%114309%' 11013307956sqlserver 创建日期时间表
从一个表SQl服务器创建日期时间轴。
timeline.sql
with
BPWithDates as
(
select distinct Tech_Operand_Ref_rk as date_rk, EffectiveFrom_dt as datefrom
from DWH_CORE.dbo.Tech_InstallationMetrics im
left join DWH_CORE.dbo.Con_CoreContract c
on im.Tech_Installation_id = c.Tech_Installation_id
--where c.Con_CoreContract_rk = 17709556
union
select distinct Tech_Operand_Ref_rk as date_rk, case WHEN EffectiveTo_dt = '9999.12.31' THEN '9999.12.31' ELSE dateadd(dd,1, EffectiveTo_dt ) END
from DWH_CORE.dbo.Tech_InstallationMetrics im
left join DWH_CORE.dbo.Con_CoreContract c
on im.Tech_Installation_id = c.Tech_Installation_id
--where c.Con_CoreContract_rk = 17709556
), bp as
(
select date_rk, datefrom, (row_number() over(partition by date_rk order by datefrom asc) - 1) as RN
from BPWithDates
), bpWithStartDatesAndRowNums as
(
select nt.date_rk, nt.datefrom, row_number() over(partition by nt.date_rk order by nt.datefrom asc) as ORN
from BPWithDates nt
), BPWithStartEndDates as
(
select bps.date_rk, bps.datefrom, case when bp.datefrom = '9999.12.31' then bp.datefrom else dateadd(dd, -1, bp.datefrom) end as dateto
from bpWithStartDatesAndRowNums bps
left outer join bp bp
on bps.date_rk= bp.date_rk
and bp.RN = bps.ORN
where case when bp.datefrom = '9999.12.31' then bp.datefrom else dateadd(dd, -1, bp.datefrom) end is not null
)
Select *
into #tempdates
from BPWithStartEndDates