February 4, 2009 at 3:10 pm
Hello Experts,
Please help me this one out, I'm really need help on this one. Here is my question
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
I know how to add all three columns of each table together. On the "DDS" Column where it won't display multiple variables but it only display one variable and not display CT, BO. Here is my script for the result below but it only display one variable entry at a time. I wish to display multiple variables in DDS column
Table TOTAL
DDS TOTALCLEARED CLEAREDDATE
BO 3 1/1/2009
CT 5 1/2/2009
NH 3 1/4/2009
NH 4 1/5/2009
BO 8 1/7/2009
SELECT dbo.BOCLEARED.BOCLEARED, dbo.CTCLEARED.CTCLEARED, dbo.MECLEARED.MECLEARED, dbo.NHCLEARED.NHCLEARED,
dbo.TotalCleared.TOTALCLEARED, dbo.TotalCleared.CLEAREDDATE, dbo.TotalCleared.DDS
FROM dbo.BOCLEARED FULL OUTER JOIN dbo.TotalCleared ON dbo.BOCLEARED.BOCLEAREDDATE = dbo.TotalCleared.CLEAREDDATE
FULL OUTER JOIN dbo.CTCLEARED ON dbo.CTCLEARED.CTCLEAREDDATE = dbo.TotalCleared.CLEAREDDATE FULL OUTER JOIN dbo.NHCLEARED ON dbo.NHCLEARED.NHCLEAREDDATE = dbo.TotalCleared.CLEAREDDATE FULL OUTER JOIN
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
Please help me .....please help me
February 4, 2009 at 3:18 pm
Do you have something that defines when they should be in a column together and when they should not?
- 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 4, 2009 at 3:21 pm
[font="Verdana"]Try something like:
substring(
case when dbo.BOCleared.DDS is null then '' else ', ' + dbo.BOCleared.DDS end +
case when dbo.CTCleared.DDS is null then '' else ', ' + dbo.CTCleared.DDS end +
case when dbo.TotalCleared.DDS is null then '' else ', ' + dbo.TotalCleared.DDS end,
3,
10
) as DDS
[/font]
February 4, 2009 at 5:02 pm
Hello GSquare,
First, thank you very much for your helps because I'm really needing all opionions from experts. I'm new to SQL 2005 and ASP.NET 2.0.
Return to your question,
when two offices are cleared the cases at the same date. for example,
BOSTON Office
DDS BOCLEARED BOCLEAREDDATE
BO 1 1/1/2009
BO 2 1/3/2009
NEW HAMPSHIRE OFFICE
DDS NHCLEARED NHCLEAREDDATE
NH 4 1/1/2009
NH 1 1/4/2009
Connecticut office
DDS CTCLEARED CTCLEAREDDATE
CT 1 1/1/2009
CT 2 1/5/2009
Because Boston office cleared 1 case on the same date(1/1/2009) with New Hampshire office and Connecticut office then it will store 3 variables. The rest of cases were cleared on the different date then they don't have to store 3 variables in one cell. The outlook below is the result that I wish to have
DDS TOTALCLEARED CLEAREDDATE
BO,NH,CT 5 1/1/2009
BO 2 1/3/2009
NH 1 1/4/2009
CT 2 1/5/2009
Thank you very much
February 4, 2009 at 5:37 pm
Hello,
Thanks for the respond, I'm appreciated for your effort. But I have a question which needs to ask you so I can understand your code.
Where should I add this code? and what does this code do ? would you please explain to me? I'm sorry I am new to SQL 2005.
can I ask you a question? at the end of your code, you added 3 and 10 what is the purpose to add 3 and 10 in there?
Thank you so much
February 4, 2009 at 5:53 pm
[font="Verdana"]In the front section of your SQL code:[/font]
josephptran2002 (2/4/2009)
SELECT dbo.BOCLEARED.BOCLEARED, dbo.CTCLEARED.CTCLEARED, dbo.MECLEARED.MECLEARED, dbo.NHCLEARED.NHCLEARED,
dbo.TotalCleared.TOTALCLEARED, dbo.TotalCleared.CLEAREDDATE, dbo.TotalCleared.DDS
[font="Verdana"]Replace the "dbo.TotalCleared.DDS" with the section of code I posted, to end up with something like this:
select dbo.BOCLEARED.BOCLEARED,
dbo.CTCLEARED.CTCLEARED,
dbo.MECLEARED.MECLEARED,
dbo.NHCLEARED.NHCLEARED,
dbo.TotalCleared.TOTALCLEARED,
dbo.TotalCleared.CLEAREDDATE,
substring(
case when dbo.BOCleared.DDS is null then '' else ', ' + dbo.BOCleared.DDS end +
case when dbo.CTCleared.DDS is null then '' else ', ' + dbo.CTCleared.DDS end +
case when dbo.TotalCleared.DDS is null then '' else ', ' + dbo.TotalCleared.DDS end,
3, 10
) as DDS
Explanation of how it works:
My code combines to all three of the values of the DDS columns into one string. It makes each string start with a ', ', and then at the end uses the substring() function to pull the very first ', ' off the front of the string. So those two numbers (3, 10) are for the substring function to sklp past the first comma and space.
Why do I add the comma and space to the front of each string? Well, I want something that looks like this:
A, B, C
But the complication is that A, B or C could be empty. So if I add a comma after A and B, I could end up with:
, B, C -- A is empty
A, , C -- B is empty
A, B, -- C is empty
, , C -- A and B are both empty
(You see the problem.)
So instead I add the comma and space to the front of the string, but only if there's a value. So now I end up with:
, A, B, C -- all present
, B, C -- A is empty
, A, C -- B is empty
, A, B -- C is empty
, C -- A and B are both empty
That's almost right, except I have that pesky ", " right at the beginning. So I use the substring() function to skip past those two characters. You can look up substring in Books Online to see why that works.
The end result is that I now end up with:
A, B, C -- all present
B, C -- A is empty
A, C -- B is empty
A, B -- C is empty
C -- A and B are both empty
Which is what I want!
[/font]
February 5, 2009 at 8:33 am
Hello Bruce,
First, I have to say Thank you very much for your efforts to help me at my learning stage to become an expert like you.
Below are the codes that I wrote
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, ISNULL(TOTALCLEARED, 0) AS 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
and it only give me one variable entry like the question which I have asked you such as
DDS TOTALCLEARED CLEAREDDATE
BO 1 1/1/2009
BO 3 1/3/2009
it supposes to give me multiple entry in DDS column if there are offices cleared cases at the same date such as
DDS TotalCleared ClearedDate
BO 1 1/1/2009
BO,CT,NH 3 1/3/2009
Then I replace a.DDS, b.DDS, etc. with your codes as below and it gives me an error. that error message says "incorrect near syntax ')' " do you know why?
WITH CTE AS (SELECT COALESCE (SUBSTRING(CASE WHEN dbo.BOCLEARED.DDS IS NULL THEN ' ' ELSE ' , ' + dbo.BOCLEARED.DDS END + CASE WHEN dbo.CTCLEARED.DDS IS NULL THEN ' ' ELSE ' , ' + dbo.CTCLEARED.DDS END + CASE WHEN dbo.MECLEARED.DDS IS NULL THEN ' ' ELSE ' , ' + dbo.MECLEARED.DDS END + CASE WHEN dbo.NHCLEARED.DDS IS NULL THEN ' ' ELSE ' , ' + dbo.NHCLEARED.DDS END + CASE WHEN dbo.RICLEARED.DDS IS NULL THEN ' ' ELSE ' , ' + dbo.RICLEARED.DDS END + CASE WHEN dbo.WOCLEARED.DDS IS NULL THEN ' ' ELSE ' , ' + dbo.WOCLEARED.DDS END + CASE WHEN dbo.VTCLEARED.DDS IS NULL THEN ' ' ELSE ' , ' + dbo.VTCLEARED.DDS END + CASE WHEN dbo.TotalCleared.DDS IS NULL THEN ' ' ELSE ' , ' + dbo.TotalCleared.DDS END, 3, 10),) 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, ISNULL(TOTALCLEARED, 0) AS TOTALCLEARED, CONVERT(varchar(16), CLEAREDDATE, 101) AS CLEAREDDATE
FROM CTE) 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, DDS WITH ROLLUP
February 5, 2009 at 9:16 am
Your schema is not very good but you can obtain your result like:
-- *** Test Data ***
DECLARE @bo-2 TABLE
(
    DDS char(2) NOT NULL
    ,BOCleared int NOT NULL
    ,BOClearedDate datetime NOT NULL PRIMARY KEY
)
INSERT INTO @bo-2
SELECT 'BO', 1, '20090101' UNION ALL
SELECT 'BO', 2, '20090103'
DECLARE @nh TABLE
(
    DDS char(2) NOT NULL
    ,NHCleared int NOT NULL
    ,NHClearedDate datetime NOT NULL PRIMARY KEY
)
INSERT INTO @nh
SELECT 'NH', 4, '20090101' UNION ALL
SELECT 'NH', 1, '20090104'
DECLARE @CT TABLE
(
    DDS char(2) NOT NULL
    ,CTCleared int NOT NULL
    ,CTClearedDate datetime NOT NULL PRIMARY KEY
)
INSERT INTO @CT
SELECT 'CT', 1, '20090101' UNION ALL
SELECT 'CT', 2, '20090105'
-- *** End Test Data ***
;WITH OfficesCleared (DDS, Cleared, ClearedDate)
AS
(
    SELECT DDS, BOCleared, BOClearedDate
    FROM @bo-2
    UNION ALL
    SELECT DDS, NHCleared, NHClearedDate
    FROM @nh
    UNION ALL
    SELECT DDS, CTCleared, CTClearedDate
    FROM @CT
)
SELECT LEFT(DDS, LEN(DDS) - 1) AS DDS
    ,Cleared, ClearedDate
FROM
(
    SELECT ClearedDate
        ,SUM(Cleared) AS Cleared
        ,STUFF
        (
            (
                SELECT ' ' + C1.DDS + ','
                FROM OfficesCleared C1
                WHERE C1.ClearedDate = C.ClearedDate
                FOR XML PATH('')
            )
            ,1, 1, ''
        ) AS DDS
    FROM OfficesCleared C
    GROUP BY ClearedDate
) D
You should really just have one table to hold all the data. Something like:
-- *** Test Data ***
DECLARE @OfficesCleared TABLE
(
    DDS char(2) NOT NULL
    ,Cleared int NOT NULL
    ,ClearedDate datetime NOT NULL
    ,PRIMARY KEY (ClearedDate, DDS)
)
INSERT INTO @OfficesCleared
SELECT 'BO', 1, '20090101' UNION ALL
SELECT 'BO', 2, '20090103' UNION ALL
SELECT 'NH', 4, '20090101' UNION ALL
SELECT 'NH', 1, '20090104' UNION ALL
SELECT 'CT', 1, '20090101' UNION ALL
SELECT 'CT', 2, '20090105'
-- *** End Test Data ***
SELECT LEFT(DDS, LEN(DDS) - 1) AS DDS
    ,Cleared, ClearedDate
FROM
(
    SELECT ClearedDate
        ,SUM(Cleared) AS Cleared
        ,STUFF
        (
            (
                SELECT ' ' + C1.DDS + ','
                FROM @OfficesCleared C1
                WHERE C1.ClearedDate = C.ClearedDate
                FOR XML PATH('')
            )
            ,1, 1, ''
        ) AS DDS
    FROM @OfficesCleared C
    GROUP BY ClearedDate
) D
February 5, 2009 at 9:31 am
Hello Ken,
Thank you for your helps. I am new to SQL 2005 and I know you are an expert (not like me). What is schema? and my question is
If I wish to store multiple entry in DDS column (the question I posted for this topic). Below are my 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, ISNULL(TOTALCLEARED, 0) AS 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
It only gives me one entry at DDS Column
for example
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
Which scripts should I change in my scripts so I can have the result that I wish
Please help me ...please help me
February 5, 2009 at 9:38 am
1. You want to search for SQL String Concatenation. I have given you one technique.
As string concatenation in TSQL is a bit of a fiddle you may want to return multiple rows to the middle tier/client and do it there.
2. The schema is your tables, constraints etc. You should read up on normalization as it makes life difficult if you store the same information in three different tables.
There are plenty of resources on the web to help you learn SQL.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply