mercredi 18 février 2015

Struggling with faulty date fields in MSSQL

I have two tables, a parent table and a child-table. The child-table is a vertical designed table (meaning it stores and Id, ParentId, Property and PropertyValue). Naturally the PropertyValue can hold all types of data.


I'm trying to filter this set but I'm struggling with faulty dates and empty fields. I'm unable to create functions due to read-only access so I have to do everything in the actual query. I tried using a subquery but I'm experiencing I'm not getting the results from the subquery to work with in the outer query.


So far I've got this:



DECLARE @Year Int
SET @Year = 2015

SELECT COUNT(Parent.ID), YEAR(PropertyValue), MONTH(PropertyValue)
FROM Parent
INNER JOIN Child
ON Parent.ID = Child.ParentID
WHERE Parent.ID IN (
SELECT ParentID
FROM Child
WHERE Child.Property = 'MyDateField'
AND ISDATE(Child.PropertyValue) = 1
)
AND Child.Property = 'MyDateField'
AND YEAR(Child.PropertyValue) = @Year
GROUP BY YEAR(Child.PropertyValue), MONTH(Child.PropertyValue)


Any suggestions on how to cut out the faulty date rows and proceed with the desired dataset?


Aucun commentaire:

Enregistrer un commentaire