[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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