February 6, 2009 at 8:20 am
Hello People,
I have three Views(queries), I wish to add the column BOCLEARED + CTCLEARED + NHCLEARED together and calls the new column TOTALCLEARED. The column TOTALCLEARED is also in a new table and that table calls Table TOTAL.
If I have 3 tables (BO, CT, NH) and each table includes:
TABLE BO
DDS BOCLEARED BOCLEAREDDATE
BO 1 1/1/2009
BO 2 1/2/2009
BO 3 1/7/2009
Table CT
DDS CTCLEARED CTCLEAREDDATE
CT 2 1/1/2009
CT 3 1/2/2009
CT 2 1/7/2009
Table NH
DDS NHCLEARED NHCLEAREDDAT
NH 3 1/4/2009
NH 4 1/5/2009
NH 3 1/7/2009
how can I add each column of each table into a new table calls TOTAL
I wish the result like this
Table TOTAL
DDS TOTALCLEARED CLEAREDDATE
BO, CT 3 1/1/2009
BO,CT 5 1/2/2009
NH 3 1/4/2009
NH 4 1/5/2009
BO,CT, NH 8 1/7/2009
Beside adding the BOCLEARED, CTCLEARED, NHCLEARED, if one column of one table has been cleared a case on the same date with other table then in the column DDS will store 2 entries in order to show the users know that there are 2 offices have been cleared number of cases on that date and point out what office that are cleared. For example like on the table TOTAL, because BO and NH have been cleared 3 cases on the same date (1/1/2009) then in the column DDS should show BO, CT. If there is only one office cleared that case then in the column DDS should show only one office like on the table TOTAL of the column DDS; NH is cleared 3 cases on 1/4/2009.
Please help me .....please help me
February 6, 2009 at 8:26 am
what is the joing condition between these 3 tables?
Thanks
Vijaya Kadiyala
February 6, 2009 at 8:31 am
Hello there,
I don't have any conditions for those 3 tables. I might misunderstand your question, would you please tell me what condition you want to ask me?
Thank you for asking me.
February 6, 2009 at 8:35 am
Hi
You said you want to concatenate the columns values in each of these tables!!! To concatenate you need to join these three tables on a certain column..othewise it will result into Cartesian Product.
Thanks
Vijaya Kadiyala
February 6, 2009 at 8:45 am
Well, if I understood you correctly (its better if you can post some sample data & the table structure in script format), this is what you are looking for....
SELECTCOALESCE
(
BO.DDS + ',' + CT.DDS + ',' + NH.DDS,
BO.DDS + ',' + CT.DDS,
BO.DDS + ',' + NH.DDS,
CT.DDS + ',' + NH.DDS
) AS DDS,
ISNULL( BO.BOCLEARED, 0 ) + ISNULL( CT.CTCLEARED, 0 ) + ISNULL( NH.NHCLEARED, 0 ) AS TOTALCLEARED,
COALESCE( BO.BOCLEAREDDATE, CT.CTCLEAREDDATE, NH.NHCLEAREDDAT ) AS CLEAREDDATE
FROMBO
FULL OUTER JOIN CT ON BO.BOCLEAREDDATE = CT.CTCLEAREDDATE
FULL OUTER JOIN NH ON BO.BOCLEAREDDATE = NH.NHCLEAREDDAT OR
CT.CTCLEAREDDATE = NH.NHCLEAREDDAT
EDIT:
I've not tested this, let me know if it works for you.
--Ramesh
February 6, 2009 at 9:22 am
Hello There,
I just try to run the query like you tell me to do so, then it gives me a syntax error and that error message is:
"Incorrect syntax error near ')' "
then I look back to the codes then I found that there is an comma near ')'. I deleted that comma then run it again, then it adds the comma back to the code and come up with the same error message
Below are the codes:
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 TOTALCLEARED, 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 CLEAREDDATE
FROM dbo.DECleared AS a FULL OUTER JOIN
dbo.SomaticMCCleared AS b ON b.SomaticMCClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.PsycMCCleared AS c ON c.PsycMCClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.DESecondCleared AS d ON d.DESecondClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.SomaticMCSecondCleared AS e ON e.SomaticMCSecondClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.PsycMCSecondCleared AS f ON f.PsycMCSecondClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.DEThirdCleared AS g ON g.DEThirdClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.SomaticMCThirdCleared AS h ON h.SomaticMCThirdClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.PsycMCThirdCleared AS i ON i.PsycMCThirdClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.DEFourthCleared AS j ON j.DEFourthClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.SomaticMCFourthCleared AS k ON k.SomaticMCFourthClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.PsycMCFourthCleared AS l ON l.PsycMCFourthClearedDate = a.DEClearedDate)
SELECT MIN(DDS) AS DDS, SUM(TOTALCLEARED) AS TOTALCLEARED, CLEAREDDATE
FROM (SELECT DDS, TOTALCLEARED, CONVERT(varchar(16), CLEAREDDATE, 101) AS CLEAREDDATE
FROM CTE AS CTE_1) AS DATA
WHERE (DDS = 'BO') OR
(DDS = 'CT') OR
(DDS = 'ME') OR
(DDS = 'NH') OR
(DDS = 'RI') OR
(DDS = 'WO') OR
(DDS = 'VT')
GROUP BY CLEAREDDATE WITH ROLLUP
February 6, 2009 at 9:49 am
...You have added an additional comma in the first column selection, i.e.
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,)
And Secondly, you need to modify the first column select to
SELECT SUBSTRING( ISNULL( ' , ' + a.DDS, '' ) + ISNULL( ' , ' + b.DDS, '' ) + ISNULL(' , ' + c.DDS, '' ), 3, 1000 )
--Ramesh
February 6, 2009 at 10:03 am
Hello Ramesh,
So now I have to use Substring( ISNULL(',' + a.DDS, '') + ISNULL(',' + b.DDS, '')+.......+ ISNULL(',' + l.DDS, ''), 3,1000) right?
I have to delete COALESCE (a.DDS + ',' + b.DDS + ','+.......+l.DDS) and replace with the above codes? Am I right?
Thank you so much for your lesson
February 6, 2009 at 10:16 am
Hello,
I replaced this code Substring as you told me then it gives me a syntax error: Incorrect syntax near ','
DO YOU KNOW WHY? THANK YOU VERY MUCH
WITH CTE AS (SELECT 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, 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 TOTALCLEARED, 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 CLEAREDDATE
FROM dbo.DECleared AS a FULL OUTER JOIN
dbo.SomaticMCCleared AS b ON b.SomaticMCClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.PsycMCCleared AS c ON c.PsycMCClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.DESecondCleared AS d ON d .DESecondClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.SomaticMCSecondCleared AS e ON e.SomaticMCSecondClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.PsycMCSecondCleared AS f ON f.PsycMCSecondClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.DEThirdCleared AS g ON g.DEThirdClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.SomaticMCThirdCleared AS h ON h.SomaticMCThirdClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.PsycMCThirdCleared AS i ON i.PsycMCThirdClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.DEFourthCleared AS j ON j.DEFourthClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.SomaticMCFourthCleared AS k ON k.SomaticMCFourthClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.PsycMCFourthCleared AS l ON l.PsycMCFourthClearedDate = a.DEClearedDate)
SELECT MIN(DDS) AS DDS, SUM(TOTALCLEARED) AS TOTALCLEARED, CLEAREDDATE
FROM (SELECT DDS, TOTALCLEARED, CONVERT(varchar(16), CLEAREDDATE, 101) AS CLEAREDDATE
FROM CTE AS CTE_1) AS DATA
WHERE (DDS = 'BO') OR
(DDS = 'CT') OR
(DDS = 'ME') OR
(DDS = 'NH') OR
(DDS = 'RI') OR
(DDS = 'WO') OR
(DDS = 'VT')
GROUP BY CLEAREDDATE WITH ROLLUP
February 6, 2009 at 11:24 am
Please help me ....please help me to solve this issue expert
February 6, 2009 at 11:41 am
When you say it added the comma back in, what added the comma back in? Are you building these queries in Management Studio, or something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 6, 2009 at 12:04 pm
Hello GS,
I use the Microsoft SQL Server Management Studio, and when i run the codes then it automatically adds to comma back in by itself.
Thanks
February 6, 2009 at 1:17 pm
Are you building the query in the New View GUI, or typing it into a connection window and running it? Typing it in and running won't add commas to it. I've had too many problems with the GUI messing up queries to trust it, myself.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 6, 2009 at 1:23 pm
Hello GS,
What is it mean GUI? I type it in View. I go to View and click new view and start typing the code in there.
Thanks
February 6, 2009 at 1:28 pm
HI GS,
DO you know the command how to insert data from multiple sources (in view). For example, I have 5 tables in views such as dbo.BOCLEARED, dbo.CTCLEARED, dbo.NHCLEARED, dbo.VTCLEARED, and dbo.TotalCleared. I have created a table calls SummaryClearance with 5 field names such as BOCLEARED, CTCLEARED, NHCLEARED, VTCLEARED and TOTALCLEARED.
I wish to transfer the data from 5 tables (views) with the same field names into SummaryClearance. Would you please tell me how?
Thank you
Viewing 15 posts - 1 through 15 (of 56 total)
You must be logged in to reply to this topic. Login to reply