query challenge: add a new column to data set at runtime that tallies whenever a condition arises

Question

query challenge: add a new column to data set at runtime that tallies whenever a condition arises

KoldCoffee

SSCoach

Points: 18971
More actions
November 3, 2014 at 8:31 pm

#292175

I am trying to find a way to provide three metrics for a table that only provides 2 of them. I need to derive a third column which tallies based on conditions that can be found in the dataset.
The table I'm querying shows
(1) total referrals
(2) total moves
but it is missing a column that tallies whenever the referral moved into the same property_id that it was referred to.
create table dimFacts
(date_value datetime
, month_name varchar(9)
, year_number varchar(4)
, month_number tinyint
, lead_id int
, referrals tinyint
, moves tinyint
, property_id int);
insert into dimFacts
values
('2012-10-15 00:00:00.000', 'October','2012', 10, 10038655, 1, 0, 52061),
('2012-10-15 00:00:00.000', 'October','2012', 10, 10038655, 1, 0, 52061),
('2012-11-01 00:00:00.000', 'November','2012', 11, 10038655, 0, 0, 52061),
('2012-11-27 00:00:00.000', 'November','2012', 11, 10038655, 0, 0, 52061),
('2012-12-30 00:00:00.000', 'December', '2012', 12, 10038655, 0, 1, 52061),
('2012-12-31 00:00:00.000', 'December', '2012', 12, 10038655, 0, 0, 52061);
How can I use sql to create one additional column for the above data set, ie. let's call it 'property_conversion' such that on the fifth row there will be a '1' showing that on 2012-12-30 lead_id 10038655 moved into the property it was referred to back on 2012-10-15 ?

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

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

If a converted lead is just a property_id where there exists a move-in for that leadID, then you should be able to use EXISTS(), right?

Something like this:

SELECT lead_id

, property_id

, MIN(date_value) AS FirstLeadDate

FROM dimFacts lds

WHERE EXISTS (

SELECT 1

FROM dimFacts f

WHERE moves = 1

AND property_id = lds.property_id

)

GROUP BY lead_id

, property_id;

just a side note - I added another record to test this (one where there is a referral but no move in... to find the referrals that did not result in a move in, just change EXISTS to NOT EXISTS and you're done.

KoldCoffee SSCoach Points: 18971 More actions · Answer 2

hi pietlinden.

Your query works to extract the lead_id having been moved into a property it was referred to, which is cool becuase you understand the logic, but I'm looking for an actual result set that takes the first table, and at run time produces a temp table that looks like this:

Like this. Note extra column with a 1 in the property conversion row next to the row in which the move occurred, to mark it as a conversion.

select '2012-10-15 00:00:00.000' date_value, 'October' month_name,'2012' year_number, 10 month_number, 10038655 lead_id, 1 referrals, 0 moves, 0 propertyConversion, 52061 property_id

union all

select '2012-10-15 00:00:00.000' date_value, 'October' month_name,'2012' year_number, 10 month_number, 10038655 lead_id, 1 referrals, 0 moves, 0 propertyConversion, 52061 property_id

union all

select '2012-10-15 00:00:00.000' date_value, 'November' month_name,'2012' year_number, 11 month_number, 10038655 lead_id, 0 referrals, 0 moves, 0 propertyConversion, 52061 property_id

union all

select '2012-11-27 00:00:00.000' date_value, 'November' month_name,'2012' year_number, 11 month_number, 10038655 lead_id, 0 referrals, 0 moves, 0 propertyConversion, 52061 property_id

union all

select '2012-12-30 00:00:00.000' date_value, 'December' month_name,'2012' year_number, 12 month_number, 10038655 lead_id, 0 referrals, 1 moves, 1 propertyConversion, 52061 property_id

union all

select '2012-12-31 00:00:00.000' date_value, 'December' month_name,'2012' year_number, 12 month_number, 10038655 lead_id, 1 referrals, 0 moves, 0 propertyConversion, 52061 property_id

Can this be done?

KoldCoffee SSCoach Points: 18971 More actions · Answer 3

ok pietlinden, I can use your query and then union all results to original query, but do you have insight on how to pull the date of the move ie. 2012-12-30 00:00:00.000. Your query does MIN which takes the date of the referral...and I need to specifically get the date of the move which is not same as MAX. Any idea?

Gazareth One Orange Chip Points: 27737 More actions · Answer 4

Just use the exists in a case statement:

select *, case when exists(select * from dimFacts df2 where df2.lead_id = df.lead_id and df2.referrals = 1 and df.moves = 1 and df2.date_value < df.date_value) then 1 else 0 end as propertyConversion

from dimFacts df

NB: this probably won't perform well on large datasets.