January 15, 2009 at 7:42 am
Hello ALL Experts,
I have created three VIEWs and I wish to merge them into one View. Would someone show me how to do that?
For example,
I've created dbo.BOWEEKLYCLEARED (in VIEW) and the outlook displays as below:
BOCLEARED BOCLEAREDDATE
2 1/2/2009
3 1/3/2009
Next, I have created dbo.CTCLEARED (also in VIEW) and the outlook displays as below:
CTCLEARED CTCLEAREDDATE
4 1/3/2009
6 1/5/2009
Then I wrote a script to add the other two VIEWS and called that one is dbo.TOTALCLEARED (also in VIEW)
TOTALCLEARED CLEAREDDATE
2 1/2/2009
7 1/3/2009
6 1/5/2009
Here is my question: how can I merge all three VIEWS into one VIEW? and I wish to the outlook displays like below:
bocleared cleareddate ctcleared cleareddate totalcleared cleareddate
2 1/2/2009 4 1/3/2009 2 1/2/2009
3 1/3/2009 6 1/5/2009 4 1/3/2009
6 1/5/2009
would you please show me how to merge three views into one view?
my next question is:
How can I add store procedure for the variable ALL? for example,
On my front end, I will create StartDate, EndDate, and OfficeCode.
I know how to create store procedure for StartDate and EndDate. but don't know how to create third variable?
here is my code for startdate and enddate:
Alter Procedure
@StartDate datetime = 0,
@EndDate datetime = 0
As
Begin
Set NOCOUNT ON;
--Insert statements for procedure here
Select sum(totalcleared) as 'Total Cases Cleared'
from dbo.TotalCleared
where clearedDate between @StartDate and @EndDate
End
The reason I wish to create the third variable because I wish to allow the users to see BO report (if they click on BO drop downbox), CT report (if they click on CT drop downbox). But for the summary report, which will show BO and CT and Total all in one report (the merge view). how do I create the variable ALL in order to be able to show all report?
Would you please show me how?
Thank You
Joe
January 15, 2009 at 7:54 am
Use ROW_NUMBER and FULL JOIN.
It will be easier if you post your three view definitions and we might be able to come up with something clever for you.
N 56°04'39.16"
E 12°55'05.25"
January 15, 2009 at 8:32 am
Hi Peso,
here are the scripts for BO, CT, and TotalCleared, respectively.
WITH CTE AS (SELECT 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 BOWEEKLYCLEARED, 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 SUM(BOWEEKLYCLEARED) AS BOWEEKLYCLEARED, BOCLEAREDDATE
FROM (SELECT BOWEEKLYCLEARED, CONVERT(varchar(16), BOCLEAREDDATE, 101) AS BOCLEAREDDATE
FROM CTE AS CTE_1) AS DATA
GROUP BY BOCLEAREDDATE WITH ROLLUP
CT:
WITH CTE AS (SELECT 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 CTWEEKLYCLEARED, 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 CTCLEAREDDATE
FROM dbo.BODECleared AS a FULL OUTER JOIN
dbo.CTSomaticMCCleared AS b ON b.SomaticMCClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.CTPsycMCCleared AS c ON c.PsycMCClearedDate = b.SomaticMCClearedDate FULL OUTER JOIN
dbo.CTDESecondCleared AS d ON d.DESecondClearedDate = c.PsycMCClearedDate FULL OUTER JOIN
dbo.CTSomaticMCSecondCleared AS e ON e.SomaticMCSecondClearedDate = d.DESecondClearedDate FULL OUTER JOIN
dbo.CTPsycMCSecondCleared AS f ON f.PsycMCSecondClearedDate = e.SomaticMCSecondClearedDate FULL OUTER JOIN
dbo.CTDEThirdCleared AS g ON g.DEThirdClearedDate = f.PsycMCSecondClearedDate FULL OUTER JOIN
dbo.CTSomaticMCThirdCleared AS h ON h.SomaticMCThirdClearedDate = g.DEThirdClearedDate FULL OUTER JOIN
dbo.CTPsycMCThirdCleared AS i ON i.PsycMCThirdClearedDate = h.SomaticMCThirdClearedDate FULL OUTER JOIN
dbo.CTDEFourthCleared AS j ON j.DEFourthClearedDate = i.PsycMCThirdClearedDate FULL OUTER JOIN
dbo.CTSomaticMCFourthCleared AS k ON k.SomaticMCFourthClearedDate = j.DEFourthClearedDate FULL OUTER JOIN
dbo.CTPsycMCFourthCleared AS l ON l.PsycMCFourthClearedDate = k.SomaticMCFourthClearedDate)
SELECT SUM(CTWEEKLYCLEARED) AS CTWEEKLYCLEARED, CTCLEAREDDATE
FROM (SELECT CTWEEKLYCLEARED, CONVERT(varchar(16), CTCLEAREDDATE, 101) AS CTCLEAREDDATE
FROM CTE AS CTE_1) AS DATA
GROUP BY CTCLEAREDDATE WITH ROLLUP
TotalCleared
WITH CTE AS (SELECT 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 CTWEEKLYCLEARED, 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 CTCLEAREDDATE
FROM dbo.BODECleared AS a FULL OUTER JOIN
dbo.CTSomaticMCCleared AS b ON b.SomaticMCClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.CTPsycMCCleared AS c ON c.PsycMCClearedDate = b.SomaticMCClearedDate FULL OUTER JOIN
dbo.CTDESecondCleared AS d ON d.DESecondClearedDate = c.PsycMCClearedDate FULL OUTER JOIN
dbo.CTSomaticMCSecondCleared AS e ON e.SomaticMCSecondClearedDate = d.DESecondClearedDate FULL OUTER JOIN
dbo.CTPsycMCSecondCleared AS f ON f.PsycMCSecondClearedDate = e.SomaticMCSecondClearedDate FULL OUTER JOIN
dbo.CTDEThirdCleared AS g ON g.DEThirdClearedDate = f.PsycMCSecondClearedDate FULL OUTER JOIN
dbo.CTSomaticMCThirdCleared AS h ON h.SomaticMCThirdClearedDate = g.DEThirdClearedDate FULL OUTER JOIN
dbo.CTPsycMCThirdCleared AS i ON i.PsycMCThirdClearedDate = h.SomaticMCThirdClearedDate FULL OUTER JOIN
dbo.CTDEFourthCleared AS j ON j.DEFourthClearedDate = i.PsycMCThirdClearedDate FULL OUTER JOIN
dbo.CTSomaticMCFourthCleared AS k ON k.SomaticMCFourthClearedDate = j.DEFourthClearedDate FULL OUTER JOIN
dbo.CTPsycMCFourthCleared AS l ON l.PsycMCFourthClearedDate = k.SomaticMCFourthClearedDate)
SELECT SUM(CTWEEKLYCLEARED) AS CTWEEKLYCLEARED, CTCLEAREDDATE
FROM (SELECT CTWEEKLYCLEARED, CONVERT(varchar(16), CTCLEAREDDATE, 101) AS CTCLEAREDDATE
FROM CTE AS CTE_1) AS DATA
GROUP BY CTCLEAREDDATE WITH ROLLUP
Do you know anything about store procedure? like the question I posted.
Thank You
January 15, 2009 at 10:50 am
Dear ALL Experts out there,
Would you please help me on the topic that I posted. I would like to have as many experts opionions as possible.
Thank you so much in advances.
January 15, 2009 at 11:03 am
bocleared cleareddate ctcleared cleareddate totalcleared cleareddate
2 1/2/2009 4 1/3/2009 2 1/2/2009
3 1/3/2009 6 1/5/2009 4 1/3/2009
6 1/5/2009
Are you sure you want your output to look like this? The doesn't seem to be a relationship on the row level between the results from the three views. Are you aiming for a result like this that would display bocleared, ctccleared, and totalclear for a date on the same row.
bocleared cleareddate ctcleared cleareddate totalcleared cleareddate
2 1/2/2009 2 1/2/2009
3 1/3/2009 4 1/3/2009 4 1/3/2009
6 1/5/2009 6 1/5/2009
January 15, 2009 at 11:21 am
Hi There
the outlook(lay out of the column headers) is what I wish it to be. But sorry for the mistyping of the figure in the "TotalCleared" column
TotalCleared ClearedDate
2 1/2/2009
7 1/3/2009
6 1/5/2009
Thank you very much
January 15, 2009 at 11:25 am
Hi ALL,
I have created each view for each office such BO and CT then I wrote a script for totalcleared and this totalcleared is combined of BO and CT.
Now, I wish to merge BO (table in view), CT (table in View) and TotalCleared(table in view) into one table in view. So I use the new table of all three to display my results on the front end (ASP.NET 2.0)
Thank you
January 15, 2009 at 12:48 pm
January 15, 2009 at 1:13 pm
Hi Robert,
Thanks for the comments, but each field name is different from the other.
BOCLEARED BOCLEAREDDATE CTCLEARED CTCLEAREDDATE TOTALCLEARED CLEAREDDATE
I wish to merge them like the outlook shown above. But I don't know the command to merge them or join them or whatsoever 🙂 I'm new to SQL 2005. If you know, would you please show me or someone out there who are considering expert on this SQL, would you please show me the command?
Thank you so much
January 15, 2009 at 1:26 pm
Robert klimes (1/15/2009)
bocleared cleareddate ctcleared cleareddate totalcleared cleareddate
2 1/2/2009 4 1/3/2009 2 1/2/2009
3 1/3/2009 6 1/5/2009 4 1/3/2009
6 1/5/2009
Are you sure you want your output to look like this? The doesn't seem to be a relationship on the row level between the results from the three views. Are you aiming for a result like this that would display bocleared, ctccleared, and totalclear for a date on the same row.
bocleared cleareddate ctcleared cleareddate totalcleared cleareddate
2 1/2/2009 2 1/2/2009
3 1/3/2009 4 1/3/2009 4 1/3/2009
6 1/5/2009 6 1/5/2009
Robert's question still stands. There does not appear to be any sort of relationship between the bo, ct, and total values in each row. Can you explain why your output should look like you've posted?
January 15, 2009 at 1:39 pm
Add a ROW_NUMBER() for each view, and then do a FULL JOIN when combining the three views on the ROW_NUMBER() column.
N 56°04'39.16"
E 12°55'05.25"
January 15, 2009 at 1:41 pm
Since you are new to SQL this is a good learning opportunity. What I think you want to do is to have BO, CT and Total in the same row grouped by date. if this is the case then an inner join on the date of all three view would satisfy your needs. Instead of just giving you the answer, which in that case you would not learn much, I will direct you to a couple of website that give a tutorial about JOINS.
http://www.w3schools.com/sql/sql_join_inner.asp
http://www.databasejournal.com/features/mssql/article.php/3630381/MS-SQL-Joins-Part-3.htm
Read these, then make an attempt at getting the result you want. If you are still not getting the correct results, I will try to help.
January 15, 2009 at 1:58 pm
Hi John
The outlook is what I wish to display but somehow I mistyped the figure for the "TotalCleared" column, it should be added up and equal BOCleared and CTCLeared
TotalCleared ClearedDate
2 1/2/2009
7 1/3/2009
6 1/5/2009
Thank You
January 15, 2009 at 2:36 pm
I would recommend following Robert's advice. Here's some code to work with. You'll have to add to the select list to get all of your columns, but this should work.
DECLARE @bo-2 TABLE (BOCleared int, ClearedDate datetime)
INSERT INTO @bo-2
SELECT 2, '1/2/2009' UNION ALL
SELECT 3, '1/3/2009'
DECLARE @CT TABLE (CTCleared int, ClearedDate datetime)
INSERT INTO @CT
SELECT 4, '1/3/2009' UNION ALL
SELECT 6, '1/5/2009'
SELECT COALESCE(BOCleared, 0) as BOCleared,
COALESCE(CTCleared, 0) as CTCleared,
(COALESCE(BOCleared, 0) + COALESCE(CTCleared, 0)) as TotalCleared,
COALESCE(BO.ClearedDate,CT.ClearedDate) as ClearedDate
FROM @bo-2 bo
FULL OUTER JOIN @CT ct ON bo.ClearedDate = ct.ClearedDate
ORDER BY COALESCE(BO.ClearedDate,CT.ClearedDate)
January 16, 2009 at 8:35 am
Hello Robert,
How are you doing? First, thank you so much for the tips (links). I got stuck in a small issue (I guess) and here is my script that I wrote
SELECT dbo.BOWEEKLYCLEARED.BOWEEKLYCLEARED, dbo.CTWEEKLYCLEARED.CTWEEKLYCLEARED,
dbo.MEWEEKLYCLEARED.MEWEEKLYCLEARED,
dbo.NHWEEKLYCLEARED.NHWEEKLYCLEARED, dbo.RIWEEKLYCLEARED.RIWEEKLYCLEARED, dbo.WOWEEKLYCLEARED.WOWEEKLYCLEARED,
dbo.VTWEEKLYCLEARED.VTWEEKLYCLEARED, dbo.TotalCleared.TotalCleared
FROM dbo.BOWEEKLYCLEARED INNER JOIN
dbo.CTWEEKLYCLEARED ON dbo.BOWEEKLYCLEARED.BOCLEAREDDATE = dbo.CTWEEKLYCLEARED.CTCLEAREDDATE INNER JOIN
dbo.MEWEEKLYCLEARED ON dbo.MEWEEKLYCLEARED.MECLEAREDDATE = dbo.BOWEEKLYCLEARED.BOCLEAREDDATE INNER JOIN
dbo.NHWEEKLYCLEARED ON dbo.NHWEEKLYCLEARED.NHCLEAREDDATE = dbo.CTWEEKLYCLEARED.CTCLEAREDDATE INNER JOIN
dbo.RIWEEKLYCLEARED ON dbo.RIWEEKLYCLEARED.RICLEAREDDATE = dbo.MEWEEKLYCLEARED.MECLEAREDDATE INNER JOIN
dbo.WOWEEKLYCLEARED ON dbo.WOWEEKLYCLEARED.WOCLEAREDDATE = dbo.NHWEEKLYCLEARED.NHCLEAREDDATE INNER JOIN
dbo.VTWEEKLYCLEARED ON dbo.VTWEEKLYCLEARED.VTCLEAREDDATE = dbo.RIWEEKLYCLEARED.RICLEAREDDATE INNER JOIN
dbo.TotalCleared ON dbo.TotalCleared.ClearedDate = dbo.WOWEEKLYCLEARED.WOCLEAREDDATE
If I run this script then I will give just column headers without displaying the data and without column header "ClearedDate" where the users know how many cases have each office been cleared on what date?
Then I insert "Group BY ClearedDate" at the bottom of the script. It gives me an error and that error is
Error Message: Column 'dbo.BOWEEKLYCLEARED.BOWEEKLYCLEARED' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I checked more than 10 times to see if I actually mispelled the word or not in the dbo.BOWEEKLYCLEARED and the column header name BOWEEKLYCLEARED, but I was spelled correctly.
So, I decide to try the different command FULL OUTER JOIN to see if it works or not.
Instead of using INNER JOIN, I replaced it by FULL OUTER JOIN or FULL JOIN. But without using Group BY, then it displays the data which I wish it to be. But it displays in an weird order (not in right order for example,
1
2
3
4
5
--
15
but it displays like this
3
2
1
15
4
5
Do you know how do I fix that?
and last question is:
If I insert the GROUP BY CLEAREDDATE at the bottom of my scripts then it give me the same error message as I mentioned above
Error Message:
Column 'dbo.BOWEEKLYCLEARED.BOWEEKLYCLEARED' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Would you please tell me how I can display the cleareddate, so the users know how many cases have each office been cleared on what date?
Thank You So Much
Very Respectful
Joe
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply