--

CREATE TABLE TwoUnderscores
(
	 Id INT IDENTITY(1,1) PRIMARY KEY
	,Data VARCHAR(1000)
)
GO

INSERT INTO TwoUnderscores VALUES 
('1234567890123_ABCD_AN_XX_SRCV_A'),
('1234567890123_ABCDE_AN_XX_FARB_B'),
('eshfew_12_45')
GO


--

--
 
SELECT Id, 
   SUBSTRING(Data,  CHARINDEX('_',Data,1) + 1,  
             CHARINDEX('_',Data,CHARINDEX('_',Data,1) + 1 ) - CHARINDEX('_',Data,1) - 1) Data
FROM TwoUnderscores


--

--
 
/*------------------------
SELECT Id, 
     SUBSTRING(Data,  CHARINDEX('_',Data,1) + 1,  
          CHARINDEX('_',Data,CHARINDEX('_',Data,1) + 1 ) - CHARINDEX('_',Data,1) - 1) Data
FROM TwoUnderscores
------------------------*/
Id          Data
----------- ------------------
1           ABCD
2           ABCDE
3           12

(3 row(s) affected)
--

--

CREATE TABLE wp_post	
(
	 ID	INT 
	,Title VARCHAR(1000)
	,Staus VARCHAR(1000)
)
GO

INSERT INTO wp_post VALUES
('123',	'Title 123',	'published'),	
('124',	'Title 124',	'published'),
('125',	'Title 125',	'draft'),
('126',	'Title 126',	'published'),
('127',	'Title 127',	'published')
GO

CREATE TABLE wp_postmeta	
(		
	 meta_id INT	
	,post_id INT	
	,meta_key VARCHAR(1000)	 
	,meta_value VARCHAR(1000)
)
GO

INSERT INTO wp_postmeta VALUES
(1	,123	,'DocID'	,'xyz123'),
(2	,123	,'wanted'	,'yes'),
(3	,123	,'another'	,'hello'),		
(4	,124	,'DocID'	,'xyz124'),
(5	,124	,'name'	,'Bill'),
(6	,124	,'another'	,'hello'),
(7	,125	,'DocID'	,'xyz125'),
(8	,125	,'wanted'	,'yes'),
(9	,125	,'another'	,'hello'),
(10	,126	,'DocID'	,'xyz126'),
(11	,126	,'name'	,'Trevor'),
(12	,126	,'another'	,'hello'),
(13	,127	,'MK1'	,'MV1'),
(14	,127	,'name'	,'Trevor'),
(15	,127	,'another'	,'hello')
GO




--
--

--
 

SELECT *
FROM wp_post p
WHERE p.Staus ='published'
AND NOT EXISTS ( SELECT 1 FROM wp_postmeta m WHERE ( m.meta_key = 'wanted' AND m.meta_key IS NOT NULL ) AND m.post_id = p.Id )
AND EXISTS ( SELECT 1 FROM wp_postmeta m WHERE ( m.meta_key = 'DocID' AND m.meta_key IS NOT NULL ) AND m.post_id = p.Id  )

--

--
 

/*------------------------
SELECT *
FROM wp_post p
WHERE p.Staus ='published'
AND NOT EXISTS ( SELECT 1 FROM wp_postmeta m WHERE ( m.meta_key = 'wanted' AND m.meta_key IS NOT NULL ) AND m.post_id = p.Id )
AND EXISTS ( SELECT 1 FROM wp_postmeta m WHERE ( m.meta_key = 'DocID' AND m.meta_key IS NOT NULL ) AND m.post_id = p.Id  )
------------------------*/
ID          Title                                                                                                                                                                                                                                                            Staus
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
124         Title 124                                                                                                                                                                                                                                                        published
126         Title 126                                                                                                                                                                                                                                                        published

(2 row(s) affected)

--

--
 


Select ID FROM wp_post where [staus] = 'Published' AND ID in 
(
Select post_ID from wp_postmeta WHERE meta_key = 'DocID'
EXCEPT
Select post_ID from wp_postmeta WHERE meta_key ='wanted'
)



--

--
 


Select p.* from wp_post p 
INNER JOIN wp_postmeta m1 ON m1.post_id = p.id
LEFT JOIN wp_postmeta m2 ON m2.meta_key ='wanted' and m1.post_id = m2.post_id
WHERE m1.meta_key = 'DocID' AND m2.post_id is null
AND p.Staus = 'Published'


--

--

CREATE TABLE Parts
(
	PartNm VARCHAR(50) NULL,
	Completed BIT NULL
)
GO

INSERT INTO Parts (PartNm,Completed)
VALUES 
('PartA',0),
('PartA',1),
('PartB',0),
('PartC',0),
('PartC',0)

--
--

--
 
SELECT 
		PartNm,
		COUNT(*) NumberOfRecords ,
		CASE WHEN SUM(CAST(Completed AS INT)) = '1' THEN 'C' ELSE '' END Completed
FROM Parts 
GROUP BY PartNm

--

--


--


CREATE TABLE DistinctRecordsMultipleColumns
(
	 c1 VARCHAR(10)
	,c2 VARCHAR(10)
)
GO
 
INSERT INTO DistinctRecordsMultipleColumns VALUES
('a','b'),
('b','a'),
('c','d'),
('e','f'),
('g','g'),
('g','g')
GO


--


--

--
 
;WITH CTE AS 
(
	SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk FROM DistinctRecordsMultipleColumns
)
SELECT c1,c2 FROM 
(
	SELECT a.*
		FROM 
				   CTE a
		INNER JOIN CTE b
			ON a.c1 = b.c2
			and a.c2 = b.c1
		WHERE a.c1 <= a.c2
	UNION ALL
		SELECT k.* 
		FROM 
				  CTE k
		LEFT JOIN CTE k1
		ON k.c1 = k1.c2 AND k1.c1 = k.c2
		WHERE k1.c1 IS NULL
)k
GROUP BY c1,c2
ORDER BY MIN(rnk)

--

--


--

CREATE TABLE MinMax
(
	changeDate date,
	customerid integer,
	sales_before varchar(100),
	sales_after varchar(100)
)
GO


INSERT INTO MinMax 
VALUES 
( '20170706', 7000275, NULL, 'Amy Smith' ) ,
( '20170714', 7000275, 'Amy Smith', 'Unassigned' ) ,
( '20170718', 7000275, 'UnAssigned', 'Amy Smith' ) ,
( '20170706', 7000287, 'Unassigned', 'John West' ) ,
( '20170705', 7000333, NULL, 'Amy Smith' ) ,
( '20170613', 7000333, 'Amy Smith', 'Unassigned' ) ,
( '20170719', 7000333, 'UnAssigned', 'Joe west' ) ,
( '20170726', 7000333, 'Joe west', 'John Doe' )
GO

--


--

--
 
SELECT * FROM 
(
	SELECT Customerid , MIN(changeDate) FirstChangeDate , MAX(changeDate) LastChangeDate , COUNT(*)  NumberChanges FROM MinMax
	GROUP BY customerid
)k
CROSS APPLY
(
	SELECT TOP 1 sales_before FROM MinMax a
	WHERE a.customerid = k.customerid 
	ORDER BY changeDate
)r
CROSS APPLY
(
	SELECT TOP 1 sales_after FROM MinMax a
	WHERE a.customerid = k.customerid 
	ORDER BY changeDate
)r1

--