February 11, 2009 at 11:47 am
Hello Ramesh,
I have four tables such as BOCLEARED, CTCLEARED, NHCLEARED, and ToTalCleared and I wish to take one column from each table and make it a new table which will be called "GrandTotal".
In table BOCLEARED, it has 3 field names such as DDS, BOCLEARED, BOCLEAREDDATE.
In table CTCLEARED, it also has 3 field names such as DDS, CTCLEARED, CTCLEAREDDATE.
In table NHCLEARED, it has 3 field names such as DDS, NHCLEARED, NHCLEAREDDATE.
In table TotalCleared, it also has 3 field names such as DDS, TOTALCLEARED, CLEAREDDATE. But the column "TOTALCLEARED" is combined from 3 columns(of 3 different tables) such as BOCLEARED, CTCLEARED, and NHCLEARED.
I wish to have the total number for each column and make it into a brand new table which is GrandTotal
for example,
BOCLEARED CTCLEARED NHCLEARED TOTALCLEARED
2 3 5 10
3 4 6 13
4 5 7 16
------------------------------------------------
9 12 18 39
I know that I have to transfer the data of column "BOCLEARED" from dbo.BOCLEARED, column "CTCLEARED" from dbo.CTCLEARED, column "NHCLEARED" from dbo.NHCLEARED, and column "TOTALCLEARED" from dbo.TotalCleared to brand new table which is called GrandTotal.
How do I do that Ramesh? would you please tell me?
Thank you so much Ramesh
February 11, 2009 at 1:19 pm
Hello Ramesh,
I am really sorry to ask you questions about SQL 2005, since I have no one to turn to ask for such questions and require genius knowledges like you.
When I pasted the codes that you helped me in View then it pops up a message states that " The following errors were encountered while parsing the contents of the SQL pane :
The PIVOT SQL construct or statement is not supported. The query cannot be represented graphically in the Diagram and Criteria Pane."
But it displays the outlook that I wish it to be, then I click on the save button in order to name the database like TotalCleared(dbo.TotalCleared in VIEW). I close the view window, and try to go back to VIEW and reopen the dbo.TotalCleared (in View) to see if it works. then it won't show the outlook that I wish it to be.
I wish to save the file, so I can go to store procedure and write the codes that will use dbo.TotalCleared as the sources to display in ASP.NET 2.0
My question is where should I paste this codes below in? So I can save the file? and treat it as the source for store procedure? Is it in view? ....etc.....I don't know? would you please tell me? or in function? How Ramesh? I am sorry I am new in this SQL 2005. Please tell me how?
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
February 12, 2009 at 3:13 am
josephptran2002 (2/11/2009)
I am really sorry to ask you questions about SQL 2005, since I have no one to turn to ask for such questions
...Don't worry we are here to help you, provided that you do some research, learn the basics
josephptran2002 (2/11/2009)
.....and require genius knowledges like you.
I ain't no GENIUS....
josephptran2002 (2/11/2009)
When I pasted the codes that you helped me in View then it pops up a message states that " The following errors were encountered while parsing the contents of the SQL pane :The PIVOT SQL construct or statement is not supported. The query cannot be represented graphically in the Diagram and Criteria Pane."
Personally, I never use the Designer to create views, because of the issues arise when re-loading the same object, specially when you have complex structures & "select * " queries. So, I recommend you to write the definitions in QUERY window rather than the designer window.
josephptran2002 (2/11/2009)
My question is where should I paste this codes below in? So I can save the file? and treat it as the source for store procedure? Is it in view? ....etc.....I don't know? would you please tell me? or in function? How Ramesh? I am sorry I am new in this SQL 2005. Please tell me how?
Just add "CREATE VIEW dbo.TotalCleared AS" (without the quotes) before the CTE query..
CREATE VIEW dbo.TotalCleared
AS
WITH AllDDS AS......
...And to view data you write
SELECT * FROM dbo.TotalCleared[/CODE]
Lastly, I suggest you start reading books/articles on SQL Server to be an EXPERT in this arena...
--Ramesh
February 12, 2009 at 6:39 am
Hello Ramesh,
Thank you so much for your help, I will find the book. I have a question about Create View
I click on New query (on the menu bar) and start writing
Create View dbo.ALLCASESCLEARED
AS
With ALLDDS AS
(Select DDS, .......
After that I click the Execute button(0n the menu bar), and at the bottom there is a message "Command(s) completed Successfully"
My question is where do they save the file dbo.ALLCASESCLEARED? Is it in View? or somewhere else? Would you please tell me?
Thank you very Ramesh,
You are trully a good person and if someday you are in the Massachusetts(USA) please let me know So we can hang out. Hopefully at that time I am already becoming an expert in SQL like you.
February 12, 2009 at 6:46 am
Hello Ramesh,
please forget about the question I just email you. I know where it is..... It is in View...I have to refresh it.
Thank you so much Ramesh
You are my good friend
If you come to the US or you come to Massachusetts, please stop at my house and we can hang out for fun.
my email Josephptran2002@hotmail.com
phone (617) 304-5925
February 12, 2009 at 7:17 am
Hello Ramesh,
Sorry that I am bothering you so much, but because I try to learn as fast as possible to be like you someday very soon. I have a question which needs your expertise and that question is:
I have four tables such as BOCLEARED, CTCLEARED, NHCLEARED, and ToTalCleared and I wish to take one column from each table and make it a new table which will be called "GrandTotal".
In table BOCLEARED, it has 3 field names such as DDS, BOCLEARED, BOCLEAREDDATE.
In table CTCLEARED, it also has 3 field names such as DDS, CTCLEARED, CTCLEAREDDATE.
In table NHCLEARED, it has 3 field names such as DDS, NHCLEARED, NHCLEAREDDATE.
In table TotalCleared, it also has 3 field names such as DDS, TOTALCLEARED, CLEAREDDATE. But the column "TOTALCLEARED" is combined from 3 columns(of 3 different tables) such as BOCLEARED, CTCLEARED, and NHCLEARED.
I wish to have the total number for each column and make it into a brand new table which is GrandTotal
for example,
BOCLEARED CTCLEARED NHCLEARED TOTALCLEARED
2 3 5 10
3 4 6 13
4 5 7 16
------------------------------------------------
9 12 18 39
I know that I have to transfer the data of column "BOCLEARED" from dbo.BOCLEARED, column "CTCLEARED" from dbo.CTCLEARED, column "NHCLEARED" from dbo.NHCLEARED, and column "TOTALCLEARED" from dbo.TotalCleared to brand new table which is called GrandTotal.
How do I do that Ramesh? would you please tell me?
Thank you so much Ramesh
February 12, 2009 at 7:46 am
Why do you need to store these values in a separate table? Why can't you query the view that you just now created? It will have all the required values in it, after including those additional columns in the FinalDDS & final select section.
If you come to the US or you come to Massachusetts, please stop at my house and we can hang out for fun.
...If luck has its mercy on me, then will surely meet someday in the future....:):)
--Ramesh
February 12, 2009 at 8:02 am
Hello Ramesh,
So if I wish to have the total at the bottom of each column what should I do? here is the code that I join each column and totalcleared. But I dont have the total(grandtotal) at the bottom of each column. what should I do?
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.DDS
FROM 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 are the codes for BOCLEARED(total cases cleared by BO Office)
WITH CTE AS (SELECT COALESCE (a.DDS, b.DDS, c.DDS, d.DDS, e.DDS, f.DDS, g.DDS, h.DDS, i.DDS, j.DDS, k.DDS, l.DDS) AS DDS, ISNULL(a.DECleared, 0)
+ ISNULL(b.SomaticMCCleared, 0) + ISNULL(c.PsycMCCleared, 0) + ISNULL(d.DESecondCleared, 0)
+ ISNULL(e.SomaticMCSecondCleared, 0) + ISNULL(f.PsycMCSecondCleared, 0) + ISNULL(g.DEThirdCleared, 0)
+ ISNULL(h.SomaticMCThirdCleared, 0) + ISNULL(i.PsycMCThirdCleared, 0) + ISNULL(j.DEFourthCleared, 0)
+ ISNULL(k.SomaticMCFourthCleared, 0) + ISNULL(l.PsycMCFourthCleared, 0) AS BOCLEARED, COALESCE (a.DEClearedDate,
b.SomaticMCClearedDate, c.PsycMCClearedDate, d.DESecondClearedDate, e.SomaticMCSecondClearedDate,
f.PsycMCSecondClearedDate, g.DEThirdClearedDate, h.SomaticMCThirdClearedDate, i.PsycMCThirdClearedDate,
j.DEFourthClearedDate, k.SomaticMCFourthClearedDate, l.PsycMCFourthClearedDate) AS BOCLEAREDDATE
FROM dbo.BODECleared AS a FULL OUTER JOIN
dbo.BOSomaticMCCleared AS b ON b.SomaticMCClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.BOPsycMCCleared AS c ON c.PsycMCClearedDate = b.SomaticMCClearedDate FULL OUTER JOIN
dbo.BODESecondCleared AS d ON d.DESecondClearedDate = c.PsycMCClearedDate FULL OUTER JOIN
dbo.BOSomaticMCSecondCleared AS e ON e.SomaticMCSecondClearedDate = d.DESecondClearedDate FULL OUTER JOIN
dbo.BOPsycMCSecondCleared AS f ON f.PsycMCSecondClearedDate = e.SomaticMCSecondClearedDate FULL OUTER JOIN
dbo.BODEThirdCleared AS g ON g.DEThirdClearedDate = f.PsycMCSecondClearedDate FULL OUTER JOIN
dbo.BOSomaticMCThirdCleared AS h ON h.SomaticMCThirdClearedDate = g.DEThirdClearedDate FULL OUTER JOIN
dbo.BOPsycMCThirdCleared AS i ON i.PsycMCThirdClearedDate = h.SomaticMCThirdClearedDate FULL OUTER JOIN
dbo.BODEFourthCleared AS j ON j.DEFourthClearedDate = i.PsycMCThirdClearedDate FULL OUTER JOIN
dbo.BOSomaticMCFourthCleared AS k ON k.SomaticMCFourthClearedDate = j.DEFourthClearedDate FULL OUTER JOIN
dbo.BOPsycMCFourthCleared AS l ON l.PsycMCFourthClearedDate = k.SomaticMCFourthClearedDate)
SELECT MIN(DISTINCT DDS) AS DDS, SUM(BOCLEARED) AS BOCLEARED, BOCLEAREDDATE
FROM (SELECT DDS, ISNULL(BOCLEARED, 0) AS BOCLEARED, CONVERT(varchar(16), BOCLEAREDDATE, 101) AS BOCLEAREDDATE
FROM CTE AS CTE_1) AS DATA
WHERE (DDS = 'BO')
GROUP BY BOCLEAREDDATE WITH ROLLUP
So, how should I have the total for each column and totalcleared column?
Thank you very much Ramesh
February 12, 2009 at 8:10 am
Hello Ramesh,
I am new to SQL 2005, so sometime the method that I used which might be a dumb method. 🙂 I used to work at the White house in Washington DC, but I decide to change my career after a long time in ..........
Do you like to come to US?
Thank you for your helps.....I am at the learning stage to be an expert like you.
February 12, 2009 at 9:05 am
Hello Ramesh,
have a question which I need your helps
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
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 13, 2009 at 5:10 am
Hello Ramesh,
How are you doing? I have created a store procedure in order to allow me to pull specific report on ASP.NET 2.0 . But somehow when I click on the dropdownbox where it contains multiple choices such as BO, CT, ME, NH, RI, VT, WO and ALL (in a dropdownbox). I pick "ALL" then it displays all reports of all office from startdate to Enddate (that what I wish) but if I pick BO or CT or VT....then I won't display that specific office.
I think something wrong with my store procedure code where it stated in bold words. Would you please tell me how can I write a code so I can pull out the specific report for each office. That why I tried to store multiple entries in "DDS" column. So I can allow the users to pick office they choose to display.
Below are my store procedure, would you please take a look my codes for me ???
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
DO I HAVE TO CHANGE SOMETHING IN ORDER TO BE ABLE TO PULL SPECIFIC REPORT?
Thank you very much Ramesh
February 13, 2009 at 10:33 am
If I read your sql statement correctly
[font="Courier New"]
SUBSTRING
....(
........ISNULL(' , ' + a.DDS, '') + ISNULL(' , ' + b.DDS, '')
......+ ISNULL(' , ' + c.DDS, '') + ISNULL(' , ' + d .DDS, '')
..... + ISNULL(' , ' + e.DDS, '') + ISNULL(' , ' + f.DDS, '')
......+ ISNULL(' , ' + g.DDS, '') + ISNULL(' , ' + h.DDS, '')
......+ ISNULL(' , ' + i.DDS, '') + ISNULL(' , ' + j.DDS, '')
......+ ISNULL(' , ' + k.DDS, '') + ISNULL(' , ' + l.DDS, '') AS DDS,[/font]
(I am using the dot character "." to force proper indentation of the code, you have to imagine they are blank spaces (which disapper when posting).
Please note that the proper format for the SUBSTRING statement is
[font="Courier New"]....SUBSTRING (of your isnull + isnull + isnull, starting position, number of characters)[/font]
and unless I am mistaken, I do not see the last two parameters in your substring statement.
If I am wrong (it might have been misled by the non obvious indentation of your code) or it still does not work, then I suggest you debug your statement by simplifying it, i.e. removing lines or items between the + sign and any table except the first two. Something like:
[font="Courier New"]SELECT SUBSTRING( ISNULL(a.dds, '') + ISNULL(b.dds, '') ), 1, 60) AS DDS
FROM dbo.DECleared AS a
OUTER JOIN dbo.SomaticMCCleared AS b
...ON b.SomaticMCClearedDate = a.DEClearedDate[/font]
Start with something small and when you got the minimum to work, then build it up line by line and retesting it every time.
February 14, 2009 at 4:14 am
Does the DDS column in the table/view contains comma separated values (of each offices)? If yes, then you have to use the LIKE operator using wildcards.
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))
--Ramesh
February 17, 2009 at 7:59 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 17, 2009 at 12:15 pm
Here's some code that seems to work for your problem. It's multi-step, and in incorporates a table variable. this would best be implemented in a stored proc. Hope it helps:
declare @DATES TABLE (OFFICELISTVARCHAR(30),
COUNTSINT,
CLEAREDDATEDATETIME )
INSERT INTO @DATES
SELECT',',0,BOCLEAREDDATE AS CLEAREDDATE
FROMBO
UNION
SELECT',',0,CTCLEAREDDATE AS CLEAREDDATE
FROMCT
UNION
SELECT',',0,NHCLEAREDDATE AS CLEAREDDATE
FROMNH
UPDATE@DATES
SETOFFICELIST = OFFICELIST + DDS + ',',
COUNTS = COUNTS + BOCLEARED
FROM@DATES D
JOINBO
ONBO.BOCLEAREDDATE = D.CLEAREDDATE
UPDATE@DATES
SETOFFICELIST = OFFICELIST + DDS + ',',
COUNTS = COUNTS + CTCLEARED
FROM@DATES D
JOINCT
ONCT.CTCLEAREDDATE = D.CLEAREDDATE
UPDATE@DATES
SETOFFICELIST = OFFICELIST + DDS + ',',
COUNTS = COUNTS + NHCLEARED
FROM@DATES D
JOINNH
ONNH.NHCLEAREDDATE = D.CLEAREDDATE
select* from @DATES
go
Viewing 15 posts - 31 through 45 (of 56 total)
You must be logged in to reply to this topic. Login to reply