problem with large number of inserts

We have an application that is connecting to our SQL 2k server remotely via
ODBC and sending a large number of individual insert statements. Apparently
this is causing problems with the transaction filling up on the sql database
the inserts are being sent to. To solve the problem, we set the transaction
log to be able to grow to 2GB. It quickly filled that space. Then we set it
to unlimited growth. Over time it grew to 12GB which began to cause other
problems. We backed up and ran DBCC Shrinkfile to bring it back under 1GB but
the remote app is not able to complete the inserts at that size. Truncate log
on checkpoint is selected in the database properties.
I found some KB articles which referred to problems doing large updates but
they had to do with using the 'where' statement and we are doing inserts with
no 'where' statement so it didn't seem to apply. Any ideas on how to solve
this problem?
Neither the Event Viewer nor the SQL Logs are offering much insight. No
errors are logged when the remote insert fails. However, the same database
appears to be failing to backup. During the weekly backup routine, we get
this in the Event Viewer:
"Could not clear 'DIFF' bitmap in database 'specialneeds' due to error 3617.
A subsequent backup operation may be slower/larger than normal."
We also get several variations of this message for both the Log and Database:
3041 :
BACKUP failed to complete the command BACKUP LOG [specialneeds] TO DISK =
N'E:\SQL Backup\specialneeds\specialneeds_db_200506190202.B AK', DISK =
N'E:\SQL Backup\specialneeds\specialneeds_Tlog.bak' WITH INIT , NOUNLOAD ,
NAME = N'specialneeds backup', NOSKIP , STATS = 10, NOFORMAT
We have 30 or so other databases, some larger some smaller, running on the
same SQL Server and none of them are having problems backing up. Thanks for
your help!
Hi
Run a DBCC CHECKDB on this database and see what it reports.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"myaffee" wrote:

> We have an application that is connecting to our SQL 2k server remotely via
> ODBC and sending a large number of individual insert statements. Apparently
> this is causing problems with the transaction filling up on the sql database
> the inserts are being sent to. To solve the problem, we set the transaction
> log to be able to grow to 2GB. It quickly filled that space. Then we set it
> to unlimited growth. Over time it grew to 12GB which began to cause other
> problems. We backed up and ran DBCC Shrinkfile to bring it back under 1GB but
> the remote app is not able to complete the inserts at that size. Truncate log
> on checkpoint is selected in the database properties.
> I found some KB articles which referred to problems doing large updates but
> they had to do with using the 'where' statement and we are doing inserts with
> no 'where' statement so it didn't seem to apply. Any ideas on how to solve
> this problem?
> Neither the Event Viewer nor the SQL Logs are offering much insight. No
> errors are logged when the remote insert fails. However, the same database
> appears to be failing to backup. During the weekly backup routine, we get
> this in the Event Viewer:
> "Could not clear 'DIFF' bitmap in database 'specialneeds' due to error 3617.
> A subsequent backup operation may be slower/larger than normal."
> We also get several variations of this message for both the Log and Database:
> 3041 :
> BACKUP failed to complete the command BACKUP LOG [specialneeds] TO DISK =
> N'E:\SQL Backup\specialneeds\specialneeds_db_200506190202.B AK', DISK =
> N'E:\SQL Backup\specialneeds\specialneeds_Tlog.bak' WITH INIT , NOUNLOAD ,
> NAME = N'specialneeds backup', NOSKIP , STATS = 10, NOFORMAT
> We have 30 or so other databases, some larger some smaller, running on the
> same SQL Server and none of them are having problems backing up. Thanks for
> your help!
>
|||Mike,
Here's what it reported:
DBCC results for 'specialneeds'.
DBCC results for 'sysobjects'.
There are 245 rows in 6 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 283 rows in 12 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 1167 rows in 25 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 220 rows in 19 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 18 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 13 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 571 rows in 4 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 2 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 1 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'leAuditCollectConfigVars'.
There are 3 rows in 1 pages for object 'leAuditCollectConfigVars'.
DBCC results for 'leAuditCollectDatabases'.
There are 59 rows in 1 pages for object 'leAuditCollectDatabases'.
DBCC results for 'leAuditCollectNotification'.
There are 1 rows in 1 pages for object 'leAuditCollectNotification'.
DBCC results for 'leAuditCollectAlerts'.
There are 5 rows in 1 pages for object 'leAuditCollectAlerts'.
DBCC results for 'affiliatelog'.
There are 0 rows in 1 pages for object 'affiliatelog'.
DBCC results for 'billing'.
There are 0 rows in 1 pages for object 'billing'.
DBCC results for 'configuration'.
There are 355 rows in 3 pages for object 'configuration'.
DBCC results for 'configuration1'.
There are 0 rows in 1 pages for object 'configuration1'.
DBCC results for 'coupons'.
There are 9 rows in 1 pages for object 'coupons'.
DBCC results for 'customerprices'.
There are 0 rows in 1 pages for object 'customerprices'.
DBCC results for 'customers'.
There are 1083 rows in 34 pages for object 'customers'.
DBCC results for 'downloads'.
There are 0 rows in 1 pages for object 'downloads'.
DBCC results for 'gifts'.
There are 0 rows in 1 pages for object 'gifts'.
DBCC results for 'hackers'.
There are 1 rows in 1 pages for object 'hackers'.
DBCC results for 'languages'.
There are 671 rows in 6 pages for object 'languages'.
DBCC results for 'mycompany'.
There are 0 rows in 1 pages for object 'mycompany'.
DBCC results for 'ordertracking'.
There are 0 rows in 1 pages for object 'ordertracking'.
DBCC results for 'pinnumbers'.
There are 0 rows in 1 pages for object 'pinnumbers'.
DBCC results for 'prodcategories'.
There are 2891 rows in 8 pages for object 'prodcategories'.
DBCC results for 'dtproperties'.
There are 0 rows in 1 pages for object 'dtproperties'.
DBCC results for 'prodfeatures'.
There are 0 rows in 1 pages for object 'prodfeatures'.
DBCC results for 'projects'.
There are 0 rows in 1 pages for object 'projects'.
DBCC results for 'quantitydiscounts'.
There are 0 rows in 1 pages for object 'quantitydiscounts'.
DBCC results for 'registrant'.
There are 0 rows in 1 pages for object 'registrant'.
DBCC results for 'registryitems'.
There are 0 rows in 1 pages for object 'registryitems'.
DBCC results for 'reviews'.
There are 0 rows in 1 pages for object 'reviews'.
DBCC results for 'savedcarts'.
There are 3 rows in 1 pages for object 'savedcarts'.
DBCC results for 'searchresults'.
There are 0 rows in 1 pages for object 'searchresults'.
DBCC results for 'shipmethods'.
There are 1 rows in 1 pages for object 'shipmethods'.
DBCC results for 'suppliers'.
There are 0 rows in 1 pages for object 'suppliers'.
DBCC results for 'tblaccess'.
There are 40 rows in 2 pages for object 'tblaccess'.
DBCC results for 'tbllog'.
There are 682 rows in 11 pages for object 'tbllog'.
DBCC results for 'orders'.
There are 997 rows in 71 pages for object 'orders'.
DBCC results for 'tbluser'.
There are 2 rows in 1 pages for object 'tbluser'.
DBCC results for 'templates'.
There are 0 rows in 1 pages for object 'templates'.
DBCC results for 'oitems'.
There are 2093 rows in 42 pages for object 'oitems'.
DBCC results for 'ipaddress'.
There are 8 rows in 1 pages for object 'ipaddress'.
DBCC results for 'privlabelproducts'.
There are 0 rows in 1 pages for object 'privlabelproducts'.
DBCC results for 'privatelabels'.
There are 1 rows in 1 pages for object 'privatelabels'.
DBCC results for 'privlabelproducts1'.
There are 32 rows in 3 pages for object 'privlabelproducts1'.
DBCC results for 'privatelabels1'.
There are 1 rows in 1 pages for object 'privatelabels1'.
DBCC results for 'privlabelproducts3'.
There are 0 rows in 1 pages for object 'privlabelproducts3'.
DBCC results for 'privatelabels3'.
There are 1 rows in 1 pages for object 'privatelabels3'.
DBCC results for 'categories'.
There are 39 rows in 1 pages for object 'categories'.
DBCC results for 'specialneeds.DIY_TEMPCOMMAND_TABLE'.
There are 0 rows in 0 pages for object 'specialneeds.DIY_TEMPCOMMAND_TABLE'.
DBCC results for 'spneeds_calendar'.
There are 10 rows in 1 pages for object 'spneeds_calendar'.
DBCC results for 'calendar'.
There are 13 rows in 1 pages for object 'calendar'.
DBCC results for 'spneeds_level'.
There are 12 rows in 1 pages for object 'spneeds_level'.
DBCC results for 'spneeds_author'.
There are 2892 rows in 18 pages for object 'spneeds_author'.
DBCC results for 'spneeds_subject'.
There are 39 rows in 1 pages for object 'spneeds_subject'.
DBCC results for 'spneeds_product'.
There are 2890 rows in 69 pages for object 'spneeds_product'.
DBCC results for 'disability_link_categories'.
There are 6 rows in 1 pages for object 'disability_link_categories'.
DBCC results for 'disability_link_images'.
There are 3 rows in 1 pages for object 'disability_link_images'.
DBCC results for 'disability_links'.
There are 16 rows in 1 pages for object 'disability_links'.
DBCC results for 'discount_codes'.
There are 1 rows in 1 pages for object 'discount_codes'.
DBCC results for 'spneeds_messages'.
There are 1 rows in 1 pages for object 'spneeds_messages'.
DBCC results for 'EmailContest'.
There are 434 rows in 8 pages for object 'EmailContest'.
DBCC results for 'products3'.
There are 3251 rows in 144 pages for object 'products3'.
DBCC results for 'products1'.
There are 3258 rows in 145 pages for object 'products1'.
DBCC results for 'products'.
There are 3258 rows in 151 pages for object 'products'.
DBCC results for 'affiliates'.
There are 1 rows in 1 pages for object 'affiliates'.
CHECKDB found 0 allocation errors and 0 consistency errors in database
'specialneeds'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Run a DBCC CHECKDB on this database and see what it reports.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "myaffee" wrote:

problem with large number of inserts

We have an application that is connecting to our SQL 2k server remotely via
ODBC and sending a large number of individual insert statements. Apparently
this is causing problems with the transaction filling up on the sql database
the inserts are being sent to. To solve the problem, we set the transaction
log to be able to grow to 2GB. It quickly filled that space. Then we set it
to unlimited growth. Over time it grew to 12GB which began to cause other
problems. We backed up and ran DBCC Shrinkfile to bring it back under 1GB bu
t
the remote app is not able to complete the inserts at that size. Truncate lo
g
on checkpoint is selected in the database properties.
I found some KB articles which referred to problems doing large updates but
they had to do with using the 'where' statement and we are doing inserts wit
h
no 'where' statement so it didn't seem to apply. Any ideas on how to solve
this problem?
Neither the Event Viewer nor the SQL Logs are offering much insight. No
errors are logged when the remote insert fails. However, the same database
appears to be failing to backup. During the weekly backup routine, we get
this in the Event Viewer:
"Could not clear 'DIFF' bitmap in database 'specialneeds' due to error 3617.
A subsequent backup operation may be slower/larger than normal."
We also get several variations of this message for both the Log and Database
:
3041 :
BACKUP failed to complete the command BACKUP LOG [specialneeds] TO DISK
=
N'E:\SQL Backup\specialneeds\specialneeds_db_2005
06190202.BAK', DISK =
N'E:\SQL Backup\specialneeds\specialneeds_Tlog.bak' WITH INIT , NOUNLOAD ,
NAME = N'specialneeds backup', NOSKIP , STATS = 10, NOFORMAT
We have 30 or so other databases, some larger some smaller, running on the
same SQL Server and none of them are having problems backing up. Thanks for
your help!Hi
Run a DBCC CHECKDB on this database and see what it reports.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"myaffee" wrote:

> We have an application that is connecting to our SQL 2k server remotely vi
a
> ODBC and sending a large number of individual insert statements. Apparentl
y
> this is causing problems with the transaction filling up on the sql databa
se
> the inserts are being sent to. To solve the problem, we set the transactio
n
> log to be able to grow to 2GB. It quickly filled that space. Then we set i
t
> to unlimited growth. Over time it grew to 12GB which began to cause other
> problems. We backed up and ran DBCC Shrinkfile to bring it back under 1GB
but
> the remote app is not able to complete the inserts at that size. Truncate
log
> on checkpoint is selected in the database properties.
> I found some KB articles which referred to problems doing large updates bu
t
> they had to do with using the 'where' statement and we are doing inserts w
ith
> no 'where' statement so it didn't seem to apply. Any ideas on how to solve
> this problem?
> Neither the Event Viewer nor the SQL Logs are offering much insight. No
> errors are logged when the remote insert fails. However, the same database
> appears to be failing to backup. During the weekly backup routine, we get
> this in the Event Viewer:
> "Could not clear 'DIFF' bitmap in database 'specialneeds' due to error 361
7.
> A subsequent backup operation may be slower/larger than normal."
> We also get several variations of this message for both the Log and Databa
se:
> 3041 :
> BACKUP failed to complete the command BACKUP LOG [specialneeds] TO DI
SK =
> N'E:\SQL Backup\specialneeds\specialneeds_db_2005
06190202.BAK', DISK =
> N'E:\SQL Backup\specialneeds\specialneeds_Tlog.bak' WITH INIT , NOUNLOAD
,
> NAME = N'specialneeds backup', NOSKIP , STATS = 10, NOFORMAT
> We have 30 or so other databases, some larger some smaller, running on the
> same SQL Server and none of them are having problems backing up. Thanks fo
r
> your help!
>|||Mike,
Here's what it reported:
DBCC results for 'specialneeds'.
DBCC results for 'sysobjects'.
There are 245 rows in 6 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 283 rows in 12 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 1167 rows in 25 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 220 rows in 19 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 18 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 13 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 571 rows in 4 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 2 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 1 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'leAuditCollectConfigVars'.
There are 3 rows in 1 pages for object 'leAuditCollectConfigVars'.
DBCC results for 'leAuditCollectDatabases'.
There are 59 rows in 1 pages for object 'leAuditCollectDatabases'.
DBCC results for 'leAuditCollectNotification'.
There are 1 rows in 1 pages for object 'leAuditCollectNotification'.
DBCC results for 'leAuditCollectAlerts'.
There are 5 rows in 1 pages for object 'leAuditCollectAlerts'.
DBCC results for 'affiliatelog'.
There are 0 rows in 1 pages for object 'affiliatelog'.
DBCC results for 'billing'.
There are 0 rows in 1 pages for object 'billing'.
DBCC results for 'configuration'.
There are 355 rows in 3 pages for object 'configuration'.
DBCC results for 'configuration1'.
There are 0 rows in 1 pages for object 'configuration1'.
DBCC results for 'coupons'.
There are 9 rows in 1 pages for object 'coupons'.
DBCC results for 'customerprices'.
There are 0 rows in 1 pages for object 'customerprices'.
DBCC results for 'customers'.
There are 1083 rows in 34 pages for object 'customers'.
DBCC results for 'downloads'.
There are 0 rows in 1 pages for object 'downloads'.
DBCC results for 'gifts'.
There are 0 rows in 1 pages for object 'gifts'.
DBCC results for 'hackers'.
There are 1 rows in 1 pages for object 'hackers'.
DBCC results for 'languages'.
There are 671 rows in 6 pages for object 'languages'.
DBCC results for 'mycompany'.
There are 0 rows in 1 pages for object 'mycompany'.
DBCC results for 'ordertracking'.
There are 0 rows in 1 pages for object 'ordertracking'.
DBCC results for 'pinnumbers'.
There are 0 rows in 1 pages for object 'pinnumbers'.
DBCC results for 'prodcategories'.
There are 2891 rows in 8 pages for object 'prodcategories'.
DBCC results for 'dtproperties'.
There are 0 rows in 1 pages for object 'dtproperties'.
DBCC results for 'prodfeatures'.
There are 0 rows in 1 pages for object 'prodfeatures'.
DBCC results for 'projects'.
There are 0 rows in 1 pages for object 'projects'.
DBCC results for 'quantitydiscounts'.
There are 0 rows in 1 pages for object 'quantitydiscounts'.
DBCC results for 'registrant'.
There are 0 rows in 1 pages for object 'registrant'.
DBCC results for 'registryitems'.
There are 0 rows in 1 pages for object 'registryitems'.
DBCC results for 'reviews'.
There are 0 rows in 1 pages for object 'reviews'.
DBCC results for 'savedcarts'.
There are 3 rows in 1 pages for object 'savedcarts'.
DBCC results for 'searchresults'.
There are 0 rows in 1 pages for object 'searchresults'.
DBCC results for 'shipmethods'.
There are 1 rows in 1 pages for object 'shipmethods'.
DBCC results for 'suppliers'.
There are 0 rows in 1 pages for object 'suppliers'.
DBCC results for 'tblaccess'.
There are 40 rows in 2 pages for object 'tblaccess'.
DBCC results for 'tbllog'.
There are 682 rows in 11 pages for object 'tbllog'.
DBCC results for 'orders'.
There are 997 rows in 71 pages for object 'orders'.
DBCC results for 'tbluser'.
There are 2 rows in 1 pages for object 'tbluser'.
DBCC results for 'templates'.
There are 0 rows in 1 pages for object 'templates'.
DBCC results for 'oitems'.
There are 2093 rows in 42 pages for object 'oitems'.
DBCC results for 'ipaddress'.
There are 8 rows in 1 pages for object 'ipaddress'.
DBCC results for 'privlabelproducts'.
There are 0 rows in 1 pages for object 'privlabelproducts'.
DBCC results for 'privatelabels'.
There are 1 rows in 1 pages for object 'privatelabels'.
DBCC results for 'privlabelproducts1'.
There are 32 rows in 3 pages for object 'privlabelproducts1'.
DBCC results for 'privatelabels1'.
There are 1 rows in 1 pages for object 'privatelabels1'.
DBCC results for 'privlabelproducts3'.
There are 0 rows in 1 pages for object 'privlabelproducts3'.
DBCC results for 'privatelabels3'.
There are 1 rows in 1 pages for object 'privatelabels3'.
DBCC results for 'categories'.
There are 39 rows in 1 pages for object 'categories'.
DBCC results for 'specialneeds.DIY_TEMPCOMMAND_TABLE'.
There are 0 rows in 0 pages for object 'specialneeds.DIY_TEMPCOMMAND_TABLE'.
DBCC results for 'spneeds_calendar'.
There are 10 rows in 1 pages for object 'spneeds_calendar'.
DBCC results for 'calendar'.
There are 13 rows in 1 pages for object 'calendar'.
DBCC results for 'spneeds_level'.
There are 12 rows in 1 pages for object 'spneeds_level'.
DBCC results for 'spneeds_author'.
There are 2892 rows in 18 pages for object 'spneeds_author'.
DBCC results for 'spneeds_subject'.
There are 39 rows in 1 pages for object 'spneeds_subject'.
DBCC results for 'spneeds_product'.
There are 2890 rows in 69 pages for object 'spneeds_product'.
DBCC results for 'disability_link_categories'.
There are 6 rows in 1 pages for object 'disability_link_categories'.
DBCC results for 'disability_link_images'.
There are 3 rows in 1 pages for object 'disability_link_images'.
DBCC results for 'disability_links'.
There are 16 rows in 1 pages for object 'disability_links'.
DBCC results for 'discount_codes'.
There are 1 rows in 1 pages for object 'discount_codes'.
DBCC results for 'spneeds_messages'.
There are 1 rows in 1 pages for object 'spneeds_messages'.
DBCC results for 'EmailContest'.
There are 434 rows in 8 pages for object 'EmailContest'.
DBCC results for 'products3'.
There are 3251 rows in 144 pages for object 'products3'.
DBCC results for 'products1'.
There are 3258 rows in 145 pages for object 'products1'.
DBCC results for 'products'.
There are 3258 rows in 151 pages for object 'products'.
DBCC results for 'affiliates'.
There are 1 rows in 1 pages for object 'affiliates'.
CHECKDB found 0 allocation errors and 0 consistency errors in database
'specialneeds'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Run a DBCC CHECKDB on this database and see what it reports.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "myaffee" wrote:
>

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)
>

Problem with dtutil hanging

I am using a script to copy a number of packages (50+) from my desktop computer (file system) to a remote server (MSDB). The script works fine, but I can never get through the entire script without it hanging up. Some times only 3-4 packages get copied, sometimes a lot more. If I close the windows cmd window and restart at the point it hung, it continues, but usually hangs up again. If you repeat this 2 or 3 times, it eventually gets all of the packages copied. It does not always hang at the same point. I have tried doing the copies with "quiet" mode - overwriting the existing packages and I have tried first deleting the packages, then copying - doesn't make a difference. I have also tried moving the packages to a couple of different installations of SQL Server 2005 using the script - the remote one and the local one on my desktop - same thing on both.

If I have the script echo a message just after each copy, the message just after the last successful copy never appears (and I know the copy is successful, because I deleted all the packages first and have checked that the package is there after the script hangs).

The copy command I am using look like this:

dtutil /FILE %1 /Decrypt %3 /Encrypt SQL;%~n1;2;%3 /DestServer %4 /DestUser admincis /DestPassword %2 /quiet
ECHO Done deploying package %~n1

%1 is the file spec of the ".dtsx" file to be copied

%2 is the password of the user account on the server

%3 is the encryption password (same one used on both ends)

%4 is the name of the server being copied to

Any ideas?

Jane Gaby

One addition to my original post. I just tried this from a different computer - copied the .dtsx files over and the scripts and ran from there with no problem - no hangup. The computer I used was another server, not a desktop. Anyway it looks like the problem is perhaps with my desktop.

Jane Gaby

Problem with Dataset.Merge

Hello guyz,
I am using SQL server and I want to merge two datasets. I have two tables which are similar in structure-(same number of columns). I want to display these two tables in one datagrid. I have 5 rows in the first table and 3 on the other. So in the datagrid it should have 8 rows. Part of my code is below…

ConnectToDatabaseServer()
Dim querystr As String = ("Select username, password from PublicAccounts")
Dim da As SqlDataAdapter = new SqlDataAdapter(querystr, myConnection)
Dim ds1 As DataSet = new DataSet()
da.Fill(ds1, "firsttable")

querystr = ("Select username, password from EmployeeAccounts")
Dim da2 As SqlDataAdapter = new SqlDataAdapter(querystr, myConnection)
Dim ds2 As DataSet = new DataSet()
da2.Fill(ds2, "secondtable")

ds1.Merge(ds2,False, System.Data.MissingSchemaAction.Add)
MergeGrid.DataSource = ds1
MergeGrid.DataBind()
myConnection.Close()

This code only displays the contents of ds1 (5 rows).
My table structure are as follows:
PublicAccounts (username Char(50), password char(50), pid integer primary key)
EmployeeAccounts (username Char(50), password char(50), eid integer primary key)

I really would appreciate any help.
Thank you..couple of ideas. nothing jumps out at me immediately. try setting preservechanges to true (the second parameter of the ds1.Merge) just for grins.

also, try wrapping the merge in a try-catch block and see if any errors are being thrown. that might help narrow it down.

cs