January 22, 2009 at 2:13 pm
Hello All Experts,
I have written the scripts and that scripts are trying to display like in this format below:
This is the format I wish to have
BOCLEAR CTCLEAR WOCLEAR TOTALCLEAR CLEARDATE
0 0 2 2 1/1/2009
1 4 0 5 1/3/2009
2 2 0 4 1/7/2009
0 1 3 4 1/8/2009
The format above is the format that I wish my scripts to display.
But somehow my scripts did not display the format that I wish it to be.
Select dbo.BOWEEKLYCLEAR.BOCLEAR, dbo.CTWEEKLYCLEAR.CTCLEAR, dbo.WOWEEKLYCLEAR.WOCLEAR, dbo.TotalClear.TotalClear, dbo.TotalClear.CLEARDATE
FROM dbo.BOWEEKLYCLEAR FULL OUTER JOIN dbo.CTWEEKLYCLEAR ON dbo.BOWEEKLYCLEAR.BOCLEARDATE = dbo.CTWEEKLYCLEAR.CTCLEARDATE FULL OUTER JOIN dbo.WOWEEKLYCLEAR.WOCLEARDATE ON dbo.WOWEEKLYCLEAR.WOCLEARDATE = dbo.BOWEEKLYCLEAR.BOCLEARDATE FULL OUTER JOIN
dbo.TOTALCLEAR ON dbo.TOTALCLEAR.CLEARDATE = dbo.WOWEEKLYCLEAR.WOCLEARDATE
It displays like this below:
BOCLEAR CTCLEAR WOCLEAR TOTALCLEAR CLEARDATE
NULL NULL 2 2 1/1/2009
1 4 NULL NULL NULL
2 2 NULL NULL NULL
NULL 1 3 4 1/8/2009
How can I display the format that I wish to show above (the format that I wish to have)?
Below is the scripts for dbo.BOWEEKLYCLEAR (IN VIEW)
WITH CTE AS
(SELECT ISNULL(a.DEClear, 0)+ISNULL(b.SomaticMCClear, 0)+ISNULL(c.PsycMCClear, 0)+ISNULL(d.DESecondClear, 0)+ISNULL(e.SomaticMCSecondClear, 0)+ISNULL(f.PsycMCSecondClear, 0)+ISNULL(g.DEThirdClear, 0)+ ISNULL(h.SomaticMCThirdClear, 0)+ISNULL(i.PsycMCThirdClear, 0)+ISNULL(j.DEFourthClear, 0)+ ISNULL(k.SomaticMCFourthClear, 0)+ISNULL(l.PsycMCFourthClear, 0) AS BOWEEKLYCLEAR,
COALESCE (a.DEClearDate,b.SomaticMCClearDate, c.PsycMCClearDate, d.DESecondClearDate, e.SomaticMCSecondClearDate,f.PsycMCSecondClearDate, g.DEThirdClearDate, h.SomaticMCThirdClearDate, i.PsycMCThirdClearDate,j.DEFourthClearDate, k.SomaticMCFourthClearDate, l.PsycMCFourthClearDate) AS BOCLEARDATE
FROM dbo.BODEClear AS a FULL OUTER JOIN dbo.BOSomaticMCClear AS b ON b.SomaticMCClearDate = a.DEClearDate FULL OUTER JOIN dbo.BOPsycMCClear AS c ON c.PsycMCClearDate = b.SomaticMCClearDate FULL OUTER JOIN dbo.BODESecondClear AS d ON d.DESecondClearDate = c.PsycMCClearDate FULL OUTER JOIN dbo.BOSomaticMCSecondClear AS e ON e.SomaticMCSecondClearDate = d.DESecondClearDate FULL OUTER JOIN dbo.BOPsycMCSecondClear AS f ON f.PsycMCSecondClearDate = e.SomaticMCSecondClearDate FULL OUTER JOIN dbo.BODEThirdClear AS g ON g.DEThirdClearDate = f.PsycMCSecondClearDate FULL OUTER JOIN dbo.BOSomaticMCThirdClear AS h ON h.SomaticMCThirdClearDate = g.DEThirdClearDate FULL OUTER JOIN dbo.BOPsycMCThirdClear AS i ON i.PsycMCThirdClearDate = h.SomaticMCThirdClearDate FULL OUTER JOIN dbo.BODEFourthClear AS j ON j.DEFourthClearDate = i.PsycMCThirdClearDate FULL OUTER JOIN dbo.BOSomaticMCFourthClear AS k ON k.SomaticMCFourthClearDate = j.DEFourthClearDate FULL OUTER JOIN dbo.BOPsycMCFourthClear AS l ON l.PsycMCFourthClearDate = k.SomaticMCFourthClearDate)
SELECT SUM(BOWEEKLYCLEAR) AS BOWEEKLYCLEAR, BOCLEARDATE
FROM (SELECT BOWEEKLYCLEAR, CONVERT(varchar(16), BOCLEARDATE, 101) AS BOCLEARDATE
FROM CTE AS CTE_1) AS DATA
GROUP BY BOCLEARDATE WITH ROLLUP
Please Help ME !!!!!!! I'm new to SQL 2005
Thank You So Much
January 22, 2009 at 2:15 pm
You'll need to use IsNull() and set the second value to 0 for the columns that can come back null. Then, for the date, check out "Cast and Convert" in Books Online, and it will give you options for formatting the date.
- 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
January 22, 2009 at 2:48 pm
Hello Square,
Thanks for the respond. I am confused about your answers, because I'm new to this, would you please explain to where should I use ISNULL()? I thought I did use ISNULL, but if I did not use where you think I should. Would you please tell me? and about the set the second value to 0 for the columns that come back Null. How can I set the second value to 0 that not allows the NULL value?
Would you please tell me?
May I ask you a question which relates to "Cast and Convert"? Why should I format the date? How and where can I access the Online Books? Please show me, I try to learn as fast as I can. So I can become an expert like you out there.
Thank You So Much
January 22, 2009 at 2:51 pm
Hello ALL,
Would you please tell me what I should do for the issues that I have posted? I will take all advices from all of the experts out there.
Thank You
P.S: I'm new to this SQL, so tell me what I have to do.
January 22, 2009 at 2:54 pm
In this line:
FROM (SELECT BOWEEKLYCLEAR, CONVERT(varchar(16), BOCLEARDATE, 101) AS BOCLEARDATE
change it to:
FROM (SELECT isnull(BOWEEKLYCLEAR, 0) as BOWEEKLYCLEAR, CONVERT(varchar(16), BOCLEARDATE, 101) AS BOCLEARDATE
That should get you the zeroes you want.
On the Cast and Convert thing, I thought you also wanted the date in a specific format, and didn't notice that you're already doing that in that same line of code (the one I just suggested a change for).
For Books Online, check your Start menu, see if you have a section for Documentation and Tutorials in you SQL Server 2005 heading. If you don't, you'll need to use http://www.msdn.com instead, or you'll need to install the documentation (from the same install source as where you got Management Studio).
- 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
January 23, 2009 at 6:35 am
HELLO ALL EXPERTS,
Would you please help me with the issues that I have posted above?
I will take all experts'opinions.
Thank You in advances
January 23, 2009 at 9:42 am
Did you not read what I posted?
There's no reason to get rude or pushy on this.
- 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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply