Subquery Totals based on clauses

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Subquery Totals based on clauses

Author

Topic

fergielala
Starting Member

2 Posts

Posted - 2013-08-01 : 09:10:09

I have a Master table with a OrderNbr which is also contained in the Detail table.

It's a 1 to Many relationship, respectively.

I want to update the MASTER.FinalizedDate using a "select top 1 FinalizedDate order by FinalizedDate DESC" from the Detail table but the clause is ALL the Status have to be "F". So OrderNbr 12345 should not get updated because it contains a 'O'. OrderNbr 67899 should get updated in the Master table to 2/26/2013 because all have a 'F' and the last date to post is the official finalized date.

Here is what I came up with.......so far, but not sure how to work in the Status piece on 1 to M.

The rub here is that even if one row has the 'O' status I want to ignore the update. If all have the 'F' then I want the udpate to happen.

Update MASTER
Set FinalizedDate = (select top 1 d.FinazliedDate from Detail d
where m.OrderNbr = d.OrderNbr
and d.Status not in ('O')
Order by FinalizedDate DESC)
From MASTER m

How do I not include all 3 rows for OrderNbr 12345 because one row has the Status "O" in the DETAIL table?

Here are the table looks........

MASTER
OrderNbr Ytotals Ztotals Xtotals Finalized Date
12345 $1,500 $1,500 $1,200
67899 $1,200 $1,100 $900
DETAIL
OrderNbr ItemNbr Price Status Finalized Date
12345 63453453 $1,400 F 1/2/2013
12345 554444 $1,500 F 1/2/2013
12345 545444 $2,200 O NULL
67899 333334 $899 F 2/24/2013
67899 3434344 $659 F 2/24/2013
67899 434676 $499 F 2/26/2013
67899 78888 $599 F 2/24/2013

bp

WAmin
Starting Member

16 Posts

Posted - 2013-08-01 : 09:30:31

UPDATE
~~dbo.~~MASTER
SET
FinalizedDate = (SELECT TOP 1 d.FinazliedDate FROM Detail d WHERE m.OrderNbr = d.OrderNbr AND NOT EXISTS
(SELECT * FROM Detail id WHERE id.OrderNbr=d.OrderNbr AND id.Status='O')
ORDER BY FinalizedDate DESC)

sorry ignore dbo, i always use dbo in my DB so just used here

fergielala
Starting Member

2 Posts

Posted - 2013-08-01 : 10:06:21

EXCELLENT!

WORKS GREAT!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-01 : 12:48:12

as i understand you just need this


UPDATE m
SET m.FinalizedDate = d.LatestDate
FROM MAster m
INNER JOIN (SELECT OrderNbr,MAX(FinalizedDate) AS LatestDate
            FROM Detail
            GROUP BY OrderNbr
            HAVING SUM(CASE WHEN Status <> 'F' THEN 1 ELSE 0 END) = 0
            )d
ON d.OrderNbr = m.OrderNbr

no need of those nested subqueries

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources