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
 General SQL Server Forums
 New to SQL Server Programming
 Help Changing Stored procedure needed!!

Author  Topic 

adminscott
Starting Member

13 Posts
Posted - 2009-01-29 : 10:16:58
Hi All,

I inherited a SQL 2005 DB with lots of stored procedures using datetimes which is causing some inacuracies in our results. I am looking to alter these stored procedures so that it requests a date input rather than a datetime - is this possible? Below is the procedure:

USE [PureH2O]
GO
/****** Object: StoredProcedure [dbo].[res_FieldSalesStatus] Script Date: 01/29/2009 15:04:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[res_FieldSalesStatus](
@From datetime,
@To datetime)

as
select r1.Region,
o.officename,
r2.RepName as teamlead,
r1.RepName as rep,
g.*

from [lt tbl reps] r1
join [lt tbl reps] r2 on r1.teamleader = r2.repid
join [lt tbl office] o on o.officeid = r1.office
join (SELECT dbo.[LT tbl Reps].RepID,
COUNT(distinct convert(nvarchar(10), repid) + convert(nvarchar(12), dateofapp, 102)) as repdays,
count(distinct appointmentid) as Appoinments,
count(case leaddisposition when 'RBO' then 1 else null end) as RBO,
count(case leaddisposition when 'RTL' then 1 else null end) as RTL,
count(case leaddisposition when 'RRS' then 1 else null end) as RRS,
count(case when (leaddisposition in ('DNS', 'DS', 'BD')) then 1 else null end) as Accessed,
count(case leaddisposition when 'DNS' then 1 else null end) as DNS,
count(case leaddisposition when 'BD' then 1 else null end) as BD,
count(case leaddisposition when 'DS' then 1 else null end) as DS
FROM dbo.[LT tbl Reps] INNER JOIN
dbo.[LT tbl Office] ON dbo.[LT tbl Reps].Office = dbo.[LT tbl Office].OfficeID INNER JOIN
dbo.[tbl AppointmentLog] ON dbo.[LT tbl Reps].RepID = dbo.[tbl AppointmentLog].Rep INNER JOIN
dbo.[LT tbl LeadDisposition] ON dbo.[tbl AppointmentLog].DispositionID = dbo.[LT tbl LeadDisposition].LeadDispositionID INNER JOIN
dbo.[LT tbl Timeslots] ON dbo.[tbl AppointmentLog].TimeslotID = dbo.[LT tbl Timeslots].TimeslotID INNER JOIN
dbo.[LT tbl TLM] ON dbo.[tbl AppointmentLog].TLM = dbo.[LT tbl TLM].TLMID INNER JOIN
dbo.[tbl Client] ON dbo.[tbl AppointmentLog].ClientID = dbo.[tbl Client].[Client ID]
WHERE dbo.[tbl AppointmentLog].DateOfApp >= @From
and dbo.[tbl AppointmentLog].DateOfApp <= @To
GROUP BY dbo.[LT tbl Reps].RepID) g on R1.repid = g.repid

order by r1.region, officename, r2.repname, r1.repname
--exec res_fieldsalesstatus '2007/01/01', '2007/01/31'


Thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2009-01-29 : 10:20:56
Just do the conversion on the call, no need for time anywhere then.

declare @datefrom datetime, @dateto datetime

set @datefrom = '2007-01-01'
set @dateto = '2007-01-31'
exec res_fieldsalesstatus @datefrom, @dateto
Go to Top of Page

adminscott
Starting Member

13 Posts
Posted - 2009-01-29 : 10:28:13
not quite sure i understand.

The procedure i quoted provides the basis for a crystal report - the report when it open prompts for @From and @To but with a time field. According to the MS notes i should be able to change the lines from

@From datetime

to

@From date

but this fails as its an unrecognised datatype (not according to MS website though)

remember i inherited SQL and have very little background with it so please simplify andswer for me or rewrite procedure if you can

Thanks
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-01-29 : 10:37:32
[code]USE [PureH2O]
GO
ALTER PROCEDURE [dbo].[res_FieldSalesStatus]
(
@From datetime,
@To datetime
)
as

set nocount on

select @from = dateadd(day, datediff(day, 0, @from), 0),
@to = dateadd(day, datediff(day, 0, @to), 1)

select r1.Region,
o.officename,
r2.RepName as teamlead,
r1.RepName as rep,
g.*
from [lt tbl reps] r1
inner join [lt tbl reps] r2 on r1.teamleader = r2.repid
inner join [lt tbl office] o on o.officeid = r1.office
inner join (
SELECT dbo.[LT tbl Reps].RepID,
count(distinct convert(nvarchar(10), repid) + convert(nvarchar(12), dateofapp, 102)) as repdays,
count(distinct appointmentid) as Appoinments,
count(case leaddisposition when 'RBO' then 1 else null end) as RBO,
count(case leaddisposition when 'RTL' then 1 else null end) as RTL,
count(case leaddisposition when 'RRS' then 1 else null end) as RRS,
count(case when (leaddisposition in ('DNS', 'DS', 'BD')) then 1 else null end) as Accessed,
count(case leaddisposition when 'DNS' then 1 else null end) as DNS,
count(case leaddisposition when 'BD' then 1 else null end) as BD,
count(case leaddisposition when 'DS' then 1 else null end) as DS
FROM dbo.[LT tbl Reps]
INNER JOIN dbo.[LT tbl Office] ON dbo.[LT tbl Reps].Office = dbo.[LT tbl Office].OfficeID
INNER JOIN dbo.[tbl AppointmentLog] ON dbo.[LT tbl Reps].RepID = dbo.[tbl AppointmentLog].Rep
INNER JOIN dbo.[LT tbl LeadDisposition] ON dbo.[tbl AppointmentLog].DispositionID = dbo.[LT tbl LeadDisposition].LeadDispositionID
INNER JOIN dbo.[LT tbl Timeslots] ON dbo.[tbl AppointmentLog].TimeslotID = dbo.[LT tbl Timeslots].TimeslotID
INNER JOIN dbo.[LT tbl TLM] ON dbo.[tbl AppointmentLog].TLM = dbo.[LT tbl TLM].TLMID
INNER JOIN dbo.[tbl Client] ON dbo.[tbl AppointmentLog].ClientID = dbo.[tbl Client].[Client ID]
WHERE dbo.[tbl AppointmentLog].DateOfApp >= @From
and dbo.[tbl AppointmentLog].DateOfApp < @To
GROUP BY dbo.[LT tbl Reps].RepID
) g on R1.repid = g.repid[/code]
E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2009-01-29 : 10:37:48
The datatype DATE came out with SQL Server 2008.
But what is your problem?
Report is prompting for values in @From and @To.
After typing in, what is the value of @From and @To and what is the problem to use in the query?

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

adminscott
Starting Member

13 Posts
Posted - 2009-01-29 : 10:41:28
the problem is that crystal prompts for a datetime - the users are not the brightest bunch and so would enter '01/02/2009 00:00:00' to '01/02/2009 00:00:00' to get a full days details which of course wont work as its looking at a zero period.
The idea of changing it to a date only is so that they input '01/02/2009' to 01/02/2009' they will get that days data regardless of time as the time section is not relevant
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-01-29 : 10:43:05
See post made 01/29/2009 : 10:37:32 .....


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2009-01-29 : 10:45:15
Got this from somewhere years ago (wish I could remember from whom), but for date manipulatulation, use one of these:


--previous Sunday
select dateadd(day, 1 - datepart(dw, getdate()), getdate())
--following Saturday
select dateadd(day, 7 - datepart(dw, getdate()), getdate())

--First Day of Month
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

--Monday of the Current Week
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

-- This example sets Sunday as the first day of the week,
-- but by changing the DATEFIRST setting any day of the week could be the first
-- day of the week. 
set DATEFIRST 7
select DATEADD(dd, 1 - DATEPART(dw, getdate()), getdate())

--First Day of the Year
select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

--First Day of the Quarter
select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

--Midnight for the Current Day
select dateadd(dd, datediff(dd,0,getdate()), 0)

--Last Day of Prior Month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  ), 0))

--Last Day of Prior Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  ), 0))

--Last Day of Current Month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0))

--Last Day of Current Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  )+1, 0))

--First Monday of the Month
select DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
Go to Top of Page

adminscott
Starting Member

13 Posts
Posted - 2009-01-29 : 10:47:13
quote:
Originally posted by Peso

USE [PureH2O]
GO
ALTER PROCEDURE [dbo].[res_FieldSalesStatus]
(
	@From datetime,
	@To datetime
)
as

set nocount on

select	@from = dateadd(day, datediff(day, 0, @from), 0),
	@to = dateadd(day, datediff(day, 0, @to), 1)

select		r1.Region,
		o.officename,
		r2.RepName as teamlead,
		r1.RepName as rep,
		g.*
from		[lt tbl reps] r1
inner join	[lt tbl reps] r2 on r1.teamleader = r2.repid
inner join	[lt tbl office] o on o.officeid = r1.office
inner join	(
			SELECT		dbo.[LT tbl Reps].RepID, 
					count(distinct convert(nvarchar(10), repid) + convert(nvarchar(12), dateofapp, 102)) as repdays,
					count(distinct appointmentid) as Appoinments,
					count(case leaddisposition when 'RBO' then 1 else null end) as RBO,
					count(case leaddisposition when 'RTL' then 1 else null end) as RTL,
					count(case leaddisposition when 'RRS' then 1 else null end) as RRS,
					count(case when (leaddisposition in ('DNS', 'DS', 'BD')) then 1 else null end) as Accessed,
					count(case leaddisposition when 'DNS' then 1 else null end) as DNS,
					count(case leaddisposition when 'BD' then 1 else null end) as BD,
					count(case leaddisposition when 'DS' then 1 else null end) as DS
			FROM		dbo.[LT tbl Reps]
			INNER JOIN	dbo.[LT tbl Office] ON dbo.[LT tbl Reps].Office = dbo.[LT tbl Office].OfficeID
			INNER JOIN	dbo.[tbl AppointmentLog] ON dbo.[LT tbl Reps].RepID = dbo.[tbl AppointmentLog].Rep
			INNER JOIN	dbo.[LT tbl LeadDisposition] ON dbo.[tbl AppointmentLog].DispositionID = dbo.[LT tbl LeadDisposition].LeadDispositionID
			INNER JOIN	dbo.[LT tbl Timeslots] ON dbo.[tbl AppointmentLog].TimeslotID = dbo.[LT tbl Timeslots].TimeslotID
			INNER JOIN	dbo.[LT tbl TLM] ON dbo.[tbl AppointmentLog].TLM = dbo.[LT tbl TLM].TLMID
			INNER JOIN	dbo.[tbl Client] ON dbo.[tbl AppointmentLog].ClientID = dbo.[tbl Client].[Client ID]
			WHERE		dbo.[tbl AppointmentLog].DateOfApp >= @From
					and dbo.[tbl AppointmentLog].DateOfApp < @To
			GROUP BY	dbo.[LT tbl Reps].RepID
		) g on R1.repid = g.repid

E 12°55'05.63"
N 56°04'39.26"



unfortunately this has not resolved the issue - i am still prompted for a datetime when running the procedure
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-01-29 : 10:49:14
No, the stored procedure will NOT prompt you for a value.
It is impossible!

If you are using Crystal, I'd suggest you ask Crystal people how to change datatype for parameters...


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2009-01-29 : 10:50:52
Yes, but it doesn't matter if they put a datetime in, you will still get a days worth if they put '01/02/2009 00:00:00' to '01/02/2009 00:00:00' into the variables.

You can also format the parameters in Crystal, or at least you always used to be able to..
Go to Top of Page

adminscott
Starting Member

13 Posts
Posted - 2009-01-29 : 10:50:57
quote:
Originally posted by RickD

Got this from somewhere years ago (wish I could remember from whom), but for date manipulatulation, use one of these:


--previous Sunday
select dateadd(day, 1 - datepart(dw, getdate()), getdate())
--following Saturday
select dateadd(day, 7 - datepart(dw, getdate()), getdate())

--First Day of Month
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

--Monday of the Current Week
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

-- This example sets Sunday as the first day of the week,
-- but by changing the DATEFIRST setting any day of the week could be the first
-- day of the week. 
set DATEFIRST 7
select DATEADD(dd, 1 - DATEPART(dw, getdate()), getdate())

--First Day of the Year
select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

--First Day of the Quarter
select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

--Midnight for the Current Day
select dateadd(dd, datediff(dd,0,getdate()), 0)

--Last Day of Prior Month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  ), 0))

--Last Day of Prior Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  ), 0))

--Last Day of Current Month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0))

--Last Day of Current Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  )+1, 0))

--First Monday of the Month
select DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate())), 0)



again i dont see how thats going to help...

i know how to convert dates when using a select command the problem is that when running the procedure i want it to prompt for a date rather than a datetime.

perhaps this just isnt possible in 2005 and we need to upgrade?

Its weird because that would make SQL the only DB program i have ever used that doesnt support this function
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-01-29 : 10:51:38
quote:
Originally posted by adminscott

unfortunately this has not resolved the issue - i am still prompted for a datetime when running the procedure

You don't have to worry at all!
Do me ONE favor. Test the SP I posted. It will disregard all timeinformation for you.
It will always use all records from @From date and INCLUDING all records for @to REGARDLESS of timeinformation of supplied parameters.

Why is it so hard to try a suggestion?
Is it because your lack of knowledge in SQL? Ignorance?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

adminscott
Starting Member

13 Posts
Posted - 2009-01-29 : 10:57:12
quote:
Originally posted by Peso

quote:
Originally posted by adminscott

unfortunately this has not resolved the issue - i am still prompted for a datetime when running the procedure

You don't have to worry at all!
Do me ONE favor. Test the SP I posted. It will disregard all timeinformation for you.
It will always use all records from @From date and INCLUDING all records for @to REGARDLESS of timeinformation of supplied parameters.

Why is it so hard to try a suggestion?
Is it because your lack of knowledge in SQL? Ignorance?


E 12°55'05.63"
N 56°04'39.26"


Wow Peso thats harsh!!

i have implemented your script and if i enter '01/02/2009 00:00:00' to '01/02/2009 00:00:00' i still get no data.

I cant manipulate the parameters in crystal because the parameters are already hard coded into the procedure this is the problem - crystal is something i am VERY good at and had never seen this until i found a report based on a procedure. All crystal options are greyed out because the procedure has control over all parameters so its the procedure that needs changing. Personally i'd have used a view - but thats just me.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-01-29 : 11:03:15
My suggestion will work for you.
The alterations made in red above is all it takes to make it work.
The stored procedure code is flawed for @to date range.

All you have to do is to add the first red section and delete the equal sign near the @to parameter

See this example
DECLARE	@From datetime,
	@To datetime

set dateformat mdy

-- values supplied by adminscott
SELECT	@from = '01/02/2009',	-- January 2nd 2009
	@to = '01/02/2009'	-- January 2nd 2009

-- Peso magic
select	@from = dateadd(day, datediff(day, 0, @from), 0),
	@to = dateadd(day, datediff(day, 0, @to), 1)

-- Display new date range
select	@from,			-- January 2nd 2009
	@to			-- January 3rd 2009
Heck, even if you put in time information, it will continue to function
DECLARE	@From datetime,
	@To datetime

set dateformat mdy

-- values supplied by adminscott
SELECT	@from = '01/02/2009 12:13pm',	-- January 2nd 2009
	@to = '01/02/2009 5:55pm'	-- January 2nd 2009

-- Peso magic
select	@from = dateadd(day, datediff(day, 0, @from), 0),
	@to = dateadd(day, datediff(day, 0, @to), 1)

-- Display new date range
select	@from,			-- January 2nd 2009
	@to			-- January 3rd 2009
The only reason for this to no work, is that table [tbl AppointmentLog] does not have records dated January 2nd 2009.

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

adminscott
Starting Member

13 Posts
Posted - 2009-01-29 : 11:34:48
quote:
Originally posted by Peso

My suggestion will work for you.
The alterations made in red above is all it takes to make it work.
The stored procedure code is flawed for @to date range.

All you have to do is to add the first red section and delete the equal sign near the @to parameter

See this example
DECLARE	@From datetime,
	@To datetime

set dateformat mdy

-- values supplied by adminscott
SELECT	@from = '01/02/2009',	-- January 2nd 2009
	@to = '01/02/2009'	-- January 2nd 2009

-- Peso magic
select	@from = dateadd(day, datediff(day, 0, @from), 0),
	@to = dateadd(day, datediff(day, 0, @to), 1)

-- Display new date range
select	@from,			-- January 2nd 2009
	@to			-- January 3rd 2009
Heck, even if you put in time information, it will continue to function
DECLARE	@From datetime,
	@To datetime

set dateformat mdy

-- values supplied by adminscott
SELECT	@from = '01/02/2009 12:13pm',	-- January 2nd 2009
	@to = '01/02/2009 5:55pm'	-- January 2nd 2009

-- Peso magic
select	@from = dateadd(day, datediff(day, 0, @from), 0),
	@to = dateadd(day, datediff(day, 0, @to), 1)

-- Display new date range
select	@from,			-- January 2nd 2009
	@to			-- January 3rd 2009
The only reason for this to no work, is that table [tbl AppointmentLog] does not have records dated January 2nd 2009.

E 12°55'05.63"
N 56°04'39.26"


well i dont know what to tell you - with your modification i get no data without your modifications if i enter '01/02/2009 00:00:00' to '01/03/2009 00:00:00' then i do get data. from the tables i can also see data so there is something in your 'magic' that the system doesnt like...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-01-29 : 11:44:37
try sending dates in format like '2009-01-02 00:00:00' & '2009-01-03 00:00:00' and see if its any better with peso's suggestion?
Go to Top of Page

adminscott
Starting Member

13 Posts
Posted - 2009-01-29 : 11:47:17
it will not make any difference - Crystal reads the format from the DB and sends the date selected from a calendar in the required format to the DB so regardless of the date form in the DB Crystal will intelligently convert this for you.

i think i'll just have to create a view for this instead and create the parameters in crystal - that way i can control them more accurately
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-01-29 : 11:52:46
so for crystal does '01/02/2009 00:00:00' represent jan 2nd 2009 or Feb 1 st 2009?
Go to Top of Page

adminscott
Starting Member

13 Posts
Posted - 2009-01-29 : 12:01:54
that would depend on the DB - Crystal loocals at the region settings of the DB its connecting to to identify the default and organises the data it sends back accordingly. When crystal prompts you for a date it displays a calendar so you dont have to manually enter the format just select the 1st jan 2008 from the calendar and it handles the format for you.

Unfortunately as the DB parameter in the procedure demands a datetime crystal will demand a datetime and this is why its falling over.

with a view i will pull all data from all dates and create a dateonly param in crystal to filter the data from the view - just means alot more work
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-01-29 : 12:10:13
ok. even with that how does it affect the data retrieval? even with 00:00:00 time part it looks only for records from start of first date (12 midnight) to start of last date (12 midnight), so you will get all records from first until start of last day
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site