Comments by PreetMDX - CodeProject

PreetMDX 28-Mar-22 12:16pm View

I have tried that already but that doesn't provide the data which I'm expecting. The resultset would be like this:

dept ID
6 2
7 4

for each ID I'm trying to compare the departments.

PreetMDX 13-Jan-22 0:26am View

Thanks @pkfox

This is working with a view,

Does this mean this can not be done in SSRS as a calculated field or if I would need to highlight the lowest value against Dept as conditional formatting?

PreetMDX 5-Oct-21 5:40am View

Thank you Gerry and Donnathan for your inputs.

The issue here is that every time there is a new record being inserted.
It is basically for data analysis purposes and not from a transactional database.
The data gets added once a month and there is a new record every time with almost redundant data except few columns get the values updated.
I won't be able to make changes to the existing process. I'm not sure how triggers and log table would work in that case.

Therefore, I was trying to compare data and get the latest value and a way to identify which of the column was different from the previous month.

I could think of using a case statement if it is different from the previous month add the latest value else 0. but there are a lot of columns as well.

So, if I go with that approach I would require some help to loop through all the columns in case statement instead of writing all those.
or I would welcome any suggestions to achieve this another way.

PreetMDX 5-Oct-21 5:38am View

Deleted

Thank you Gerry and Donnathan for your inputs.

The issue here is that every time there is a new record being inserted.
It is basically for data analysis purposes and not from a transactional database.
The data gets added once a month and there is a new record every time with almost redundant data except few columns get the values updated.
I won't be able to make changes to the existing process. I'm not sure how triggers and log table would work in that case.

Therefore, I was trying to compare data and get the latest value and a way to identify which of the column was different from the previous month.

I could think of using a case statement if it is different from the previous month add the latest value else 0. but there are a lot of columns as well.

So, if I go with that approach I would require some help to loop through all the columns in case statement instead of writing all those.
or I would welcome any suggestions to achieve this another way.

PreetMDX 26-Aug-21 2:21am View

Thanks Richard, Changing the order did fix that issue as well:)

PreetMDX 20-Aug-21 5:00am View

Thank you @wendelius, Your understanding is absolutely correct on this and the solution works like charm.
The missing link was coalesce function.

PreetMDX 20-Aug-21 4:36am View

Thank Richard,

The problem is that this is not actually grouping the max dates by name.

suppose the data is like:
EmpID Name Date1 Date2
1 A 29/Jan/2021 20/Jan/2021
1 A 30/Jan/2021 20/Jan/2021
2 B Null 20/Jan/2021
2 B NUll 21/Jan/2021

The output this query returns based on date order defined in row number.
It returns what row of Date1 comes first.
If the Date2 has same dates for a name as shown above. It would fetch the corresponding date from Date1 column which is

EmpID Name Date1
1 A 29/Jan/2021
2 B null

However it should be 30/Jan/2021 for name A.

Comments by PreetMDX (Top 7 by date)