Year-to-Year comparison (parameters)

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 2000 Forums

Transact-SQL (2000)

Year-to-Year comparison (parameters)

Author

Topic

syoselevsky
Starting Member

7 Posts

Posted - 2006-03-02 : 14:27:25

I'm trying to create less parameters for my end-users, by having them only enter one set of start/end dates for the report but returning year-to-year comparison, I tried to use the dateadd feature, but it is giving me an error:

Server: Msg 155, Level 15, State 2, Procedure GenerateCBCSalesBrandDate, Line 9
'datetime' is not a recognized CURSOR option.

I'm assuming it doesn't like the parameter in there, I need some code that will allow the user to only enter in one set of dates...

here is my code:

CREATE proc GenerateCBCSalesBrandDate
(
@brand nvarchar(3),
@startdate date,
@enddate date
)
as

declare startty datetime
declare endty datetime
declare startly datetime
declare endly datetime

set startty = @startdate
set endty = @enddate
set startly = dateadd(year,1,@startdate)
set endly = dateadd(year,1,@enddate)

select
c.name as customer,
b.item,
i.itemdescription,
sum(case when b.invoice_date between startty and endty then b.sales else 0.0 end) as salesty,
sum(case when b.invoice_date between startty and endty then b.kg else 0.0 end) as kgty,
sum(case when b.invoice_date between startty and endty then b.kg/i.caseweight else 0.0 end) as casesty,
sum(case when b.invoice_date between startty and endty then b.gm-b.freight else 0.0 end) as gmty,
sum(case when b.invoice_date between startty and endty then b.agm else 0.0 end) as agmty,
sum(case when b.invoice_date between startly and endly then b.sales else 0.0 end) as salesly,
sum(case when b.invoice_date between startly and endly then b.kg else 0.0 end) as kgly,
sum(case when b.invoice_date between startly and endly then b.kg/i.caseweight else 0.0 end) as casesly,
sum(case when b.invoice_date between startly and endly then b.gm-b.freight else 0.0 end) as gmly,
sum(case when b.invoice_date between startly and endly then b.agm else 0.0 end) as agmly

from bfiles b

inner join branch br on b.branch = br.branchid
inner join customerorig c on c.customer = b.customer
inner join itemfinal i on i.itemcode = b.item

where
b.customer in (990904,990905,990906,990907,990908,990909,990910,991923) and
i.brand = @brand

group by
c.name,
b.item,
i.itemdescription

order by
c.name

return

GO

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-02 : 14:34:13

Do you have a question or problem that you want help with?

CODO ERGO SUM

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-02 : 14:35:57

And your question is ????

By the way -- why do you have that big list of customer numbers hard-coded in your SQL? If those customers have a common attribute that means they should be included in this stored proc (i.e., they are all "active" or something like that) then you should store a value in the customer table indicating this and filter using that.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-03 : 01:25:34

You missed out the @ in the declaring the variables

CREATE proc GenerateCBCSalesBrandDate
(
@brand nvarchar(3),
@startdate date,
@enddate date
)
as

declare @startty datetime
declare @endty datetime
declare @startly datetime
declare @endly datetime

set @startty = @startdate
set @endty = @enddate
set @startly = dateadd(year,1,@startdate)
set @endly = dateadd(year,1,@enddate)

select
c.name as customer,
b.item,
i.itemdescription,
sum(case when b.invoice_date between @startty and @endty then b.sales else 0.0 end) as salesty,
sum(case when b.invoice_date between @startty and @endty then b.kg else 0.0 end) as kgty,
sum(case when b.invoice_date between @startty and @endty then b.kg/i.caseweight else 0.0 end) as casesty,
sum(case when b.invoice_date between @startty and @endty then b.gm-b.freight else 0.0 end) as gmty,
sum(case when b.invoice_date between @startty and @endty then b.agm else 0.0 end) as agmty,
sum(case when b.invoice_date between @startly and @endly then b.sales else 0.0 end) as salesly,
sum(case when b.invoice_date between @startly and @endly then b.kg else 0.0 end) as kgly,
sum(case when b.invoice_date between @startly and @endly then b.kg/i.caseweight else 0.0 end) as casesly,
sum(case when b.invoice_date between @startly and @endly then b.gm-b.freight else 0.0 end) as gmly,
sum(case when b.invoice_date between @startly and @endly then b.agm else 0.0 end) as agmly

from bfiles b

inner join branch br on b.branch = br.branchid
inner join customerorig c on c.customer = b.customer
inner join itemfinal i on i.itemcode = b.item

where
b.customer in (990904,990905,990906,990907,990908,990909,990910,991923) and
i.brand = @brand

group by
c.name,
b.item,
i.itemdescription

order by
c.name

return

----------------------------------
'KH'

syoselevsky
Starting Member

7 Posts

Posted - 2006-03-03 : 18:32:31

Thanks soooo much... funny how just having another fresh set of eyes on something helps! I also had to change the

set startly = dateadd(year,1,@startdate)
set endly = dateadd(year,1,@enddate)

to -1's... it works like a charm!

Stacy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources