February 19, 2009 at 6:20 am
Hello Ramesh,
How are you doing? how was your weekend? Thank you so much for your helps, I have a question and that question is
the store procedure that you sent me, it works now but saying for example if in the column DDS there are two variables in the same cell with comma in the middle of two variables.
If I pick the office BO only, then it displays the report for BO Office ( that's right and I wish it to be like that). But if I pick CT then it displays CT and NH. DO YOU KNOW WHY?
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, 3, 1000) AS DDS,
ClearedDate AS CLEAREDDATE,
TotalCleared AS TOTALCLEARED
FROM FinalDDS
Below is the outlook of the code above(provided by Mr. Ramesh)
DDS TOTALCLEARED
BO 1
, ME 1
BO, CT 2
, VT 1
BO 1
, CT, NH 2
, WO 1
, CT 1
, ME 1
, VT 1
, CT , ME 2
, NH , WO 2
, ME 1
, VT 1
, CT 1
, RI 1
, NH, ME , WO 3
, RI 1
, RI 1
, VT 1
, CT 1
, NH , WO 2
, CT , VT 2
, VT 1
BO, CT , ME 3
, CT, NH , VT, WO 4
BO, CT , RI 3
, VT 1
, NH , WO 2
, RI, VT 2
BO, CT 3
, ME 1
, VT 1
, CT 1
, RI 1
, CT , VT 2
, CT, NH , WO 3
, VT 1
, CT 1
BO, CT , ME 3
, NH 1
, CT , RI , WO 3
, NH , WO 2
BO, CT , RI 3
, CT 1
, NH , WO 2
, CT, NH , WO 3
BO, CT , RI 3
, ME 1
, CT, NH , WO 3
BO , RI 2
BO , RI 2
BO , RI 2
, ME 1
, CT 1
, ME 1
, ME 1
DO YOU KNOW WHY SOMETIME THEY DO HAVE COMMA RIGHT IN FRONT OF SINGLE WORD ( for example, ' , ME ' )
Thank you Ramesh
February 19, 2009 at 6:52 am
josephptran2002 (2/19/2009)
If I pick the office BO only, then it displays the report for BO Office ( that's right and I wish it to be like that). But if I pick CT then it displays CT and NH. DO YOU KNOW WHY?
What exactly you wanted to show in your report? First you concatenated all offices in DDS column for each day. Now you wanted only those rows based on the selected office parameter.
josephptran2002 (2/19/2009)
DO YOU KNOW WHY SOMETIME THEY DO HAVE COMMA RIGHT IN FRONT OF SINGLE WORD ( for example, ' , ME ' )
I don't see any reason, why you are keep getting the comma in front of the text. Can you do add DDS column as DDS1 in the final select query and show us the output?
--Ramesh
February 19, 2009 at 7:16 am
Hello Ramesh,
the reason I wish to concatenate them because if there are 2 cases were cleared on the same date. But 2 cases were cleared in different offices, for example
"CT" Office is cleared one case on 1/1/2009
"NH" Office is also cleared on case on 1/1/2009
then on DDS column, and CLEAREDDATE column
DDS TOTALCLEARED CLEAREDDATE
CT, NH 2 1/1/2009
BO 1 1/3/2009
On ASP.NET 2.0, I create a dropdownbox which includes BO, CT, NH, BO,ALL.
If I click on CT then I wish it to display:
BOCLEARED CTCLEARED NHCLEARED TOTALCLEARED CLEAREDDATE
1--CT 1-TOTAL 1/1/2009
NOT LIKE BELOW:
BOCLEARED CTCLEARED NHCLEARED TOTALCLEARED CLEAREDDATE
1--CT 1--NH 2--TOTAL 1/1/2009
For the comma, you suggest me to add DDS AS DDS1. How do I do? Would you please tell me how?
Thank you Ramesh
Please help me
February 19, 2009 at 7:29 am
And what do you want to show in the report when ALL is selected?
--Ramesh
February 19, 2009 at 7:34 am
Hello Ramesh,
If I Click ALL
for example, I have 3 offices cleared 5 cases
BO, CT, NH
BO CLEARED 2 cases on 1/1/2009
CT CLEARED 1 cases on 1/1/2009
NH CLEARED 2 cases on 1/5/2009
Then ALL will display
BOCLEARED CTCLEARED NHCLEARED TOTALCLEARED CLEAREDDATE
2-BO 1-CT 3-TOTAL 1/1/2009
2-NH 2-TOTAL 1/5/2009
The office does not clear any cases then it will be blanked
February 19, 2009 at 7:54 am
So, what you really need to do is filter offices according to the parameter.
WITH AllDDS AS
(SELECT DDS, DEClearedDate AS ClearedDate, DECleared AS Cleared
FROM dbo.DECleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, SomaticMCClearedDate AS ClearedDate, SomaticMCCleared AS Cleared
FROM dbo.SomaticMCCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, PsycMCClearedDate AS ClearedDate, PsycMCCleared AS Cleared
FROM dbo.PsycMCCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, DESecondClearedDate AS ClearedDate, DESecondCleared AS Cleared
FROM dbo.DESecondCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, SomaticMCSecondClearedDate AS ClearedDate, SomaticMCSecondCleared AS Cleared
FROM dbo.SomaticMCSecondCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, PsycMCSecondClearedDate AS ClearedDate, PsycMCSecondCleared AS Cleared
FROM dbo.PsycMCSecondCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, DEThirdClearedDate AS ClearedDate, DEThirdCleared AS Cleared
FROM dbo.DEThirdCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, SomaticMCThirdClearedDate AS ClearedDate, SomaticMCThirdCleared AS Cleared
FROM dbo.SomaticMCThirdCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, PsycMCThirdClearedDate AS ClearedDate, PsycMCThirdCleared AS Cleared
FROM dbo.PsycMCThirdCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, DEFourthClearedDate AS ClearedDate, DEFourthCleared AS Cleared
FROM dbo.DEFourthCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, SomaticMCFourthClearedDate AS ClearedDate, SomaticMCFourthCleared AS Cleared
FROM dbo.SomaticMCFourthCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, PsycMCFourthClearedDate AS ClearedDate, PsycMCFourthCleared AS Cleared
FROM dbo.PsycMCFourthCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
),
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, 3, 1000) AS DDS,
DDS AS DDS1, -- This is to identify the actual value (i.e. front comma)
ClearedDate AS CLEAREDDATE,
TotalCleared AS TOTALCLEARED
FROM FinalDDS
--Ramesh
February 19, 2009 at 8:54 am
Hello Ramesh,
The code that you give me .....Does it run in view/query? or Store Procedure?
This is what I did below: (but It give a syntax error "Must declare the scalar variable '@Parameter' " ) do you why?
Create View dbo.ALLCASESCLEARED AS
WITH AllDDS AS
(SELECT DDS, DEClearedDate AS ClearedDate, DECleared AS Cleared
FROM dbo.DECleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, SomaticMCClearedDate AS ClearedDate, SomaticMCCleared AS Cleared
FROM dbo.SomaticMCCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, PsycMCClearedDate AS ClearedDate, PsycMCCleared AS Cleared
FROM dbo.PsycMCCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, DESecondClearedDate AS ClearedDate, DESecondCleared AS Cleared
FROM dbo.DESecondCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, SomaticMCSecondClearedDate AS ClearedDate, SomaticMCSecondCleared AS Cleared
FROM dbo.SomaticMCSecondCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, PsycMCSecondClearedDate AS ClearedDate, PsycMCSecondCleared AS Cleared
FROM dbo.PsycMCSecondCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, DEThirdClearedDate AS ClearedDate, DEThirdCleared AS Cleared
FROM dbo.DEThirdCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, SomaticMCThirdClearedDate AS ClearedDate, SomaticMCThirdCleared AS Cleared
FROM dbo.SomaticMCThirdCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, PsycMCThirdClearedDate AS ClearedDate, PsycMCThirdCleared AS Cleared
FROM dbo.PsycMCThirdCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, DEFourthClearedDate AS ClearedDate, DEFourthCleared AS Cleared
FROM dbo.DEFourthCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, SomaticMCFourthClearedDate AS ClearedDate, SomaticMCFourthCleared AS Cleared
FROM dbo.SomaticMCFourthCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, PsycMCFourthClearedDate AS ClearedDate, PsycMCFourthCleared AS Cleared
FROM dbo.PsycMCFourthCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
),
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, 3, 1000) AS DDS,
DDS AS DDS1, -- This is to identify the actual value (i.e. front comma)
ClearedDate AS CLEAREDDATE,
TotalCleared AS TOTALCLEARED
FROM FinalDDS
February 19, 2009 at 9:02 am
Joseph,
Did you look at the code i posted yesterday? I ran it against the data you provided, and it gives the result you want.
Thanks,
Shahryar
February 20, 2009 at 6:35 am
Hello Ramesh,
Please help me!!!!
For the code that you send me, do I have to type in View/query? Because it says that @Parameter is not declared.
THank you Ramesh,
Create View dbo.ALLCASESCLEARED AS
WITH AllDDS AS
(SELECT DDS, DEClearedDate AS ClearedDate, DECleared AS Cleared
FROM dbo.DECleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, SomaticMCClearedDate AS ClearedDate, SomaticMCCleared AS Cleared
FROM dbo.SomaticMCCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, PsycMCClearedDate AS ClearedDate, PsycMCCleared AS Cleared
FROM dbo.PsycMCCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, DESecondClearedDate AS ClearedDate, DESecondCleared AS Cleared
FROM dbo.DESecondCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, SomaticMCSecondClearedDate AS ClearedDate, SomaticMCSecondCleared AS Cleared
FROM dbo.SomaticMCSecondCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, PsycMCSecondClearedDate AS ClearedDate, PsycMCSecondCleared AS Cleared
FROM dbo.PsycMCSecondCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, DEThirdClearedDate AS ClearedDate, DEThirdCleared AS Cleared
FROM dbo.DEThirdCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, SomaticMCThirdClearedDate AS ClearedDate, SomaticMCThirdCleared AS Cleared
FROM dbo.SomaticMCThirdCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, PsycMCThirdClearedDate AS ClearedDate, PsycMCThirdCleared AS Cleared
FROM dbo.PsycMCThirdCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, DEFourthClearedDate AS ClearedDate, DEFourthCleared AS Cleared
FROM dbo.DEFourthCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, SomaticMCFourthClearedDate AS ClearedDate, SomaticMCFourthCleared AS Cleared
FROM dbo.SomaticMCFourthCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
UNION ALL
SELECT DDS, PsycMCFourthClearedDate AS ClearedDate, PsycMCFourthCleared AS Cleared
FROM dbo.PsycMCFourthCleared
WHERE @Parameter = 'ALL' OR DDS = @Parameter
),
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, 3, 1000) AS DDS,
DDS AS DDS1, -- This is to identify the actual value (i.e. front comma)
ClearedDate AS CLEAREDDATE,
TotalCleared AS TOTALCLEARED
FROM FinalDDS
February 22, 2009 at 11:59 pm
Hello Ramesh,
How are you doing? Sorry to bother you Ramesh, are you ok? Did I make you mad about the way I ask you questions which relate to SQL? Because I am a SQL beginner, so sometime I have posted a question which is not a very smart question. But I need to ask your helps.
If I make you feel unpolite then I am apoloziged.
For the question relates to SQL, would you please help me? So I can be an expert like you someday.
The question you emailed me last week about adding Where clause in the each office. But it won't allow in VIEWS with the variable, so what should I do?
Thank you Ramesh,
Very respectful
February 23, 2009 at 8:17 am
josephptran2002 (2/22/2009)
How are you doing? Sorry to bother you Ramesh, are you ok? Did I make you mad about the way I ask you questions which relate to SQL? Because I am a SQL beginner, so sometime I have posted a question which is not a very smart question. But I need to ask your helps.
I was kind of busy doing few deployments, so I wasn't able to reply to your questions.
josephptran2002 (2/22/2009)
If I make you feel unpolite then I am apoloziged.
...Not yet
josephptran2002 (2/22/2009)
The question you emailed me last week about adding Where clause in the each office. But it won't allow in VIEWS with the variable, so what should I do?
You can't use variables inside a view, so you have to use procedure. And here is that procedure for you. And I hope this time it solves all of your problems.
CREATE PROCEDURE [dbo].[GetWeeklyClearances]
(
@Start DATETIME ,
@End DATETIME,
@Parameter VARCHAR(3) = 'ALL'
)
AS
BEGIN
SET NOCOUNT ON
SELECT@Start = COALESCE( @Start, '01-Jan-2000' ),
@End = COALESCE( @End, GETDATE() ),
@Parameter = COALESCE( @Parameter, 'ALL' )
;WITH AllDDS
AS
(
SELECTDDS, DEClearedDate AS ClearedDate, DECleared AS Cleared
FROMdbo.DECleared
WHEREClearedDate BETWEEN @Start AND @End
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECTDDS, SomaticMCClearedDate AS ClearedDate, SomaticMCCleared AS Cleared
FROMdbo.SomaticMCCleared
WHEREClearedDate BETWEEN @Start AND @End
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECTDDS, PsycMCClearedDate AS ClearedDate, PsycMCCleared AS Cleared
FROMdbo.PsycMCCleared
WHEREClearedDate BETWEEN @Start AND @End
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECTDDS, DESecondClearedDate AS ClearedDate, DESecondCleared AS Cleared
FROMdbo.DESecondCleared
WHEREClearedDate BETWEEN @Start AND @End
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECTDDS, SomaticMCSecondClearedDate AS ClearedDate, SomaticMCSecondCleared AS Cleared
FROMdbo.SomaticMCSecondCleared
WHEREClearedDate BETWEEN @Start AND @End
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECTDDS, PsycMCSecondClearedDate AS ClearedDate, PsycMCSecondCleared AS Cleared
FROMdbo.PsycMCSecondCleared
WHEREClearedDate BETWEEN @Start AND @End
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECTDDS, DEThirdClearedDate AS ClearedDate, DEThirdCleared AS Cleared
FROMdbo.DEThirdCleared
WHEREClearedDate BETWEEN @Start AND @End
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECTDDS, SomaticMCThirdClearedDate AS ClearedDate, SomaticMCThirdCleared AS Cleared
FROMdbo.SomaticMCThirdCleared
WHEREClearedDate BETWEEN @Start AND @End
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECTDDS, PsycMCThirdClearedDate AS ClearedDate, PsycMCThirdCleared AS Cleared
FROMdbo.PsycMCThirdCleared
WHEREClearedDate BETWEEN @Start AND @End
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECTDDS, DEFourthClearedDate AS ClearedDate, DEFourthCleared AS Cleared
FROMdbo.DEFourthCleared
WHEREClearedDate BETWEEN @Start AND @End
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECTDDS, SomaticMCFourthClearedDate AS ClearedDate, SomaticMCFourthCleared AS Cleared
FROMdbo.SomaticMCFourthCleared
WHEREClearedDate BETWEEN @Start AND @End
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECTDDS, PsycMCFourthClearedDate AS ClearedDate, PsycMCFourthCleared AS Cleared
FROMdbo.PsycMCFourthCleared
WHEREClearedDate BETWEEN @Start AND @End
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
),
PivotDDS
AS
(
SELECTClearedDate,
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]
FROMAllDDS
PIVOT
(
SUM( Cleared ) FOR DDS IN( [BO], [CT], [NH], [ME], [RI], [VT], [WO] )
) P
GROUP BY ClearedDate
),
FinalDDS
AS
(
SELECTClearedDate, [BO] AS BOCleared, [CT] AS CTCleared, [NH] AS NHCleared,
[ME] AS MECleared, [RI] AS RICleared, [VT] AS VTCleared, [WO] AS WOCleared,
[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
FROMPivotDDS
)
SELECTSUBSTRING( DDS, 3, 1000 ) AS DDS, ClearedDate AS ClearedDate,
BOCleared, CTCleared, NHCleared, MECleared, RICleared, VTCleared, WOCleared,
TotalCleared AS TotalCleared
FROMFinalDDS
ORDER BY ClearedDate
END
GO
--Ramesh
February 25, 2009 at 6:43 am
Hello Ramesh,
How are you doing? I was sick couple days because the flu season in the US. :hehe: Thanks for the respond back to me,
I have a question for you Ramesh, before you taught me how to dbo. in VIEWS such as "Create View as....". Now you suggest me to use the store procedure because it has the variable, I understand that. Before I used CREATE VIEW and join the rest of the dbo. together in order to get a summary report, so I can display on the front end. Now, I use store procedure and I know that I can not merge dbo. in Store Procedure with dbo. in VIEWS, so I can get the summary report.
Below are the codes for summary report, and the bold dbo. was the one that you told me to create in VIEWS instead of Store procedure (all dbo. below are in VIEWS)
SELECT dbo.BOCLEARED.BOCLEARED, dbo.CTCLEARED.CTCLEARED, dbo.MECLEARED.MECLEARED, dbo.NHCLEARED.NHCLEARED,
dbo.RICLEARED.RICLEARED, dbo.WOCLEARED.WOCLEARED, dbo.VTCLEARED.VTCLEARED, dbo.ALLCASESCLEARED.TOTALCLEARED,
dbo.ALLCASESCLEARED.CLEAREDDATE, dbo.ALLCASESCLEARED.DDSFROM dbo.BOCLEARED FULL OUTER JOIN
dbo.ALLCASESCLEARED ON dbo.BOCLEARED.BOCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.CTCLEARED ON dbo.CTCLEARED.CTCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.MECLEARED ON dbo.MECLEARED.MECLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.NHCLEARED ON dbo.NHCLEARED.NHCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.RICLEARED ON dbo.RICLEARED.RICLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.WOCLEARED ON dbo.WOCLEARED.WOCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.VTCLEARED ON dbo.VTCLEARED.VTCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE
Below is dbo.ALLCASESCLEARED in VIEWS
Create VIEWS AS
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, 3, 1000) AS DDS, DDS AS DDS1, /* This is to identify the actual value (i.e. front comma)*/ TotalCleared AS TOTALCLEARED,
ClearedDate AS CLEAREDDATE
FROM FinalDDS
Now, How do I do? in order to have summary report and store procedure at the same time.
When I use the command "CREATE PROCEDURE", IS IT IN STORE PROCEDURE RAMESH? If it is then I just wish to know why the dbo. that I just create in Store procedure where it(dbo. file) did not show up in store procedure?
Thank you
Viewing 12 posts - 46 through 56 (of 56 total)
You must be logged in to reply to this topic. Login to reply