?
Solved

odd results with less than or equal critia limiting lower negative values

Posted on 2014-11-08
8
Medium Priority
?
125 Views
Last Modified: 2014-11-09
I have a stored procedure that when i have a criteria for DTS of say <= 200 i get results that are under 200, but are limited to those values that are under -200?  Why is this, i expected <= to be just that.  Open ended on the negative scale.

USE [zABC_v2]
GO
/****** Object:  StoredProcedure [dbo].[sp_Pipeline]    Script Date: 11/8/2014 12:29:47 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE sp_PipelineParam 
	-- Add the parameters for the stored procedure here
	@crit_Salesman varchar(50),
	@crit_DTS int,
	@crit_Age int,
	@crit_Prob float(8),
	@crit_Conf varchar(3)
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

select
tblJobs.Job_Num,
   dbo.Customers.[Company],
   tblJobs.Salesman,
   tblJobs.Contract_Date,
   tblJobs.fldChangeDateStamp,
   tblJobs.Sale_Date,
   tblJobs.Contract_Date,
   tblJobs.Probability,
   tblJobs.SetCall,
   tblJobs.Deposit, 
   tblJobs.Confirmed,
   Iif([sale_date]>'1/1/1900', 0,[deposit]) AS condDeposit, 
   isnull([Amount],0)+[Tax]+[Tax 2] as Gross,
	datediff(d,GETDATE(),[setcall]) AS DTS,
	datediff(d,[Contract_Date],GETDATE()) AS Age,
	isnull([Amount]+[Tax]+[Tax 2],0)-isnull([SumOfAmount Alloc],0) as Due,
	isnull([Amount],0)-(isnull([SumOfCost],0)+isnull([SumPOLI],0))as GP
   		
		FROM dbo.tblJobs
		
		LEFT JOIN (
			SELECT payments.fldJob_Num, 
			  Sum(payments.[amount alloc]) AS [SumOfAmount Alloc]
       		FROM   payments 
			GROUP  BY payments.fldjob_num
			) AS sumPMTs ON dbo.tblJobs.Job_Num = sumPMTs.fldJob_Num


		LEFT JOIN (
			SELECT
			tblJobsLineItems.Job_Num,
			Sum([Cost]*[Quantity]*[Days]) AS SumofCost,
			Sum([Retail]*[Quantity]*[Days]) AS Amount 
		FROM dbo.[tblJobsLineItems] 
		GROUP BY dbo.tblJobsLineItems.Job_Num
		) AS sumLIs ON dbo.tbljobs.job_num = sumLIs.Job_num   
   
	  LEFT JOIN (
		 SELECT
		  [Purchase Order Lineitems].Job_Num,
		  Sum([Price]*[Quantity]) AS SumPOLI  
		FROM [Purchase Order Lineitems] 
		GROUP BY [Purchase Order Lineitems].Job_Num
		) as sumPOs ON tbljobs.job_num = sumPOs.Job_Num


		inner join
			[dbo].[Customers]
		on (dbo.tblJobs.[Customer Number] = dbo.customers.[Customer Number])

			WHERE dbo.tblJobs.Confirmed <> 'No'
		and
			isnull([Amount]+[Tax]+[Tax 2],0)-isnull([SumOfAmount Alloc],0) >0
		AND
			datediff(d,[setcall],GETDATE()) <= @crit_DTS
		AND
			datediff(d,[Contract_Date],GETDATE()) <= @crit_Age
		AND
			tblJobs.Salesman = @crit_Salesman
		AND
			tbljobs.Probability >= @crit_Prob
		AND
			tbljobs.Confirmed like @crit_Conf

	
END

Open in new window



 I am calling it like this:

exec dbo.sp_PipelineParam
@crit_Salesman = 'Tom Jones',
@crit_Age = 9999,
@crit_DTS = 400,
@crit_prob = .0,
@crit_conf = '%'

Results:
Tom Jones      -302
Tom Jones      -327
Tom Jones      -285
Tom Jones      -284
Tom Jones      52
Tom Jones      -398
Tom Jones      67

exec dbo.sp_PipelineParam
@crit_Salesman = 'Tom Jones',
@crit_Age = 9999,
@crit_DTS = 300,
@crit_prob = .0,
@crit_conf = '%'

Results:
Tom Jones      -285                                                                                          
Tom Jones      -284                                                                                          
Tom Jones      52                                                                                          
Tom Jones      -239                                                                                          
Tom Jones      67                                                                                          
Tom Jones      67                                                                                          


I an interested to get this figured out.  Thank you
0
Comment
Question by:pressMac
[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
  • 4
  • 3
8 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40430876
can you provide the  tblJobs.SetCall column for those results please
0
 

Author Comment

by:pressMac
ID: 40430908
this shows other columns.
Salesman      Contract_Date      Probability      SetCall      Confirmed     DTS      Age
Tom Jones      1/7/2014 15:34       1         1/10/2014 11:00      Yes      -302      305
Tom Jones      12/9/2013 11:53      0.7      12/16/2013 13:00      Yes      -327      334
Tom Jones      2/18/2014 12:53      0.5       1/27/2014 8:00       Yes      -285      263
Tom Jones      11/13/2013 6:23      0.7       1/28/2014 9:00       Yes      -284      360
Tom Jones      8/7/2014 18:28       1        12/30/2014 0:00       May      52        93
Tom Jones      8/27/2013 11:09      0.7      10/ 6/2013 1:00       Yes      -398      438
Tom Jones      5/27/2014 20:05      0.7       1/14/2015 10:00      Yes      67        165
Tom Jones      5/27/2014 20:05      0.7       1/14/2015 10:00      May      67        165
Tom Jones      10/8/2013 16:46      0.7       3/14/2014 1:00       Yes      -239      396
Tom Jones      6/26/2014 16:46      1         7/ 3/2014 7:00       Yes      -128      135

Open in new window

0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1000 total points
ID: 40430957
You are comparing an integer value (@crit_DTS int,) to the outcome of DATEDIFF()

datediff(d,[setcall],GETDATE()) <= @crit_DTS

If SetCall is in the future (after getdate) then the result is a negative number
If SetCall is in the past (before getdate) then the result is a positive number

The comparison is working correctly, but it might not be what you are expecting.
Try reversing the order of parameters in datedif() perhaps?

datediff(d,GETDATE(),[setcall]) <= @crit_DTS
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 

Author Comment

by:pressMac
ID: 40431294
I was expecting that if the crit_DTS (Days unTil Show) was 300 i would get values such as 67, 52 , -239, -284, -285  AND ALSO -398  since -398 is less than 300.  But i have to enter 400 as a criteria.  Why is the -398 not in the results for crit_DTS =300?  The dateDiff calculation is correct.
0
 
LVL 70

Assisted Solution

by:Qlemo
Qlemo earned 1000 total points
ID: 40431645
And yet Paul is correct. You are displaying datediff(d,GETDATE(),[setcall]) AS DTS but checking for datediff(d,[setcall],GETDATE()) <= @crit_DTS, which is the negated value. The results appear off for that reason.

On another note, it is much better to use NO expression containing a field, so I would write it
setcall <= dateadd(d, @crit_DTS, now) or setcall >= dateadd(d, -crit, now) or whatever your condition shall be correctly.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40431655
I cannot reproduce your issue when filtering only on that calculation.
update TblJobs
set SetCall = dateadd(day,399,cast(getdate() as date))
where Salesman = 'PP'
;

declare @crit_DTS int
set @crit_DTS = 200

select
    SetCall
  , datediff(day,SetCall,getdate())
  , @crit_DTS "@crit_DTS"
from TblJobs
where datediff(day,SetCall,getdate()) <= @crit_DTS
order by 
    SetCall

Open in new window

|                         SETCALL | COLUMN_1 | @CRIT_DTS |
|---------------------------------|----------|-----------|
|     July, 03 2014 07:00:00+0000 |      129 |       200 |
| December, 30 2014 00:00:00+0000 |      -51 |       200 |
|  January, 14 2015 10:00:00+0000 |      -66 |       200 |
|  January, 14 2015 10:00:00+0000 |      -66 |       200 |
| December, 13 2015 00:00:00+0000 |     -399 |       200 |

Open in new window


CREATE TABLE TblJobs
	([Salesman] varchar(20), [Contract_Date] datetime, [Probability] int, [SetCall] datetime, [Confirmed] varchar(3), [DTS] int, [Age] int)
;
	
INSERT INTO TblJobs
	([Salesman], [Contract_Date], [Probability], [SetCall], [Confirmed], [DTS], [Age])
VALUES
	('PP', '2014-01-07 15:34:00', 1, '2016-01-10 11:00:00', 'Yes', -302, 305),
	('Tom Jones', '2014-01-07 15:34:00', 1, '2014-01-10 11:00:00', 'Yes', -302, 305),
	('Tom Jones', '2013-12-09 11:53:00', 0.7, '2013-12-16 13:00:00', 'Yes', -327, 334),
	('Tom Jones', '2014-02-18 12:53:00', 0.5, '2014-01-27 08:00:00', 'Yes', -285, 263),
	('Tom Jones', '2013-11-13 06:23:00', 0.7, '2014-01-28 09:00:00', 'Yes', -284, 360),
	('Tom Jones', '2014-08-07 18:28:00', 1, '2014-12-30 00:00:00', 'May', 52, 93),
	('Tom Jones', '2013-08-27 11:09:00', 0.7, '2013-10-06 01:00:00', 'Yes', -398, 438),
	('Tom Jones', '2014-05-27 20:05:00', 0.7, '2015-01-14 10:00:00', 'Yes', 67, 165),
	('Tom Jones', '2014-05-27 20:05:00', 0.7, '2015-01-14 10:00:00', 'May', 67, 165),
	('Tom Jones', '2013-10-08 16:46:00', 0.7, '2014-03-14 01:00:00', 'Yes', -239, 396),
	('Tom Jones', '2014-06-26 16:46:00', 1, '2014-07-03 07:00:00', 'Yes', -128, 135)
;

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40431659
well spotted Qlemo
0
 

Author Comment

by:pressMac
ID: 40431939
Hi Olemo,

Yep, you got it.  I was chasing my tail.  

regarding removing expressions containing field.

on the baseline old criteria, i had the following performance: ( i ran twice for testing, 20ms was first )
  Client processing time      10              20      
  Total execution time              420              420      
  Wait time on server replies      400              400      

then i changed the DTS criteria to:
 -- datediff(d,GETDATE(),[setcall]) <= @crit_DTS  --old
setcall <= dateadd(d, @crit_DTS, GETDATE())

new results with new DTS criteria construction ( i ran  twice, 30 was first)  Hmmm, this is slow

  Client processing time      40             30
  Total execution time      410             480
  Wait time on server replies      370             450

So i reconstructed both AGE and DST criteria like this:
- datediff(d,GETDATE(),[setcall]) <= @crit_DTS
setcall <= dateadd(d, @crit_DTS, GETDATE())
            AND
 -- datediff(d,[Contract_Date],GETDATE()) <= @crit_Age
Contract_Date] <= dateadd(d, @crit_DTS, GETDATE())

So my final results were like this.  That is better!
  Client processing time      10            10
  Total execution time      410            390
  Wait time on server replies      400            380

So it looks like when those two criteria were setup one with expression and one without, there was more work.

So your suggest is faster and appreciated.
  Rows affected by INSERT, DELETE, or UPDATE statements	0		0		0		0		0		0		0.0000
  Number of SELECT statements 	1		1		1		1		1		1		1.0000
  Rows returned by SELECT statements	0		0		0		0		0		0		0.0000
  Number of transactions 	0		0		0		0		0		0		0.0000
Network Statistics													
  Number of server roundtrips	1		1		1		1		1		1		1.0000
  TDS packets sent from client	1		1		1		1		1		1		1.0000
  TDS packets received from server	53		53		53		53		53		53		53.0000
  Bytes sent from client	272		272		272		272		272		272		272.0000
  Bytes received from server	214585		214585		214585		214585		214585		214585		214585.0000
Time Statistics													
  Client processing time	10		10		40		30		10		20		20.0000
  Total execution time	410		390		410		480		410		420		420.0000
  Wait time on server replies	400		380		370		450		400		400		400.0000

Open in new window

0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.
Suggested Courses

762 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