Solved

Add another "Having" clause?

Posted on 2016-07-18
9
42 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 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

20 Experts available now in Live!

Get 1:1 Help Now