January 9, 2009 at 1:51 pm
Hello all,
I need your expertises in this query, and here is my question
If I have multiple column headers in a table such as
Office Code, DEClearedDate, SomaticMCClearedDate, PsycMCClearedDate, DESecondClearedDate, SomaticMCSecondClearedDate, PsycMCSecondClearedDate, DEThirdClearedDate, SomaticMCThirdClearedDate, DEFourthClearedDate, SomaticMCFourthClearedDate, and PsycMCFourthClearedDate.
the codes provided below are showing that I wish to add all data of these column headers EXCEPT "Office Code" Data. And all data of these column headers are in date format (mm/dd/yyyy) EXCEPT "Office Code" Data. "Office code" Data is in string format such as BO, NH, CT, VT......
below are the codes for adding all of these date format together in order to get TotalCleared and the date for each day showing how many cases have been cleared.
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 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 = b.SomaticMcClearedDate FULL OUTER JOIN
dbo.DESecondCleared AS d ON d.DESecondClearedDate = c.PsycMCClearedDate FULL OUTER JOIN
dbo.SomaticMCSecondCleared AS e ON e.SomaticMCSecondClearedDate = d.DESecondClearedDate FULL OUTER JOIN
dbo.PsycMCSecondCleared AS f ON f.PsycMCSecondClearedDate = e.SomaticMCSecondClearedDate FULL OUTER JOIN
dbo.DEThirdCleared AS g ON g.DEThirdClearedDate = f.PsycMCSecondClearedDate FULL OUTER JOIN
dbo.SomaticMCThirdCleared AS h ON h.SomaticMCThirdClearedDate = g.DEThirdClearedDate FULL OUTER JOIN
dbo.PsycMCThirdCleared AS i ON i.PsycMCThirdClearedDate = h.SomaticMCThirdClearedDate FULL OUTER JOIN
dbo.DEFourthCleared AS j ON j.DEFourthClearedDate = i.PsycMCThirdClearedDate FULL OUTER JOIN
dbo.SomaticMCFourthCleared AS k ON k.SomaticMCFourthClearedDate = j.DEFourthClearedDate FULL OUTER JOIN
dbo.PsycMCFourthCleared AS l ON l.PsycMCFourthClearedDate = k.SomaticMCFourthClearedDate)
SELECT SUM(TotalCleared) AS TotalCleared, ClearedDate
FROM (SELECT TotalCleared, CONVERT(varchar(10), ClearedDate, 101) AS ClearedDate
FROM CTE ) AS DATA
GROUP BY ClearedDate WITH ROLLUP
Now, I wish to know how many cases have each Office Code been cleared? instead of knowing the general cases were cleared but not knowing how many cases each office code have been cleared?
Office code is column headers in the same table with the other column headers and they are: BO, NH, CT, RI, etc....
Would you please help me how can I write a scripts and knowing the cases have been cleared by BO as an example.
Thank You Very Much
Very Respectful
January 9, 2009 at 2:41 pm
I think the using PIVOT command may be able to get you your results.
January 12, 2009 at 1:02 pm
Hello There,
how are you doing? I'm sorry that I'm new to SQL Server. Would you please show me how to use this Pivot Command for my example?
Thank You So Much for your precious helps.
Very Respectful
January 12, 2009 at 1:51 pm
Hello All Experts,
Would you all help me with this issue? please read my question. I'm new to SQL Server 2005.
Thank You So Much
Respect to all answers
Joe
January 12, 2009 at 2:12 pm
josephptran2002 (1/9/2009)
below are the codes for adding all of these date format together in order to get TotalCleared and the date for each day showing how many cases have been cleared.
Joe, does the query you posted do what you expect it to do? The reason I ask is because it's very unusual to see so many full outer joins in one query.
Please read the link in my sig below, it will guide you in how to post information to help others to help you. Posting your query is a minimum: it's very helpful to see a sample of the data output from your current query, and a sample of what you would like to see.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 12, 2009 at 2:14 pm
After re-reading the question, there may be an easier way to get what you need without a pivot table. in your CTE, include Office Code as one of the columns returned. Since I don't know what table office code is in i can't say exactly how to do it but this may help
WITH CTE AS
(SELECT table.Office_code,
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 = b.SomaticMcClearedDate FULL OUTER JOIN
dbo.DESecondCleared AS d ON d.DESecondClearedDate = c.PsycMCClearedDate FULL OUTER JOIN
dbo.SomaticMCSecondCleared AS e ON e.SomaticMCSecondClearedDate = d.DESecondClearedDate FULL OUTER JOIN
dbo.PsycMCSecondCleared AS f ON f.PsycMCSecondClearedDate = e.SomaticMCSecondClearedDate FULL OUTER JOIN
dbo.DEThirdCleared AS g ON g.DEThirdClearedDate = f.PsycMCSecondClearedDate FULL OUTER JOIN
dbo.SomaticMCThirdCleared AS h ON h.SomaticMCThirdClearedDate = g.DEThirdClearedDate FULL OUTER JOIN
dbo.PsycMCThirdCleared AS i ON i.PsycMCThirdClearedDate = h.SomaticMCThirdClearedDate FULL OUTER JOIN
dbo.DEFourthCleared AS j ON j.DEFourthClearedDate = i.PsycMCThirdClearedDate FULL OUTER JOIN
dbo.SomaticMCFourthCleared AS k ON k.SomaticMCFourthClearedDate = j.DEFourthClearedDate FULL OUTER JOIN
dbo.PsycMCFourthCleared AS l ON l.PsycMCFourthClearedDate = k.SomaticMCFourthClearedDate)
then when you are selecting from the CTE use , for example,table.Office_code = 'BO' as where clause or group by table.Office_code.
You may also want to take a look at this article. http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]. You may get more help as it would be easier for people to help.
January 12, 2009 at 2:38 pm
Hi there,
First, thank you for your helps.
OfficeCode is the column headers and it is in the same table with other column headers.
In the OfficeCode (header name), it contains these variables (BO, VT, ME,CT, RI, WO, NH). The codes that I wrote which only gives me Total Cases were cleared for all 7 offices. But not for each office, I wish to know how many cases have each office been cleared and it should add up to be equal to TotalCases of all 7 offices. For example, if the total cases were cleared of 7 offices are 7 cases. then each office may have 1 case and then 7 offices should add up to be equal to total cases of all offices.
Thank you so much
Very respectful
January 12, 2009 at 2:50 pm
Hello There,
First, thank you for your respond.
this is the outlook of my scripts:
TotalCleared ClearedDate
1 01/07/2009
2 01/08/2009
3 01/09/2009
5 01/11/2009
OfficeCode is the column headers and it is in the same table with other column headers.
In the OfficeCode (header name), it contains these variables (BO, VT, ME,CT, RI, WO, NH). The codes that I wrote which only gives me Total Cases were cleared for all 7 offices. But not for each office, I wish to know how many cases have each office been cleared and it should add up to be equal to TotalCases of all 7 offices. For example, if the total cases were cleared of 7 offices are 7 cases. then each office may have 1 case and then 7 offices should add up to be equal to total cases of all offices.
I wish to display the outlook likes below:
OfficeCode TotalCleared ClearedDate
BO 1 1/1/2009
CT 2 1/2/2009
NH 3 1/4/2009
ME 4 1/7/2009
VT 5 1/8/2009
RI 6 1/12/2009
WO 7 1/13/2009
The scripts that I wrote which gave me the total cases were cleared of all 7 offices.
and the outlook is displayed like below
TotalCleared ClearedDate
1 1/1/2009
2 1/2/2009
3 1/4/2009
4 1/7/2009
5 1/8/2009
6 1/12/2009
7 1/13/2009
I also wish to display the total cases were cleared by each office and the total cases combined of all 7 offices.
Would you please help me?
Thank you so much
Very Respectful
Thank you so much
Very respectful
January 12, 2009 at 3:36 pm
A quick example on how you can get you results. Just take the example and apply it to your query.
This code creates the test data
create table ocode(
officecodevarchar(2),
datedatetime,
numint)
insert into ocode
select 'bo',getdate(),1
union all
select 'bo',getdate(),1
union all
select 'nh',getdate(),1
union all
select 'bk',getdate(),1
union all
select 'nh',getdate(),1
union all
select 'bo',getdate()-2,1
union all
select 'bo',getdate()-2,1
union all
select 'nh',getdate()-2,1
union all
select 'bk',getdate()-2,1
union all
select 'nh',getdate()-2,1
this is what you have right now
with CTE as (select date,num from ocode)
select date,count(*) from cte
group by date
and this code simulates what you need to do
with CTE as (select officecode,date,num from ocode)
select officecode,date,count(*) from cte
group by officecode, date
notice in the second code block I added office code in the CTE as well as the statement that selects from the CTE and the group by clause.
January 13, 2009 at 7:09 am
Hello There,
How are you doing? Thank you very much for your helps, I have couple questions relate to your answers and that questions are:
1) Do I have to create a new table which is called ocode?
2) num int why do you have to have that variable in there?
3) When you insert into ocode the number 1 and -2,1, would you please explain to me what do these integers do?
4) Do I have to use CTE command?
5) Do the codes that you provided adding up all of the total cases of 7 offices?
I'm sorry to ask you dumb questions, because I'm new to this SQL.
Thank you so much
Very respectful
January 13, 2009 at 7:20 am
Hello again,
Sorry to ask you another question relates to Union all command, would you please explain for me the command below which is written by you?
Why you have to join 'BO' with 'BO' and 'BO' with 'NH' (I understand this one) but I don't understand why 'BK' (is this a make up variable by you) to join with NH?
Sorry, I'm in a learning stage but try to learn fast.
Thank you for your wonderful helps.
V/R
January 13, 2009 at 7:51 am
Hello There,
I wrote this codes and it works but there is one thing it's not showing what I would like to see and that is the total cases of all offices.
SELECT COUNT(DEClearedDate) AS DECleared, COUNT(OfficeCode) AS OfficeCode, CONVERT(varchar(16), DEClearedDate, 101) AS DEClearedDate
FROM dbo.ROCAPData
WHERE (DDS = 'BO') OR
(DDS = 'CT') OR
(DDS = 'ME') OR
(DDS = 'NH') OR
(DDS = 'RI') OR
(DDS = 'WO') OR
(DDS = 'VT')
GROUP BY DEClearedDate
The outlook for this scripts is:
DECleared(this is total cases) DDS DEClearedDate
1 1 1/1/2009
2 2 1/2/2009
3 3 1/5/2009
-------- ----
6 6
I wish to show the total cases for all offices are 6 cases.
Do you know how to do the total cases of all offices.
Thank you so much
V/R
January 13, 2009 at 7:57 am
What I posted is an example. It has nothing to do with your actual query except demonstrating what changes you would need to make in your own. Since you don't seem to be very knowledgeable in T-SQL, I would first suggest getting the basics down before tackling a problem such as this. Although not overly difficult, you don't seem to know about simple concepts as group by. In any case here are the answers to your questions:
1) Do I have to create a new table which is called ocode?
No, just an example. Create this in a test database and run the two statements below. Notice the difference in the statements. then apply the concept to your own query.
2) num int why do you have to have that variable in there?
just to have other data in table, no real reason
3) When you insert into ocode the number 1 and -2,1, would you please explain to me what do these integers do?
the 1 is just data, and -2 applies to the getdate function
4) Do I have to use CTE command?
your original query is using a CTE (Common Table Expression)
5) Do the codes that you provided adding up all of the total cases of 7 offices?
the code I provided is only an EXAMPLE
January 13, 2009 at 8:05 am
Hello There,
Sorry to bother you and ask you so much questions and these questions my confused you. I'm apologized for being confusing you. I'm so frustrated for codes, all I wish to know how can I display the total cases for all offices, when the users go to front end and they will enter the "STARTDATE", "ENDDATE" and "OFFICECODE" then the SQL Code will be able to display the reports for each office and all offices such as for example:
BO VT NH RI CT ME WO ALL
1 2 3 4 5 6 7 28 cases from 1/1/2009 to 1/2/2009
1/1/2009(StartDate: the users will input this date) to 1/2/2009 (EndDate: will input this date) to be able to pull out the report for this time frame.
Please help me all.
Thank you so much for all of your helps
V/R
January 13, 2009 at 8:06 am
This probably will not return the correct results based on original query you posted. You will get something that looks right but the data may be wrong.
Again look online for some T-SQL tutorials to help in your professional development.
SELECT COUNT(DEClearedDate) AS DECleared, OfficeCode, CONVERT(varchar(16), DEClearedDate, 101) AS DEClearedDate
FROM dbo.ROCAPData
WHERE (DDS = 'BO') OR
(DDS = 'CT') OR
(DDS = 'ME') OR
(DDS = 'NH') OR
(DDS = 'RI') OR
(DDS = 'WO') OR
(DDS = 'VT')
GROUP BY OfficeCode,DEClearedDate
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply