Solved

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

Posted on 2014-11-08
8
78 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
  • 4
  • 3
8 Comments
 
LVL 48

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 48

Accepted Solution

by:
PortletPaul earned 250 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
 

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
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 68

Assisted Solution

by:Qlemo
Qlemo earned 250 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 48

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 48

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

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 …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

757 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

22 Experts available now in Live!

Get 1:1 Help Now