This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE table #TBL | |
(DATE1 DATETIME | |
,ID INT | |
,Col1 Varchar(5) | |
,Col2 Varchar(5) | |
) | |
Insert into #TBL values | |
('12/1/22', 1001, 'abc', 'df') | |
,('12/5/22', 1001, 'abc', 'def') | |
,('12/5/22', 1002, 'dcb', 'ef') |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create table #temp (ID varchar(50) | |
,DateTime_ dateTime) | |
insert into #temp (ID,DateTime_) values ('1001','2022-01-01 06:01:24.000') | |
,('1002','2022-01-01 06:07:24.000') | |
,('1003','2022-01-01 06:15:24.000') | |
, ('1004','2022-01-01 06:20:24.000') | |
,('1005','2022-01-01 06:39:24.000') | |
, ('1006','2022-01-01 06:46:24.000') | |
, ('1007','2022-01-01 16:59:24.000') |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Create table test(id INT, dates VARCHAR(200)) | |
INSERT INTO test (id, dates) | |
VALUES (1001, '5,10,15,20,25') | |
, (1002,'9,12,12,25,30') | |
Declare @GivenMonth varchar(8)='JUN-2022' | |
;with mycte as ( | |
select id ,'['+ dates +']' jsondates | |
from test) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create table FruitsTest (theDate date,Apple int, Kiwi int, Grape int, Orange int, Banana int) | |
Insert into FruitsTest values | |
('2022-06-10',2,2,4,3,1), | |
('2022-06-11',3,2,5,2,1) | |
;with mycte (TheKey, TheValue) as ( | |
SELECT [KEY], Value from OpenJson(( | |
select * from FruitsTest FOR JSON AUTO )) | |
WHERE type = 5) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create table dbo.myTable | |
( | |
ItemName varchar(30), | |
Qty int, Rate int, [Time] int | |
) | |
insert into myTable | |
values | |
( 'A1',5,0 ,null), | |
( 'A2',23,4 ,null), |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE [dbo].[RentalInvoice]( | |
[InvoiceId] [int] IDENTITY(1,1) NOT NULL, | |
[InvoiceNo] [varchar](15) NOT NULL, | |
[InvoiceDate] [date] NOT NULL, | |
[RentalId] [int] NOT NULL, | |
[StartDate] [date] NOT NULL, | |
[EndDate] [date] NOT NULL, | |
CONSTRAINT [PK_RentalInvoice] PRIMARY KEY CLUSTERED | |
( | |
[InvoiceId] ASC |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create table callTable (DateCol date, Calls int) | |
insert into callTable values | |
('1/1/2022',5), | |
('1/2/2022',7), | |
('1/3/2022',9), | |
('1/4/2022',4), | |
('1/5/2022',2), | |
('1/6/2022',5), | |
('1/7/2022',6), |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--Yitzhak Khabinsky's solution | |
--https://docs.microsoft.com/en-us/answers/questions/845428/need-sql-help-to-split-comma-separated-value-into.html | |
create table #test | |
(col VARCHAR(2000)) | |
insert into #test | |
values('2292,0192,20211018,0778988158494,0000600,150,000000000000001') | |
,('2292,14642,20210923,630996500903,0000500,061,1') | |
,('2292,4164,20211021,4006592590505,0002180,150,000000000000001,EUR,21,0000000,501,1,21-01917') | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create table datatable (TrendType varchar(12), date date, sales int) | |
insert into datatable values ('Monthly','5/1/2021',1234), | |
('Monthly','3/1/2021',1234), | |
('Monthly','2/1/2022',1234), | |
('Monthly','5/1/2022',1234), | |
('Monthly','8/1/2021',1234), | |
('Monthly','8/1/2022',1234), | |
('Monthly','12/1/2021',1234), | |
('Monthly','11/1/2022',1234) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Declare @JSON varchar(max) | |
SELECT @JSON=BulkColumn | |
FROM OPENROWSET (BULK 'C:\temp\datafile22Copy.JSON', SINGLE_CLOB) import | |
--datafile22Copy.JSON content is the same in the following string | |
*/ | |
declare @json varchar(max)='{ "metadata" : {"page" : ["LineOfBusiness","GeoSubMarket","TransportType","ContractType","Entity_MR","SourceType","Products","MRCTiers","DesignatedMarketArea"],"column" : ["ScenarioSource"],"row" : ["Period","LiabilityType","SalesChannel","Measures","ReptCustSegCd","SubscriptionDevices"]} | |
,"data" : [["","","","","","","TOTAL_ACT"],["01-25-22","TOTSIGCON","BRANDED","GROSSUPG","S604","VOICEDEV","1.0"],["01-25-22","TOTSIGCON","BRANDED","GROSSUPG","X013","VOICEDEV","22400.0"],["01-25-22","TOTSIGCON","BRANDED","GROSSUPG","X013","NONVOICEDEV","708.0"],["01-25-22","TOTSIGCON","BRANDED","GROSSUPG","BUABSBMM","VOICEDEV","1.0"]] }' |
NewerOlder