3 Replies - 208 Views - Last Post: 01 April 2019 - 09:26 AM

#1 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,215
  • Joined: 12-January 10

QUERY not returning the desired output

Posted 01 April 2019 - 05:39 AM

the query is supposed to return the remaining amounts from the day before to the added amounts to the current production date
instead it just returns null not sure why

if you run the select in the case statement it returns info 55.00


query:

declare		@DivisionID	varchar(50) = 'ATL'
declare		@ProdDate	datetime = '1/10/2019'
	
BEGIN
SET NOCOUNT ON;

	declare @DivisionKey int
	select @DivisionKey = DivisionKey from dbo.aaa_tUptimeDivision where DivisionID = @DivisionID

	
	SELECT Alias
			   ,dbo.aaa_tUptimeCategorydivision.WorkCenter	
			   ,dbo.aaa_tUptimeCategorydivision.[Categorykey] 
			   ,dbo.aaa_tuptimecategory.CategoryID	
			   ,dbo.aaa_tUptimeFeedstock.ProdDate	
			 
			  ,CASE WHEN  BLK_Tons_Added IS NULL OR BLK_Tons_Added = 0 THEN (BLK_Tons_Added + (SELECT TOP 1 BLK_RemainingOnHand from dbo.aaa_tuptimecategory
	inner join dbo.aaa_tUptimeCategorydivision 
	on dbo.aaa_tuptimecategory.Categorykey = dbo.aaa_tUptimeCategorydivision.Categorykey
	left outer join dbo.aaa_tuptimefeedstock
	on dbo.aaa_tuptimefeedstock.divisionkey = dbo.aaa_tUptimeCategorydivision.DivisionKey 
	and dbo.aaa_tuptimefeedstock.Categorykey =  dbo.aaa_tUptimeCategorydivision.Categorykey
	and  dbo.aaa_tUptimeFeedstock.ProdDate< '1/10/2019'[email protected]
	
	where
	dbo.aaa_tUptimeCategorydivision.divisionkey = 31 [email protected]
	and dbo.aaa_tUptimeCategorydivision.active = 1 order by ProdDate desc )) ELSE BLK_Tons_Added END AS BLK_Tons_Added
	,dbo.aaa_tUptimeFeedstock.[BLK_Consumed_Tons]
	,dbo.aaa_tUptimeFeedstock.[BLK_RemainingOnHand]
	,dbo.aaa_tUptimeFeedstock.[BLKWT_Tons_Added]
	,CASE WHEN  BLKWT_Tons_Added IS NULL OR BLK_Tons_Added = 0 THEN (BLKWT_Tons_Added + (SELECT TOP 1 BLKWT_RemainingOnHand from dbo.aaa_tuptimecategory
	inner join dbo.aaa_tUptimeCategorydivision 
	on dbo.aaa_tuptimecategory.Categorykey = dbo.aaa_tUptimeCategorydivision.Categorykey
	left outer join dbo.aaa_tuptimefeedstock
	on dbo.aaa_tuptimefeedstock.divisionkey = dbo.aaa_tUptimeCategorydivision.DivisionKey 
	and dbo.aaa_tuptimefeedstock.Categorykey =  dbo.aaa_tUptimeCategorydivision.Categorykey
	and  dbo.aaa_tUptimeFeedstock.ProdDate< @ProdDate 
	
	where
	dbo.aaa_tUptimeCategorydivision.divisionkey = @DivisionKey 
	and dbo.aaa_tUptimeCategorydivision.active = 1 order by ProdDate desc)) ELSE BLKWT_Tons_Added END AS BLKWT_Tons_Added
	,dbo.aaa_tUptimeFeedstock.[BLKWT_Consumed_Tons]
	, dbo.aaa_tUptimeFeedstock.[BLKWT_RemainingOnHand]

	into	#tmpGrade

	from dbo.aaa_tuptimecategory
	inner join dbo.aaa_tUptimeCategorydivision 
	on dbo.aaa_tuptimecategory.Categorykey = dbo.aaa_tUptimeCategorydivision.Categorykey
	left outer join dbo.aaa_tuptimefeedstock
	on dbo.aaa_tuptimefeedstock.divisionkey = dbo.aaa_tUptimeCategorydivision.DivisionKey 
	and dbo.aaa_tuptimefeedstock.Categorykey =  dbo.aaa_tUptimeCategorydivision.Categorykey
	and  dbo.aaa_tUptimeFeedstock.ProdDate = @ProdDate 
	and dbo.aaa_tUptimeFeedstock.status = 1
	where
	dbo.aaa_tUptimeCategorydivision.divisionkey = @DivisionKey 
	and dbo.aaa_tUptimeCategorydivision.active = 1

	--if not exists(select BLK_Tons_Added from #tmpGrade )
	--begin
	--	insert	#tmpGrade

	----	SELECT ISNULL((SELECT TOP 1 BLK_RemainingOnHand from dbo.aaa_tuptimecategory
	----inner join dbo.aaa_tUptimeCategorydivision 
	----on dbo.aaa_tuptimecategory.Categorykey = dbo.aaa_tUptimeCategorydivision.Categorykey
	----left outer join dbo.aaa_tuptimefeedstock
	----on dbo.aaa_tuptimefeedstock.divisionkey = dbo.aaa_tUptimeCategorydivision.DivisionKey 
	----and dbo.aaa_tuptimefeedstock.Categorykey =  dbo.aaa_tUptimeCategorydivision.Categorykey
	----and  dbo.aaa_tUptimeFeedstock.ProdDate< '1/10/2019' 
	----and dbo.aaa_tUptimeFeedstock.status = 1
	----where
	----dbo.aaa_tUptimeCategorydivision.divisionkey =31
	----and dbo.aaa_tUptimeCategorydivision.active = 1
	----order by ProdDate desc),0)

	----END


	

	select * from #tmpGrade
	drop table #tmpGrade

	
END




output:

Shredder	601	1	Primary1	NULL	NULL	NULL	NULL	NULL	NULL	NULL
2" Granulator	632	13	Granulator1	NULL	NULL	NULL	NULL	NULL	NULL	NULL




from the test table is attached and the highlighted is the field it checks

Attached image(s)

  • Attached Image


Is This A Good Question/Topic? 0
  • +

Replies To: QUERY not returning the desired output

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15356
  • View blog
  • Posts: 61,557
  • Joined: 12-June 08

Re: QUERY not returning the desired output

Posted 01 April 2019 - 08:28 AM

Grumpy this morning, but serially - what the heck is up with all of htat?! No aliasing.. those CASE when sub queries could be done up front into a temp table.. an 'into' chucked in the middle... no aliasing!

I wish I could help more but that without all the other claptrap definitions to run it I can't tinker with it. I would start by examining the main jonis (inner and left outter).. seeing which maybe (if you are lucky) muckin things up.


Side note - missing with(nolock)s..
Was This Post Helpful? 0
  • +
  • -

#3 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,215
  • Joined: 12-January 10

Re: QUERY not returning the desired output

Posted 01 April 2019 - 08:37 AM

I figured it out but thanks


fixed code:

BEGIN

SET NOCOUNT ON;

    declare @DivisionKey int
	select @DivisionKey = DivisionKey from dbo.aaa_tUptimeDivision where DivisionID = @DivisionID
 
    SELECT Alias

               ,dbo.aaa_tUptimeCategorydivision.WorkCenter 
               ,dbo.aaa_tUptimeCategorydivision.[Categorykey]
               ,dbo.aaa_tuptimecategory.CategoryID 
               ,dbo.aaa_tUptimeFeedstock.ProdDate  
			   ,CASE WHEN  dbo.aaa_tuptimefeedstock.BLK_Tons_Added IS NULL OR dbo.aaa_tuptimefeedstock.BLK_Tons_Added = 0 THEN (dbo.aaa_tuptimefeedstock.BLK_Tons_Added + (SELECT TOP 1 dbo.aaa_tuptimefeedstock.BLK_RemainingOnHand from dbo.aaa_tuptimecategory
			       inner join dbo.aaa_tUptimeCategorydivision
				   on dbo.aaa_tuptimecategory.Categorykey = dbo.aaa_tUptimeCategorydivision.Categorykey
				   left outer join dbo.aaa_tuptimefeedstock
				   on dbo.aaa_tuptimefeedstock.divisionkey = dbo.aaa_tUptimeCategorydivision.DivisionKey
				   and dbo.aaa_tuptimefeedstock.Categorykey =  dbo.aaa_tUptimeCategorydivision.Categorykey
				   and  dbo.aaa_tUptimeFeedstock.ProdDate< @[email protected]    
				   where
				   dbo.aaa_tUptimeCategorydivision.divisionkey = @DivisionKey [email protected]
				   and dbo.aaa_tUptimeCategorydivision.active = 1 order by ProdDate desc )) ELSE BLK_Tons_Added END AS BLK_Tons_Added
			   ,dbo.aaa_tUptimeFeedstock.[BLK_Consumed_Tons]
		       ,dbo.aaa_tUptimeFeedstock.[BLK_RemainingOnHand]
			   ,CASE WHEN  BLKWT_Tons_Added IS NULL OR BLK_Tons_Added = 0 THEN (BLKWT_Tons_Added + (SELECT TOP 1 WIPBLKWT_RemainingOnHand from dbo.aaa_tuptimecategory
				   inner join dbo.aaa_tUptimeCategorydivision
				   on dbo.aaa_tuptimecategory.Categorykey = dbo.aaa_tUptimeCategorydivision.Categorykey
				   left outer join dbo.aaa_tuptimefeedstock
				   on dbo.aaa_tuptimefeedstock.divisionkey = dbo.aaa_tUptimeCategorydivision.DivisionKey
				   and dbo.aaa_tuptimefeedstock.Categorykey =  dbo.aaa_tUptimeCategorydivision.Categorykey
				   and  dbo.aaa_tUptimeFeedstock.ProdDate< @ProdDate    
				   where
				   dbo.aaa_tUptimeCategorydivision.divisionkey = @DivisionKey
				   and dbo.aaa_tUptimeCategorydivision.active = 1 order by ProdDate desc)) ELSE BLKWT_Tons_Added END AS BLKWT_Tons_Added
			   ,dbo.aaa_tUptimeFeedstock.[BLKWT_Consumed_Tons]
			   ,dbo.aaa_tUptimeFeedstock.[WIPBLKWT_RemainingOnHand]

    into    #tmpGrade

    from dbo.aaa_tuptimecategory
    inner join dbo.aaa_tUptimeCategorydivision
    on dbo.aaa_tuptimecategory.Categorykey = dbo.aaa_tUptimeCategorydivision.Categorykey
    left outer join dbo.aaa_tuptimefeedstock
    on dbo.aaa_tuptimefeedstock.divisionkey = dbo.aaa_tUptimeCategorydivision.DivisionKey
    and dbo.aaa_tuptimefeedstock.Categorykey =  dbo.aaa_tUptimeCategorydivision.Categorykey
    and  dbo.aaa_tUptimeFeedstock.ProdDate = @ProdDate
    and dbo.aaa_tUptimeFeedstock.status = 1
    where
	dbo.aaa_tUptimeCategorydivision.divisionkey = @DivisionKey
    and dbo.aaa_tUptimeCategorydivision.active = 1

	declare @tempBlk_added as integer
	select @tempBlk_added =  BLK_Tons_Added from #tmpGrade 
	inner join dbo.aaa_tuptimecategory
	on #tmpGrade.Categorykey = dbo.aaa_tuptimecategory.Categorykey

	  if @tempBlk_added is null  	   
	  update  #tmpGrade	 
	  set BLK_Tons_Added =	  
	  (
		  SELECT TOP 1 dbo.aaa_tuptimefeedstock.BLK_RemainingOnHand
		  from  dbo.aaa_tuptimecategory
			inner join dbo.aaa_tUptimeCategorydivision
			on dbo.aaa_tuptimecategory.Categorykey = dbo.aaa_tUptimeCategorydivision.Categorykey
			left outer join dbo.aaa_tuptimefeedstock
			on dbo.aaa_tuptimefeedstock.divisionkey = dbo.aaa_tUptimeCategorydivision.DivisionKey  
			 and dbo.aaa_tuptimefeedstock.Categorykey =  dbo.aaa_tUptimeCategorydivision.Categorykey
			and  dbo.aaa_tUptimeFeedstock.ProdDate< @ProdDate    
			where
			dbo.aaa_tUptimeCategorydivision.divisionkey = @DivisionKey
			and dbo.aaa_tUptimeCategorydivision.active = 1 
			and #tmpGrade.Categorykey = dbo.aaa_tuptimefeedstock.Categorykey
			order by ProdDate desc
	 )
	
	declare @tempBlkWT_added as integer
	select @tempBlkWT_added =  BLKWT_Tons_Added from #tmpGrade 
	inner join dbo.aaa_tuptimecategory
	on #tmpGrade.Categorykey = dbo.aaa_tuptimecategory.Categorykey
	 
	  if @tempBlkWT_added is null  	   
	  update  #tmpGrade	 
	  set BLKWT_Tons_Added =	  
	  (
		  SELECT TOP 1 dbo.aaa_tuptimefeedstock.WIPBLKWT_RemainingOnHand
		  from  dbo.aaa_tuptimecategory
			inner join dbo.aaa_tUptimeCategorydivision
			on dbo.aaa_tuptimecategory.Categorykey = dbo.aaa_tUptimeCategorydivision.Categorykey
			left outer join dbo.aaa_tuptimefeedstock
			on dbo.aaa_tuptimefeedstock.divisionkey = dbo.aaa_tUptimeCategorydivision.DivisionKey  
			 and dbo.aaa_tuptimefeedstock.Categorykey =  dbo.aaa_tUptimeCategorydivision.Categorykey
			and  dbo.aaa_tUptimeFeedstock.ProdDate< @ProdDate    
			where
			dbo.aaa_tUptimeCategorydivision.divisionkey = @DivisionKey
			and dbo.aaa_tUptimeCategorydivision.active = 1 
			and #tmpGrade.Categorykey = dbo.aaa_tuptimefeedstock.Categorykey
			order by ProdDate desc
	 )
	 	 
   select * from #tmpGrade
   drop table #tmpGrade

 
END



View Postmodi123_1, on 01 April 2019 - 11:28 AM, said:

Grumpy this morning, but serially - what the heck is up with all of htat?! No aliasing.. those CASE when sub queries could be done up front into a temp table.. an 'into' chucked in the middle... no aliasing!

I wish I could help more but that without all the other claptrap definitions to run it I can't tinker with it. I would start by examining the main jonis (inner and left outter).. seeing which maybe (if you are lucky) muckin things up.


Side note - missing with(nolock)s..


they dont like alias' to be used here
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15356
  • View blog
  • Posts: 61,557
  • Joined: 12-June 08

Re: QUERY not returning the desired output

Posted 01 April 2019 - 09:26 AM

Shame.. getting a kick in nuts vs getting a kick in the aliased nuts.. I know which one I would pick. :D hahaha
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1