Help with SQL query is required please

Hi

I have the following table:

date number called answered
2006-08-10 9051231234 0
2006-08-10 9051231235 1
2006-08-11 9051231231 0
2006-08-11 9051231211 0
2006-08-06 9051231222 1
2006-08-06 9051231233 0
2006-08-06 9051231233 0

need to get the report on how many calls have been placed for particular day and how many were answered, something like this:

2006-08-06 3 1
2006-08-10 2 0
2006-08-11 2 1

How to do this in one query or without creating an interim table.
Thanks.Can you post the URL or a copy of the assignment exactly as it came from the teacher? There are usually some subtle quirks in how the assignments are worded that will influcence how you need to solve the problem to get a good grade.

It would also help to know exactly what hardware/software they'll test this on, since that can influence the choices we make too.

-PatP|||pat, you cynic

905 area code is just outside toronto, and i happen to know there are no sql classes going on right now

this has to be a real world problem, not a homework assignment!! :)

xirurg, would you please show the query that you've managed to work out by yourself so far? use correct table and column names, please|||This should be relatively simple, viz. grouping by date, counting to find the second column, and summing to get the third one.|||SQL classes? Yes I think I'd need them :-). Yes it's the real issue.
Software is mySQL. Here are the fields which matter (there are more in the table but they are irrelevant for this task):
+----+-----+--+--+-------+---+
| Field | Type | Null | Key | Default | Extra |
+----+-----+--+--+-------+---+
| calldate | datetime | | MUL | 0000-00-00 00:00:00 | |
| dcontext | varchar(80) | | | | |
| disposition | varchar(45) | | | | |

dcontext has values outbond or incoming - I'm interesting in outbound only. Disposition has values ANSWERED, NO ANSWER, FAILED. I'm interesed in ANSWERED only.
Report should look like:
calldate, number of outbound calls, number of answered calls.

I can get those numbers by using 2 separate queries:
select left(calldate,11),count(*) from cdr where dcontext<>"incoming" group by left(calldate,11);
select left(calldate,11),count(*) from cdr where disposition="ANSWERED" and dcontext<>"incoming" group by left(calldate,11);

but then I have to combine results either in temp table or inside programming code (Perl in this case) so I was thinking there is a way to do it in one query. I tried to use UNION SELECT and CUBE grouping, CASE as well - no luck

Xirurg

pat, you cynic

905 area code is just outside toronto, and i happen to know there are no sql classes going on right now

this has to be a real world problem, not a homework assignment!! :)

xirurg, would you please show the query that you've managed to work out by yourself so far? use correct table and column names, please|||dcontext has values outbond or incoming - I'm interesting in outbound only. Disposition has values ANSWERED, NO ANSWER, FAILED. I'm interesed in ANSWERED only.
Try this:SELECT LEFT(calldate,11) AS calldate,
COUNT(*) AS number_of_outbound_calls,
SUM(CASE disposition WHEN 'ANSWERED' THEN 1 ELSE 0 END)
AS number_of_answered_calls
FROM cdr
WHERE dcontext<>'incoming'
GROUP BY LEFT(calldate,11)Instead of "LEFT(calldate,11)" also try "CAST(calldate AS date)" -- should be better in terms of performance, in case mySQL supports this.|||Thanks Peter

Help With SQL Query

Hello,
With the following table how would I create a query that would return all
rows whos EndDate minus StartDate is more than 28 Days.
TableName: Customers
ID - Integer
CustomerID - VarChar
StartDate - Date
EndDate - Date
Table:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
2 Mike1 8/25/06 9/15/06
3 Dinah 8/23/06 9/1/06
4 James 7/11/06 8/30/06
The Query Should Return:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
4 James 7/11/06 8/30/06
Thanks,
Chuck
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>
|||Thank You
Chuck
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uHoF7yQ2GHA.3656@.TK2MSFTNGP04.phx.gbl...
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>

Help With SQL Query

Hello,
With the following table how would I create a query that would return all
rows whos EndDate minus StartDate is more than 28 Days.
TableName: Customers
ID - Integer
CustomerID - VarChar
StartDate - Date
EndDate - Date
Table:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
2 Mike1 8/25/06 9/15/06
3 Dinah 8/23/06 9/1/06
4 James 7/11/06 8/30/06
The Query Should Return:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
4 James 7/11/06 8/30/06
Thanks,
ChuckSELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message news:O6E$smQ2GHA.5048@.T
K2MSFTNGP05.phx.gbl...
> Hello,
>
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
>
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
>
> Table:
>
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
>
> The Query Should Return:
>
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
>
> Thanks,
>
> Chuck
>
>|||Thank You
Chuck
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uHoF7yQ2GHA.3656@.TK2MSFTNGP04.phx.gbl...
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>

Help with SQL query

Hi, I have a table like the following fields:

TradeDate Item Price

Now, suppose I want to do an update like this: every price
corresponding to a date higher than '30 Sep 2005' will be reset to the
latest price in that item. Can I do something like the following

UPDATE Table t SET Price = (SELECT TOP 1 Price FROM Table q WHERE
q.Item = t.Item ORDER BY TradeDate DESC) WHERE t.TradeDate > '30 Sep
2005'

or is there a better way?

Thank you very much.

BrunoHi Bruno,

Perfect :-D Looks good to me.

HTH, Jens Suessmeyer

Help with SQL Query

I need help building the following query..

My table has the following schema: eventID, typeID

Sample Rows:

1,1
1,2
1,3
2,1
3,2
3,2
4,3
4,4
5,2

I want to be able to query for all eventID's such that type = 2 and
type <> 1. So the result should be

3,2
4,2

The result should NOT include 1,2 because eventID 1 is also "related"
to typeID 1 and 3.Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Your personal pseudo-code is wrong at many levels; did
you mean this?

CREATE TABLE EventSchedules
(event_id INTEGER NOT NULL
REFERENCES Events (event_id),
event_type INTEGER NOT NULL
CHECK (event_type > 0), --assumption
PRIMARY KEY (event_id, event_type)); --requirement!

INSERT INTO EventSchedules VALUES (1,1);
INSERT INTO EventSchedules VALUES (1,2);
INSERT INTO EventSchedules VALUES (1,3);
INSERT INTO EventSchedules VALUES (2,1);
INSERT INTO EventSchedules VALUES (3,2);
INSERT INTO EventSchedules VALUES (3,2);-- removed dup row!!
INSERT INTO EventSchedules VALUES (4,3);
INSERT INTO EventSchedules VALUES (4,4);
INSERT INTO EventSchedules VALUES (5,2);

A data element name like "type_id" makes no sense. Either it is an
identifier for a particular kind of entity or it is some kind of code
for an attribute. It cannot be both an attribute and an entity. You
might want to get a book on data modeling and the ISO-11179 Standards.

>> I want to be able to query for all event_id's such that event_type = 2 and event_type <> 1. <<

Here is one way.

SELECT event_id
FROM EventSchedules
GROUP BY event_id
HAVING MIN(event_type) > 1
AND MAX (CASE WHEN event_type <> 2 THEN 0 ELSE 2 END) = 2;

And I am sure that someone will come up with a self-join solution, too.|||b_naick@.yahoo.ca wrote:
> I need help building the following query..
> My table has the following schema: eventID, typeID
> Sample Rows:
> 1,1
> 1,2
> 1,3
> 2,1
> 3,2
> 3,2
> 4,3
> 4,4
> 5,2
> I want to be able to query for all eventID's such that type = 2 and
> type <> 1. So the result should be
> 3,2
> 4,2
> The result should NOT include 1,2 because eventID 1 is also "related"
> to typeID 1 and 3.

--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

Shouldn't the last pair be 5,2, since there isn't any 4,2.

Try,

SELECT DISTINCT eventID, typeID
FROM t as t1
WHERE typeID = 2
AND eventID NOT IN (SELECT eventID FROM t WHERE typeID != 2)

Change table name "t" to your table's true name.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQpTZ5IechKqOuFEgEQKIrgCfeW81ytgRIXUnl//jAA0RU8zZwLQAoOPN
UBuYtqvs/JqhLjVuFIYYTzqF
=aqWw
--END PGP SIGNATURE--|||(b_naick@.yahoo.ca) writes:
> I need help building the following query..
> My table has the following schema: eventID, typeID
> Sample Rows:
> 1,1
> 1,2
> 1,3
> 2,1
> 3,2
> 3,2
> 4,3
> 4,4
> 5,2
> I want to be able to query for all eventID's such that type = 2 and
> type <> 1. So the result should be
> 3,2
> 4,2
> The result should NOT include 1,2 because eventID 1 is also "related"
> to typeID 1 and 3.

I assume that desired result is

3,2
5,2

Else there is something I don't understand at all.

This could be a good query:

SELECT *
FROM tbl a
WHERE a.type = 2
AND NOT EXISTS (SELECT *
FROM tbl b
WHERE a.eventID = b.eventID
AND EXISTS (SELECT *
FROM tbl c
WHERE c.eventID = b.eventID
ABD c.type = 1))

Since you did not include CREATE TABLE and INSERT statements, I
have not tested this.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Help with SQL Query

Given the following two tables below, I need help in writing a query that would retreive only 3 or less distinct values of BDesc from tbB table for every row found in tbA.

for example:

I expect result to be:

Aid Bdesc
100 1st Desc for 100
100 2nd Desc for 100
100 3rd Desc for 100
200 1st Desc for 200
200 2nd Desc for 200
200 3rd Desc for 200
300 1st Desc for 300
300 2nd Desc for 300
300 3rd Desc for 300
400 1st Desc for 400
500 1st Desc for 500
500 3rd Desc for 500

The tables are:

use tempdb

go

set nocount on

if exists (select name from sysobjects where name = 'TbA')

Drop table TbA

Create Table TbA ( Aid int )

Insert into TbA values(100)

Insert into TbA values(200)

Insert into TbA values(300)

Insert into TbA values(400)

--select * from TbA

if exists (select name from sysobjects where name = 'TbB')

Drop table TbB

Create Table TbB ( Bid int , BDesc varchar(50) )

INSERT INTO TbB Values(100, '1st Desc for 100')

INSERT INTO TbB Values(100, '2nd Desc for 100')

INSERT INTO TbB Values(100, '3rd Desc for 100')

INSERT INTO TbB Values(100, '3rd Desc for 100')

INSERT INTO TbB Values(200, '1st Desc for 200')

INSERT INTO TbB Values(200, '2nd Desc for 200')

INSERT INTO TbB Values(200, '3rd Desc for 200')

INSERT INTO TbB Values(200, '4th Desc for 200')

INSERT INTO TbB Values(200, '1st Desc for 200')

INSERT INTO TbB Values(300, '1st Desc for 300')

INSERT INTO TbB Values(300, '2nd Desc for 300')

INSERT INTO TbB Values(300, '3rd Desc for 300')

INSERT INTO TbB Values(300, '4th Desc for 300')

INSERT INTO TbB Values(400, '1st Desc for 400')

INSERT INTO TbB Values(400, '1st Desc for 400')

INSERT INTO TbB Values(500, '1st Desc for 500')

INSERT INTO TbB Values(500, '1st Desc for 500')

INSERT INTO TbB Values(500, '3rd Desc for 500')

--select * from TbB

Thanks for your help with this...

Here ya go

Code Snippet

selectdistinct TbB.*

from(selectdistinct aid from TbA)as TbA

innerjoin TbB

on TbA.Aid = TbB.Bid

and TbB.BDesc in

(selectdistincttop 3 BDesc from TbB where Bid = TbA.Aid orderby BDesc)

|||Thanks|||

How about if I wanted to get the result like this:

ColA ColB

100 1st Descfor 100, 2nd Descfor 100, 3rd Descfor 100

200 1st Descfor 200, 2nd Descfor 200, 3rd Descfor 200

300 1st Descfor 300, 2nd Descfor 300, 3rd Descfor 300

400 1st Descfor 400

500 1st Descfor 500, 3rd Descfor 500

|||hi, you can try using a udf

CREATE FUNCTION dbo.GetBDesc
(
@.AID int
)
RETURNS varchar(800)
AS
BEGIN
DECLARE @.BDesc varchar(100)
SET @.BDesc = ''
SELECT
@.BDesc = @.BDesc + BDesc + ','
FROM (SELECT DISTINCT TOP 3 * FROM TbB a WHERE a.BID = @.AID) b
WHERE BID = @.AID
ORDER BY
BDesc

IF @.BDesc <> '' SET @.BDesc = LEFT(@.BDesc, LEN(@.BDesc) - 1)

RETURN @.BDesc

END

GO

select *
, dbo.GetBDesc(AID)
from tba|||

If you use SQL Server 2005 you dont need a function...

Here the sample,

Code Snippet

Create Table #TableA(

Aid int );

Insert into #TableA values(100)

Insert into #TableA values(200)

Insert into #TableA values(300)

Insert into #TableA values(400)

Insert into #TableA values(500)

Create Table #TableB(

Bid int

,BDesc varchar(50)

)

INSERT INTO #TableB Values(100, '1st Desc for 100')

INSERT INTO #TableB Values(100, '2nd Desc for 100')

INSERT INTO #TableB Values(100, '3rd Desc for 100')

INSERT INTO #TableB Values(100, '3rd Desc for 100')

INSERT INTO #TableB Values(200, '1st Desc for 200')

INSERT INTO #TableB Values(200, '2nd Desc for 200')

INSERT INTO #TableB Values(200, '3rd Desc for 200')

INSERT INTO #TableB Values(200, '4th Desc for 200')

INSERT INTO #TableB Values(200, '1st Desc for 200')

INSERT INTO #TableB Values(300, '1st Desc for 300')

INSERT INTO #TableB Values(300, '2nd Desc for 300')

INSERT INTO #TableB Values(300, '3rd Desc for 300')

INSERT INTO #TableB Values(300, '4th Desc for 300')

INSERT INTO #TableB Values(400, '1st Desc for 400')

INSERT INTO #TableB Values(400, '1st Desc for 400')

INSERT INTO #TableB Values(500, '1st Desc for 500')

INSERT INTO #TableB Values(500, '1st Desc for 500')

INSERT INTO #TableB Values(500, '3rd Desc for 500')

;With DistinctData

as

(

Select Distinct A.Aid,B.BDesc from #TableA A Join #TableB B On A.Aid =B.Bid

),

RowData

as

(

Select Aid,Bdesc,Row_Number() Over(Partition By Aid Order By BDesc) RowID From DistinctData

)

/*

Select

Aid,

BDesc

From

RowData

Where

RowID <=3

*/

Select Distinct

Aid

,Substring((Select ',' + BDesc as [text()] From RowData Sub Where Sub.Aid=Main.Aid And Sub.RowId<=3 For XML Path(''), Elements),2,8000) as Descs

From

RowData Main

|||MG,

How about these two queries (both require SQL 2005)

select a.aid, b.bdesc
from tbA a
cross apply
(select distinct top (3) bdesc from tbB b where b.bid = a.aid) b
;

select a.aid as ColA, stuff((select distinct top (3) ', ' + bdesc from tbB b where b.bid = a.aid order by 1 for xml path('')),1,2,'') as ColB
from tbA a
;

The second one puts them into a single column for you.

Rob|||I should have mentioned that this is for SQL 2000 and for an OLTP environment. The procedure processes approx. 20,000 rows and right now it's using cursor logic which is slowing things down, so I was looking for ways to use set based processing. The function idea is good, but again its going to be row by row processing.|||

The function approach should be a significant improvement over any cursor processing.

Is there something we're not understanding about what you want to accomplish?

|||

Hi Rhamille Golimlim,

There is an issue when using "order by" during an aggregate concatenation query.

PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location

http://support.microsoft.com/default.aspx/kb/287515

AMB

|||thanks for the tip hunchback. would it still show a different execution plan if we put the order by inside the subquery?|||

Hi Rhamille Golimlim,

If you put the "order by" clause inside the derived table, then how are you going to be sure that the result is sorted if the only way to asure a sorted resultset is using the "order by" clause in the statement that pull the data?. It is like sorting inside a view and not using "order by" clause when you pull from the view.

Concatenating row values in T-SQL

http://www.projectdmx.com/tsql/rowconcatenate.aspx

AMB

|||hi hunchback,

cool, would the xml path approach be the best work around for this scenario? or are there other alternatives or tsql hacks?

/rhamille

Help with sql query

Hi

I have a query which should do the following..

Bring up all records from customer table that

datelastvisited field is not during the last 3 months
or is null
AND has a region matching a user input value

OR

in a customercalls table with a one to many relationship on customerid

if nextcalldate is within the lst 3 months
or is null

i thought i had it working but its not..

below is the query that i thought was working before I tried adding the null criteria..

SELECT customers.*, customers.CustomerLastVisitDate, CustomerCalls.CustomerCallDateNext FROM customers INNER JOIN CustomerCalls ON customers.CustomerID = CustomerCalls.CustomerID WHERE ((([customers.customerregion])='" & Me.cboRegion & "') AND ([customers.CustomerLastVisitDate] Not Between Date() And DateAdd('m',-3,Date()))) AND ((CustomerCalls.CustomerCallDateNext) Between Date() And DateAdd('m',-3,Date())) OR (([customercalls.customercalldatenext])=Date());

can anyone help me?

I seem to have lsot the plot..

thanks
matselect customers.*
from customers
where customerregion = '" & Me.cboRegion & "'
and (
CustomerLastVisitDate is null
or CustomerLastVisitDate
Not Between Date()
and DateAdd('m',-3,Date())
)
union
select customers.*
from customers
inner
join CustomerCalls
on customers.CustomerID
= CustomerCalls.CustomerID
where CustomerCalls.CustomerCallDateNext is null
or CustomerCalls.CustomerCallDateNext
Between Date()
and DateAdd('m',-3,Date())|||Thanks very much..
Ur a star!

mat

Help With SQL Query

Hello,
With the following table how would I create a query that would return all
rows whos EndDate minus StartDate is more than 28 Days.
TableName: Customers
ID - Integer
CustomerID - VarChar
StartDate - Date
EndDate - Date
Table:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
2 Mike1 8/25/06 9/15/06
3 Dinah 8/23/06 9/1/06
4 James 7/11/06 8/30/06
The Query Should Return:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
4 James 7/11/06 8/30/06
Thanks,
ChuckThis is a multi-part message in MIME format.
--=_NextPart_000_086D_01C6D8D2.0BEDBF60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message =news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> > With the following table how would I create a query that would return =all > rows whos EndDate minus StartDate is more than 28 Days.
> > TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> > Table:
> > ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
> > > The Query Should Return:
> > ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
> > > Thanks,
> > Chuck > >
--=_NextPart_000_086D_01C6D8D2.0BEDBF60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

SELECT
=ID
, =CustomerID
, =StartDate
, =EndDate
FROM Customers
WHERE datediff( day, StartDate, =EndDate ) > 28
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"Charles A. Lackman" wrote in message news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...> =Hello,> > With the following table how would I create a query that would =return all > rows whos EndDate minus StartDate is more than 28 =Days.> > TableName: Customers> ID - Integer> CustomerID - VarChar> StartDate - Date> EndDate - Date> > =Table:> > ID CustomerID StartDate EndDate> 1 Chuck1 &=nbsp; 9/1/06 =9/30/06> 2 Mike1 &n=bsp; 8/25/06 =9/15/06> 3 Dinah &n=bsp; 8/23/06 9/1/06> 4 James &n=bsp; 7/11/06 8/30/06> > > The Query Should Return:> => ID CustomerID StartDate EndDate> 1 Chuck1 &=nbsp; 9/1/06 =9/30/06> 4 James &n=bsp; 7/11/06 8/30/06> > > Thanks,> > Chuck => >

---

--=_NextPart_000_086D_01C6D8D2.0BEDBF60--|||Thank You
Chuck
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uHoF7yQ2GHA.3656@.TK2MSFTNGP04.phx.gbl...
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>sql

help with SQL queries please

hi

could anyone please help me some SQL queries?

i have the following tables:

http://img.photobucket.com/albums/v...a66/tables2.jpg

and just needed help on constructing these SQL queriers:

1. show a list of the sponsers and which elephants they sponser. include sponsers name and amount sponsered.

2. show a count of how many volunteers work at each sanctuary.

3. provide a list of baby elephants born in august 2006, include their name dob and mothers name.

4. display the emergency contact details for a member of staff called "bob jones"

any help with any of these queries will be greatly appreciated.so for query number 3 i have:

SELECT DOB, UNIQUE NUMBER, BABY NAME, GENDER, MOTHER NAME

FROM BABY ELEPHANT

WHERE DOB = "AUGUST 2006"

does this sound about right?
i dont have access to a SQL program so its hard to test the actual queries. :/|||First order of business, we don't do homework outright. We might offer comments or help, but we don't do it outright.

Second, your diagram is missing, the Photo Bucket web site generates a 404 page when I try to follow that link.

-PatP|||sorry the link should work now
http://img.photobucket.com/albums/v635/monkey_mafia66/tables2.jpg|||I'm hoping that the diagram you've posted is old, because it doesn't have some of the columns from your SELECT statement,and it also doesn't seem to have much of the information needed to solve the questions in your homework.

-PatP|||as far as i can see all the information is present to answer the questions.
what information are you referring to that is not present?|||YOUR queries are not present.

Also, how on Earth are you going to study SQL without a database?!? If you ate without food, you'd already be dead. Lucky you, databases usually don't kill.

Help with SQL Function

Hello All:
From the Following Table, I want to enter a Temperature and then have the
SQL Function Return the Web Color
-- TemperatureIndex --
ID TempMin TempMax WebColor
1 0 9 #E59DCB
2 10 19 #8569FA
3 20 29 #3F9CFB
4 30 39 #73E96F
I would like to Enter a Temperature and return the following WebColor output
15 -> #E59DCB
28 -> #3F9CFB
32 -> #73E96FCREATE TABLE TemperatureIndex
(
ID int NOT NULL,
TempMin int NOT NULL,
TempMax int NOT NULL,
WebColor char(7) NOT NULL
)
GO
INSERT INTO TemperatureIndex VALUES(1, 0, 9, '#E59DCB')
INSERT INTO TemperatureIndex VALUES(2, 10, 19, '#8569FA')
INSERT INTO TemperatureIndex VALUES(3, 20, 29, '#3F9CFB')
INSERT INTO TemperatureIndex VALUES(4, 30, 39, '#73E96F')
GO
CREATE UNIQUE CLUSTERED INDEX TemperatureIndex_cdx
ON TemperatureIndex(TempMin, TempMax)
GO
ALTER TABLE TemperatureIndex
ADD CONSTRAINT PK_TemperatureIndex
PRIMARY KEY NONCLUSTERED (ID)
GO
CREATE FUNCTION dbo.GetWebColorForTemperature(@.Temp int)
RETURNS char(7)
AS
BEGIN
RETURN (SELECT WebColor
FROM TemperatureIndex
WHERE @.Temp BETWEEN TempMin AND TempMax
)
END
GO
SELECT dbo.GetWebColorForTemperature(15)
SELECT dbo.GetWebColorForTemperature(28)
SELECT dbo.GetWebColorForTemperature(32)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Stuart Shay" <sshay@.yahoo.com> wrote in message
news:uzg9rvG%23FHA.160@.TK2MSFTNGP12.phx.gbl...
> Hello All:
> From the Following Table, I want to enter a Temperature and then have the
> SQL Function Return the Web Color
> -- TemperatureIndex --
> ID TempMin TempMax WebColor
> 1 0 9 #E59DCB
> 2 10 19 #8569FA
> 3 20 29 #3F9CFB
> 4 30 39 #73E96F
> I would like to Enter a Temperature and return the following WebColor
> output
> 15 -> #E59DCB
> 28 -> #3F9CFB
> 32 -> #73E96F
>|||Dan:
Thanks for your help !!!
Best
Stuart
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uev6wbH%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> CREATE TABLE TemperatureIndex
> (
> ID int NOT NULL,
> TempMin int NOT NULL,
> TempMax int NOT NULL,
> WebColor char(7) NOT NULL
> )
> GO
> INSERT INTO TemperatureIndex VALUES(1, 0, 9, '#E59DCB')
> INSERT INTO TemperatureIndex VALUES(2, 10, 19, '#8569FA')
> INSERT INTO TemperatureIndex VALUES(3, 20, 29, '#3F9CFB')
> INSERT INTO TemperatureIndex VALUES(4, 30, 39, '#73E96F')
> GO
> CREATE UNIQUE CLUSTERED INDEX TemperatureIndex_cdx
> ON TemperatureIndex(TempMin, TempMax)
> GO
> ALTER TABLE TemperatureIndex
> ADD CONSTRAINT PK_TemperatureIndex
> PRIMARY KEY NONCLUSTERED (ID)
> GO
> CREATE FUNCTION dbo.GetWebColorForTemperature(@.Temp int)
> RETURNS char(7)
> AS
> BEGIN
> RETURN (SELECT WebColor
> FROM TemperatureIndex
> WHERE @.Temp BETWEEN TempMin AND TempMax
> )
> END
> GO
> SELECT dbo.GetWebColorForTemperature(15)
> SELECT dbo.GetWebColorForTemperature(28)
> SELECT dbo.GetWebColorForTemperature(32)
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Stuart Shay" <sshay@.yahoo.com> wrote in message
> news:uzg9rvG%23FHA.160@.TK2MSFTNGP12.phx.gbl...
>

Help with SQL DateTime

hello, i created a web app. that will query the SQL DB for the DateTime. My SQL DB has the following DateTime format: '2003-08-06 08:55:00.000', but when i i query the database and show the results to my webpage it displays a different format: '8/6/2003 8:55:00 AM', and also when i try to query the database again with the DateTime it gave to me it returns no result. why is this happening? please help... thanks!show me your sql, and what object are you setting the datetime field to?

Help With SQL Command

I have the following Table
Table1
ID = Int
Division = VarChar
FirstName = VarChar
LastName = VarChar
This is an example of the Data (Select * From Table1)
ID Division FirstName LastName
1 E Chuck Martin
2 E Frank Smith
3 F Chuck Martin
4 G Chuck Martin
5 A Mark James
6 E Mark James
I would like the query to return the following (Example)
First + Last Divisions
Chuck Martin EFG
Frank Smith E
Mark James AE
I can do the First and Last Column, but don't know how to do the "Divisions"
Column.
Any assistance will be greatly appreciated.
Unfortunately, I cannot change the table.
Chuck
Concatenating row values in T-SQL
http://www.projectdmx.com/tsql/rowconcatenate.aspx
AMB
"Charles A. Lackman" wrote:

> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
>
>
|||Here's how I did it, using a UDF to concatenate the strings.
CREATE TABLE [dbo].[Table1](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Division] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL
) ON [PRIMARY]
go
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Frank','Smith')
INSERT INTO Table1 (Division, FirstName, LastName) values
('F','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('G','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('A','Mark','James')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Mark','James')
go
CREATE FUNCTION [dbo].[ConcatDiv](@.fn varchar(50), @.ln varchar(50))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SET @.Output = ''
SELECT
@.Output = @.Output + division
FROM
table1
WHERE
firstname = @.fn and lastname = @.ln
ORDER BY
division
RETURN @.Output
END
go
-- query to select the output
SELECT DISTINCT
firstname as [First Name],
lastname as [Last Name],
dbo.ConcatDiv(firstname, lastname) as Division
FROM
table1
go
On Apr 30, 4:17 pm, "Charles A. Lackman"
<Char...@.CreateItSoftware.net> wrote:
> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck

Help With SQL Command

I have the following Table
Table1
ID = Int
Division = VarChar
FirstName = VarChar
LastName = VarChar
This is an example of the Data (Select * From Table1)
ID Division FirstName LastName
1 E Chuck Martin
2 E Frank Smith
3 F Chuck Martin
4 G Chuck Martin
5 A Mark James
6 E Mark James
I would like the query to return the following (Example)
First + Last Divisions
Chuck Martin EFG
Frank Smith E
Mark James AE
I can do the First and Last Column, but don't know how to do the "Divisions"
Column.
Any assistance will be greatly appreciated.
Unfortunately, I cannot change the table.
ChuckConcatenating row values in T-SQL
http://www.projectdmx.com/tsql/rowconcatenate.aspx
AMB
"Charles A. Lackman" wrote:

> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Division
s"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
>
>|||Here's how I did it, using a UDF to concatenate the strings.
CREATE TABLE [dbo].[Table1](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Division] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL
) ON [PRIMARY]
go
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Frank','Smith')
INSERT INTO Table1 (Division, FirstName, LastName) values
('F','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('G','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('A','Mark','James')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Mark','James')
go
CREATE FUNCTION [dbo].[ConcatDiv](@.fn varchar(50), @.ln varchar(50))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SET @.Output = ''
SELECT
@.Output = @.Output + division
FROM
table1
WHERE
firstname = @.fn and lastname = @.ln
ORDER BY
division
RETURN @.Output
END
go
-- query to select the output
SELECT DISTINCT
firstname as [First Name],
lastname as [Last Name],
dbo.ConcatDiv(firstname, lastname) as Division
FROM
table1
go
On Apr 30, 4:17 pm, "Charles A. Lackman"
<Char...@.CreateItSoftware.net> wrote:
> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Division
s"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck

Help With SQL Command

I have the following Table
Table1
ID = Int
Division = VarChar
FirstName = VarChar
LastName = VarChar
This is an example of the Data (Select * From Table1)
ID Division FirstName LastName
1 E Chuck Martin
2 E Frank Smith
3 F Chuck Martin
4 G Chuck Martin
5 A Mark James
6 E Mark James
I would like the query to return the following (Example)
First + Last Divisions
Chuck Martin EFG
Frank Smith E
Mark James AE
I can do the First and Last Column, but don't know how to do the "Divisions"
Column.
Any assistance will be greatly appreciated.
Unfortunately, I cannot change the table.
Chuck
Concatenating row values in T-SQL
http://www.projectdmx.com/tsql/rowconcatenate.aspx
AMB
"Charles A. Lackman" wrote:

> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
>
>
|||Here's how I did it, using a UDF to concatenate the strings.
CREATE TABLE [dbo].[Table1](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Division] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL
) ON [PRIMARY]
go
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Frank','Smith')
INSERT INTO Table1 (Division, FirstName, LastName) values
('F','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('G','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('A','Mark','James')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Mark','James')
go
CREATE FUNCTION [dbo].[ConcatDiv](@.fn varchar(50), @.ln varchar(50))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SET @.Output = ''
SELECT
@.Output = @.Output + division
FROM
table1
WHERE
firstname = @.fn and lastname = @.ln
ORDER BY
division
RETURN @.Output
END
go
-- query to select the output
SELECT DISTINCT
firstname as [First Name],
lastname as [Last Name],
dbo.ConcatDiv(firstname, lastname) as Division
FROM
table1
go
On Apr 30, 4:17 pm, "Charles A. Lackman"
<Char...@.CreateItSoftware.net> wrote:
> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck

Help With SQL Command

I have the following Table
Table1
ID = Int
Division = VarChar
FirstName = VarChar
LastName = VarChar
This is an example of the Data (Select * From Table1)
ID Division FirstName LastName
1 E Chuck Martin
2 E Frank Smith
3 F Chuck Martin
4 G Chuck Martin
5 A Mark James
6 E Mark James
I would like the query to return the following (Example)
First + Last Divisions
Chuck Martin EFG
Frank Smith E
Mark James AE
I can do the First and Last Column, but don't know how to do the "Divisions"
Column.
Any assistance will be greatly appreciated.
Unfortunately, I cannot change the table.
ChuckConcatenating row values in T-SQL
http://www.projectdmx.com/tsql/rowconcatenate.aspx
AMB
"Charles A. Lackman" wrote:
> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
>
>|||Here's how I did it, using a UDF to concatenate the strings.
CREATE TABLE [dbo].[Table1](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Division] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL
) ON [PRIMARY]
go
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Frank','Smith')
INSERT INTO Table1 (Division, FirstName, LastName) values
('F','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('G','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('A','Mark','James')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Mark','James')
go
CREATE FUNCTION [dbo].[ConcatDiv](@.fn varchar(50), @.ln varchar(50))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SET @.Output = ''
SELECT
@.Output = @.Output + division
FROM
table1
WHERE
firstname = @.fn and lastname = @.ln
ORDER BY
division
RETURN @.Output
END
go
-- query to select the output
SELECT DISTINCT
firstname as [First Name],
lastname as [Last Name],
dbo.ConcatDiv(firstname, lastname) as Division
FROM
table1
go
On Apr 30, 4:17 pm, "Charles A. Lackman"
<Char...@.CreateItSoftware.net> wrote:
> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck

Help with sproc with one parameter that can contain multiple values

I am trying to get the following procedure to work and I am getting hung up
on the @.strClaim parameter, this could be either 1 or more claim numbers
for one terminal number. I want to be able to get all the claim detail
information for, say, terminal # 1222222abc that are in claims 521, 522,
523, 530.
I don't know how to handle the @.strClaim so that the procedure will for all
claim numbers in that list.
Any help appreciated.
TIA
Nancy
Create Procedure usp_GetClaims
(@.strClaim as Char(10),
@.strTerminal as Char(30))
as
Select X_CLAIMS_NO,X_TERMINAL_NUMBER
from
dbo.X_HCFA_CLAIM
where
Cast(X_CLAIMS_NO as char(10)) IN @.strClaim
AND
X_TERMINAL_NUMBER = @.strTerminal
exec usp_GetClaims '574, 573', 'RMFAHESSSXYHLLLX'To pass a CSV list as a VARCHAR(n) parameter, you will have to use something
different. For various alternatives, refer to:
http://www.sommarskog.se/arrays-in-sql.html
Anith|||Thanks, I think I found what I needed. Great site too!
Nancy
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OVgTMF1oFHA.3316@.tk2msftngp13.phx.gbl...
> To pass a CSV list as a VARCHAR(n) parameter, you will have to use
> something different. For various alternatives, refer to:
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Anith
>

HELP with SP_configure

I am running the following command:

exec sp_configure 'allow updates', '0'

I get:

Server: Msg 15247, Level 16, State 1, Procedure sp_configure, Line 169
User does not have permission to perform this action.

What's causeing this?

Thanks!

Kenyou need to be associated (at least) with serveradmin role to be able to perform this operation. check with your sql server dba on that.

Help with sp code - cursor

Friends,
When I step through the following code in QA it works fine - when I alter
the proc and run it, I get an error (you should be able to copy the code as
it is into your own QA for testing) - Anybody know where I am making the
mistke? Thanks in advance for your help ... Bill Morgan
create proc Tester
as
/* this is test code that creates a table and then alters that table
to add columns that are the required USA states - it then populates
the date column and updates
one of the state columns*/
set nocount on
DECLARE @.sql nvarchar(4000),
@.state varchar(10),
@.dater smalldatetime
set @.sql = 'alter table #main '
If object_id('tempdb..#states') is not null
begin
drop table #states
end
If object_id('tempdb..#main') is not null
begin
drop table #main
end
create table #states
(state varchar(5) null)
Create Table #main
(Dates smalldatetime null)
insert into #states values ('CA')
insert into #states values ('MN')
insert into #states values ('ND')
insert into #states values ('NJ')
insert into #states values ('NY')
insert into #states values ('TX')
insert into #states values ('IL')
insert into #states values ('IA')
insert into #states values ('WY')
insert into #states values ('FL')
DECLARE mycursor CURSOR
FOR
SELECT state
FROM #states
begin tran
OPEN mycursor
FETCH NEXT
FROM mycursor
INTO @.state
WHILE @.@.fetch_status = 0
BEGIN
set @.sql = 'alter table #main '
set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
exec sp_executesql @.sql
FETCH NEXT
FROM mycursor
INTO @.state
END
CLOSE mycursor
DEALLOCATE mycursor
set @.dater = getdate()
while @.dater < getdate() + 365
begin
insert into #main (Dates)
values
(@.dater)
set @.dater = @.dater + 1
end
update #main
set ca = 'a'
select * from #main
set nocount off
returnYour table #main doesn't have a column "ca" in it, just a column "Dates":

> update #main
> set ca = 'a'
"bill_morgan" <bill_morgan@.discussions.microsoft.com> wrote in message
news:7F945501-89B0-4B6C-81BC-4767C92B0464@.microsoft.com...
> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code
> as
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Look at where you create the #Main table, there is no "ca" column defined in
it, only a "dates" column... Therefore, later on where you try to update the
'ca' column, it fails... Can;t begin to suggest a fix until I know what
Stored Proc is SUpposed t odo...
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Sorry, Now I see what you're doing...
Wat's wrong is that You have an Open uncommitted transaction
just delete the Begin Tran line and try it again... If you need the tran,
then you have to put in a corresponding Commit tran...
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Here is the explanation of what is happening.
http://groups.google.ca/groups?selm...FTNGP11.phx.gbl
Also, you have a begin transaction without a matching commit / rollback.
Example:
-- I commented the begin transaction
use northwind
go
create proc Tester
as
/* this is test code that creates a table and then alters that table
to add columns that are the required USA states - it then populates
the date column and updates
one of the state columns*/
set nocount on
DECLARE @.sql nvarchar(4000),
@.state varchar(10),
@.dater smalldatetime
set @.sql = 'alter table #main '
If object_id('tempdb..#states') is not null
begin
drop table #states
end
If object_id('tempdb..#main') is not null
begin
drop table #main
end
create table #states
(state varchar(5) null)
Create Table #main
(Dates smalldatetime null)
insert into #states values ('CA')
insert into #states values ('MN')
insert into #states values ('ND')
insert into #states values ('NJ')
insert into #states values ('NY')
insert into #states values ('TX')
insert into #states values ('IL')
insert into #states values ('IA')
insert into #states values ('WY')
insert into #states values ('FL')
DECLARE mycursor CURSOR
FOR
SELECT state
FROM #states
--begin tran
OPEN mycursor
FETCH NEXT
FROM mycursor
INTO @.state
WHILE @.@.fetch_status = 0
BEGIN
set @.sql = 'alter table #main '
set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
exec sp_executesql @.sql
FETCH NEXT
FROM mycursor
INTO @.state
END
CLOSE mycursor
DEALLOCATE mycursor
set @.dater = getdate()
while @.dater < getdate() + 365
begin
insert into #main (Dates)
values (@.dater)
set @.dater = @.dater + 1
end
exec ('update #main set ca = ''a''')
select * from #main
set nocount off
return
go
exec tester
go
drop procedure tester
go
AMB
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Bill, What is going on is that SQL7/2000 has what is called delayed
verification o(or something like that) which basically does NOT check the
column names of tables whoch ddo not currently exist when you create the
Stored Proc. It waits until run time... Then it checks again, BEFORE The
stored Proc runs, to make sure that every column and table exists...
So what's going on here is that the compiler sees that you're going to
create the #Main table, and that it will have a column named 'dates', but it
doesn't (no way it can) see that you're going to alter the table and add all
those state name columns, so the Update #Main Set CA = 'a' line fails the
compiler test...
If you comment that line out, (and fx the Open Transaction issue), the code
will work.
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||my apologies for the begin tran statement - i was monkeying with the
procedure and forgot to take that out - once it's removed you can step
through the procedure, but trying to run it all at once fails ...
"CBretana" wrote:
> Sorry, Now I see what you're doing...
> Wat's wrong is that You have an Open uncommitted transaction
> just delete the Begin Tran line and try it again... If you need the tran,
> then you have to put in a corresponding Commit tran...
> "bill_morgan" wrote:
>|||Thanks for the guidance ... my apologies for the begin tran - i forgot to
take that out before I posted this question (I thought the begin tran /
commit tran) might fix things ... I am visiting the sight you suggest ...
thanks ...
"Alejandro Mesa" wrote:
> Here is the explanation of what is happening.
> [url]http://groups.google.ca/groups?selm=uxV68C33DHA.3468%40TK2MSFTNGP11.phx.gbl[/url
]
> Also, you have a begin transaction without a matching commit / rollback.
> Example:
> -- I commented the begin transaction
> use northwind
> go
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> --begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values (@.dater)
> set @.dater = @.dater + 1
> end
> exec ('update #main set ca = ''a''')
> select * from #main
> set nocount off
> return
> go
> exec tester
> go
> drop procedure tester
> go
>
> AMB
>
> "bill_morgan" wrote:
>|||Yes ..!! I created a new proc to handle that final update ... Proc 1 calls
Proc 2 and it works great ... thank you for the new knowledge ...
"CBretana" wrote:
> Bill, What is going on is that SQL7/2000 has what is called delayed
> verification o(or something like that) which basically does NOT check the
> column names of tables whoch ddo not currently exist when you create the
> Stored Proc. It waits until run time... Then it checks again, BEFORE The
> stored Proc runs, to make sure that every column and table exists...
> So what's going on here is that the compiler sees that you're going to
> create the #Main table, and that it will have a column named 'dates', but
it
> doesn't (no way it can) see that you're going to alter the table and add a
ll
> those state name columns, so the Update #Main Set CA = 'a' line fails the
> compiler test...
> If you comment that line out, (and fx the Open Transaction issue), the cod
e
> will work.
> "bill_morgan" wrote:
>sql

Help with SP

Hello,
I have the following two tables
tb_ProductIssue
issueID productID statusID Name
========================================
====
1 10 1 Error 256
2 12 2 Can't install
3 10 3 Constant Reboot
tb_status
statusID status_name
1 Open
2 Closed
3 Pending
I want to write a stored procedure that will retreive the issueID, productID
, status_name, and name
for a row in the tb_ProductIssue table. The stored procedure will have one
parameter, the
productID, to select any records in the tb_ProductIssue that correspond to t
he product. Here is
what I have so far, but I just stuck! Any help will be greatly appreciated
create procedure dbo.p_ProductIssuesGet
(
@.ProductID int
)
SELECT issueID, productID, statusID, name FROM tb_ProductIssue WHERE product
ID = @.ProductIDTry this
Exec('SELECT issueID, productID, statusID, name FROM tb_ProductIssue
WHERE productID = '+@.ProductID )|||Thanks for the help...but I found a way to do it with Inner Joins!
Ed_P. wrote:
> Hello,
> I have the following two tables
> tb_ProductIssue
> issueID productID statusID Name
> ========================================
====
> 1 10 1 Error 256
> 2 12 2 Can't install
> 3 10 3 Constant Reboot
> tb_status
> statusID status_name
> 1 Open
> 2 Closed
> 3 Pending
> I want to write a stored procedure that will retreive the issueID,
> productID, status_name, and name for a row in the tb_ProductIssue
> table. The stored procedure will have one parameter, the productID, to
> select any records in the tb_ProductIssue that correspond to the
> product. Here is what I have so far, but I just stuck! Any help will be
> greatly appreciated
> create procedure dbo.p_ProductIssuesGet
> (
> @.ProductID int
> )
> SELECT issueID, productID, statusID, name FROM tb_ProductIssue WHERE
> productID = @.ProductID|||Try this
Exec('SELECT issueID, productID, statusID, name FROM tb_ProductIssue
WHERE productID = '+@.ProductID )
Madhivanan

Help with Sort ID differences

Hi. I was wondering what is the default differences in the following 2 sort
order ids:
51 - SQL_Latin1_General_CP1_CS_AS collation.
71 - Latin1_General_CS_AS collation.
As some of you may have guess, the same SQL code returns different results
sets from two servers that are nearly the same with the execption being, the
Server's Sort ID.
Thanks,
JoeJoe,
The sort order will be differant for unicode data as well as ordinary string
data as windows and sql collations are slightly differant. Microsoft
describe the sql_ collations nicely in this article
http://support.microsoft.com/?id=322112.
This also points you to some examples of where the sort orders differ
e.g. a-c and ab
where the - is sorted differantly between the two collations
Chris
"Joe D" wrote:
> Hi. I was wondering what is the default differences in the following 2 sort
> order ids:
> 51 - SQL_Latin1_General_CP1_CS_AS collation.
> 71 - Latin1_General_CS_AS collation.
> As some of you may have guess, the same SQL code returns different results
> sets from two servers that are nearly the same with the execption being, the
> Server's Sort ID.
> Thanks,
> Joe
>
>|||Hi Chris,
Thanks for the pointer. I'll check it out.
Joe
"Chris Hoare" <choare@.nospam.nospam> wrote in message
news:2A1473C4-760B-4D2A-8421-1739AF812F45@.microsoft.com...
> Joe,
> The sort order will be differant for unicode data as well as ordinary
> string
> data as windows and sql collations are slightly differant. Microsoft
> describe the sql_ collations nicely in this article
> http://support.microsoft.com/?id=322112.
> This also points you to some examples of where the sort orders differ
> e.g. a-c and ab
> where the - is sorted differantly between the two collations
> Chris
> "Joe D" wrote:
>> Hi. I was wondering what is the default differences in the following 2
>> sort
>> order ids:
>> 51 - SQL_Latin1_General_CP1_CS_AS collation.
>> 71 - Latin1_General_CS_AS collation.
>> As some of you may have guess, the same SQL code returns different
>> results
>> sets from two servers that are nearly the same with the execption being,
>> the
>> Server's Sort ID.
>> Thanks,
>> Joe
>>