danawexler (4/29/2013)
thank you both Lynn and Sean - I appreciate your patience.below is a more detail - i wanted to try to keep it simple but apparently i over-simplified the first time!
select reg.CUSTOMER_ID, jh.join_date,
case when jh.join_date BETWEEN DATEADD(mi,-45,web.START_DATE_TIME) and web.END_DATE_TIME then 'Attended Webinar'
else 'Did Not Attend Webinar' end as AttendedWebinar
from Webinar.Registration reg
INNER JOIN Webinar.Webinars web ON web.PRODUCT_CODE = reg.PRODUCT_CODE
inner join dbo.CUSTOMER cus on cus.MASTER_CUSTOMER_ID = reg.CUSTOMER_ID
left outer join Webinar.JoinHistory jh on jh.WEBINAR_HOST_REGISTRATION_ID = reg.WEBINAR_HOST_REGISTRATION_ID
where reg.REGISTRATION_TYPE = 'LIVE'
and reg.PRODUCT_CODE = @WebinarID
and (@ReportType = case when jh.join_date BETWEEN DATEADD(mi,-45,web.START_DATE_TIME) and web.END_DATE_TIME then 'Attended Webinar' else 'Did Not Attend Webinar' end
or
@ReportType = 'All Webinar Registrants')
the problem i have is reflected in the below result set:
CUSTOMER_IDJOIN_DATEAttendedWebinar
0000000330644/17/2013 4:28:18 PMDid Not Attend
0000000330644/18/2013 9:40:59 AMAttended
The result i need is either Attended or Did Not Attend, and i realize my query is working correctly as written - it needs to be tweaked to allow for one or the other.
As Lynn has already stated we need to see ddl and sample data. This means we need a script to create your table(s) and insert statement to populate them with data that represent your issue. Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/