need help in creating function

Question

need help in creating function

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
October 30, 2018 at 2:57 pm

#376142

i have the data if AFG is Yes or Type is N/A then we need to count as 1 valid record
Condition 1
AFG       Type
yes          N/A
COnditon 2 - If AGF is No or Type is Phys then we need to count as another valid record
AFG   Type
No       Phys

Also for these both conditions we have to check the record is active or not, for that we need to check start date should be less than current date and end date should be future data.. if it is valid then only we can check both the oncditons

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

mcfarlandparkway - Tuesday, October 30, 2018 2:57 PM
i have the data if AFG is Yes or Type is N/A then we need to count as 1 valid record
Condition 1
AFG       Type
yes          N/A
COnditon 2 - If AGF is No or Type is Phys then we need to count as another valid record
AFG   Type
No       Phys

Also for these both conditions we have to check the record is active or not, for that we need to check start date should be less than current date and end date should be future data.. if it is valid then only we can check both the oncditons

You should know the deal by now. For any help we need DDL, sample data and expected results. I'd also like to know what have you tried as a simple CASE expression should do the trick.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

saravanatn SSCarpal Tunnel Points: 4533 More actions · Answer 2

mcfarlandparkway - Tuesday, October 30, 2018 2:57 PM
i have the data if AFG is Yes or Type is N/A then we need to count as 1 valid record
Condition 1
AFG       Type
yes          N/A
COnditon 2 - If AGF is No or Type is Phys then we need to count as another valid record
AFG   Type
No       Phys

Also for these both conditions we have to check the record is active or not, for that we need to check start date should be less than current date and end date should be future data.. if it is valid then only we can check both the oncditons

Adding to what Luis said I think you need to use count and case expression along with group by function

Saravanan

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Something like this:
SELECT
[RowIsValid] = CASE
  WHEN AFG = 'Yes' OR [Type] = 'N/A' THEN 1
  WHEN AGF = 'No' OR [Type] = 'Phys' THEN 2
  END,
[RowIsActive] = CASE WHEN [start date] < GETDATE() AND [end date] > GETDATE() THEN 1 ELSE 0 END

As others have stated, much easier to do when sample data is provided.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden