Conditional Query
-
I need help building a query that will update a column "Status" with a value based on these criteria. My problem is wrapping my mind around determing whether 100% of the records IS NULL or Not Null.
Tables:
TableParent ~ top level table
TableParentID, Status
TableChild ~ detail table
TableChildID, TableParentID, Date1, Date2
Pseudo Query:
If all records in TableChild has NULL value in the Date1
Then update TableParent.Status with 1
Else
If TableChild records has at least 1 null value for Date1
then Update TableParent.Status with 2
Else
If all records in TableChild has a date Value in Date1 and Date2 has at least 1 NULL value
then Update TableParent.Status with 3
Else
If all records in tableChild has a date value in Date1 and a date value in Date2
Then update TableParentStatus with 4
-
Minor detail
You state "If all records in table child" Does this mean table wide, or if All detail records for a parent?
If table wide, then what status record should be updated?
If Detail records for a parent.
Create table Parent (pk int identity Primary Key, Name varchar(100), Status tinyint)
Create table Detail (pk int identity Primary Key, ParentID int, Date1 smalldatetime, Date2 smalldatetime)
alter table Detail
Add constraint Parentid Foreign Key (ParentID) references Parent(pk)
insert into Parent(Name)
Select 'One' Union
Select 'Two' Union
Select 'Three' Union
Select 'Four'
Insert into Detail (ParentID, Date1, Date2)
-- All Null Scenario
Select 1, NULL,NULL Union all
Select 1, NULL,NULL Union all
-- 1 null date1
Select 2, NULL,NULL Union all
Select 2, '01/01/2005',NULL Union all
-- 0 Null Date1, 1 Null Date2
Select 3, '01/01/2005',NULL Union all
Select 3, '01/01/2005','01/01/2005' Union all
-- 0 Null Date1, 0 Null Date2
Select 4, '01/01/2005','01/02/2005' Union all
Select 4, '01/01/2005','01/01/2005'
Select *
from Parent a
Join Detail b on ParentID = a.pk
id,Name,Status,id,Parent,Date1,Date2
1 One NULL 1 1 NULL NULL
1 One NULL 2 1 NULL NULL
2 Two NULL 3 2 NULL NULL
2 Two NULL 4 2 2005-01-01 00:00:00 NULL
3 Three NULL 5 3 2005-01-01 00:00:00 NULL
3 Three NULL 6 3 2005-01-01 00:00:00 2005-01-01 00:00:00
4 Four NULL 7 4 2005-01-01 00:00:00 2005-01-02 00:00:00
4 Four NULL 8 4 2005-01-01 00:00:00 2005-01-01 00:00:00
-- UPdate parent where count(*) Counts all records for the child is greater than the count(Date1) This excludes Nulls
Update Parent
set Status = 1
where pk in (select ParentID
from Detail
group by ParentID
having count(*) > count(Date1) and count(Date1) = 0)
Update Parent
set Status = 2
where pk in (select ParentID
from Detail
group by ParentID
having count(*) > count(Date1) and count(Date1) > 0)
Update Parent
set Status = 3
where pk in (select ParentID
from Detail
group by ParentID
having count(*) = count(Date1) and count(Date2) > 0 and count(Date2) < count(*))
Update Parent
set Status = 4
where pk in (select ParentID
from Detail
group by ParentID
having count(*) = count(Date1) and count(Date2) > 0 and count(Date2) = count(*))
select * from Parent
id,Name,Status
1 One 1
2 Two 2
3 Three 3
4 Four 4
drop table Parent
drop table Detail
You get the idea
-
Not tested, but here is a single SQL statement that should work. The solution uses a nested table with counts for rows, date1 not null and date2 not null for each ParentId
UPDATE TableParent
set Status
= CASE
-- No NULLs
WHEN ChildCount = Date1NotNullCnt
AND ChildCount = Date2NotNullCnt
THEN 4
-- ALL Date1 are not null but at least one DATE2 is null
WHEN ChildCount = Date1NotNullCnt
AND ChildCount != Date2NotNullCnt
THEN 3
-- All Date1 are not null
WHEN ChildCount = Date1NotNullCnt
THEN 2
ELSE 1
END
from (select TableParentID
, count(*) as ChildCount
, SUM ( CASE WHEN Date1 IS NULL then 0 ELSE 1 END )
as Date1NotNullCnt
, SUM ( CASE WHEN Date2 IS NULL then 0 ELSE 1 END )
as Date2NotNullCnt
From TableChild
Group by TableParentID
) as ChildCounts
where ChildCounts.TableParentID = TableParent.TableParentID
SQL = Scarcely Qualifies as a Language
-
SELECT TP.TableParentID , Count(TP.TableParentID ), Count(TC.TableChildID)
FROM TableParent TP
LEFT JOIN TableChild TC on TC.TableParentID = TP.TableParentID and TC.Date1 IS NULL
GROUP BY TP.TableParentID
criteria for select:
Having Count(TC.TableChildID) = 0
HAVING Count(TP.TableParentID ) = Count(TC.TableChildID)
Count(TC.TableChildID) = 0 if no rows referenced to this particular TableParentID have Date1 = null
Count(TP.TableParentID ) = Count(TC.TableChildID) if all rows have Date1 = null
etc.
_____________
Code for TallyGenerator -
Trick is COUNT(ColumnName) counts only not null values, count(*) counts everything.
_____________
Code for TallyGenerator -
Hi, this is just summing up what Carl and Sergiy presented and it works correctly when tested on a sample data (I used the values from Ray's post). You didn't mention status of a childless parent, so I ignored this possibility. If you change the JOIN TableChild to LEFT JOIN, you'll get status '1' for childless parents; if you need different status, you'd have to modify it further. The 'ERROR' status is there for testing, once you're confident that it works fine, you can remove it - if the SQL is correct, no records should escape through the previous steps of CASE.
UPDATE par
SET Status =
CASE WHEN Q.d1Stat = 0 THEN '1'
WHEN Q.d1Stat < total THEN '2'
WHEN Q.d2Stat < total THEN '3'
WHEN Q.d2Stat = total THEN '4'
ELSE 'ERROR' END
FROM TableParent par
JOIN
(select tp.TableParentID,
count(*) as total, /*count of children*/
count(tc.Date1) as d1Stat, /*count where Date1 is not null*/
count(tc.Date2) as d2Stat /*count where Date2 is not null*/
from TableParent tp
join TableChild tc on tp.TableParentID = tc.TableParentID
group by tp.TableParentID) AS Q
ON Q.TableParentID = par.TableParentID
HTH, Vladan
... and the DDL on which it worked:
CREATE TABLE TableParent (TableParentID int identity, Status varchar(5))
CREATE TABLE TableChild (TableChildID int identity, TableParentID int, Date1 datetime, Date2 datetime)
-
Im starting to understand the count(*) and count(column), but what i dont understand is the
"count(column) = 0" statement. Does this translate to count(column)'s null values = 0?
-
"count(column) = 0" statement. This translate to count(column)'s non null values = 0
* Noel -
It is as noeld said... You can use the inner part of the query (derived table) to see better what happens and how the counts are evaluated:
SELECT tp.TableParentID, count(*) as total, count(tc.Date1) as d1Stat, count(tc.Date2) as d2Stat
FROM TableParent tp
JOIN TableChild tc on tp.TableParentID = tc.TableParentID
GROUP BY tp.TableParentID
This will show you the respective counts and you can compare them with actual data.
-
Thanks for all your help. Now i have another issue. How do check determine if a parent record has 0 child records?
-
Hi,
since COUNT(column_name) counts only values that are not NULL, and the CASE statement stops executing as soon as it hits TRUE, you could rephrase the query along these lines (I took the liberty of assigning status 'NONE' to such records - replace it with whatever status you need):
UPDATE par
SET Status =
CASE WHEN Q.total = 0 THEN 'NONE' /*0 child records*/
WHEN Q.d1Stat = 0 THEN '1'
WHEN Q.d1Stat < total THEN '2'
WHEN Q.d2Stat < total THEN '3'
WHEN Q.d2Stat = total THEN '4'
ELSE 'ERROR' END
FROM TableParent par
JOIN
(select tp.TableParentID,
count(tc.TableChildID) as total, /*count of children*/
count(tc.Date1) as d1Stat, /*count where Date1 is not null*/
count(tc.Date2) as d2Stat /*count where Date2 is not null*/
from TableParent tp
left join TableChild tc on tp.TableParentID = tc.TableParentID
group by tp.TableParentID) AS Q
ON Q.TableParentID = par.TableParentID
EDIT : for the TOTAL, count(tc.TableParentID) and count(tc.TableChildID) give the same results, but the code looks more understandable when Child is referenced.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply