Comments by kmoorevs (Top 20 by date)

Sorry, I misread your queries originally. I was thrown off by the extra ) at the end of the outer query. It's not clear what you are trying to get as results. Top sales person per product type? (what about product type 555?)

Second this answer, just wanted to add another benefit for using a database is the security. If you are really stuck on using files, consider xml...easy to load/manipulate/save and can be queried with linq.

Is that last date correct? (or should it be 2018) If the dates should be ordered ascending you can use subqueries to do what you want. If so, I can provide an example that works.

Just to add, subqueries in the following form can solve many problems:
Select Sum(sq.Sum_Sales) as Sales, Sum(sq.Sum_Purchases) as Purchases, sq.ItemCode
From (Select Sum(A.Sales) as Sum_Sales, 0 as Sum_Purchases, A.ItemCode
From tblSales A
Group By A.ItemCode
Union All
Select 0 as Sum_Sales, Sum(B.Purchases) as Sum_Purchases, B.ItemCode
From tblPurchases B
Group By B.ItemCode) as sq

You were given two solutions last week. Did anything work for you? After a couple of valid answers here without a positive response, I'll think twice before putting any effort here again.

As John mentioned, the results you want are from a pivot function. You can use the grouping table to build the pivot query dynamically using c#. Just google sql pivot and fill in the spaces. Ideally, you would lose the dependency on a 'range table' that requires maintenance.

//scope is same level as listView1
private List<string> strList = new List<string>();

When you add an item to listView1, also add the item to strList, but only after making sure it doesn't already exist in the list. (you were on the right track earlier) Instead of checking for the value in the listView Items collection, check against the generic list.

   if (! strList.Contains(epc))
      {
          listView1.Items.Add(new ListViewItem(epc));

           strList.Add(epc);
       }

The downside to this solution is that you have to maintain the list, which is easy enough. When you remove or even edit an item in the listview, make sure you also remove or edit from the list. Here's to remove selected items:

foreach (ListViewItem l in listView1.SelectedItems)
{
    strList.Remove(l.Text);
    l.Remove();
}

Another hack I've used before is keeping a delimited string. That would look like this:

private string delimitedString = "";

//when adding item(s) to listview
            if (!delimitedString.Contains("|" + epc + "|"))
            {
                listView1.Items.Add(new ListViewItem(epc));
                if (listView1.Items.Count == 1)
                {
                    delimitedString = "|";
                }
                delimitedString += epc  + "|";
            }
//when removing item(s) also remove from the delimited string like this:
            foreach (ListViewItem l in listView1.SelectedItems)
            {
                delimitedString = delimitedString.Replace("|" + l.Text + "|", "|");
                l.Remove();
            }

I had reposted the question under the Database forum and noted there (in an update yesterday) that the problem was likely something on the customer's end. We are now successfully connecting to the availability group database using the sqloledb driver. We are now forcing protocol and connecting on the listener. (tcp: ag-listener) Even this was not working until they switched something on their end...then it miraculously started working for all the end users! According to their guy, switching the provider/driver to sql native 11 was the key, but after it started working, I switched it back to sqloledb and it works...and it means the end users don't have to waste time installing the native driver(s).

Thanks OG! The provider parameter is required for my application...leaving it out (connecting to a local sql database) fails with 'no default driver specified'. I just tried the SQLNCLI11 (sql native) driver and found that it works locally, but unfortunately I still have no idea if it will solve the problem connecting in an Availability Group. Thanks!

This one's about grouping and aggregates. hint: min and max

I came back after two days and gave this question a 1 vote. OP apparently doesn't care to respond to those trying to help.

Also, what driver are you using? ODBC are notoriously slow. OLEDB is much faster.

For CR, try using field definitions/ttx for your datasource.

What to do about database? Just looking at the list of related questions here should help, but I first wanted to ask about the nature of your application to see if sql server might be overkill. If it's a simple single-user application you might consider something like sql compact or even Access.

Just wanted you to know that I learned something from your question! I was not aware that iif worked in SQL. Thank you!