?
Solved

Add another "Having" clause?

Posted on 2016-07-18
9
Medium Priority
?
51 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 49

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 49

Accepted Solution

by:
PortletPaul earned 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 49

Expert Comment

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

Author Comment

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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

770 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