Solved

Add another "Having" clause?

Posted on 2016-07-18
9
45 Views
Last Modified: 2016-07-19
I had this question after viewing How can I add this SQL to the existing SQL I have?.

Paul helped me with the the solution in the link above and it works but I just realized I need to have another clause. I tried it in the "where" clause but that won't work.

1. I need to have this
LoadTypeId not in (2, 3,6,10)

Open in new window

2. We actually have a "view" to read the enums so instead of #1, I want to use the view

I have it to here but how can I add another having clause to it?

This is SQL 2014

SELECT
      c.CommSalesOrderId
    , a.Code
	, count(CASE WHEN (lv.Name = 'Settled') THEN l.Loadid END) AS SettledLoads
	,count(CASE WHEN ( lv.Name = 'Waived') THEN l.Loadid END) AS WavedLoads

FROM (
        select CommSalesOrder_CommSalesOrderId
        from loads
		left join dbo.LoadStatusER as V
                  on V.enumId = loadStatus 
        left join [dbo].[LoadTypeTypesER] ltv on ltv.EnumId = loadtypeId  ---*** I added this but now how do I exclude the values?
		 where shipdate  >= (@shipdateFrom) AND ShipDate < (@shipdateTo +1)
		 --and LoadTypeId not in (2, 3,6,10)  --*** this won't work
		    
        group by CommSalesOrder_CommSalesOrderId
		having  sum(case when v.enumId is not null then 0 else 1 end) = 0 

		
      ) AS lgroup
INNER JOIN CommSalesOrders AS c ON c.CommSalesOrderId = lgroup.CommSalesOrder_CommSalesOrderId
inner join loads l  ON c.CommSalesOrderId = l.CommSalesOrder_CommSalesOrderId
inner join accounts a on a.AccountId = c.Seller_AccountId
--inner join Addresses ad on ad.AccountId = a.AccountId
inner join [dbo].[LoadStatusER] lv on l.LoadStatus = lv.enumId

group by
      c.CommSalesOrderId
    , a.Code

Open in new window

0
Comment
Question by:Camillia
  • 6
  • 3
9 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41717896
You can include a subquery for NOT IN like this:

             where shipdate  >= (@shipdateFrom) AND ShipDate < (@shipdateTo +1)
             and LoadTypeId not in (select LoadTypeId from that_view)  

but be careful with NULL values when using NOT IN this way.
But I'm afraid I don't fully understand this question. All I do understand is that line 14 does not work and that you have a view.

What is the name of that view?
What columns does it contains? (does it have LoadTypeId?)
What column of the subquery will you compare it to? (is it still LoadTypeId ?)
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41717969
By the way, using a subquery inside a NOT IN () construct can lead to problems if a NULL is returned by the subquery, so please do take care, e.g.


     where shipdate  >= (@shipdateFrom) AND ShipDate < (@shipdateTo +1)
     and LoadTypeId not in (select v.LoadTypeId from that_view v where v.LoadTypeId IS NOT NULL)
0
 
LVL 7

Author Comment

by:Camillia
ID: 41717979
Thanks, Paul. Let me try it in the morning. I'll post back.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 7

Author Comment

by:Camillia
ID: 41719216
No, that won't do it. It has to be in "Having" clause. This is the related question above.

If I add "not in"...it won't bring back any rows. There are 3 rows. One has to be brought back.

It needs to be in "Having" like the inner SQL. You see I have the left-join and now somehow I need to have "Having" just like the other "having" clause you helped me with.
 left join [dbo].[LoadTypeTypesER] ltv on ltv.EnumId = loadtypeId

Open in new window


   select CommSalesOrder_CommSalesOrderId
        from loads
		left join dbo.LoadStatusER as V
                  on V.enumId = loadStatus 
        left join [dbo].[LoadTypeTypesER] ltv on ltv.EnumId = loadtypeId  ---*** I added this but now how do I exclude the values?
		 where shipdate  >= (@shipdateFrom) AND ShipDate < (@shipdateTo +1)
		 --and LoadTypeId not in (2, 3,6,10)  --*** this won't work
		    
        group by CommSalesOrder_CommSalesOrderId
		having  sum(case when v.enumId is not null then 0 else 1 end) = 0 

Open in new window

0
 
LVL 7

Author Comment

by:Camillia
ID: 41719240
I did this and for one row, I get the correct result but not sure if I will run into issues...

I added to the 'Having' clause on the outer SQL. I've noted the section below

SELECT
      c.CommSalesOrderId
    , a.Code
	, count(CASE WHEN (lv.Name = 'Settled') THEN l.Loadid END) AS SettledLoads
	, count(CASE WHEN ( lv.Name = 'Waived') THEN l.Loadid END) AS WavedLoads
	,l.loadid

FROM (
        select CommSalesOrder_CommSalesOrderId, loadid
        from loads
		left join dbo.LoadStatusER as V
                  on V.enumId = loadStatus 
      --  left join [dbo].[LoadTypeTypesER] ltv on ltv.EnumId = loadtypeId
		 where shipdate  >= (@shipdateFrom) AND ShipDate < (@shipdateTo +1)
		--and LoadTypeId not in (select v.EnumId from [dbo].[LoadTypeTypesER] v where v.EnumId IS NOT NULL)
		       and CommSalesOrder_CommSalesOrderId = 81972 ---------------------------*************** remove this
        group by CommSalesOrder_CommSalesOrderId, loadid
		having  sum(case when v.enumId is not null then 0 else 1 end) = 0 

		
      ) AS lgroup
INNER JOIN CommSalesOrders AS c ON c.CommSalesOrderId = lgroup.CommSalesOrder_CommSalesOrderId
inner join loads l  ON c.CommSalesOrderId = l.CommSalesOrder_CommSalesOrderId
inner join accounts a on a.AccountId = c.Seller_AccountId
--inner join Addresses ad on ad.AccountId = a.AccountId
inner join [dbo].[LoadStatusER] lv on l.LoadStatus = lv.enumId
inner join [dbo].[LoadTypeTypesER] ltv on ltv.EnumId = loadtypeId ---**** Added it here

group by
      c.CommSalesOrderId
    , a.Code
	,l.LoadId
having ( (count(CASE WHEN (lv.Name = 'Settled') THEN l.Loadid END) > 0  or  count(CASE WHEN ( lv.Name = 'Waived') THEN l.Loadid END) > 0 ) 
           AND  ---***** added these here *****
            ( count(CASE WHEN ( ltv.Name = 'PC') THEN l.Loadid END) = 0  or count(CASE WHEN ( ltv.Name = 'DL') THEN l.Loadid END) = 0 
			  or count(CASE WHEN ( ltv.Name = 'RJ') THEN l.Loadid END) = 0  or count(CASE WHEN ( ltv.Name = 'SC') THEN l.Loadid END) = 0  --*****
			
			)
	     )

Open in new window

0
 
LVL 7

Author Comment

by:Camillia
ID: 41719248
This one works for one test row as well. In this one, I put it in the "Having" of the inner SQL. I get the same one row but what's the difference between the one I have above (in outer SQL) and this one (inner sql). Would I get unexpected results?

SELECT
      c.CommSalesOrderId
    , a.Code
	, count(CASE WHEN (lv.Name = 'Settled') THEN l.Loadid END) AS SettledLoads
	, count(CASE WHEN ( lv.Name = 'Waived') THEN l.Loadid END) AS WavedLoads
	,l.loadid

FROM (
        select CommSalesOrder_CommSalesOrderId, loadid
        from loads
		left join dbo.LoadStatusER as V
                  on V.enumId = loadStatus 
               inner join [dbo].[LoadTypeTypesER] ltv on ltv.EnumId = loadtypeId ---***added it here
		 where shipdate  >= (@shipdateFrom) AND ShipDate < (@shipdateTo +1)
		
		       and CommSalesOrder_CommSalesOrderId = 81972 ---------------------------*************** remove this
        group by CommSalesOrder_CommSalesOrderId, loadid
		having  ( sum(case when v.enumId is not null then 0 else 1 end) = 0 
		
		         AND ( ----***** I added it here
				 
				  ( count(CASE WHEN ( ltv.Name = 'PC') THEN Loadid END) = 0  or count(CASE WHEN ( ltv.Name = 'DL') THEN Loadid END) = 0 
		        	  or count(CASE WHEN ( ltv.Name = 'RJ') THEN Loadid END) = 0  or count(CASE WHEN ( ltv.Name = 'SC') THEN Loadid END) = 0 
			
		     	)
				 ) 
		
		          )

		
      ) AS lgroup
INNER JOIN CommSalesOrders AS c ON c.CommSalesOrderId = lgroup.CommSalesOrder_CommSalesOrderId
inner join loads l  ON c.CommSalesOrderId = l.CommSalesOrder_CommSalesOrderId
inner join accounts a on a.AccountId = c.Seller_AccountId
--inner join Addresses ad on ad.AccountId = a.AccountId
inner join [dbo].[LoadStatusER] lv on l.LoadStatus = lv.enumId
inner join [dbo].[LoadTypeTypesER] ltv on ltv.EnumId = loadtypeId

group by
      c.CommSalesOrderId
    , a.Code
	,l.LoadId
having ( (count(CASE WHEN (lv.Name = 'Settled') THEN l.Loadid END) > 0  or  count(CASE WHEN ( lv.Name = 'Waived') THEN l.Loadid END) > 0 ) 
          -- AND 
          --  ( count(CASE WHEN ( ltv.Name = 'PC') THEN l.Loadid END) = 0  or count(CASE WHEN ( ltv.Name = 'DL') THEN l.Loadid END) = 0 
			--  or count(CASE WHEN ( ltv.Name = 'RJ') THEN l.Loadid END) = 0  or count(CASE WHEN ( ltv.Name = 'SC') THEN l.Loadid END) = 0 
			
			--)
	     )

Open in new window

0
 
LVL 7

Author Closing Comment

by:Camillia
ID: 41719305
Going to close this and post another one with some sample data. Thanks for your help
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41719932
Ok. Thanks.
0
 
LVL 7

Author Comment

by:Camillia
ID: 41719938
Thanks, again, Paul.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now