October 1, 2008 at 4:43 am
Hello I have this stored procedure writen like this:
USE [db]
GO
/****** Object: StoredProcedure [dbo].[usprunreport] Script Date: 10/01/2008 11:38:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspfruadreport]
as
declare @startdate datetime;
declare @enddate datetime;
set @enddate = convert(datetime, convert(varchar(11),getdate(),103),103);
set @startdate = dateadd(d,-5000,@enddate);
;with OrderDet (vOrderDate,vThreeMore, vAll, vNextDay, vAllOrders,DelivNotBill,vNextDayDelivery,vOver300,vNumOrdCC7Days)
as (select convert(datetime, convert(varchar(11),OrderDate,106),106) as vOrderDate
,sum(case when t.orderTotal >=300 then 1 else 0 end) as vThreeMore
,sum(1) as vAll
,sum(case when datediff(dd, t.OrderDate,t.ShipDate)<=1 then 1 else 0 end ) as vNextDay
,sum(1) as vAllOrders
,sum(case when (t.ShipAdd1 <> c.Add1)
OR
(t.ShipPostcode <> c.Postcode)
then 1 else 0 end
) as DelivNotBill
,sum(case when datediff(dd,t.OrderDate,t.ShipDate)<=1 then 1 else 0 end) as vNextDayDelivery
,sum(case when t.OrderTotal>300 then 1 else 0 end) as vOver300
,sum(isnull(p.Cnt,0)) as vNumOrdCC7Days
-- ,sum(p.Cnt) as vNumOrdCC7Days
from t_OrderHeader t
inner join t_Customers c
ON c.CustomerID = t.CustomerID
inner join t_Payments tp
on t.orderID = tp.OrderID
left outer join (select h1.orderID pOrderId, ccnumber pccnumber,
convert(datetime, convert(varchar(11),OrderDate,103),103) pOrderDate, count(*) cnt
from t_Payments p1 join t_orderHeader h1 on p1.orderid = h1.orderid where h1.OrderDate >=
dateadd(d,-5000,@endDate) group by h1.orderID , ccnumber , convert(datetime, convert(varchar(11),OrderDate,103),103)) p
on p.pOrderDate = convert(datetime, convert(varchar(11),OrderDate,103),103)
and p.porderId <> t.orderid
and p.pccnumber = tp.ccnumber
where OrderDate BETWEEN @startdate AND @enddate
group by convert(datetime, convert(varchar(11),OrderDate,106),106)
)
select * ,(CAST(vnextday AS FLOAT)/vallorders)*100 as '% Orders on next day delivery'
,(CAST(vThreeMore AS FLOAT)/vallorders)*100 as '% Orders over £300'
,(CAST(vNumOrdCC7Days AS FLOAT)/vallorders)*100 as '% Orders with same CC'
from OrderDet
order by vOrderDate desc
What I want to do is to be able to excute the stored proc as
exec usprunreport
within a date range to be created withn SSRS ...@startdate and @enddate
October 1, 2008 at 4:54 am
try this out
USE [db]
GO
/****** Object: StoredProcedure [dbo].[usprunreport] Script Date: 10/01/2008 11:38:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspfruadreport]
@startdate datetime,@enddate datetime
as
declare @startdate datetime;
declare @enddate datetime;
set @enddate = convert(datetime, convert(varchar(11),getdate(),103),103);
set @startdate = dateadd(d,-5000,@enddate);
;with OrderDet (vOrderDate,vThreeMore, vAll, vNextDay, vAllOrders,DelivNotBill,vNextDayDelivery,vOver300,vNumOrdCC7Days)
as (select convert(datetime, convert(varchar(11),OrderDate,106),106) as vOrderDate
,sum(case when t.orderTotal >=300 then 1 else 0 end) as vThreeMore
,sum(1) as vAll
,sum(case when datediff(dd, t.OrderDate,t.ShipDate)<=1 then 1 else 0 end ) as vNextDay
,sum(1) as vAllOrders
,sum(case when (t.ShipAdd1 <> c.Add1)
OR
(t.ShipPostcode <> c.Postcode)
then 1 else 0 end
as DelivNotBill
,sum(case when datediff(dd,t.OrderDate,t.ShipDate)<=1 then 1 else 0 end) as vNextDayDelivery
,sum(case when t.OrderTotal>300 then 1 else 0 end) as vOver300
,sum(isnull(p.Cnt,0)) as vNumOrdCC7Days
-- ,sum(p.Cnt) as vNumOrdCC7Days
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 1, 2008 at 5:28 am
Got the following errors:
Msg 134, Level 15, State 1, Procedure usprunreport, Line 5
The variable name '@startdate' has already been declared. Variable names must be unique within a query batch or stored procedure.
Msg 134, Level 15, State 1, Procedure usprunreport, Line 6
The variable name '@enddate' has already been declared. Variable names must be unique within a query batch or stored procedure.
Msg 102, Level 15, State 1, Procedure usprunreport, Line 21
Incorrect syntax near ','.
October 1, 2008 at 6:44 am
USE [db]
GO
/****** Object: StoredProcedure [dbo].[usprunreport] Script Date: 10/01/2008 11:38:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspfruadreport]
@startdate datetime,@enddate datetime
as
set @enddate = convert(datetime, convert(varchar(11),enddate ,103),103);
set @startdate = dateadd(d,-5000,@enddate);
;with OrderDet (vOrderDate,vThreeMore, vAll, vNextDay, vAllOrders,DelivNotBill,vNextDayDelivery,vOver300,vNumOrdCC7Days)
as (select convert(datetime, convert(varchar(11),OrderDate,106),106) as vOrderDate
,sum(case when t.orderTotal >=300 then 1 else 0 end) as vThreeMore
,sum(1) as vAll
,sum(case when datediff(dd, t.OrderDate,t.ShipDate)<=1 then 1 else 0 end ) as vNextDay
,sum(1) as vAllOrders
,sum(case when (t.ShipAdd1 <> c.Add1)
OR
(t.ShipPostcode <> c.Postcode)
then 1 else 0 end
as DelivNotBill
,sum(case when datediff(dd,t.OrderDate,t.ShipDate)<=1 then 1 else 0 end) as vNextDayDelivery
,sum(case when t.OrderTotal>300 then 1 else 0 end) as vOver300
,sum(isnull(p.Cnt,0)) as vNumOrdCC7Days
-- ,sum(p.Cnt) as vNumOrdCC7Days
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy