• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

query wont pick up single events where data changed

I have this:


SELECT v.AbiCode, MIN(v.ChangeYearMonth) AS minYM, MAX(v.ChangeYearMonth) AS maxYM, First(v.BodyPrev) AS Tprev, Last(v.BodyChange) AS Tchange
FROM (SELECT DISTINCT AbiCode, ChangeYearMonth, bodyPrev, bodyChange FROM Tblbody)  AS v
GROUP BY v.AbiCode
HAVING First(v.bodyPrev) <> 	Last(v.bodyChange);

Open in new window


its a great query and i had hoped it would pick up singular events where the data changed in one period as well.

for example this doesnt get picked up:
ChangeId                 AbiCode      BodyChange      ChangeYearMonth      BodyPrev
735                        80120029                                    2012-08                     R
0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 8
  • 4
  • 3
2 Solutions
 
lwadwellCommented:
Is BodyChange null? If it was blank, it should be not equal ... try
   HAVING First(NZ(v.bodyPrev,'')) <> Last(NZ(v.bodyChange,''))

If you want all single events, try this change to the HAVING clause
   HAVING First(v.bodyPrev) <> Last(v.bodyChange) OR count(*) = 1;
0
 
als315Commented:
In your example BodyChange has no value. Try this query:
SELECT v.AbiCode, Min(v.ChangeYearMonth) AS minYM, Max(v.ChangeYearMonth) AS maxYM, First(v.BodyPrev) AS Tprev, Last(v.BodyChange) AS Tchange
FROM (SELECT DISTINCT AbiCode, ChangeYearMonth, bodyPrev, bodyChange FROM Tblbody)  AS v
GROUP BY v.AbiCode
HAVING (((First(v.BodyPrev))<>Nz(Last([v].[bodyChange]),"")));

Open in new window

0
 
PeterBaileyUkAuthor Commented:
one entry may have null if it goes from unpopulated value to populated value in the future, so it needs any event of that type 1 to x events.

als315 that solution has worked

so should i do like this to get all events an amalgamation of both experts:

SELECT v.AbiCode, Min(v.ChangeYearMonth) AS minYM, Max(v.ChangeYearMonth) AS maxYM, First(v.BodyPrev) AS Tprev, Last(v.BodyChange) AS Tchange
FROM (SELECT DISTINCT AbiCode, ChangeYearMonth, bodyPrev, bodyChange FROM Tblbody)  AS v
GROUP BY v.AbiCode
HAVING (((First(v.BodyPrev))<>Nz(Last([v].[bodyChange]),""))) OR count(*) >= 1

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PeterBaileyUkAuthor Commented:
i notice its also picking up null null or '' ''
AbiCode                     minYM      maxYM      Tprev      Tchange
12095001            2012-07      2012-08
0
 
lwadwellCommented:
'or count(*) >= 1' is meaningless ... it is always true.
0
 
PeterBaileyUkAuthor Commented:
what i could do is post another question to remove the blanks
0
 
als315Commented:
What result do you like to have if both fields are empty?
0
 
lwadwellCommented:
The NZ should be on both sides ... just in case.  If you want to treat null as different to blank and pick them up ... use a value that will not occur instead of '' in the NZ e.g.
   HAVING ( NZ(First(v.BodyPrev),"@")<>Nz(Last([v].[bodyChange]),"@") );
0
 
PeterBaileyUkAuthor Commented:
if the value goes from null to blank or blank to null the event can be ignored. a previous query that populates the events tables has inadvertantly picked up nulls to blanks i believe.

i am posting it now so u can pick up the points
0
 
PeterBaileyUkAuthor Commented:
I have taken the count out as it was giving erroneous results so it looks like this picks up single changes and changes where 1 or more events exist:

SELECT v.AbiCode, Min(v.ChangeYearMonth) AS minYM, Max(v.ChangeYearMonth) AS maxYM, First(v.TransmissionPrev) AS Tprev, Last(v.TransmissionChange) AS Tchange
FROM (SELECT DISTINCT AbiCode, ChangeYearMonth, TransmissionPrev, TransmissionChange FROM TblTransmission)  AS v
GROUP BY v.AbiCode
HAVING (((First(v.transmissionPrev))<>Nz(Last([v].[transmissionChange]),"")));
0
 
lwadwellCommented:
I still believe that you need the NZ on BOTH sides in the HAVING, i.e.

SELECT v.AbiCode, Min(v.ChangeYearMonth) AS minYM, Max(v.ChangeYearMonth) AS maxYM, First(v.TransmissionPrev) AS Tprev, Last(v.TransmissionChange) AS Tchange
FROM (SELECT DISTINCT AbiCode, ChangeYearMonth, TransmissionPrev, TransmissionChange FROM TblTransmission)  AS v
GROUP BY v.AbiCode
HAVING ((NZ(First(v.transmissionPrev),"")<>Nz(Last([v].[transmissionChange]),"")));
0
 
PeterBaileyUkAuthor Commented:
I found that it was returning values that went from having a value to not having a value, those  can be ignored, i mustn't ignore where the value changed from null to something (something means anything other than null or blank) does that affect the query now?
0
 
PeterBaileyUkAuthor Commented:
looks like this is it:
SELECT v.AbiCode, MIN(v.ChangeYearMonth) AS minYM, MAX(v.ChangeYearMonth) AS maxYM, First(v.transmissionPrev) AS Tprev, Last(v.transmissionChange) AS Tchange
FROM (SELECT DISTINCT AbiCode, ChangeYearMonth, transmissionPrev, transmissionChange FROM Tbltransmission)  AS v
GROUP BY v.AbiCode
HAVING ((NZ(First(v.transmissionPrev),"")<>Last([v].[transmissionChange])));
0
 
als315Commented:
Have you tested query with NZ on both sides? If Empty string is possible in your records, you can set NZ to output something Non-real:
HAVING ((NZ(First(v.transmissionPrev),"a0b1c2")<>Nz(Last([v].[transmissionChange]),"a0b1c2")))
0
 
PeterBaileyUkAuthor Commented:
ok all done now and working i tried nz both sides but got results i didn't need. now its fine i have split the points for the contributions and effort of both experts
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 8
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now