Filtering an Coalesce Field

[TechieTony]

I have a query that shows a date but some of the dates are NULL. I am using COALESCE to turn my NULLS into a identifiable number: COALESCE (P.year_and_period, '200699') AS Year_Period2

I would like to limit this query by the dates that are pulling from the Year_Period2 BETWEEN '200699' AND GETDATE()

Is this possible or do I have to create another view to accomplish this

Noncentz

My Query

SELECT
TOP (100) PERCENT P.item_id, P.item_desc, P.location_id, P.inv_min, P.inv_max, P.product_group_id, P.actual_demand, P.qty_on_hand,P.product_type, IL.moving_average_cost, IL.inv_mast_uid, P.year_and_period, COALESCE(P.year_and_period, '200699') AS Year_Period2


FROM
dbo.p21_view_purchase_method_review_report AS P INNER JOIN dbo.p21_view_inv_loc AS IL ON P.location_id = IL.location_id AND P.item_id = IL.item_id

WHERE
(P.location_id = 9000)OR(P.location_id = 3130)

ORDER BY P.location_id

 

[jbenson001]

I would take your initial select and use it in a Select INTO a temp table. Then you can use your Where clause on the temp table. However, looking at your Where clause, you will have to remove the time portion of GetDate() to compare correctly.

 

[SQLSister]

since 200699 is not a valid date how can you do a date comparison?

"NOTHING is more important in a database than integrity." ESquared

 

[jbenson001]

Very true... you have to fix your "date" columns first.

 

[TechieTony]

opps sorry about that:

Year_Period2 BETWEEN '200699' AND 200801

I realized that a while back but forgot to update my code, I will try the select INTO and see what I come up with

Thx for the replies