problem with If Exist select

Have patience, I'm just a script kiddie. I'm trying to write a
vbsscript that queries a sql 2005 database to see if a record exists
and if so update some values and if it doesn't then insert an entry. On
the If Exists(Select * FROM HDW WHERE UserID = " & strID & ") line I'm
getting the following error:
Char: 11
Error: Syntax error
Code: 800a03ea
Can anyone tell me what I'm doing wrong.
Thanks.
Set objCon = CreateObject("adodb.connection")
objCon.Open("Driver={SQL Server}; Server=NDS-SQL2005; Database=User;
uid=something; pwd=something")
on error resume Next
If Exists(Select * FROM HDW WHERE UserID = " & strID & ")
strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC = '" &
strMac & "') AND (Processor = " & strProc & ") And (Memory =" & strMem
& ")"
Else
strSQL = "insert into HDW(UserID, IPAddress, MAC, Processor, Memory) "
& _
"values ('" & strID & "', '" & strIP & "', '" & strMac & "', " &
strProc & ", " & strMem & ")"
End If
if err.number <> 0 then
msgbox err.description
end if
on error resume next
objCon.Execute(strSQL)
if err.number <> 0 then
msgbox err.description
end if
objCon.Closemcgrew.michael@.gmail.com wrote:
> Have patience, I'm just a script kiddie. I'm trying to write a
> vbsscript that queries a sql 2005 database to see if a record exists
> and if so update some values and if it doesn't then insert an entry. On
> the If Exists(Select * FROM HDW WHERE UserID = " & strID & ") line I'm
> getting the following error:
> Char: 11
> Error: Syntax error
> Code: 800a03ea
> Can anyone tell me what I'm doing wrong.
> Thanks.
>
> Set objCon = CreateObject("adodb.connection")
> objCon.Open("Driver={SQL Server}; Server=NDS-SQL2005; Database=User;
> uid=something; pwd=something")
> on error resume Next
> If Exists(Select * FROM HDW WHERE UserID = " & strID & ")
> strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC = '" &
> strMac & "') AND (Processor = " & strProc & ") And (Memory =" & strMem
> & ")"
> Else
> strSQL = "insert into HDW(UserID, IPAddress, MAC, Processor, Memory) "
> & _
> "values ('" & strID & "', '" & strIP & "', '" & strMac & "', " &
> strProc & ", " & strMem & ")"
>
instead of using if exits (select...........)
u should open a recordset of this SQL query and check condition....
let say if Rst as recorset..
then ur statement should be like this
if Rst.eof then
strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC =
'" &
strMac & "') AND (Processor = " & strProc & ") And (Memory =" &
strMem
& ")"
else
....|||Better yet, put stored procedures in your database and call them from VB.
Pass criteria using the parameters objects. Dynamic SQL can get you into
all kinds of problems.
Also, you can include all of this logic in a single stored procedure and
make one call to the database, rather than having the VB app checking all of
this logic on the client. You need to look at the type of logic you need to
enforce and determine whether to do this on the client app or the database,
but it is worth considering.
SQL Injection and Parameters:
http://www.sqlservercentral.com/col...ectionpart1.asp
http://www.sqlservercentral.com/col...qlinjection.asp
Dynamic SQL in stored procedures:
http://www.sommarskog.se/dynamic_sql.html
"SQL-Star (Rajeev Shukla)" <dreams.alot@.gmail.com> wrote in message
news:1145465365.635163.158830@.e56g2000cwe.googlegroups.com...
> mcgrew.michael@.gmail.com wrote:
> instead of using if exits (select...........)
> u should open a recordset of this SQL query and check condition....
> let say if Rst as recorset..
> then ur statement should be like this
> if Rst.eof then
> strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC =
> '" &
> strMac & "') AND (Processor = " & strProc & ") And (Memory =" &
> strMem
> & ")"
> else
> ....
>

Problem with getdate function in optional parameters

Hi, I want to write a StoredProcedure with one optional input parameter of Date and when it is missing I want current date to be used.

I have written the following StoredProcedure, but getdate function doesn`t work. If I replace it with a constant date, it works.

ALTER PROCEDURE

[dbo].[LinksFees_Record]

@.Date

datetime=getdate

AS

INSERT INTOLinkSearchFees

(LinkID, Price, [Date])

SELECTIDASLinkID, SearchDayFeeASPrice, @.DateFROMLinksWHERE(SearchDayFee > 0)

RETURNWhen I call the StoredProcedure the following exception occur:Conversion failed when converting datetime from character string.

How can I fix it?

Hi!

Try this:

ALTER PROCEDURE

[dbo].[LinksFees_Record]
@.Date

datetime = NULL
AS

IF @.DATE IS NULL SET @.Date= getdate()
... rest of your procedure goes here ...

Now if the users passes no parameter then @.Date will be replaced by getdate() result. Beware that if the user passes a NULL it will also be replaced by GetDate() results.

Have a good day,

David

Problem with full text search

Hello!
First of all sorry for my poor english... I write from Italy...
I'm using full-text search with SQL Express 2005 and I can't undestand a
lot...
This is the situation:
- I'm using Italian word breaker;
- I've removed all words from my noise word list (noiseITA.txt)
- I've these entries in my thesaurus file (tsITA.xml):
<expansion>
_{REGGIO NELL'EMILIA}
_{REGGIO EMILIA}
_{REGGIO DI EMILIA}
</expansion>
- I have these field's value in my records:
id location
-- --
1REGGIO EMILIA
2REGGIO-EMILIA
3REGGIO NELL'EMILIA
4REGGIO DI EMILIA
Case 1 (without thesaurus)
SELECT id FROM table1 WHERE CONTAINS(location , '"REGGIO-EMILIA"')
Result set: 2
- Why not "REGGIO EMILIA" too?
Case 2 (without thesaurus)
SELECT id FROM table1 WHERE CONTAINS(location , '"REGGIO EMILIA"')
Result set: 1, 3
- Why "REGGIO NELL'EMILIA" too?
Case 3 (without thesaurus)
SELECT id FROM table1 WHERE CONTAINS(location , '"REGGIO NELL''EMILIA"')
Result set: 1, 3
- Why "REGGIO EMILIA" too?
Case 4 (without thesaurus)
SELECT id FROM table1 WHERE CONTAINS(location , '"REGGIO DI EMILIA"')
Result set: 4
Case 5 (with thesaurus)
SELECT id FROM table1 WHERE CONTAINS(location , 'FORMSOF
(THESAURUS,"REGGIO-EMILIA")',LANGUAGE 'Italian')
Result set: 2
- Why only "REGGIO-EMILIA" ?
Case 6 (with thesaurus)
SELECT id FROM table1 WHERE CONTAINS(location , 'FORMSOF (THESAURUS,"REGGIO
EMILIA")',LANGUAGE 'Italian')
SELECT id FROM table1 WHERE CONTAINS(location , 'FORMSOF (THESAURUS,"REGGIO
NELL''EMILIA")',LANGUAGE 'Italian')
SELECT id FROM table1 WHERE CONTAINS(location , 'FORMSOF (THESAURUS,"REGGIO
DI EMILIA")',LANGUAGE 'Italian')
Result set: 1, 3, 4
- Why not "REGGIO-EMILIA" too?
I can't understand... is the hyphen (-) a word breaker sign?
What shall I do to obtain all the four records when I search for each of the
locations above ?
It doesn't work fine if I add "REGGIO-EMILIA" in the thesaurus file... :-(
Thank you very much!!!!
Paola
YOUR PROBLEM IS THAT YOUR CONTENT IS ALL UPPERCASE. THE ITALIAN WORD
BREAKER WILL BREAK REGGIO-EMILIA AS ONE WORD. HOWEVER IT WILL BREAK
Reggio-Emilia AS TWO WORDS. HAVE A LOOK AT THIS:
create database italian1
go
use italian1
GO
create fulltext catalog italian as default
go
create table italian (id int not null identity constraint italianPK primary
key, location varchar(30))
GO
create fulltext index on italian(location language italian) key index
italianpk
GO
insert into italian (location) values('REGGIO EMILIA')
insert into italian (location) values('REGGIO-EMILIA')
insert into italian (location) values('REGGIOEMILIA')
insert into italian (location) values('REGGIO NELL''EMILIA')
insert into italian (location) values('REGGIO DI EMILIA')
insert into italian (location) values('Reggio Emilia')
insert into italian (location) values('Reggio-Emilia')
insert into italian (location) values('Reggioemilia')
insert into italian (location) values('Reggio Nell''Emilia')
insert into italian (location) values('Reggio nell''Emilia')
insert into italian (location) values('Reggio Di Emilia')
insert into italian (location) values('Reggio di Emilia')
GO
SELECT id FROM italian WHERE CONTAINS(location , '"REGGIO-EMILIA"')
Result set: 2
-- Why not "REGGIO EMILIA" too?
--all uppercase will consider the word as one token
SELECT id FROM italian WHERE CONTAINS(location , '"Reggio-Emilia"')
--returns, 1, 3, 5, 6, 7, 8, 9, & 10
Case 2 (without thesaurus)
SELECT id,location FROM italian WHERE CONTAINS(location , '"REGGIO EMILIA"')
Result set: 1, 3
-- Why "REGGIO NELL'EMILIA" too? --nell is a noise word and as such is
discarded in the search
SELECT id FROM italian WHERE CONTAINS(location , '"Reggio Emilia"')
--returns, 1, 3, 6, 7, & 10
Case 3 (without thesaurus)
SELECT id,location FROM italian WHERE CONTAINS(location , '"REGGIO
NELL''EMILIA"')
Result set: 1, 3
-- Why "REGGIO EMILIA" too? nella is a noise word and thrown away
Case 4 (without thesaurus)
SELECT id,location FROM italian WHERE CONTAINS(location , '"REGGIO DI
EMILIA"')
Result set: 4
Case 5 (with thesaurus)
SELECT id FROM italian WHERE CONTAINS(location , 'FORMSOF
(THESAURUS,"REGGIO-EMILIA")',LANGUAGE 'Italian')
Result set: 2
-- Why only "REGGIO-EMILIA" ? Cause its uppper case, try this
SELECT id FROM italian WHERE CONTAINS(location , 'FORMSOF
(THESAURUS,"Reggio-Emilia")',LANGUAGE 'Italian')
--1, 3, 5, 6, 7, 8, 9, & 10 are returned
Case 6 (with thesaurus)
SELECT id FROM italian WHERE CONTAINS(location , 'FORMSOF (THESAURUS,"REGGIO
EMILIA")',LANGUAGE 'Italian')
SELECT id FROM italian WHERE CONTAINS(location , 'FORMSOF (THESAURUS,"REGGIO
NELL''EMILIA")',LANGUAGE 'Italian')
-- I can't figure out case 6 however
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"paola" <paola@.discussions.microsoft.com> wrote in message
news:FF634D5E-B3B9-412A-89AC-CA4C10F1A364@.microsoft.com...
> Hello!
> First of all sorry for my poor english... I write from Italy...
> I'm using full-text search with SQL Express 2005 and I can't undestand a
> lot...
> This is the situation:
> - I'm using Italian word breaker;
> - I've removed all words from my noise word list (noiseITA.txt)
> - I've these entries in my thesaurus file (tsITA.xml):
> <expansion>
> _{REGGIO NELL'EMILIA}
> _{REGGIO EMILIA}
> _{REGGIO DI EMILIA}
> </expansion>
>
> - I have these field's value in my records:
> id location
> -- --
> 1 REGGIO EMILIA
> 2 REGGIO-EMILIA
> 3 REGGIO NELL'EMILIA
> 4 REGGIO DI EMILIA
>
> Case 1 (without thesaurus)
> SELECT id FROM table1 WHERE CONTAINS(location , '"REGGIO-EMILIA"')
> Result set: 2
> - Why not "REGGIO EMILIA" too?
> Case 2 (without thesaurus)
> SELECT id FROM table1 WHERE CONTAINS(location , '"REGGIO EMILIA"')
> Result set: 1, 3
> - Why "REGGIO NELL'EMILIA" too?
> Case 3 (without thesaurus)
> SELECT id FROM table1 WHERE CONTAINS(location , '"REGGIO NELL''EMILIA"')
> Result set: 1, 3
> - Why "REGGIO EMILIA" too?
> Case 4 (without thesaurus)
> SELECT id FROM table1 WHERE CONTAINS(location , '"REGGIO DI EMILIA"')
> Result set: 4
> Case 5 (with thesaurus)
> SELECT id FROM table1 WHERE CONTAINS(location , 'FORMSOF
> (THESAURUS,"REGGIO-EMILIA")',LANGUAGE 'Italian')
> Result set: 2
> - Why only "REGGIO-EMILIA" ?
> Case 6 (with thesaurus)
> SELECT id FROM table1 WHERE CONTAINS(location , 'FORMSOF
> (THESAURUS,"REGGIO
> EMILIA")',LANGUAGE 'Italian')
> SELECT id FROM table1 WHERE CONTAINS(location , 'FORMSOF
> (THESAURUS,"REGGIO
> NELL''EMILIA")',LANGUAGE 'Italian')
> SELECT id FROM table1 WHERE CONTAINS(location , 'FORMSOF
> (THESAURUS,"REGGIO
> DI EMILIA")',LANGUAGE 'Italian')
> Result set: 1, 3, 4
> - Why not "REGGIO-EMILIA" too?
>
> I can't understand... is the hyphen (-) a word breaker sign?
> What shall I do to obtain all the four records when I search for each of
> the
> locations above ?
> It doesn't work fine if I add "REGGIO-EMILIA" in the thesaurus file...
> :-(
> Thank you very much!!!!
> Paola
>
>
|||Ok, thanks a lot Hilary...
I'm doing some tests to undestand... and so... why the query:
SELECT * FROM italian WHERE CONTAINS(location ,
'"Reggio Emilia"',LANGUAGE 'Italian')
doesn't return "Reggio di Emilia" ?
And :
SELECT * FROM italian WHERE CONTAINS(location ,
'"Reggio di Emilia"',LANGUAGE 'Italian')
returns only:
REGGIO DI EMILIA
Reggio Di Emilia
Reggio di Emilia
"di" is a noise word, isn't it?...
And so I suppose that "di" is ignored in search string and in field value
too. Why not?
Thanks a lot...
Paola
"Hilary Cotter" wrote:

> YOUR PROBLEM IS THAT YOUR CONTENT IS ALL UPPERCASE. THE ITALIAN WORD
> BREAKER WILL BREAK REGGIO-EMILIA AS ONE WORD. HOWEVER IT WILL BREAK
> Reggio-Emilia AS TWO WORDS. HAVE A LOOK AT THIS:
> create database italian1
> go
> use italian1
> GO
> create fulltext catalog italian as default
> go
> create table italian (id int not null identity constraint italianPK primary
> key, location varchar(30))
> GO
> create fulltext index on italian(location language italian) key index
> italianpk
> GO
> insert into italian (location) values('REGGIO EMILIA')
> insert into italian (location) values('REGGIO-EMILIA')
> insert into italian (location) values('REGGIOEMILIA')
> insert into italian (location) values('REGGIO NELL''EMILIA')
> insert into italian (location) values('REGGIO DI EMILIA')
> insert into italian (location) values('Reggio Emilia')
> insert into italian (location) values('Reggio-Emilia')
> insert into italian (location) values('Reggioemilia')
> insert into italian (location) values('Reggio Nell''Emilia')
> insert into italian (location) values('Reggio nell''Emilia')
> insert into italian (location) values('Reggio Di Emilia')
> insert into italian (location) values('Reggio di Emilia')
> GO
> SELECT id FROM italian WHERE CONTAINS(location , '"REGGIO-EMILIA"')
> Result set: 2
> -- Why not "REGGIO EMILIA" too?
> --all uppercase will consider the word as one token
> SELECT id FROM italian WHERE CONTAINS(location , '"Reggio-Emilia"')
> --returns, 1, 3, 5, 6, 7, 8, 9, & 10
> Case 2 (without thesaurus)
> SELECT id,location FROM italian WHERE CONTAINS(location , '"REGGIO EMILIA"')
> Result set: 1, 3
> -- Why "REGGIO NELL'EMILIA" too? --nell is a noise word and as such is
> discarded in the search
> SELECT id FROM italian WHERE CONTAINS(location , '"Reggio Emilia"')
> --returns, 1, 3, 6, 7, & 10
> Case 3 (without thesaurus)
> SELECT id,location FROM italian WHERE CONTAINS(location , '"REGGIO
> NELL''EMILIA"')
> Result set: 1, 3
> -- Why "REGGIO EMILIA" too? nella is a noise word and thrown away
> Case 4 (without thesaurus)
> SELECT id,location FROM italian WHERE CONTAINS(location , '"REGGIO DI
> EMILIA"')
> Result set: 4
> Case 5 (with thesaurus)
> SELECT id FROM italian WHERE CONTAINS(location , 'FORMSOF
> (THESAURUS,"REGGIO-EMILIA")',LANGUAGE 'Italian')
> Result set: 2
> -- Why only "REGGIO-EMILIA" ? Cause its uppper case, try this
> SELECT id FROM italian WHERE CONTAINS(location , 'FORMSOF
> (THESAURUS,"Reggio-Emilia")',LANGUAGE 'Italian')
> --1, 3, 5, 6, 7, 8, 9, & 10 are returned
> Case 6 (with thesaurus)
> SELECT id FROM italian WHERE CONTAINS(location , 'FORMSOF (THESAURUS,"REGGIO
> EMILIA")',LANGUAGE 'Italian')
> SELECT id FROM italian WHERE CONTAINS(location , 'FORMSOF (THESAURUS,"REGGIO
> NELL''EMILIA")',LANGUAGE 'Italian')
> -- I can't figure out case 6 however
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "paola" <paola@.discussions.microsoft.com> wrote in message
> news:FF634D5E-B3B9-412A-89AC-CA4C10F1A364@.microsoft.com...
>
>
|||Because it knows there is something in-between. Try this
insert into italian (location) values('Reggio XX Emilia')
and now this
SELECT * FROM italian WHERE CONTAINS(location ,
'"Reggio di Emilia"',LANGUAGE 'Italian')
you will see the hit to
Reggio di Emilia
and
Reggio XX Emilia
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"paola" <paola@.discussions.microsoft.com> wrote in message
news:7319F1AA-1CFC-42BF-9A70-F81C38E70840@.microsoft.com...[vbcol=seagreen]
> Ok, thanks a lot Hilary...
> I'm doing some tests to undestand... and so... why the query:
> SELECT * FROM italian WHERE CONTAINS(location ,
> '"Reggio Emilia"',LANGUAGE 'Italian')
> doesn't return "Reggio di Emilia" ?
> And :
> SELECT * FROM italian WHERE CONTAINS(location ,
> '"Reggio di Emilia"',LANGUAGE 'Italian')
> returns only:
> REGGIO DI EMILIA
> Reggio Di Emilia
> Reggio di Emilia
> "di" is a noise word, isn't it?...
> And so I suppose that "di" is ignored in search string and in field value
> too. Why not?
> Thanks a lot...
> Paola
>
>
>
> "Hilary Cotter" 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 droping users, please help

Hi

I'm trying to write a script that will make sure the user accounts are set up correctly after de attaching a database moving it to another machine and reattaching it. The problem is the user example1 had a login Name of example1 but after I've copied it over and re attached it the user example1 has No user at all, throught EM all I have to do is Delete the Database user and recreate it correctly, but We need to do this as part on a script, However I don't seem to be able to do the same thing through SQL.

The way I was attempting to drop and recreate the user was like this:

Execute sp_dropuser example1
Execute sp_addlogin example1
Execute sp_adduser example1
Execute sp_addrolemember @.rolename = db_datareader, @.membername = example1
Execute sp_addrolemember @.rolename = db_datawriter, @.membername = example1

However it comes back with this error:

Server: Msg 15175, Level 16, State 1, Procedure sp_droplogin, Line 93
Login 'example1' is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login.

Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 56
The login 'example1' already exists.

Does any one now how I can get it too work like the EM version!

My eternal gratitude, NixiesYou need to exec sp_grantdbaccess first before you do exec sp_addrolemember.|||Originally posted by joejcheng
You need to exec sp_grantdbaccess first before you do exec sp_addrolemember.

Just doubled checked, dbaccess is fine and the sp_addrolemember went through with out any errors. My big problem is that I can't drop the user to recreate them. Any ideas?|||I had a similar problem a while back and was able to use the procedures from Microsoft to fix. Check out:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;298897.

Basically, you need to resynchronize the login SID's before you can perform any security edits. Hope this helps.

Problem with displaying image from DB

I am able to write an image to a SQL table. The problem I am having is understanding how to retrieve the image from the table AND display it on a page along with the other page content.

Does anyone have an example or an article to help me with this procedure? I have searched the discussion and read a few articles but they all talk about reading an image into a datagrid which isn't what I want.

Dazed & confused!!!If you can display the image in a datagrid, you can display it by itself too.

Try looking at:
http://authors.aspalliance.com/das/readimage.aspx

That has some code (VB.NET) to show images from a SQL database.

I would recommend reading the other articles linked to that page as well.

You'll probably find that datagrid image code you've already seen will have a hint to the answer.|||I think thats the original page where I got the code to write the image to a database.

As you can see from the link, when they retrieve the image, they either retrieve it in a datagarid or on a page by itself.

It must be a lot harder to pull it into a page with existing data than I can figure out. My hands are tied. All examples are limited to either datagrid examples or a page with only the image and nothing else...ugghhhh!

Problem with delete trigger

I am trying to write a delete trigger that fires when the user deletes the only record for a given entity in the table. In this situation I have an entity PMA which can have one or many uses. I want the trigger to fire if the user tries to delete the last use record for that PMA_NUM from the PMA_USE table.

Here is my code:

if (SELECT Count(pma_use.pma_num) FROM dbo.PMA_USE
inner join dbo.deleted as D1 on dbo.PMA_USE.PMA_NUM= d1.PMA_NUM) = 0
BEGIN
RAISERROR ('Cannot delete only Use for PMA!', 16, 1)
ROLLBACK TRANSACTION
END

I seem to be getting inconsistent results when I create the trigger and when I try to test it re the deleted table

Sometimes when I run the trigger script in Management Studio Express, it has trouble with my using dbo.deleted and sometimes it doesn't

When I try to test the trigger by using MSE to view the table and delete the last use record for a PMA, I get an invalid object cannot find 'dbo.deleted' or cannot find 'deleted'

What am I doing wrong?

Roberta

1 - Use just [deleted]

2 - you do not need to count in order to prove existence. Use EXISTS operator.

if exists(select * from dbo.PMA_USE as a innner join deleted as b on a.PMA_NUM = b.PMA_NUM)

BEGIN
RAISERROR ('Cannot delete only Use for PMA!', 16, 1)
ROLLBACK TRANSACTION
END

AMB

Problem with DateTime and strings in stored procedures

I'm keeping in the database a log of all the sessions for my
application. I'm trying to write a stored procedure that returns all
the sessions that; the login contains a certain string, loggedin after
a certain datetime and loggedout before another datetime. Any
combination of these parameters can be used and, if none, returns all
the log.

Below is the code I came up with but I'm having a "Syntax error
converting datetime from character string" exception. When not using
DateTime parameters everything works fine. Can you tell me how can I
avoid this exception? Thanks in advance...

ALTER PROCEDURE dbo.RetrieveAllSessionHistoryItemsContaining
(
@.Pattern Varchar(255),
@.From DateTime,
@.To DateTime
)
AS
DECLARE @.Query VARCHAR(500)
SET @.Query = 'SELECT * FROM SessionHistoryItems, Sessions WHERE
SessionHistoryItems.SessionId = Sessions.SessionId'

DECLARE @.conditions nvarchar(257)
SET @.conditions = '';

IF LEN(@.Pattern) > 0 BEGIN
SET @.conditions = @.conditions + ' Sessions.Login LIKE ''%' + @.Pattern
+ '%'''
END

IF @.From IS NOT NULL BEGIN
IF LEN(@.conditions) > 0 BEGIN
SET @.conditions = @.conditions + ' AND '
END
SET @.conditions = @.conditions + ' SessionHistoryItems.LoggedOutAt >=
' + @.From
END

IF @.To IS NOT NULL BEGIN
IF LEN(@.conditions) > 0 BEGIN
SET @.conditions = @.conditions + ' AND '
END
SET @.conditions = @.conditions + ' SessionHistoryItems.LoggedInAt <= '
+ @.To
END

IF LEN(@.conditions) > 0 BEGIN
EXEC(@.Query + ' AND ' + @.conditions)
END
ELSE BEGIN
EXEC(@.Query)
END
RETURN<antao@.iilab.com> wrote in message
news:1117467828.906603.299460@.g49g2000cwa.googlegr oups.com...
> I'm keeping in the database a log of all the sessions for my
> application. I'm trying to write a stored procedure that returns all
> the sessions that; the login contains a certain string, loggedin after
> a certain datetime and loggedout before another datetime. Any
> combination of these parameters can be used and, if none, returns all
> the log.
> Below is the code I came up with but I'm having a "Syntax error
> converting datetime from character string" exception. When not using
> DateTime parameters everything works fine. Can you tell me how can I
> avoid this exception? Thanks in advance...
> ALTER PROCEDURE dbo.RetrieveAllSessionHistoryItemsContaining
> (
> @.Pattern Varchar(255),
> @.From DateTime,
> @.To DateTime
> )
> AS
> DECLARE @.Query VARCHAR(500)
> SET @.Query = 'SELECT * FROM SessionHistoryItems, Sessions WHERE
> SessionHistoryItems.SessionId = Sessions.SessionId'
> DECLARE @.conditions nvarchar(257)
> SET @.conditions = '';
> IF LEN(@.Pattern) > 0 BEGIN
> SET @.conditions = @.conditions + ' Sessions.Login LIKE ''%' + @.Pattern
> + '%'''
> END
> IF @.From IS NOT NULL BEGIN
> IF LEN(@.conditions) > 0 BEGIN
> SET @.conditions = @.conditions + ' AND '
> END
> SET @.conditions = @.conditions + ' SessionHistoryItems.LoggedOutAt >=
> ' + @.From
> END
> IF @.To IS NOT NULL BEGIN
> IF LEN(@.conditions) > 0 BEGIN
> SET @.conditions = @.conditions + ' AND '
> END
> SET @.conditions = @.conditions + ' SessionHistoryItems.LoggedInAt <= '
> + @.To
> END
> IF LEN(@.conditions) > 0 BEGIN
> EXEC(@.Query + ' AND ' + @.conditions)
> END
> ELSE BEGIN
> EXEC(@.Query)
> END
> RETURN

It looks like you need to CAST or CONVERT the datetime to a string (and add
quotes) in order to build up the @.conditions string:

declare @.dt datetime
set @.dt = getdate()

select 'x' + @.dt -- fails
select 'x''' + cast(@.dt as varchar(20)) + '''' -- succeeds

But in this case, using sp_executesql would probably be a better approach
anyway:

exec sp_executesql
N'select col1, col2 from dbo.MyTable where datecol >= @.From and datecol <=
@.To',
N'@.From datetime, @.To datetime',
@.From, @.To

See sp_executesql in Books Online, and also these articles for more
information/ideas:

http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html

Simon