Returning only documents by condition

Question

Returning only documents by condition

luissantos

SSCertifiable

Points: 7391
More actions
August 6, 2015 at 12:16 pm

#306406

Hello comunity
I need to build a query that can return only documents where the field "u_DIM4" for the same document have more than one different value:
my script are this one just to having an example:
Select docnome [documentname], adoc [docnr], count(*) [countAlldifferentbyDoc], u_dim4
from fn
where u_dim4 <> '' and data between '2015-01-01' and '2015-07-31'
AND adoc = '02634'
Group by docnome,adoc,u_dim4
ORDER BY 2 asc
the return is:
documentnamedocnr countAlldifferentDIM4byDocu_dim4
F13-V/Fatura FSE 02634 1 V03
F13-V/Fatura FSE 02634 1 V05
F13-V/Fatura FSE 02634 3 V09
F13-V/Fatura FSE 02634 2 V10
F13-V/Fatura FSE 02634 2 V11
I´m pretend the following:
documentname docnr countAlldifferentDIM4byDoc
F13-V/Fatura FSE 02634 5
Best regards,
Luis Santos

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

luissantos (8/6/2015)
Hello comunity
I need to build a query that can return only documents where the field "u_DIM4" for the same document have more than one different value:
my script are this one just to having an example:
Select docnome [documentname], adoc [docnr], count(*) [countAlldifferentbyDoc], u_dim4
from fn
where u_dim4 <> '' and data between '2015-01-01' and '2015-07-31'
AND adoc = '02634'
Group by docnome,adoc,u_dim4
ORDER BY 2 asc
the return is:
documentnamedocnr countAlldifferentDIM4byDocu_dim4
F13-V/Fatura FSE 02634 1 V03
F13-V/Fatura FSE 02634 1 V05
F13-V/Fatura FSE 02634 3 V09
F13-V/Fatura FSE 02634 2 V10
F13-V/Fatura FSE 02634 2 V11
I´m pretend the following:
documentname docnr countAlldifferentDIM4byDoc
F13-V/Fatura FSE 02634 5
Best regards,
Luis Santos

It would be really helpful if you would post your test data in a manner that makes it easy for others to just copy/paste it, and use it. Please see the first link in my signature for how to do this.

That being said, I took your test data and put it into a cte.

How does this query work for you?

WITH fn AS

(

SELECT *

FROM (VALUES ('F13-V/Fatura FSE', '02634', 'V03', '2015-01-01'),

('F13-V/Fatura FSE', '02634', 'V05', '2015-01-01'),

('F13-V/Fatura FSE', '02634', 'V09', '2015-01-01'),

('F13-V/Fatura FSE', '02634', 'V10', '2015-01-01'),

('F13-V/Fatura FSE', '02634', 'V11', '2015-01-01'),

('F13-V/Fatura FSE', '02634', 'V11', '2015-01-01')

) dt(docnome, adoc, u_dim4, data)

), cte AS

(

SELECT docnome [documentname], adoc [docnr], count(*) [countAlldifferentbyDoc], u_dim4

, COUNT(*) OVER (PARTITION BY docnome

ORDER BY fn.u_dim4

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) CountUDIM4Bydocnome

FROM fn

WHERE u_dim4 <> ''

AND data BETWEEN '2015-01-01' AND '2015-07-31'

AND adoc = '02634'

GROUP BY docnome,adoc,u_dim4

)

SELECT DISTINCT cte.documentname, cte.docnr, cte.CountUDIM4Bydocnome

FROM cte

WHERE cte.CountUDIM4Bydocnome > 1

ORDER BY 2 ASC;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 2

COUNT(*) OVER (PARTITION BY docnome
ORDER BY fn.u_dim4
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) CountUDIM4Bydocnome

It's 2008 forum, and I see no hints OP has other version.

luissantos SSCertifiable Points: 7391 More actions · Answer 3

Hello

Thanks for yours reply they work fine.

Best regards

Luis Santos

WayneS SSC Guru Points: 95460 More actions · Answer 4

serg-52 (8/7/2015)
COUNT(*) OVER (PARTITION BY docnome
ORDER BY fn.u_dim4
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) CountUDIM4Bydocnome
It's 2008 forum, and I see no hints OP has other version.

Hmm, not sure how I missed that.

The change for 2008 would be:

COUNT(*) OVER (PARTITION BY docnome ) CountUDIM4Bydocnome

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2