Posted: September 22, 2017 | Author: Jingyang Li | Filed under: Uncategorized |
--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
Posted: September 14, 2017 | Author: Jingyang Li | Filed under: Uncategorized |
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>1 then 'First_'
when rnFirst>1 and rnLast=1 then 'Last_'
End +CheckedType as checkCol
from mycte1
Order by CheckedTime
drop table #temp
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c9ca548b-af34-4726-9ffe-92351369c981/select-in-and-out-time-based-on-flag?forum=transactsql
Posted: September 14, 2017 | Author: Jingyang Li | Filed under: Uncategorized |
--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