asked on

MS T-SQL data validation

Hi,

I have a table of data that looks like the following

Storage_Container Folder DataField1 DataField2
----–---------—–------------------
Storage1, Folder1, Data1, Data2...
Storage2,Folder1,Data1,Data2...
Storage3,Folder1,Data1,Data2...

So from above I need to make sure that all the data field columns DataField1 and DataField2 have an entry for each storage_container above as well as for each Folder.
Also note that the data fields match as well.

I don't know if this is possible but is there some MS T-SQL to perform this type of validation.
I
Thanks Ward

Pawan Kumar

can you pls provide some sample data and the expected output. I can help you writing the query.

Also please add MS SQL Server as topic for future reads.

ste5an

Please rephrase your question. Any context?

Cause otherwise its pretty simply: You use constraints for this.

CREATE TABLE SampleTable
    (
        Storage_Container NVARCHAR(255) NOT NULL ,
        Folder NVARCHAR(255) NOT NULL ,
        DataField1 NVARCHAR(255) NOT NULL ,
        DataField2 NVARCHAR(255) NOT NULL ,
        CONSTRAINT PK_SampleTable
            PRIMARY KEY ( Storage_Container ) ,
        CONSTRAINT CK_SampleTable_Storage_Container CHECK ( Storage_Container <> N'' ) ,
        CONSTRAINT CK_SampleTable_Folder CHECK ( Folder <> N'' ) ,
        CONSTRAINT CK_SampleTable_DataField1 CHECK ( DataField1 <> N'' ) ,
        CONSTRAINT CK_SampleTable_DataField2 CHECK ( DataField2 <> N'' )
    );

Open in new window

The above ensures that you enter values (non-empty strings) in all columns, which would satisfy your first two requirements. But what does the third mean?

Also note that the data fields match as well.

D'oh?

whorsfall

ASKER

Hi,

Thanks for the response so let's say the data looked like

Storage_Container Folder DataField1 DataField2
----–---------—–------------------
Storage1, Folder1, Data1, Data2
Storage2,Folder1,Data1,Data2
Storage3,Folder1,Data1,Data2
Storage1,Folder1,Data2,Data3

Then would like to see output

Data1,Data2,Valid
Data2,Data3,Invalid

Thanks

Ward

ste5an

What is the decision criteria for your validation?

Pawan Kumar

Please try full solution -

CREATE TABLE stores
(
	 Storage_Container VARCHAR(15)
	,Folder  VARCHAR(15)
	,DataField1  VARCHAR(15)
	,DataField2  VARCHAR(15)
)
GO

INSERT INTO stores VALUES
('Storage1','Folder1','Data1','Data2'),
('Storage2','Folder1','Data1','Data2'),
('Storage3','Folder1','Data1','Data2'),
('Storage1','Folder1','Data2','Data3')
GO


SELECT DataField1,DataField2, MAX(CASE WHEN cnt=cnts THEN 'Valid' ELSE 'Invalid' END) IsValid FROM 
(
	SELECT DataField1,DataField2,COUNT(*) OVER(PARTITION BY DataField1,DataField2) cnt , cnts
	FROM stores	
	CROSS APPLY
	(
		SELECT COUNT(*) cnts FROM ( SELECT DISTINCT Storage_Container FROM stores )t
	)t
)u 
GROUP BY DataField1,DataField2

Open in new window

Output

/*------------------------

OUTPUT
------------------------*/
DataField1      DataField2      IsValid
--------------- --------------- -------
Data1           Data2           Valid
Data2           Data3           Invalid

(2 row(s) affected)

Open in new window

Pawan Kumar

If you have older version of SQL you can use below -

SELECT DataField1,DataField2, MAX(CASE WHEN cnt=cnts THEN 'Valid' ELSE 'Invalid' END) IsValid FROM 
(
	SELECT *  FROM ( SELECT DataField1,DataField2,COUNT(*) cnt 
	FROM stores		
	GROUP BY DataField1,DataField2 ) r
	CROSS APPLY
	(
		SELECT COUNT(*) cnts FROM ( SELECT DISTINCT Storage_Container FROM stores )t
	)t
	
)u 
GROUP BY DataField1,DataField2

Open in new window

Output

/*------------------------
Output
------------------------*/
DataField1      DataField2      IsValid
--------------- --------------- -------
Data1           Data2           Valid
Data2           Data3           Invalid

(2 row(s) affected)

Open in new window

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.