1
Answer

selecting and Grouping data

Lynn Emery

Lynn Emery

12y
981
1
Hi I am trying to select sepecific data from a table and group it.
In the table which I am extracting the data from I have the following fields:

Table Fields: Customer¦Sales¦ Date¦Description

Example table data:


Customer¦ Sales ¦Date¦ Product description

Customer A¦20¦ 01/10/2010¦ Product description
Customer A¦10¦ 05/10/2010¦ Product description
Customer B¦35¦ 21/09/2010¦ Product description
Customer B¦15¦ 16/09/2010¦ Product description

I want to create an SQL statement which filters and selects data from the table to show the the highest Sales for each customer by month and year.

Therefore the data should display this from the example table data above:

Customer¦ Sales ¦Month¦Year¦ Product description

Customer A¦20¦ 10¦ 2010¦ Product description
Customer B¦35¦ 09¦2010¦ Product description


However I am getting duplications for each month and year rather than a single entry.

for example:

Customer¦ Sales ¦Month¦Year¦ Product description

Customer A¦20¦ 10¦ 2010¦ Product description
Customer A¦10¦ 10¦ 2010¦ Product description
Customer B¦35¦ 09¦2010¦ Product description


My SQL statement appears to work fine until I add the field product description.

Below shows my current SQL statement:

SELECT        Customer, Description, MAX(sales) AS Maxsales, CASE WHEN MONTH([Date]) = '01' THEN '01' WHEN MONTH([Date_Rec]) = '02' THEN '02' END AS Month,CASE WHEN YEAR([Date]) = '2010' THEN '2010'  END AS Year

FROM            dbo.CCAR_Log

GROUP BY Customer, Description, sales  CASE WHEN MONTH([Date]) = '01' THEN '01' WHEN MONTH([Date]) = '02' THEN '02' END, CASE WHEN YEAR([Date]) = '2010' THEN '2010'  END

I hope that all makes sense.
Thank you for your help in advance.








Answers (1)
0
Vulpes
NA 98.3k 1.5m 13y
You'll need to call Application.DoEvents() before Thread.Sleep(), otherwise the color changes will be cached by GDI+ and won't take effect until the method ends.

Here's how I'd do it. I'm assuming that the buttons all have different colors to start with and you want them to retain their colors when they are in sorted order. You don't need to deal with the case i == 5 as it will never be called:

        public void SwapColor(Button b1, Button b2)
        {
            Color tempColor = b1.BackColor;
            b1.BackColor = b2.BackColor;
            b2.BackColor = tempColor;
        }

        public void BubbleSort(int[] array, int Size)
        {
            int temp;

            for (int pass = 1; pass < Size; pass++)
            {
                for (int i = 0; i < Size - pass; i++)
                {
                    if (array[i] > array[i + 1])
                    {
                        if (i == 0)
                        {
                            SwapColor(btn1, btn2);
                        }
                        if (i == 1)
                        {
                            SwapColor(btn2, btn3);                       
                        }
                        if (i == 2)
                        {
                            SwapColor(btn3, btn4);
                        }
                        if (i == 3)
                        {
                            SwapColor(btn4, btn5);
                        }
                        if (i == 4)
                        {
                            SwapColor(btn5, btn6);
                        }
                       
                        Application.DoEvents();
                        Thread.Sleep(1000); // 2000 is a bit long 
                        temp = array[i];
                        array[i] = array[i + 1];
                        array[i + 1] = temp;
                    }
                }
            }
            a();
        }