Hi All,

I have 3 tables: Suppliers, Documents and Verifications, each document may have multiple verifications where I need to get the last verification according to verification date.

So I just need a "Select top 1 result from Documents,Verifications where doc_iddoc=ver_iddoc and result='True' order by ver_date desc" so far I get the result of the last verification, but here's the problem:

I need to get a row with the count of documents for each supplier, I mean:

Supplier Name     Docs      NegativeVerifications
Acme Co                10                     1

that is I would need to loop for each supplier and each document and get the last verification, if one of any of documents have negative verification then add it to negative results.

Is it possible to achive this with a query or do I have to do it through stored procedure?

Thanks in advance

If you were to post sample table data, you'd get close to an exact answer.

But chances are, something like this will do just what you want:

With Step1_CTE as
(
Select *
  , row_number() over(partition by S.ID, D.ID, V.Date descending) as RN_Verification
 from Supplier S
inner join Documents D on joincolumn = joincolumn
inner join Verification V on joincolumn = joincolumn
)
Select SupplierName, count(distinct docID)
   , count(case when minverification = 1 then 'x' else null end) as Ct_Min_Verifications  
  From Step1_CTE 
 Where RN_Verification = 1
 Group by SupplierName

• Proposed as answer by disssss 8 hours 31 minutes ago
• Edited by johnqflorida 8 hours 28 minutes ago added Min Verification count

There's absolutely no reason at all to loop through anything to get what you're looking for. That is rather easy to do with a set based solution.

Just post create table statements along with some sample data and tell us the expected output based on the sample data.

 

Thank you both, here's the data code:

CREATE TABLE [dbo].[Supplier](
	[sup_id] [int] IDENTITY(1,1) NOT NULL,
	[sup_idclient] [int] NOT NULL,
	[sup_Name] [varchar](100) NOT NULL
)

CREATE TABLE [dbo].[Documents](
	[doc_id] [int] IDENTITY(1,1) NOT NULL,
	[doc_idclient] [int] NOT NULL,
	[doc_idsupplier] [int] NOT NULL,
	[doc_document] [varchar](400) NOT NULL,
	[doc_date] [varchar](22) NULL
)

CREATE TABLE [dbo].[Verifications](
	[ver_id] [int] IDENTITY(1,1) NOT NULL,
	[ver_iddoc] [int] NOT NULL,
	[ver_result] [varchar](10) NOT NULL,
	[ver_date] [smalldatetime] NOT NULL
)

INSERT INTO Supplier([sup_idclient],[sup_name]) VALUES (1,'SUPPLIER LTD')

INSERT INTO Documents([doc_idclient],[doc_idsupplier],[doc_document],[doc_date]) VALUES (1,1,'SOMETEXT1',GETDATE())
INSERT INTO Documents([doc_idclient],[doc_idsupplier],[doc_document],[doc_date]) VALUES (1,1,'SOMETEXT2',GETDATE())
INSERT INTO Documents([doc_idclient],[doc_idsupplier],[doc_document],[doc_date]) VALUES (1,1,'SOMETEXT3',GETDATE())

INSERT INTO Verifications([ver_iddoc],[ver_result],[ver_date]) VALUES (1,'OK','20150322 00:00')
INSERT INTO Verifications([ver_iddoc],[ver_result],[ver_date]) VALUES (1,'BAD','20150318 00:00')
INSERT INTO Verifications([ver_iddoc],[ver_result],[ver_date]) VALUES (1,'OK','20150315 00:00')
INSERT INTO Verifications([ver_iddoc],[ver_result],[ver_date]) VALUES (2,'BAD','20150322 00:00')
INSERT INTO Verifications([ver_iddoc],[ver_result],[ver_date]) VALUES (2,'OK','20150313 00:00')
INSERT INTO Verifications([ver_iddoc],[ver_result],[ver_date]) VALUES (2,'OK','20150310 00:00')
INSERT INTO Verifications([ver_iddoc],[ver_result],[ver_date]) VALUES (3,'OK','20150322 00:00')
INSERT INTO Verifications([ver_iddoc],[ver_result],[ver_date]) VALUES (3,'OK','20150313 00:00')
INSERT INTO Verifications([ver_iddoc],[ver_result],[ver_date]) VALUES (3,'BAD','20150312 00:00')

What I need for output is to sum the documents where last verification is BAD or:

Supplier Name     Docs      NegativeVerifications
Supplier LTD            3                     1

Thanks in advance for your time folks

Based on your description, I think this is what you're looking for...

SELECT
	s.sup_Name,
	COUNT(d.doc_id) AS DocCount,
	SUM(CASE WHEN LatestV.ver_result = 'BAD' THEN 1 ELSE 0 END) AS NegVerifications
FROM
	dbo.Supplier s
	JOIN dbo.Documents d
		ON s.sup_id = d.doc_idsupplier
	CROSS APPLY (
		SELECT TOP 1
			v.ver_result
		FROM 
			dbo.Verifications v
		WHERE 
			d.doc_id = v.ver_iddoc
		ORDER BY 
			v.ver_date DESC
		) LatestV
GROUP BY 
	s.sup_Name

OMG, you are a genius !!!

As you can see, I am just a rookie at SQL, I'd buy you a beer if I could.

Thanks for your time and support

And I'd drink it! :)

Your kind words are appreciated but I can assure you that there are folks on this forum that'll code circles around me.

As far as loops & cursors... They do have their legitimate uses but they are few and far between. In the database world, we work with sets of data as a whole rather than iterating through things row by row. It just takes a little while to get the head wrapped around that as a concept, especially if you come from an OOP programming background.

As to the solution... If you aren't familiar with the APPLY operator. It's one of those things that can really come in handy, and may be worth doing a little reading up on.

Speaking of which... there are two flavors, CROSS APPLY & OUTER APPLY. The CROSS APPLY works much like an INNER JOIN while the OUTER APPLY works like an OUTER JOIN. So, considering the solution I supplied, if a given document didn't have any verification, that document would be filtered out (because I used CROSS APPLY).

If you want all documents, even the ones that don't have any verifications, you'll want to use the following instead.

SELECT
	s.sup_Name,
	COUNT(d.doc_id) AS DocCount,
	SUM(CASE WHEN COALESCE(LatestV.ver_result, '') = 'BAD' THEN 1 ELSE 0 END) AS NegVerifications
FROM
	dbo.Supplier s
	JOIN dbo.Documents d
		ON s.sup_id = d.doc_idsupplier
	OUTER APPLY (
		SELECT TOP 1
			v.ver_result
		FROM 
			dbo.Verifications v
		WHERE 
			d.doc_id = v.ver_iddoc
		ORDER BY 
			v.ver_date DESC
		) LatestV
GROUP BY 
	s.sup_Name

Your real problem is that you have no idea what a table is! By definition,  we never use IDENTITY in RDBMS. It is a proprietary table property that counts the physical insertion attempts (not even successes!) on one disk. Instead,  we need a key for each table. A key is a subset oi attributes of the entity modeled by the table. We also use industry standards for identifiers,  like a DUNS for suppler. Do you really have one with a hundred character name?? The USPS only uses 35 for an address line. But if you allow garbage data,  you will get garbage data.You do not know ISO-11179 naming rules. Or that we have a DATE data type. The old Sybase getdate() is now replaced by the ANSI/ISO Standard CURRENT_TIMESTAMP. And we have the ANSI/ISO Standard insertion syntax.  But what you really screwed up is the lack of  tables for any relationships. It looks like your  supplier_idclient, et al are fake pointers from inside a table to another table! You even used INTEGER, which was the default data type for 1970's network databases.  That is not how we write RDBMS; we have references. There is no physical ordering in a table. CREATE TABLE Suppliers(supplier_duns CHAR(9) NOT NULL PRIMARY KEY,  supplier_name VARCHAR(35) NOT NULL);INSERT INTO Suppliers VALUES ('000000001', 'Supplier Ltd');CREATE TABLE Documents(document_nbr CHAR(5)  NOT NULL PRIMARY KEY, supplier_duns CHAR(9) NOT NULL REFERENCES Suppliers (supplier_duns), doc_txt VARCHAR(400) NOT NULL,  doc_date CURRENT_TIMESTAMP DATE);INSERT INTO Documents(document_nbr, supplier_duns, doc_document_txt) VALUES ('00001', '000000001', 'sometext1') ('00002', '000000001', 'sometext2'),  ('00003', '000000001', 'sometext3');CREATE TABLE Verifications(document_nbr CHAR(5)  NOT NULL  REFERENCES Documents(document_nbr) ON DELETE CASCADE, verification_date DATE NOT NULL,PRIMARY KEY (document_nbr, verification_date) verification_result CHAR(3) NOT NULL CHECK verification_result IN ( 'BAD', 'OK')), );INSERT INTO Verifications(document_nbr, verification_result, verification_date) VALUES (1, 'OK', '2015-03-22'),(1, 'BAD', '2015-03-18'),(1, 'OK', '2015-03-15'),(2, 'BAD', '2015-03-22'),(2, 'OK', '2015-03-13'),(2, 'OK', '2015-03-10'),(3, 'OK', '2015-03-22'),(3, 'OK', '2015-03-13'),(3, 'BAD', '2015-03-12');>> What I need for output is to sum [sic: you show a count, not a sum] the documents where last verification_result is BAD <<Here is a guess, based on the narrative:WITH Recent_Verifications AS(SELECT document_nbr, verification_result,  MAX(verification_date) AS recent_verification_date FROM Verifications)Bad_Recent_VerificationsAS(SELECT D.supplier_name, COUNT(DISTINCT document_nbr) AS document_cnt FROM Recent_Verifications AS R, Documents AS DWHERE R.document_nbr = D.document_nbr  AND R.verification_result = 'BAD')GROUP BY D.supplier_name)SELECT * FROM Bad_Recent_Verifications;

Your real problem is that you have no idea what a table is! By definition,  we never use IDENTITY in RDBMS. It is a proprietary table property that counts the physical insertion attempts (not even successes!) on one disk. Instead,  we need a key for each table. A key is a subset oi attributes of the entity modeled by the table. We also use industry standards for identifiers,  like a DUNS for suppler. Do you really have one with a hundred character name?? The USPS only uses 35 for an address line. But if you allow garbage data,  you will get garbage data.

You do not know ISO-11179 naming rules. Or that we have a DATE data type. The old Sybase getdate() is now replaced by the ANSI/ISO Standard CURRENT_TIMESTAMP. And we have the ANSI/ISO Standard insertion syntax.  

But what you really screwed up is the lack of  relationships among th4 tables. It looks like your   supplier_idclient, et al are fake pointers from inside a table to another table! You even used INTEGER, which was the default data type for 1970's network databases.  That is not how we write RDBMS; we have references. There is no physical ordering in a table. 

CREATE TABLE Suppliers
(supplier_duns CHAR(9) NOT NULL PRIMARY KEY, 
 supplier_name VARCHAR(35) NOT NULL);

INSERT INTO Suppliers VALUES ('000000001', 'Supplier Ltd');

CREATE TABLE Documents
(document_nbr  CHAR(5)  NOT NULL PRIMARY KEY,
 supplier_duns CHAR(9) NOT NULL
  REFERENCES  Suppliers (supplier_duns),
 doc_txt VARCHAR(400) NOT NULL, 
 doc_date DEFAULT CURRENT_TIMESTAMP DATE NOT NULL);

Let the default clause provide data. 

INSERT INTO Documents(document_nbr, supplier_duns, doc_document_txt)
 VALUES
 ('00001', '000000001', 'sometext1')
 ('00002', '000000001', 'sometext2'), 
 ('00003', '000000001', 'sometext3');

CREATE TABLE Verifications
(document_nbr  CHAR(5)  NOT NULL 
  REFERENCES Documents(document_nbr)
  ON DELETE CASCADE,
 verification_date DATE NOT NULL,
PRIMARY KEY (document_nbr, verification_date)
 verification_result CHAR(3) NOT NULL
  CHECK verification_result IN ( 'BAD', 'OK')), 
);

INSERT INTO Verifications(document_nbr, verification_result, verification_date) 
VALUES (1, 'OK', '2015-03-22'),
(1, 'BAD', '2015-03-18'), --got him!
(1, 'OK', '2015-03-15'),
(2, 'BAD', '2015-03-22'),  -- got him!
(2, 'OK', '2015-03-13'),
(2, 'OK', '2015-03-10'),
(3, 'OK', '2015-03-22'), -- not used
(3, 'OK', '2015-03-13'),
(3, 'BAD', '2015-03-12');  

>> What I need for output is to sum [sic: you show a count, not a sum] the documents where last verification_result is BAD <<

Here is a guess, based on the narrative:

WITH Recent_Verifications
 AS
(SELECT  document_nbr, verification_result, 
             MAX(verification_date) AS recent_verification_date
  FROM  Verifications)

Bad_Recent_Verifications
AS
(SELECT D.supplier_name, COUNT(DISTINCT document_nbr) AS document_cnt
  FROM Recent_Verifications AS R, Documents AS D
WHERE R.document_nbr = D.document_nbr 
     AND  R.verification_result = 'BAD')
GROUP BY D.supplier_name)

SELECT * FROM Bad_Recent_Verifications;