February 6, 2009 at 1:50 pm
Hi GS,
How can I avoid the query add back the comma?
here what I did, I click on view and click new view and copy the codes that I already had and change Substring or ISNULL like i posted, then i execute the query. it adds the comma as I don't want it to be there
How can I avoid it GS
Thanks
February 7, 2009 at 4:48 am
Hi Joseph,
I am providing you the simplified version of your requirement, which can be easily extended to all other tables as specified in your requirement.
-- Get all DDS into a single virtual table AllDDS
WITH AllDDS
AS
(
SELECTDDS, DEClearedDate AS ClearedDate, DECleared AS Cleared
FROMdbo.DECleared
UNION ALL
SELECTDDS, SomaticMCClearedDate AS ClearedDate, SomaticMCCleared AS Cleared
FROMdbo.SomaticMCCleared
UNION ALL
SELECTDDS, PsycMCClearedDate AS ClearedDate, PsycMCCleared AS Cleared
FROMdbo.PsycMCCleared
-- You need to extend the same above logic for all other tables
),
-- PIVOT all the DDS for each Cleared Date into a new virtual table PivotDDS
PivotDDS
AS
(
SELECTClearedDate,
ISNULL( SUM( ISNULL( [BO], 0 ) ), 0 ) AS [BO],
ISNULL( SUM( ISNULL( [NH], 0 ) ), 0 ) AS [NH],
ISNULL( SUM( ISNULL( [CT], 0 ) ), 0 ) AS [CT]
-- Same logic should be extended here
FROMAllDDS
PIVOT
(
SUM( Cleared ) FOR DDS IN( [BO], [NH], [CT] ) ---- Same logic should be extended here
) P
GROUP BY ClearedDate
),
-- Sum up all DDS, Concat all DDS into a single column, dump into a virtual table FinalDDS
FinalDDS
AS
(
SELECTClearedDate,
[BO] + [NH] + [CT] AS TotalCleared, -- Same logic should be extended here
( CASE WHEN [BO] > 1 THEN ', BO' ELSE '' END )
+ ( CASE WHEN [NH] > 1 THEN ', NH' ELSE '' END )
+ ( CASE WHEN [CT] > 1 THEN ', CT' ELSE '' END ) AS DDS -- Same logic should be extended here
FROMPivotDDS
)
-- Substring is used to remove the first comma & a space generated by the above
-- virtual table and also adding grouping and rollup clauses
SELECTSUBSTRING( DDS, 3, 1000 ) AS DDS,
ClearedDate AS CLEAREDDATE,
TotalCleared AS TOTALCLEARED
FROMFinalDDS
GROUP BY ClearedDate WITH ROLLUP
--Ramesh
February 10, 2009 at 6:46 am
hello
how are you? I'm sorry to take me this long to say thank you very much for your helps. because i broke my arm, i will try your codes. if i have the problems then may i email you?
thank you so much
February 10, 2009 at 7:02 am
hello
may i ask you a question? all codes that provided by you, do i have to write in one query?
thank you
February 10, 2009 at 7:15 am
josephptran2002 (2/10/2009)
hellohow are you? I'm sorry to take me this long to say thank you very much for your helps. because i broke my arm, i will try your codes. if i have the problems then may i email you?
I'm feeling good, but I feel sorry about ur broken arm..., how is it now????
josephptran2002 (2/10/2009)
may i ask you a question? all codes that provided by you, do i have to write in one query?
Well, first make sure that you understand the query and the logic it uses. Once understood, you can easily extend the same logic for all other tables as well..., believe me its not that hard.
BTW, how did you broke your arm??:D
--Ramesh
February 10, 2009 at 9:03 am
Hello Ramesh,
I fell off the train, because someone pushed me. :w00t:
I have tried your codes and it gives me a error msg:
"Column 'FinalDDS.DDS is invalid in the select list because it's not contained in either an aggregate function or the group by clause. "
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] > 1 THEN ', BO' ELSE ' ' END)
+ (CASE WHEN [CT] > 1 THEN ', CT' ELSE ' ' END)
+ (CASE WHEN [NH] > 1 THEN ', NH' ELSE ' ' END)
+ (CASE WHEN [ME] > 1 THEN ', ME' ELSE ' ' END)
+ (CASE WHEN [RI] > 1 THEN ', RI' ELSE ' ' END)
+ (CASE WHEN [VT] > 1 THEN ', VT' ELSE ' ' END)
+ (CASE WHEN [WO] > 1 THEN ', WO' ELSE ' ' END) AS DDS
FROM PivotDDS)
SELECT Substring(DDS, 3, 1000) AS DDS,
ClearedDate AS CLEAREDDATE,
TotalCleared AS TOTALCLEARED
FROM FinalDDS
GROUP BY ClearedDate WITH ROLLUP
Thank you
February 10, 2009 at 10:08 am
Since you are using the GROUP BY clause, it means you need to include non-aggregated columns in that clause....
GROUP BY DDS, ClearedDate, TotalCleared WITH ROLLUP
--Ramesh
February 10, 2009 at 10:54 am
Hello Ramesh,
I changed the group by clause at the end of the codes and it gives me the outlook/display With Empty DDS variable (except the last two cells of DDS column, it gives me CT variable in DDS). And The TotalCleared Column, it supposed to display the Total number of cases were cleared at the bottom of the column (CLearedDate)(It is not displayed the total case(sum) at the bottom of the column) 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] > 1 THEN ', BO' ELSE ' ' END)
+ (CASE WHEN [CT] > 1 THEN ', CT' ELSE ' ' END)
+ (CASE WHEN [NH] > 1 THEN ', NH' ELSE ' ' END)
+ (CASE WHEN [ME] > 1 THEN ', ME' ELSE ' ' END)
+ (CASE WHEN [RI] > 1 THEN ', RI' ELSE ' ' END)
+ (CASE WHEN [VT] > 1 THEN ', VT' ELSE ' ' END)
+ (CASE WHEN [WO] > 1 THEN ', WO' ELSE ' ' END) AS DDS
FROM PivotDDS)
SELECT Substring(DDS, 3, 1000) AS DDS,
ClearedDate AS CLEAREDDATE,
TotalCleared AS TOTALCLEARED
FROM FinalDDS
GROUP BY DDS, TOTALCLEARED, CLEAREDDATE WITH ROLLUP
February 10, 2009 at 12:03 pm
Hello Ramesh,
I changed the group by clause at the end of the codes (like you told me) and it gives me the outlook/display With Empty DDS variable (except the last two cells of DDS column, it gives me CT variable in DDS). And The TotalCleared Column, it supposed to display the Total number of cases were cleared at the bottom of the column (CLearedDate)(It is not displayed the total case(sum) at the bottom of the column) Do you know why? Before it display the report, it pops up the window with a message: "The PIVOT SQL construct or statement is not supported" 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] > 1 THEN ', BO' ELSE ' ' END)
+ (CASE WHEN [CT] > 1 THEN ', CT' ELSE ' ' END)
+ (CASE WHEN [NH] > 1 THEN ', NH' ELSE ' ' END)
+ (CASE WHEN [ME] > 1 THEN ', ME' ELSE ' ' END)
+ (CASE WHEN [RI] > 1 THEN ', RI' ELSE ' ' END)
+ (CASE WHEN [VT] > 1 THEN ', VT' ELSE ' ' END)
+ (CASE WHEN [WO] > 1 THEN ', WO' ELSE ' ' END) AS DDS
FROM PivotDDS)
SELECT Substring(DDS, 3, 1000) AS DDS,
ClearedDate AS CLEAREDDATE,
TotalCleared AS TOTALCLEARED
FROM FinalDDS
GROUP BY DDS, TOTALCLEARED, CLEAREDDATE WITH ROLLUP
February 11, 2009 at 6:47 am
K, it was my bad....
Just change the > 1 condition in FinalDDS to > 0, and also try removing the group by clause
on the final select.
BTW, do you want to group as well as roll up the results?
--Ramesh
February 11, 2009 at 7:08 am
Hello Ramesh,
How are you doing? Thank you so much for your efforts to help me. I'm greatly appreciated your help ......no words can describe how I want to say Thank you very much.
In your code you say that I have to change the 1 to 0 and the last group by that I don't understand your question? would you please explain for me? I am new to this SQL, please explain and show me where it needs to change in order to make the codes work
Thank you very very very much
February 11, 2009 at 7:14 am
...Thanks for the appreciation:), you really made me feel happy...:)
And the solution...
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
--GROUP BY DDS, TOTALCLEARED, CLEAREDDATE WITH ROLLUP
EDIT:
It's already late here in India, So I must be leaving....
--Ramesh
February 11, 2009 at 7:15 am
Just change the > 1 condition in FinalDDS to > 0, and also try removing the group by clause
on the final select.
BTW, do you want to group as well as roll up the results?
--Ramesh
Hello Ramesh
Just wish to ask you an additional question which relates to your questions
What is it meant when you ask me "Do I want to group as well as roll up the results? What does roll up do?
Thank you
February 11, 2009 at 7:32 am
Hello Ramesh,
Thank you so so so much for your efforts, I owed you one.
I have two small questions for your Ramesh,
it displays what I wish it to be 🙂 Thanks God that I know you.....so much.
My questions are:
On DDS columns, how can I eliminate the comma if there is only one entry in a cell for example
DDS
BO, CT
, VT
I wish to eliminate the comma which is right in front of the VT but not the comma in between BO and CT?
Second,
For TOTALCLEARED column, If I wish to know the total at the bottom of the totalcleared column. how do I do?
For example,
TOTALCLEARED
1
2
3
4
5
8
---
23 --> I wish to know the number 23 at the bottom of TotalCleared.
would you please tell me?
Thank you
February 11, 2009 at 9:05 am
josephptran2002 (2/11/2009)
On DDS columns, how can I eliminate the comma if there is only one entry in a cell for example
This part is taken care by the SUBSTRING function in the final SELECT, you see we are adding a comma before each DDS in the FinalDDS query. If you look at the final select query, it read as
SUBSTRING( DDS, 3, 1000 ) which means to take a part of the string starting from position 3, which means we are eliminating the first 2 characters (i.e. " ,")
Second,
For TOTALCLEARED column, If I wish to know the total at the bottom of the totalcleared column. how do I do?
This is why the GROUP BY...ROLLUP clause is there for. But, I recommend it to be done at the front end application, 'cause it's really a part of the presentation.
--Ramesh
Viewing 15 posts - 16 through 30 (of 56 total)
You must be logged in to reply to this topic. Login to reply