SELECT
   person_id,
   MONTH(user_date),
   YEAR(user_date),
   max(total_amount) OVER (PARTITION BY person_id)
FROM
   person
WHERE
   user_date >= @start_date
   AND user_date <= @end_date

quote:

---

Originally posted by potn1

SELECT person_id, MONTH(user_date), YEAR(user_date), total_amount
FROM person
WHERE user_date >= @start_date AND user_date <= @end_date

Right now my query will display all rows within the specific range of dates. The total_amount for 1 of these months would be 50 and the rest would be 0. Basically I just would like a way where I could auto-fill the other rows when the total_amount is 0 to display as 50. These will differ between people so I cannot just use the ISNULL(total_amount,50). Thanks!

edit: Would there be a way to select the MAX value from the result and replace that value in my total_amount column for all rows?

---

quote:

---

Originally posted by sunitabeck

This is picking the max amount for each person_id within the date range you have specified

SELECT
   person_id,
   MONTH(user_date),
   YEAR(user_date),
   max(total_amount) OVER (PARTITION BY person_id)
FROM
   person
WHERE
   user_date >= @start_date
   AND user_date <= @end_date

---

quote:

---

Originally posted by phipywr

The method to adjust the value to 50 when zero would be like this:
SELECT person_id
, MONTH(user_date)
, YEAR(user_date)
, case when total_amount = 0
then 50
else total_amount
end as total_amount
FROM person
WHERE user_date >= @start_date AND user_date <= @end_date

quote:

---

Originally posted by potn1

SELECT person_id, MONTH(user_date), YEAR(user_date), total_amount
FROM person
WHERE user_date >= @start_date AND user_date <= @end_date

Right now my query will display all rows within the specific range of dates. The total_amount for 1 of these months would be 50 and the rest would be 0. Basically I just would like a way where I could auto-fill the other rows when the total_amount is 0 to display as 50. These will differ between people so I cannot just use the ISNULL(total_amount,50). Thanks!

edit: Would there be a way to select the MAX value from the result and replace that value in my total_amount column for all rows?

---

Have an ordinary day

---

quote:

---

Originally posted by potn1

quote:

---

Originally posted by sunitabeck

This is picking the max amount for each person_id within the date range you have specified

SELECT
   person_id,
   MONTH(user_date),
   YEAR(user_date),
   max(total_amount) OVER (PARTITION BY person_id)
FROM
   person
WHERE
   user_date >= @start_date
   AND user_date <= @end_date

---

I tested this on person_id 18 who has 5 records (Jan,Feb,March,Apr,May) between the date range and it seems to be leaving the other values as 0. February is the only month where they have a total_amount of 50. January, March, April and May the total_amount is 0. I am trying to get those 4 other months to also display a total_amount of 50. Is what you posted supposed to work for what I'm looking for?

Thanks!

---

;WITH Totals AS
(
   SELECT person_id, MAX(total_amount) AS TotalForUser GROUP BY person_id
)
SELECT
   p.person_id,
   MONTH(user_date),
   YEAR(user_date),
   TotalForUser
FROM
   person p
   INNER JOIN Totals t ON t.person_id = p.person_id
WHERE
   user_date >= @start_date
   AND user_date <= @end_date

quote:

---

Originally posted by potn1

[quote]Originally posted by phipywr

The method to adjust the value to 50 when zero would be like this:
SELECT person_id
, MONTH(user_date)
, YEAR(user_date)
, case when total_amount = 0
then 50
else total_amount
end as total_amount
FROM person
WHERE user_date >= @start_date AND user_date <= @end_date


But the value will not always be 50 that I want displayed. It will be the MAX value of total_amount returned per each specific person_id. So replacing 0 with 50 would only work for this specific person.

Is there anyway to use a CASE when total_amount is 0 to replace that with the MAX total_amount from a dataset?

---

quote:

---

Originally posted by potn1

Is Totals basically a temporary table that determines each person_id MAX total_amount and then you grab that data to display within your query?

---

quote:

---

Originally posted by sunitabeck

quote:

---

Originally posted by potn1

Is Totals basically a temporary table that determines each person_id MAX total_amount and then you grab that data to display within your query?

---

It is a CTE (common table expression), which conceptually you can think of as a temporary table or table variable - but there are some important differences.

---