February 12, 2009 at 10:28 am
Hello experts,
I have a question which I need all the helps from experts
I have a store procedure in SQL 2005, and I have DDS column which is stored multiple entries like
BOCLEARED CTCLEARED TotalCleared CLEAREDDATE DDS
1 2 3 1/1/2009 BO, CT
3 3 1/5/2009 BO
5 5 1/7/2009 CT
Below are my codes in SQL (Store procedure)
ALTER PROCEDURE [dbo].[WeeklyClearances]
-- Add the parameters for the stored procedure here
@Start Datetime = 0,
@End Datetime = 0,
@Parameter varchar(3) = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT BOCLEARED, CTCLEARED, MECLEARED, NHCLEARED, RICLEARED, WOCLEARED, VTCLEARED, TOTALCLEARED, CLEAREDDATE, DDS
FROM dbo.SummaryWeeklyCleared
Where (ClearedDate between @Start AND @End) AND ((DDS = @Parameter) OR ('ALL' = @Parameter))
END
Below are the codes where it stores multiple entries in a cell
WITH AllDDS AS
(SELECT DDS, DEClearedDate AS ClearedDate, DECleared AS Cleared
FROM dbo.DECleared
UNION ALL
SELECT DDS, SomaticMCClearedDate AS ClearedDate, SomaticMCCleared AS Cleared
FROM dbo.SomaticMCCleared
UNION ALL
SELECT DDS, PsycMCClearedDate AS ClearedDate, PsycMCCleared AS Cleared
FROM dbo.PsycMCCleared
UNION ALL
SELECT DDS, DESecondClearedDate AS ClearedDate, DESecondCleared AS Cleared
FROM dbo.DESecondCleared
UNION ALL
SELECT DDS, SomaticMCSecondClearedDate AS ClearedDate, SomaticMCSecondCleared AS Cleared
FROM dbo.SomaticMCSecondCleared
UNION ALL
SELECT DDS, PsycMCSecondClearedDate AS ClearedDate, PsycMCSecondCleared AS Cleared
FROM dbo.PsycMCSecondCleared
UNION ALL
SELECT DDS, DEThirdClearedDate AS ClearedDate, DEThirdCleared AS Cleared
FROM dbo.DEThirdCleared
UNION ALL
SELECT DDS, SomaticMCThirdClearedDate AS ClearedDate, SomaticMCThirdCleared AS Cleared
FROM dbo.SomaticMCThirdCleared
UNION ALL
SELECT DDS, PsycMCThirdClearedDate AS ClearedDate, PsycMCThirdCleared AS Cleared
FROM dbo.PsycMCThirdCleared
UNION ALL
SELECT DDS, DEFourthClearedDate AS ClearedDate, DEFourthCleared AS Cleared
FROM dbo.DEFourthCleared
UNION ALL
SELECT DDS, SomaticMCFourthClearedDate AS ClearedDate, SomaticMCFourthCleared AS Cleared
FROM dbo.SomaticMCFourthCleared
UNION ALL
SELECT DDS, PsycMCFourthClearedDate AS ClearedDate, PsycMCFourthCleared AS Cleared
FROM dbo.PsycMCFourthCleared),
PivotDDS AS
(SELECT ClearedDate,
ISNULL(SUM(ISNULL([BO], 0)), 0) AS [BO], ISNULL(SUM(ISNULL([CT], 0)), 0) AS [CT], ISNULL(SUM(ISNULL([NH], 0)), 0) AS [NH], ISNULL(SUM(ISNULL([ME], 0)), 0) AS [ME], ISNULL(SUM(ISNULL([RI], 0)), 0) AS [RI], ISNULL(SUM(ISNULL([VT], 0)), 0) AS [VT], ISNULL(SUM(ISNULL([WO], 0)), 0) AS [WO]
FROM AllDDS PIVOT (SUM(Cleared) FOR DDS IN ([BO], [CT], [NH], [ME], [RI], [VT], [WO])) P
GROUP BY ClearedDate), FinalDDS AS
(SELECT ClearedDate, [BO] + [CT] + [NH] + [ME] + [RI] + [VT] + [WO] AS TotalCleared, (CASE WHEN [BO] > 0 THEN ', BO' ELSE ' ' END) + (CASE WHEN [CT] > 0 THEN ', CT' ELSE ' ' END) + (CASE WHEN [NH] > 0 THEN ', NH' ELSE ' ' END) + (CASE WHEN [ME] > 0 THEN ', ME' ELSE ' ' END) + (CASE WHEN [RI] > 0 THEN ', RI' ELSE ' ' END) + (CASE WHEN [VT] > 0 THEN ', VT' ELSE ' ' END) + (CASE WHEN [WO] > 0 THEN ', WO' ELSE ' ' END) AS DDS
FROM PivotDDS)
SELECT Substring(DDS, 2, 1000) AS DDS, TotalCleared AS TOTALCLEARED, ClearedDate AS CLEAREDDATE
FROM FinalDDS
But when I click the dropdownbox to choose the specific office like BO......it is not showing anything ....but if I choose ALL then it shows everything
DO YOU KNOW WHY?
February 12, 2009 at 1:40 pm
Hi,
I have made two small adjustments, the first one adjusts the parameter to have SQL wildcards around it. The second is to change your WHERE clause to use a LIKE comparator.
Does this fix it?
ALTER PROCEDURE [dbo].[WeeklyClearances]
-- Add the parameters for the stored procedure here
@Start Datetime = 0,
@End Datetime = 0,
@Parameter varchar(3) = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Adjust parameter....
set @Parameter = '%' + @Parameter + '%'
-- Insert statements for procedure here
SELECT BOCLEARED, CTCLEARED, MECLEARED, NHCLEARED, RICLEARED, WOCLEARED, VTCLEARED, TOTALCLEARED, CLEAREDDATE, DDS
FROM dbo.SummaryWeeklyCleared
Where (ClearedDate between @Start AND @End) AND ((DDS like @Parameter) OR ('ALL' = @Parameter))
END
B
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply