Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Badly written SP need help

Author  Topic 

mike13
Posting Yak Master

219 Posts

Posted - 2015-03-24 : 08:27:51
Hi,

IU got this SP that returns the amount of customers that registered that month and did not have a sucessfull order (status=8)
the Changedate is filed that had the registration date.

ALTER PROCEDURE [dbo].[SP_STATS_GET_TOTAL_new_customer_with_NO_orders]
@totals as nvarchar(100) output,
@site as varchar(50),
@month as CHAR(2),
@year as CHAR(4)
AS

DECLARE @ADate DATETIME
DECLARE @amountdaysinmonth int

SET @ADate = @year +'-' + @month +'-01'

set @amountdaysinmonth=DAY(EOMONTH(@ADate))

SELECT @totals=COUNT(distinct T_Order_Main.CustomerID)
FROM dbo.T_Customer INNER JOIN
dbo.T_Order_Main ON dbo.T_Customer.CustomerID = dbo.T_Order_Main.CustomerID
WHERE (dbo.T_Order_Main.Orderstatus <> 8) and
dbo.T_Customer.changedate BETWEEN cast(@year as varchar) + '-' + cast(@month as varchar) + '-01 00:00:00' AND cast(@year as varchar) + '-' + cast(@month as varchar) +'-'+ CAST(@amountdaysinmonth AS varchar) +' 23:59:59'

AND (dbo.T_Customer.site = @site)
AND NOT T_Order_Main.CustomerID IN
(SELECT distinct T_Order_Main.CustomerID
FROM dbo.T_Customer INNER JOIN
dbo.T_Order_Main ON dbo.T_Customer.CustomerID = dbo.T_Order_Main.CustomerID
WHERE (dbo.T_Order_Main.Orderstatus = 8) AND
dbo.T_Customer.changedate BETWEEN cast(@year as varchar) + '-' + cast(@month as varchar) + '-01 00:00:00' AND cast(@year as varchar) + '-' + cast(@month as varchar) +'-'+ CAST(@amountdaysinmonth AS varchar) + ' 23:59:59'


AND (dbo.T_Customer.site = @site))


Thanks a lot

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-24 : 08:52:19
No idea what you want to do here. Does the proc work? If so, what's the problem? If not, how does it fail?
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-03-24 : 10:43:27
[code]
dbo.T_Customer.changedate
BETWEEN cast(@year as varchar) + '-' + cast(@month as varchar) + '-01 00:00:00'
AND cast(@year as varchar) + '-' + cast(@month as varchar) +'-'+ CAST(@amountdaysinmonth AS varchar) +' 23:59:59'
[/code]
Will have lousy performance, and there is probably one millisecond at the end of the month that will escape this test

Change it to
[code]
dbo.T_Customer.changedate >= @StartDate AND dbo.T_Customer.changedate < @EndDate
[/code]
Precalculate both @StartDate and @EndDate because if you leave a complex formula in the WHERE clause there is a high chance that SQL will not be able to use any suitable index.

Don't fiddle about with string dates with punctuation like "-" because if the server locale setting changes, or the user connects with a different LANGUAGE setting, or a host of other such things your assumptions about date will fail. The only valid string date to use which is guaranteed UNambiguous is 'yyyymmdd' or ISO format 'yyyy-mm-ddThh:mm:ss'
[code]
SELECT @StartDate = CONVERT(datetime, @year + @month + '01'),
@EndDate = DATEADD(Month, 1, @StartDate)
[/code]
Go to Top of Page

mike13
Posting Yak Master

219 Posts

Posted - 2015-03-24 : 10:43:51
Sorry it performs real slow!
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-03-24 : 10:46:25
quote:
Originally posted by mike13

Sorry it performs real slow!



My suggestion, above, on Date Range test may help.

If not: what indexes do you have on the tables?
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-03-24 : 13:20:30
[code]
ALTER PROCEDURE [dbo].[SP_STATS_GET_TOTAL_new_customer_with_NO_orders]
@totals as nvarchar(100) output,
@site as varchar(50),
@month as CHAR(2),
@year as CHAR(4)
AS
SET NOCOUNT ON;

SELECT @totals = COUNT(*)
FROM dbo.T_Order_Main
WHERE CustomerID IN (
SELECT CustomerID
FROM dbo.T_Customer
WHERE
changedate >= @year + RIGHT('0' + @month, 2) + '01' AND
changedate < DATEADD(MONTH, 1, CAST(@year + RIGHT('0' + @month, 2) + '01' AS date)) AND
site = @site
)
GROUP BY CustomerID
HAVING MAX(CASE WHEN Orderstatus = 8 THEN 1 ELSE 0 END) = 0 --verify no successful order
AND MAX(CASE WHEN Orderstatus <> 8 THEN 1 ELSE 0 END) = 1 --Edit: verify unsuccessful order

[/code]
Go to Top of Page
   

- Advertisement -