Help with summrazing dates and character columns in a Union Query

[actcognosuser]

Hi folks,

Here is a result of a union query

Country  City  Region   User     Designation    Status Date    Trainer_Name   Training_Date  Supervisor_Name  Promotion _Date
USA        ABC  1b         MRTX    Manager      Apr1,2021        ARB                  Jan 1 ,2021
USA        ABC   1B        MRTX    Manager      Apr1, 2021                                                      DDD                  Feb1 , 2021

Expected Result set is:
Country  City  Region   User     Designation    Status Date    Trainer_Name   Training_Date  Supervisor_Name  Promotion _Date
USA        ABC  1b         MRTX    Manager      Apr1,2021        ARB                  Jan 1 ,2021    DDD                     Feb1 , 2021


Using Aggregate function Maximum does not work on Trainer Name , Trainer Date , Supervisor Name and Promotion Date columns.
Result is still 2 rows instead of one.

Thanks in advance!

[MFGF]

Hi folks,

Here is a result of a union query

Country  City  Region   User     Designation    Status Date    Trainer_Name   Training_Date  Supervisor_Name  Promotion _Date
USA        ABC  1b         MRTX    Manager      Apr1,2021        ARB                  Jan 1 ,2021
USA        ABC   1B        MRTX    Manager      Apr1, 2021                                                      DDD                  Feb1 , 2021

Expected Result set is:
Country  City  Region   User     Designation    Status Date    Trainer_Name   Training_Date  Supervisor_Name  Promotion _Date
USA        ABC  1b         MRTX    Manager      Apr1,2021        ARB                  Jan 1 ,2021    DDD                     Feb1 , 2021


Using Aggregate function Maximum does not work on Trainer Name , Trainer Date , Supervisor Name and Promotion Date columns.
Result is still 2 rows instead of one.

Thanks in advance!

Hi,

Are there typos in the data you posted? I notice Region is 1b in the first row and 1B in the second row? The values in Status Date appear slightly different too? If the values in the first six items are not all identical, you won't get a single grouped row. Also check that there are no leading/trailing spaces in the values that might not be visible.

Cheers!

MF.

[actcognosuser]

Hi MF,

Sorry that was a typo. The first six fields are identical in both rows.

[MFGF]

Hi MF,

Sorry that was a typo. The first six fields are identical in both rows.

Hi,

Provided all six of the first items are identical, using Detail Aggregation of Maximum on the other four should produce the results you are looking for. If it isn't, there's something else going on. Have you checked there are no leading/trailing spaces in the first six items in either row? Are these 10 items the only items in the query?

Cheers!

MF.

[dougp]

Same question about the dates:  Are they the same on both?  But those were clearly not pasted in.  The date formats there are not something I've seen.

What are the results from each of the queries that are the sources for the UNION?

View the generated SQL, copy and paste it into a query tool (like SSMS for MS SQL Server), and figure out what's going on.

[dougp]

...or are those date columns not a date data type?  maybe a character type?