I need help with creating list

Hi Amber,

The first item in your list is more suited to the functionality of Reports and Utilities. I would try the following:

In Reports and Utilities, run the the Subscription Detail by Constituent report for the season you're looking for. Save the report as a .csv file. Open the file and highlight the customer_no column, then select Conditional Formatting in the toolbar > Highlight cells rules > Duplicate values. That should tell you if any subscribers have multiple orders.

You could also try this with the Package Order listing report to cross reference and see if that gives you better data. 

For number three, this SQL in a list should get you what you want.:

select
	distinct m.kept_id as customer_no
from T_MERGED as m
where status = 'S'
	and m.merge_dt >= CONVERT(datetime, '2022/01/01')

This isn't doing any household expansions, and I don't know off the top of my head if you can set up a manual query that will allow household operations?

Edit: Advanced Relationship Options work just fine with this.

For #2, something like this might work:

select
	distinct o.customer_no
from T_SUB_LINEITEM as sli
	inner join T_PKG as pkg on pkg.pkg_no = sli.pkg_no
		and pkg.pkg_no <> 0
		and pkg.pkg_type in (1,2,3,4,5,6,7,8,9,10) --replace with the correct package types
	inner join TR_SEASON as s on s.id = pkg.season
		and s.fyear = 2023
	inner join T_ORDER as o on o.order_no = sli.order_no
		and o.initiator_no is not null

Now, you are going to be getting the customer on the order (the "subscriber"), not the initiator.  This just says that there is a specified initiator.  if you wanted the actual initiator in your list you'd change the select to "distinct o.initiator_no".

Thank you!

Thanks!

Thank you!

More than welcome, hope it works for you and let us know how it goes!