SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 T Sql query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dotnet2010
Starting Member

6 Posts

Posted - 07/09/2013 :  00:06:21  Show Profile  Reply with Quote
I have a packages and items
Each package has more than one item with status
The package got his status from the items
I want two query that can show for me how many packages that didn't have the same status of at least one of his items
The second show the packlist if not all his items have the same status as his status

Package
PackageId StatusId
1 -------- 1
2 -------- 2
3 -------- Null

Item
ItemId PackageId StatusID Date
1 -------- 1 -------- 1 ------ 1/1/2000
2 -------- 1 --------1 -------- 1/1/2001
3 -------- 3 -------- 2 -------- 1/1/2001
4 -------- 3 -------- 2 -------- 1/2/2001
5 -------- 2 -------- 2 -------- 1/2/2001
6 -------- 2 -------- 3 -------- 1/3/2001

So the first query will show PackageID 3 that hasn't the same status as his items it is null
with these details
PackageId and one of the dates of the items.

the second query will show packlageId 2 and Package Id 3
with the same details

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/09/2013 :  01:13:02  Show Profile  Reply with Quote
do you mean this for first scenario?

SELECT p.PackageID,MAX(i.Date)
FROM Item i
INNER JOIN Package p
ON p.PackageID = i.PackageID
GROUP BY p.PackageID
HAVING SUM(CASE WHEN i.StatusId <> COALESCE(p.StatusId,-1) THEN 1 ELSE 0 END) =0



SELECT p.PackageID,MAX(i.Date)
FROM Item i
INNER JOIN Package p
ON p.PackageID = i.PackageID
GROUP BY p.PackageID
HAVING SUM(CASE WHEN i.StatusId = COALESCE(p.StatusId,-1) THEN 1 ELSE 0 END) =0


and for second


SELECT p.PackageID,MAX(i.Date)
FROM Item i
INNER JOIN Package p
ON p.PackageID = i.PackageID
GROUP BY p.PackageID
HAVING SUM(CASE WHEN i.StatusId <> COALESCE(p.StatusId,-1) THEN 1 ELSE 0 END) > 0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000