Dear All,
Please Have a look at the parameters that I am passing to the DashBoard stored proc. Its working fine when I pass the parameter as date, but I need it to be working when I pass the parameter for both date and time.
e.g., ALTER proc [dbo].[DashBoardTime] '01/01/2011 9:00:00 AM','01/25/2011 10:00:00 PM'
USE [FTA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[DashBoard] --'01/01/2011','01/25/2011'
(
@startdate datetime,
@enddate datetime
)
as
begin
set nocount on
DECLARE @temptable TABLE
(
HubId BIGINT,
HubName NVARCHAR(50),
InwardPass BIGINT,
InwardFail BIGINT
)
INSERT INTO @temptable(HubId,HubName)
--SELECT 0 AS 'HUBId','Total' AS 'HubName' UNION
SELECT DISTINCT HUBId,HubName FROM tbl_DataHubView WHERE IsActive=1
UPDATE @temptable SET InwardPass =
(select COUNT(InwardID) FROM tbl_Inward A WHERE A.iDataHubID=B.HubId AND InwardStatus='Accept' AND Convert(VARCHAR(50),InwardUpdateTime,101) between Convert(VARCHAR(50),@startdate,101) AND Convert(VARCHAR(50),@enddate,101))
FROM @temptable B
UPDATE @temptable SET InwardFail =
(select COUNT(InwardID) FROM tbl_Inward A WHERE A.iDataHubID=B.HubId AND InwardStatus='Reject' AND Convert(VARCHAR(50),InwardUpdateTime,101) between Convert(VARCHAR(50),@startdate,101) AND Convert(VARCHAR(50),@enddate,101))
FROM @temptable B
INSERT INTO @temptable(HubId,HubName,InwardPass,InwardFail)
SELECT 0 AS 'HUBId','Total' AS 'HubName',SUM(InwardPass) AS 'InwardPass',SUM(InwardFail) AS 'InwardFail'
FROM @temptable
select HubName,InwardPass,InwardFail FROM @temptable
end
Can Anyone Help me in this?
Thanks in advance.
Raj