Problem with FULL JOIN

I need to write a stored proc for a report. Each line of the report will
have a description of the type of case and then the number of cases opened
during the time period for that type of case followed by the third column
which will be the number of cases closed during the time period for that type
of case. For example:
__________________________________________
1st Degree Murder 1 0
3rd Degree Murder 1 2
___________________________________________
To get at these data I need to got into our assignment table to find the
first date the case was assigned and then find out the type of cases it is.
I am doing that as a subquery that looks something like this:
SELECT zPASService.ServiceDescription,
COUNT(zPASService.ServiceDescription) AS OpenedCases
FROM [#FirstAssignedData] f INNER JOIN
[Case] c ON f.CaseID =
c.CaseID INNER JOIN
zPASService ON c.ServiceId
= zPASService.ServiceId
WHERE FirstAssignedDt > '5 / 1 / 04'
GROUP BY zPASService.ServiceDescription
ORDER BY zPASService.ServiceDescription FULL JOIN
(ServiceDescription is the code description for the case type. Right now I
have hardcoded the search for assignments to be any greater that 5/1/04).
Next I have to look to see if there are any cases with disposition dates
within the time period. Again I have hardcoded that test. That part comes
out to something like this:
SELECT
zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
DispositionedCases, OpenedCases
FROM [Case] c
INNER JOIN
zPASService ON c.ServiceId = zPASService.ServiceId
WHERE
DispositionDt > '8 / 1 / 05'
GROUP BY
zPASService.ServiceDescription
My question is how to bring these two results together? I am thinking I
want to do a FULL JOIN since I can't be sure that the case type that is in
either the assigned results or the dispositioned results is in the other case
type. If so, I have found examples on how to do a FULL JOIN for two or more
tables, but can't see how to do it when dealing with results from two
queries. Perhaps it is not a FULL JOIN I am looking for. I also looked at
UNION but since my columns are not the same (the first query returns the
number of cases assigned the second the number of cases dispositioned and the
report needs to get those two numbers seperately) I thougth I needed
something else.
Thanks...
- Steve
Thanks...
Steve,
It will be good if you also post some DDL, sample data and expected result.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Steve" wrote:

> I need to write a stored proc for a report. Each line of the report will
> have a description of the type of case and then the number of cases opened
> during the time period for that type of case followed by the third column
> which will be the number of cases closed during the time period for that type
> of case. For example:
> __________________________________________
> 1st Degree Murder 1 0
> 3rd Degree Murder 1 2
> ___________________________________________
> To get at these data I need to got into our assignment table to find the
> first date the case was assigned and then find out the type of cases it is.
> I am doing that as a subquery that looks something like this:
> ----
> SELECT zPASService.ServiceDescription,
> COUNT(zPASService.ServiceDescription) AS OpenedCases
> FROM [#FirstAssignedData] f INNER JOIN
> [Case] c ON f.CaseID =
> c.CaseID INNER JOIN
> zPASService ON c.ServiceId
> = zPASService.ServiceId
> WHERE FirstAssignedDt > '5 / 1 / 04'
> GROUP BY zPASService.ServiceDescription
> ORDER BY zPASService.ServiceDescription FULL JOIN
> ----
>
> (ServiceDescription is the code description for the case type. Right now I
> have hardcoded the search for assignments to be any greater that 5/1/04).
> Next I have to look to see if there are any cases with disposition dates
> within the time period. Again I have hardcoded that test. That part comes
> out to something like this:
> SELECT
> zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
> DispositionedCases, OpenedCases
> FROM [Case] c
> INNER JOIN
> zPASService ON c.ServiceId = zPASService.ServiceId
> WHERE
> DispositionDt > '8 / 1 / 05'
> GROUP BY
> zPASService.ServiceDescription
>
> My question is how to bring these two results together? I am thinking I
> want to do a FULL JOIN since I can't be sure that the case type that is in
> either the assigned results or the dispositioned results is in the other case
> type. If so, I have found examples on how to do a FULL JOIN for two or more
> tables, but can't see how to do it when dealing with results from two
> queries. Perhaps it is not a FULL JOIN I am looking for. I also looked at
> UNION but since my columns are not the same (the first query returns the
> number of cases assigned the second the number of cases dispositioned and the
> report needs to get those two numbers seperately) I thougth I needed
> something else.
> Thanks...
> - Steve
> Thanks...
>
|||Will do, but first let me ask a more basic question. Can you use a FULL JOIN
with the result of a query or does the subject of the JOIN have to be a
table? What I am need to do (or at least what I think I need to do) is to do
a FULL JOIN with the results of a GROUP BY so I have counts for my case types
with another GROUP BY that will have counts of cases assigned. I just wanted
to make sure that I am walking down the correct path. Let me know if you
need the DDL and sample data before you can even answer the question in this
post.
Thanks...
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Steve,
> It will be good if you also post some DDL, sample data and expected result.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "Steve" wrote:
|||A full join is a type of join. The requirements for its usage do not differ
(substantially) from any other type of join. Whether you need to use a full
join cannot be determined without a better understanding of the problem and
your proposed solution. With only a brief review of your initial post, I
doubt that a full join will help solve your problem.
To put your problem into the proper perspective, you need to think in terms
of sets of information and the relationships between the data that is used
to generate these sets. Your goal is to generate a report containing a
certain set of information (all types of cases) along with some related data
about each type. If you look at it from this perspective, you need
something that contains this basic set of information (all possible types of
cases). Does this exist somewhere? Can't tell without knowing your schema.
If it doesn't, then that is the first problem you must overcome. If it
does, then that information drives the query. Select the rows and then
figure out how to generate the other information. Here is a hint - try an
outer join to the case information and use aggregate functions. It is
likely that you will need to use the case expression. Timeperiods factor
into this problem somehow, but that aspect is not clear. It is likely that
you may also need something that contains the timeperiods of interest; in
this case a cross join **might** be useful.
Note that there are many ways to accomplish your goal; this is but a single
suggestion. Perhaps the best way to approach this is to concentrate on the
data that you do have and create a query that generates the desired
information using only inner joins. Obviously that will only include those
types of cases that have supporting data. That basic query can often be
modified to then generate the missing bits. Below is an example from
Northwind that should give you some ideas.
-- For each period and customer, get all orders ordered or shipped
select convert(char(12), periods.begindate, 102) as bdate,
convert(char(12), periods.enddate, 102) as edate,
cust.CustomerID, left(cust.CompanyName, 15) as cname,
convert(char(12), ord.OrderDate, 102) as orddate,
convert(char(12), ord.ShippedDate, 102) as shipdate
from Customers as cust
inner join Orders as ord
on cust.CustomerID = ord.CustomerID
inner join (select cast('19970601' as datetime) as begindate,
cast('19970630 23:59:59.997' as datetime) as enddate
union all
select '19970701', '19970731 23:59:59.997' ) as periods
on ord.OrderDate between periods.begindate and periods.enddate
or ord.ShippedDate between periods.begindate and periods.enddate
order by periods.begindate, cust.CompanyName, ord.OrderDate
-- For each period and customer, count the number of orders ordered
select convert(char(12), periods.begindate, 102) as bdate,
convert(char(12), periods.enddate, 102) as edate,
cust.CustomerID, left(cust.CompanyName, 15) as cname,
sum(case when ord.OrderDate between periods.begindate and
periods.enddate
then 1 else 0 end) as ordercnt
from Customers as cust
inner join Orders as ord
on cust.CustomerID = ord.CustomerID
inner join (select cast('19970601' as datetime) as begindate,
cast('19970630 23:59:59.997' as datetime) as enddate
union all
select '19970701', '19970731 23:59:59.997' ) as periods
on ord.OrderDate between periods.begindate and periods.enddate
or ord.ShippedDate between periods.begindate and periods.enddate
group by convert(char(12), periods.begindate, 102),
convert(char(12), periods.enddate, 102),
cust.CustomerID, cust.CompanyName
order by bdate, cname
|||On Mon, 10 Oct 2005 14:40:02 -0700, Steve wrote:

>Will do, but first let me ask a more basic question. Can you use a FULL JOIN
>with the result of a query or does the subject of the JOIN have to be a
>table?
(snip)
Hi Stevem
I didn't read all details in your post, so I don't know if it will help
in your case, but the answer to your basic question is that you can
always use a (non-corelated) subquery in place of a table. This is
called a derived table. Example of using tw derived tables with a FULL
OUTER JOIN:
SELECT d1.Col1, d1.Col2, d2.Col4
FROM (SELECT Col1, Col2, Col3
FROM Table1
WHERE Col4 = 4) AS d1
FULL OUTER JOIN
(SELECT Col3, Col4
FROM Table2
WHERE Col5 = 5) AS d2
ON d2.col3 = d1.col3
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks to all of you for helping me on this one. It is working now. Here is
what I came up with:
SELECT CASE WHEN RptOpenedCases.ServiceDescription IS NULL
THEN RptClosedCases.ServiceDescription ELSE
RptOpenedCases.ServiceDescription END AS 'RptServiceDescription',
RptOpenedCases.OpenedCases, RptClosedCases.CasesClosed
FROM (SELECT zPASService.ServiceDescription,
COUNT(zPASService.ServiceDescription) AS OpenedCases
FROM [Case] AS c INNER JOIN
zPASService ON c.ServiceId =
zPASService.ServiceId INNER JOIN
(SELECT CaseID,
MIN(StartDt) AS FirstAssignedDt
FROM AttyAssign
GROUP BY CaseID) AS
FirstAssignment ON c.CaseID = FirstAssignment.CaseID
WHERE (FirstAssignment.FirstAssignedDt > '5/1/05')
GROUP BY zPASService.ServiceDescription) AS
RptOpenedCases FULL OUTER JOIN
(SELECT zPASService_1.ServiceDescription,
COUNT(zPASService_1.ServiceDescription) AS CasesClosed
FROM [Case] AS c INNER JOIN
zPASService AS
zPASService_1 ON c.ServiceId = zPASService_1.ServiceId
WHERE (c.DispositionDt > '5/1/04')
GROUP BY zPASService_1.ServiceDescription) AS
RptClosedCases ON
RptOpenedCases.ServiceDescription =
RptClosedCases.ServiceDescription
ORDER BY RptServiceDescription
"Hugo Kornelis" wrote:

> On Mon, 10 Oct 2005 14:40:02 -0700, Steve wrote:
> (snip)
> Hi Stevem
> I didn't read all details in your post, so I don't know if it will help
> in your case, but the answer to your basic question is that you can
> always use a (non-corelated) subquery in place of a table. This is
> called a derived table. Example of using tw derived tables with a FULL
> OUTER JOIN:
> SELECT d1.Col1, d1.Col2, d2.Col4
> FROM (SELECT Col1, Col2, Col3
> FROM Table1
> WHERE Col4 = 4) AS d1
> FULL OUTER JOIN
> (SELECT Col3, Col4
> FROM Table2
> WHERE Col5 = 5) AS d2
> ON d2.col3 = d1.col3
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Problem with FULL JOIN

I need to write a stored proc for a report. Each line of the report will
have a description of the type of case and then the number of cases opened
during the time period for that type of case followed by the third column
which will be the number of cases closed during the time period for that typ
e
of case. For example:
________________________________________
__
1st degree Murder 1 0
3rd degree Murder 1 2
________________________________________
___
To get at these data I need to got into our assignment table to find the
first date the case was assigned and then find out the type of cases it is.
I am doing that as a subquery that looks something like this:
----
SELECT zPASService.ServiceDescription,
COUNT(zPASService.ServiceDescription) AS OpenedCases
FROM [#FirstAssignedData] f INNER JOIN
[Case] c ON f.CaseID =
c.CaseID INNER JOIN
zPASService ON c.ServiceId
= zPASService.ServiceId
WHERE FirstAssignedDt > '5 / 1 / 04'
GROUP BY zPASService.ServiceDescription
ORDER BY zPASService.ServiceDescription FULL JOIN
----
-
(ServiceDescription is the code description for the case type. Right now I
have hardcoded the search for assignments to be any greater that 5/1/04).
Next I have to look to see if there are any cases with disposition dates
within the time period. Again I have hardcoded that test. That part comes
out to something like this:
---
SELECT
zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
DispositionedCases, OpenedCases
FROM [Case] c
INNER JOIN
zPASService ON c.ServiceId = zPASService.ServiceId
WHERE
DispositionDt > '8 / 1 / 05'
GROUP BY
zPASService.ServiceDescription
---
My question is how to bring these two results together? I am thinking I
want to do a FULL JOIN since I can't be sure that the case type that is in
either the assigned results or the dispositioned results is in the other cas
e
type. If so, I have found examples on how to do a FULL JOIN for two or more
tables, but can't see how to do it when dealing with results from two
queries. Perhaps it is not a FULL JOIN I am looking for. I also looked at
UNION but since my columns are not the same (the first query returns the
number of cases assigned the second the number of cases dispositioned and th
e
report needs to get those two numbers seperately) I thougth I needed
something else.
Thanks...
- Steve
Thanks...Steve,
It will be good if you also post some DDL, sample data and expected result.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Steve" wrote:

> I need to write a stored proc for a report. Each line of the report will
> have a description of the type of case and then the number of cases opened
> during the time period for that type of case followed by the third column
> which will be the number of cases closed during the time period for that t
ype
> of case. For example:
> ________________________________________
__
> 1st degree Murder 1 0
> 3rd degree Murder 1 2
> ________________________________________
___
> To get at these data I need to got into our assignment table to find the
> first date the case was assigned and then find out the type of cases it is
.
> I am doing that as a subquery that looks something like this:
> ----
--
> SELECT zPASService.ServiceDescription,
> COUNT(zPASService.ServiceDescription) AS OpenedCases
> FROM [#FirstAssignedData] f INNER J
OIN
> [Case] c ON f.CaseID
=
> c.CaseID INNER JOIN
> zPASService ON c.Service
Id
> = zPASService.ServiceId
> WHERE FirstAssignedDt > '5 / 1 / 04'
> GROUP BY zPASService.ServiceDescription
> ORDER BY zPASService.ServiceDescription FULL JO
IN
> ----
--
>
> (ServiceDescription is the code description for the case type. Right now
I
> have hardcoded the search for assignments to be any greater that 5/1/04).
> Next I have to look to see if there are any cases with disposition dates
> within the time period. Again I have hardcoded that test. That part come
s
> out to something like this:
> ---
> SELECT
> zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
> DispositionedCases, OpenedCases
> FROM [C
ase] c
> INNER JOIN
> zPASService ON c.ServiceId = zPASService.ServiceId
> WHERE
> DispositionDt > '8 / 1 / 05'
> GROUP BY
> zPASService.ServiceDescription
> ---
> My question is how to bring these two results together? I am thinking I
> want to do a FULL JOIN since I can't be sure that the case type that is in
> either the assigned results or the dispositioned results is in the other c
ase
> type. If so, I have found examples on how to do a FULL JOIN for two or mo
re
> tables, but can't see how to do it when dealing with results from two
> queries. Perhaps it is not a FULL JOIN I am looking for. I also looked a
t
> UNION but since my columns are not the same (the first query returns the
> number of cases assigned the second the number of cases dispositioned and
the
> report needs to get those two numbers seperately) I thougth I needed
> something else.
> Thanks...
> - Steve
> Thanks...
>|||Will do, but first let me ask a more basic question. Can you use a FULL JOI
N
with the result of a query or does the subject of the JOIN have to be a
table? What I am need to do (or at least what I think I need to do) is to d
o
a FULL JOIN with the results of a GROUP BY so I have counts for my case type
s
with another GROUP BY that will have counts of cases assigned. I just wante
d
to make sure that I am walking down the correct path. Let me know if you
need the DDL and sample data before you can even answer the question in this
post.
Thanks...
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Steve,
> It will be good if you also post some DDL, sample data and expected result
.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "Steve" wrote:
>|||A full join is a type of join. The requirements for its usage do not differ
(substantially) from any other type of join. Whether you need to use a full
join cannot be determined without a better understanding of the problem and
your proposed solution. With only a brief review of your initial post, I
doubt that a full join will help solve your problem.
To put your problem into the proper perspective, you need to think in terms
of sets of information and the relationships between the data that is used
to generate these sets. Your goal is to generate a report containing a
certain set of information (all types of cases) along with some related data
about each type. If you look at it from this perspective, you need
something that contains this basic set of information (all possible types of
cases). Does this exist somewhere? Can't tell without knowing your schema.
If it doesn't, then that is the first problem you must overcome. If it
does, then that information drives the query. Select the rows and then
figure out how to generate the other information. Here is a hint - try an
outer join to the case information and use aggregate functions. It is
likely that you will need to use the case expression. Timeperiods factor
into this problem somehow, but that aspect is not clear. It is likely that
you may also need something that contains the timeperiods of interest; in
this case a cross join **might** be useful.
Note that there are many ways to accomplish your goal; this is but a single
suggestion. Perhaps the best way to approach this is to concentrate on the
data that you do have and create a query that generates the desired
information using only inner joins. Obviously that will only include those
types of cases that have supporting data. That basic query can often be
modified to then generate the missing bits. Below is an example from
Northwind that should give you some ideas.
-- For each period and customer, get all orders ordered or shipped
select convert(char(12), periods.begindate, 102) as bdate,
convert(char(12), periods.enddate, 102) as edate,
cust.CustomerID, left(cust.CompanyName, 15) as cname,
convert(char(12), ord.OrderDate, 102) as orddate,
convert(char(12), ord.ShippedDate, 102) as shipdate
from Customers as cust
inner join Orders as ord
on cust.CustomerID = ord.CustomerID
inner join (select cast('19970601' as datetime) as begindate,
cast('19970630 23:59:59.997' as datetime) as enddate
union all
select '19970701', '19970731 23:59:59.997' ) as periods
on ord.OrderDate between periods.begindate and periods.enddate
or ord.ShippedDate between periods.begindate and periods.enddate
order by periods.begindate, cust.CompanyName, ord.OrderDate
-- For each period and customer, count the number of orders ordered
select convert(char(12), periods.begindate, 102) as bdate,
convert(char(12), periods.enddate, 102) as edate,
cust.CustomerID, left(cust.CompanyName, 15) as cname,
sum(case when ord.OrderDate between periods.begindate and
periods.enddate
then 1 else 0 end) as ordercnt
from Customers as cust
inner join Orders as ord
on cust.CustomerID = ord.CustomerID
inner join (select cast('19970601' as datetime) as begindate,
cast('19970630 23:59:59.997' as datetime) as enddate
union all
select '19970701', '19970731 23:59:59.997' ) as periods
on ord.OrderDate between periods.begindate and periods.enddate
or ord.ShippedDate between periods.begindate and periods.enddate
group by convert(char(12), periods.begindate, 102),
convert(char(12), periods.enddate, 102),
cust.CustomerID, cust.CompanyName
order by bdate, cname|||On Mon, 10 Oct 2005 14:40:02 -0700, Steve wrote:

>Will do, but first let me ask a more basic question. Can you use a FULL JO
IN
>with the result of a query or does the subject of the JOIN have to be a
>table?
(snip)
Hi Stevem
I didn't read all details in your post, so I don't know if it will help
in your case, but the answer to your basic question is that you can
always use a (non-corelated) subquery in place of a table. This is
called a derived table. Example of using tw derived tables with a FULL
OUTER JOIN:
SELECT d1.Col1, d1.Col2, d2.Col4
FROM (SELECT Col1, Col2, Col3
FROM Table1
WHERE Col4 = 4) AS d1
FULL OUTER JOIN
(SELECT Col3, Col4
FROM Table2
WHERE Col5 = 5) AS d2
ON d2.col3 = d1.col3
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to all of you for helping me on this one. It is working now. Here i
s
what I came up with:
SELECT CASE WHEN RptOpenedCases.ServiceDescription IS NULL
THEN RptClosedCases.ServiceDescription ELSE
RptOpenedCases.ServiceDescription END AS 'RptServiceDescription',
RptOpenedCases.OpenedCases, RptClosedCases.CasesClosed
FROM (SELECT zPASService.ServiceDescription,
COUNT(zPASService.ServiceDescription) AS OpenedCases
FROM [Case] AS c INNER JOIN
zPASService ON c.ServiceId =
zPASService.ServiceId INNER JOIN
(SELECT CaseID,
MIN(StartDt) AS FirstAssignedDt
FROM AttyAssign
GROUP BY CaseID) AS
FirstAssignment ON c.CaseID = FirstAssignment.CaseID
WHERE (FirstAssignment.FirstAssignedDt > '5/1/05')
GROUP BY zPASService.ServiceDescription) AS
RptOpenedCases FULL OUTER JOIN
(SELECT zPASService_1.ServiceDescription,
COUNT(zPASService_1.ServiceDescription) AS CasesClosed
FROM [Case] AS c INNER JOIN
zPASService AS
zPASService_1 ON c.ServiceId = zPASService_1.ServiceId
WHERE (c.DispositionDt > '5/1/04')
GROUP BY zPASService_1.ServiceDescription) AS
RptClosedCases ON
RptOpenedCases.ServiceDescription =
RptClosedCases.ServiceDescription
ORDER BY RptServiceDescription
"Hugo Kornelis" wrote:

> On Mon, 10 Oct 2005 14:40:02 -0700, Steve wrote:
>
> (snip)
> Hi Stevem
> I didn't read all details in your post, so I don't know if it will help
> in your case, but the answer to your basic question is that you can
> always use a (non-corelated) subquery in place of a table. This is
> called a derived table. Example of using tw derived tables with a FULL
> OUTER JOIN:
> SELECT d1.Col1, d1.Col2, d2.Col4
> FROM (SELECT Col1, Col2, Col3
> FROM Table1
> WHERE Col4 = 4) AS d1
> FULL OUTER JOIN
> (SELECT Col3, Col4
> FROM Table2
> WHERE Col5 = 5) AS d2
> ON d2.col3 = d1.col3
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Problem with FULL JOIN

I need to write a stored proc for a report. Each line of the report will
have a description of the type of case and then the number of cases opened
during the time period for that type of case followed by the third column
which will be the number of cases closed during the time period for that type
of case. For example:
__________________________________________
1st Degree Murder 1 0
3rd Degree Murder 1 2
___________________________________________
To get at these data I need to got into our assignment table to find the
first date the case was assigned and then find out the type of cases it is.
I am doing that as a subquery that looks something like this:
----
SELECT zPASService.ServiceDescription,
COUNT(zPASService.ServiceDescription) AS OpenedCases
FROM [#FirstAssignedData] f INNER JOIN
[Case] c ON f.CaseID = c.CaseID INNER JOIN
zPASService ON c.ServiceId
= zPASService.ServiceId
WHERE FirstAssignedDt > '5 / 1 / 04'
GROUP BY zPASService.ServiceDescription
ORDER BY zPASService.ServiceDescription FULL JOIN
----
(ServiceDescription is the code description for the case type. Right now I
have hardcoded the search for assignments to be any greater that 5/1/04).
Next I have to look to see if there are any cases with disposition dates
within the time period. Again I have hardcoded that test. That part comes
out to something like this:
---
SELECT
zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
DispositionedCases, OpenedCases
FROM [Case] c
INNER JOIN
zPASService ON c.ServiceId = zPASService.ServiceId
WHERE
DispositionDt > '8 / 1 / 05'
GROUP BY
zPASService.ServiceDescription
---
My question is how to bring these two results together? I am thinking I
want to do a FULL JOIN since I can't be sure that the case type that is in
either the assigned results or the dispositioned results is in the other case
type. If so, I have found examples on how to do a FULL JOIN for two or more
tables, but can't see how to do it when dealing with results from two
queries. Perhaps it is not a FULL JOIN I am looking for. I also looked at
UNION but since my columns are not the same (the first query returns the
number of cases assigned the second the number of cases dispositioned and the
report needs to get those two numbers seperately) I thougth I needed
something else.
Thanks...
- Steve
Thanks...Steve,
It will be good if you also post some DDL, sample data and expected result.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Steve" wrote:
> I need to write a stored proc for a report. Each line of the report will
> have a description of the type of case and then the number of cases opened
> during the time period for that type of case followed by the third column
> which will be the number of cases closed during the time period for that type
> of case. For example:
> __________________________________________
> 1st Degree Murder 1 0
> 3rd Degree Murder 1 2
> ___________________________________________
> To get at these data I need to got into our assignment table to find the
> first date the case was assigned and then find out the type of cases it is.
> I am doing that as a subquery that looks something like this:
> ----
> SELECT zPASService.ServiceDescription,
> COUNT(zPASService.ServiceDescription) AS OpenedCases
> FROM [#FirstAssignedData] f INNER JOIN
> [Case] c ON f.CaseID => c.CaseID INNER JOIN
> zPASService ON c.ServiceId
> = zPASService.ServiceId
> WHERE FirstAssignedDt > '5 / 1 / 04'
> GROUP BY zPASService.ServiceDescription
> ORDER BY zPASService.ServiceDescription FULL JOIN
> ----
>
> (ServiceDescription is the code description for the case type. Right now I
> have hardcoded the search for assignments to be any greater that 5/1/04).
> Next I have to look to see if there are any cases with disposition dates
> within the time period. Again I have hardcoded that test. That part comes
> out to something like this:
> ---
> SELECT
> zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
> DispositionedCases, OpenedCases
> FROM [Case] c
> INNER JOIN
> zPASService ON c.ServiceId = zPASService.ServiceId
> WHERE
> DispositionDt > '8 / 1 / 05'
> GROUP BY
> zPASService.ServiceDescription
> ---
> My question is how to bring these two results together? I am thinking I
> want to do a FULL JOIN since I can't be sure that the case type that is in
> either the assigned results or the dispositioned results is in the other case
> type. If so, I have found examples on how to do a FULL JOIN for two or more
> tables, but can't see how to do it when dealing with results from two
> queries. Perhaps it is not a FULL JOIN I am looking for. I also looked at
> UNION but since my columns are not the same (the first query returns the
> number of cases assigned the second the number of cases dispositioned and the
> report needs to get those two numbers seperately) I thougth I needed
> something else.
> Thanks...
> - Steve
> Thanks...
>|||Will do, but first let me ask a more basic question. Can you use a FULL JOIN
with the result of a query or does the subject of the JOIN have to be a
table? What I am need to do (or at least what I think I need to do) is to do
a FULL JOIN with the results of a GROUP BY so I have counts for my case types
with another GROUP BY that will have counts of cases assigned. I just wanted
to make sure that I am walking down the correct path. Let me know if you
need the DDL and sample data before you can even answer the question in this
post.
Thanks...
"Alejandro Mesa" wrote:
> Steve,
> It will be good if you also post some DDL, sample data and expected result.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "Steve" wrote:
> > I need to write a stored proc for a report. Each line of the report will
> > have a description of the type of case and then the number of cases opened
> > during the time period for that type of case followed by the third column
> > which will be the number of cases closed during the time period for that type
> > of case. For example:
> > __________________________________________
> > 1st Degree Murder 1 0
> > 3rd Degree Murder 1 2
> > ___________________________________________
> >
> > To get at these data I need to got into our assignment table to find the
> > first date the case was assigned and then find out the type of cases it is.
> > I am doing that as a subquery that looks something like this:
> > ----
> > SELECT zPASService.ServiceDescription,
> > COUNT(zPASService.ServiceDescription) AS OpenedCases
> > FROM [#FirstAssignedData] f INNER JOIN
> > [Case] c ON f.CaseID => > c.CaseID INNER JOIN
> > zPASService ON c.ServiceId
> > = zPASService.ServiceId
> > WHERE FirstAssignedDt > '5 / 1 / 04'
> > GROUP BY zPASService.ServiceDescription
> > ORDER BY zPASService.ServiceDescription FULL JOIN
> > ----
> >
> >
> > (ServiceDescription is the code description for the case type. Right now I
> > have hardcoded the search for assignments to be any greater that 5/1/04).
> >
> > Next I have to look to see if there are any cases with disposition dates
> > within the time period. Again I have hardcoded that test. That part comes
> > out to something like this:
> > ---
> > SELECT
> > zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
> > DispositionedCases, OpenedCases
> > FROM [Case] c
> > INNER JOIN
> >
> > zPASService ON c.ServiceId = zPASService.ServiceId
> > WHERE
> > DispositionDt > '8 / 1 / 05'
> > GROUP BY
> > zPASService.ServiceDescription
> >
> > ---
> >
> > My question is how to bring these two results together? I am thinking I
> > want to do a FULL JOIN since I can't be sure that the case type that is in
> > either the assigned results or the dispositioned results is in the other case
> > type. If so, I have found examples on how to do a FULL JOIN for two or more
> > tables, but can't see how to do it when dealing with results from two
> > queries. Perhaps it is not a FULL JOIN I am looking for. I also looked at
> > UNION but since my columns are not the same (the first query returns the
> > number of cases assigned the second the number of cases dispositioned and the
> > report needs to get those two numbers seperately) I thougth I needed
> > something else.
> >
> > Thanks...
> > - Steve
> >
> > Thanks...
> >|||A full join is a type of join. The requirements for its usage do not differ
(substantially) from any other type of join. Whether you need to use a full
join cannot be determined without a better understanding of the problem and
your proposed solution. With only a brief review of your initial post, I
doubt that a full join will help solve your problem.
To put your problem into the proper perspective, you need to think in terms
of sets of information and the relationships between the data that is used
to generate these sets. Your goal is to generate a report containing a
certain set of information (all types of cases) along with some related data
about each type. If you look at it from this perspective, you need
something that contains this basic set of information (all possible types of
cases). Does this exist somewhere? Can't tell without knowing your schema.
If it doesn't, then that is the first problem you must overcome. If it
does, then that information drives the query. Select the rows and then
figure out how to generate the other information. Here is a hint - try an
outer join to the case information and use aggregate functions. It is
likely that you will need to use the case expression. Timeperiods factor
into this problem somehow, but that aspect is not clear. It is likely that
you may also need something that contains the timeperiods of interest; in
this case a cross join **might** be useful.
Note that there are many ways to accomplish your goal; this is but a single
suggestion. Perhaps the best way to approach this is to concentrate on the
data that you do have and create a query that generates the desired
information using only inner joins. Obviously that will only include those
types of cases that have supporting data. That basic query can often be
modified to then generate the missing bits. Below is an example from
Northwind that should give you some ideas.
-- For each period and customer, get all orders ordered or shipped
select convert(char(12), periods.begindate, 102) as bdate,
convert(char(12), periods.enddate, 102) as edate,
cust.CustomerID, left(cust.CompanyName, 15) as cname,
convert(char(12), ord.OrderDate, 102) as orddate,
convert(char(12), ord.ShippedDate, 102) as shipdate
from Customers as cust
inner join Orders as ord
on cust.CustomerID = ord.CustomerID
inner join (select cast('19970601' as datetime) as begindate,
cast('19970630 23:59:59.997' as datetime) as enddate
union all
select '19970701', '19970731 23:59:59.997' ) as periods
on ord.OrderDate between periods.begindate and periods.enddate
or ord.ShippedDate between periods.begindate and periods.enddate
order by periods.begindate, cust.CompanyName, ord.OrderDate
-- For each period and customer, count the number of orders ordered
select convert(char(12), periods.begindate, 102) as bdate,
convert(char(12), periods.enddate, 102) as edate,
cust.CustomerID, left(cust.CompanyName, 15) as cname,
sum(case when ord.OrderDate between periods.begindate and
periods.enddate
then 1 else 0 end) as ordercnt
from Customers as cust
inner join Orders as ord
on cust.CustomerID = ord.CustomerID
inner join (select cast('19970601' as datetime) as begindate,
cast('19970630 23:59:59.997' as datetime) as enddate
union all
select '19970701', '19970731 23:59:59.997' ) as periods
on ord.OrderDate between periods.begindate and periods.enddate
or ord.ShippedDate between periods.begindate and periods.enddate
group by convert(char(12), periods.begindate, 102),
convert(char(12), periods.enddate, 102),
cust.CustomerID, cust.CompanyName
order by bdate, cname|||On Mon, 10 Oct 2005 14:40:02 -0700, Steve wrote:
>Will do, but first let me ask a more basic question. Can you use a FULL JOIN
>with the result of a query or does the subject of the JOIN have to be a
>table?
(snip)
Hi Stevem
I didn't read all details in your post, so I don't know if it will help
in your case, but the answer to your basic question is that you can
always use a (non-corelated) subquery in place of a table. This is
called a derived table. Example of using tw derived tables with a FULL
OUTER JOIN:
SELECT d1.Col1, d1.Col2, d2.Col4
FROM (SELECT Col1, Col2, Col3
FROM Table1
WHERE Col4 = 4) AS d1
FULL OUTER JOIN
(SELECT Col3, Col4
FROM Table2
WHERE Col5 = 5) AS d2
ON d2.col3 = d1.col3
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to all of you for helping me on this one. It is working now. Here is
what I came up with:
SELECT CASE WHEN RptOpenedCases.ServiceDescription IS NULL
THEN RptClosedCases.ServiceDescription ELSE
RptOpenedCases.ServiceDescription END AS 'RptServiceDescription',
RptOpenedCases.OpenedCases, RptClosedCases.CasesClosed
FROM (SELECT zPASService.ServiceDescription,
COUNT(zPASService.ServiceDescription) AS OpenedCases
FROM [Case] AS c INNER JOIN
zPASService ON c.ServiceId =zPASService.ServiceId INNER JOIN
(SELECT CaseID,
MIN(StartDt) AS FirstAssignedDt
FROM AttyAssign
GROUP BY CaseID) AS
FirstAssignment ON c.CaseID = FirstAssignment.CaseID
WHERE (FirstAssignment.FirstAssignedDt > '5/1/05')
GROUP BY zPASService.ServiceDescription) AS
RptOpenedCases FULL OUTER JOIN
(SELECT zPASService_1.ServiceDescription,
COUNT(zPASService_1.ServiceDescription) AS CasesClosed
FROM [Case] AS c INNER JOIN
zPASService AS
zPASService_1 ON c.ServiceId = zPASService_1.ServiceId
WHERE (c.DispositionDt > '5/1/04')
GROUP BY zPASService_1.ServiceDescription) AS
RptClosedCases ON
RptOpenedCases.ServiceDescription =RptClosedCases.ServiceDescription
ORDER BY RptServiceDescription
"Hugo Kornelis" wrote:
> On Mon, 10 Oct 2005 14:40:02 -0700, Steve wrote:
> >Will do, but first let me ask a more basic question. Can you use a FULL JOIN
> >with the result of a query or does the subject of the JOIN have to be a
> >table?
> (snip)
> Hi Stevem
> I didn't read all details in your post, so I don't know if it will help
> in your case, but the answer to your basic question is that you can
> always use a (non-corelated) subquery in place of a table. This is
> called a derived table. Example of using tw derived tables with a FULL
> OUTER JOIN:
> SELECT d1.Col1, d1.Col2, d2.Col4
> FROM (SELECT Col1, Col2, Col3
> FROM Table1
> WHERE Col4 = 4) AS d1
> FULL OUTER JOIN
> (SELECT Col3, Col4
> FROM Table2
> WHERE Col5 = 5) AS d2
> ON d2.col3 = d1.col3
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>