CODEWITHSUNDEEP
sql-server
Dan
Dan

Reputation: 61

How can I assign a value based on a Date field?

I have two tables. One with dates and the other with gift dates and gift types. The third table displays the results that I am looking to achieve. I want to display a column on the table with the product dates called 'Results'. The results should return:

Table 1

Donor   Date
Steve   2/1/2020
Steve   3/1/2020
Steve   4/1/2020
Steve   5/1/2020
Steve   6/1/2020
Steve   7/1/2020
Steve   9/1/2020
Steve   10/1/2020
Bill    2/1/2020
Bill    3/1/2020
Bill    4/1/2020
Bill    5/1/2020
Bill    6/1/2020
Bill    7/1/2020
Bill    8/1/2020

Table 2

Donor   Gift Date   Gift Type
Steve   8/15/2020   Product
Steve   9/15/2020   Product
Bill    5/15/2020   Product
Bill    6/15/2020   Other
Bill    7/15/2020   Product

Expected Result

Donor   Date        Results
Steve   2/1/2020    Not A Donor
Steve   3/1/2020    Not A Donor
Steve   4/1/2020    Not A Donor
Steve   5/1/2020    Not A Donor
Steve   6/1/2020    Not A Donor
Steve   7/1/2020    Not A Donor
Steve   9/1/2020    Products Donor
Steve   10/1/2020   Multiple Products Donor
Bill    2/1/2020    Not A Donor
Bill    3/1/2020    Not A Donor
Bill    4/1/2020    Not A Donor
Bill    5/1/2020    Not A Donor
Bill    6/1/2020    Products Donor
Bill    7/1/2020    Other Donor
Bill    8/1/2020    Other Donor

Upvotes: 0

Views: 157

Answers (1)

iceblade
iceblade

Reputation: 641

You can use a Case statement to consider each possible output:

select  T.Donor, 
        T.Date,
        Results = Case  when not exists (Select top 1 1 From table2 where Donor = T.Donor and [Gift Date] < T.[Date]) then 'Not A Donor'
                        when exists (Select top 1 1 From table2 where Donor = T.Donor and [Gift Date] < T.[Date] and [Gift Type] = 'Other') then 'Other Donor'
                        when (Select count(1) From table2 where Donor = T.Donor and [Gift Date] < T.[Date] and [Gift Type] = 'Product') = 1 then 'Products Donor'
                        when (Select count(1) From table2 where Donor = T.Donor and [Gift Date] < T.[Date] and [Gift Type] = 'Product') > 1  then 'Multiple Products Donor'
                    end
from table1 T

I don't know the name of your tables so you'll have to replace them.

Upvotes: 1

Related Questions