asked on

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

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

PortletPaul

can you provide the tblJobs.SetCall column for those results please

pressMac

ASKER

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

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pressMac

ASKER

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.

SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

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

PortletPaul

well spotted Qlemo

pressMac

ASKER

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