Query that may need groupby - SQL Server Forums

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

Query that may need groupby

Author

Topic

dog2bert
Starting Member

13 Posts

Posted - 2012-03-12 : 16:18:06

I am trying to provide an audit report where a material is marked for deletion at all of the plants for a given company code and the company code and client code delete indicators (DI) are NULL.

Here is the table of some "faked" data:

----------------------------------------------------------------------------------------------------------------------------------------------------
| Material ID | Client | Company | Plant | Client DI | Company DI | Plant DI |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | 10 | 1 | aa | | | I |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | 10 | 2 | bb | | | I |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | 10 | 3 | cc | | | I |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 2 | 30 | 50 | aa | | | I |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 2 | 50 | 43 | bb | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------

dog2bert
Starting Member

13 Posts

Posted - 2012-03-12 : 16:19:31

So all of Material 1 should be returned.

For material 2, only client 30 company 50 should be returned.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 16:50:01

[code]
SELECT *
FROM table
WHERE [Client DI] IS NULL
AND [Company DI] IS NULL
AND [Plant DI] IS NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dog2bert
Starting Member

13 Posts

Posted - 2012-03-13 : 11:25:21

That SQL would only give me results with Client, Company, and Plant Delete Indicators set to 0.

I need it where the Client and Company Delete Indicator is blank, and all of the plants of a particular company have a value for Plant Delete indicator

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-13 : 15:08:06

quote:
Originally posted by dog2bert

That SQL would only give me results with Client, Company, and Plant Delete Indicators set to 0.

I need it where the Client and Company Delete Indicator is blank, and all of the plants of a particular company have a value for Plant Delete indicator

then tweak where condition accordingly


SELECT *
FROM table
WHERE [Client DI]  = ''
AND [Company DI] = ''
AND [Plant DI] > ''

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dog2bert
Starting Member

13 Posts

Posted - 2012-03-13 : 15:28:13

With this set of data, the client and company should not be returned because every plant at company 10 did not have plant delete indicator set to I
Client A [DI:""] Company 10 [DI:""] Plant A1 [DI:I]
Client A [DI:""] Company 10 [DI:""] Plant A2 [DI:I]
Client A [DI:""] Company 10 [DI:""] Plant A3 [DI:I]
Client A [DI:""] Company 10 [DI:""] Plant A4 [DI:""]

With this data client and company should be returned:
Client A [DI:""] Company 10 [DI:""] Plant A1 [DI:I]
Client A [DI:""] Company 10 [DI:""] Plant A2 [DI:I]
Client A [DI:""] Company 10 [DI:""] Plant A3 [DI:I]
Client A [DI:""] Company 10 [DI:""] Plant A4 [DI:I]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-13 : 15:52:18

[code]
SELECT Client
FROM table
GROUP BY Client
HAVING SUM(CASE WHEN [Plant DI] > '' THEN 0 ELSE 1 END)=0
AND SUM(CASE WHEN [Client DI] = '' AND [Company DI] = '' THEN 0 ELSE 1 END)=0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources