Help with SQL query

I have a ContactTypes table as follows:-
ContactTypeId int PK
ContactType varchar(50)
I have several different types of contact types (i.e. Invoice,
Business, Manager, Home, etc).
I have a Client table as follows:
ClienttId int PK
ClientName varchar(50)
I have a ContactAddresses table as follows:-
ContactAddressId int PK
ClientId
ContactTypeId
Address1
Address2
Town
Postcode
Can anyone tell me how i can write an SQL query to fetch all the
different types of contacts for a client, regardless if they exist or
not?
The query should return something like for a selected Client:-
Contact Type Address1 Address2 Town PostCode
Invoice 123 somewhere there AB12 C12
Business 456 overthere here CD34 E21
Manager
Home 987 here where D21 S12
So far i have come up with the following but it requires separate SQL
statements for each column. Can anyone recommend a way around this?
SELECT ContactType
,(SELECT Address1 FROM ContactAddresses CA WHERE ClientId = @.clientId
AND CA.ContactTypeId = CT.ContactTypeId)
,(SELECT Address2 FROM ContactAddresses CA WHERE ClientId = @.clientId
AND CA.ContactTypeId = CT.ContactTypeId)
,(SELECT Town FROM ContactAddresses CA WHERE ClientId = @.clientId AND
CA.ContactTypeId = CT.ContactTypeId)
,(SELECT PostCode FROM ContactAddresses CA WHERE ClientId = @.clientId
AND CA.ContactTypeId = CT.ContactTypeId)
FROM ContactTypes CT
Thanks in advance for any suggestions.
DanI think you want a cross join to get all possible combinations of client and
contact type, and an outer join to get the addresses that exist, while still
keeping the contact type/client combinations that do not.
select c.clientname
, b.ContactType
, a.Address1
, a.Address2
, a.Town
, a.PostCode
from ContactTypeId b
cross join Client c
left outer join ContactAddresses a
on c.ClienttId = a.ClienttId
and c.ContactTypeID = b.ContactTypeID
<dan_williams@.newcross-nursing.com> wrote in message
news:1148503334.127146.20190@.i39g2000cwa.googlegroups.com...
> I have a ContactTypes table as follows:-
> ContactTypeId int PK
> ContactType varchar(50)
> I have several different types of contact types (i.e. Invoice,
> Business, Manager, Home, etc).
> I have a Client table as follows:
> ClienttId int PK
> ClientName varchar(50)
> I have a ContactAddresses table as follows:-
> ContactAddressId int PK
> ClientId
> ContactTypeId
> Address1
> Address2
> Town
> Postcode
>
> Can anyone tell me how i can write an SQL query to fetch all the
> different types of contacts for a client, regardless if they exist or
> not?
> The query should return something like for a selected Client:-
> Contact Type Address1 Address2 Town PostCode
> Invoice 123 somewhere there AB12 C12
> Business 456 overthere here CD34 E21
> Manager
> Home 987 here where D21 S12
> So far i have come up with the following but it requires separate SQL
> statements for each column. Can anyone recommend a way around this?
> SELECT ContactType
> ,(SELECT Address1 FROM ContactAddresses CA WHERE ClientId = @.clientId
> AND CA.ContactTypeId = CT.ContactTypeId)
> ,(SELECT Address2 FROM ContactAddresses CA WHERE ClientId = @.clientId
> AND CA.ContactTypeId = CT.ContactTypeId)
> ,(SELECT Town FROM ContactAddresses CA WHERE ClientId = @.clientId AND
> CA.ContactTypeId = CT.ContactTypeId)
> ,(SELECT PostCode FROM ContactAddresses CA WHERE ClientId = @.clientId
> AND CA.ContactTypeId = CT.ContactTypeId)
> FROM ContactTypes CT
> Thanks in advance for any suggestions.
> Dan
>

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 SQL

I have a table named "policy_details" having four columns called policy_details(varchar),effectdate(datetime),Historyid(int) and policy_status(varchar).

I want to keep only the maximum historyid records in "policy_details" where policy_details and effectdate should be equal and policy_status should be '30240084' . I want to delete the remaining records where policy_details and effectdate should be equal and policy_status should be '30240084'.

Cheers

Praveen

So if you order by policy_details ASC, affectdate ASC, historyid DESC then you only want to keep the first row from each (policy_details, affectdate) group.

I would use the ROW_NUMBER() function and delete where it's not 1

DELETE
FROM policy_details
WHERE ROW_NUMBER() OVER ( PARTITION BY policy_details, affectdate
ORDER BY historyid DESC
) > 1
AND policy_status = '30240084'

|||

Adam,

Thanks for your query.

I am getting an error "The ranking function "ROW_NUMBER" must have an ORDER BY clause."

One more thing is i want same effectdate with only date but not time part.

DELETE
FROM policy_details
WHERE ROW_NUMBER() OVER ( PARTITION BY policy_details,CONVERT(CHAR(10),effectdate,103)
ORDER BY historyid DESC
) > 1
AND policy_status = '30240084'

I did not understand "The ranking function "ROW_NUMBER" must have an ORDER BY clause" error though it is having order by clause. Any ideas?

Cheers

Praveen

|||

Use A CTE, it will work:

WITH myCTE

AS

(SELECT ROW_NUMBER() OVER ( PARTITION BY policy_details,CONVERT(CHAR(10),effectdate,103) ORDER BY historyid DESC) as num

FROM policy_details

WHERE policy_status = '30240084')

DELETE FROM myCTE WHERE num>1

|||

Interesting, when I write the following query, which is almost exactly like yours:

begin transaction

DELETE
FROM person.address
WHERE ROW_NUMBER() OVER ( PARTITION BY addressId
ORDER BY addressLine1 DESC
) > 1
and addressLine1 = 'fred'
rollback transaction

The error I get is:

Msg 4108, Level 15, State 1, Line 3
Windowed functions can only appear in the SELECT or ORDER BY clauses.

(which is more clear).

|||Hello,
After some research, I tested out this syntax which works:
DELETE c
FROM (SELECT * FROM (SELECT RANK() OVER (PARTITION BY policy_details,CONVERT(CHAR(10),effectdate,103) ORDER BY historyid DESC) as num
FROM policy_details WHERE policy_status = '30240084' ) AS t
WHERE t.num>1) c|||

Hi Limno,

Thanks for your help with the SQL which works fine with my data.

I have written a small strored proc which deletes some data based on certain criteria which works fine but it takes approximately 30 minutes for one million records. Is there any way to write this stored proc logic into a single SQl query or sub queries?

The SP is :

-- ***********************************************************************

declare @.policy_details_id uniqueidentifier

declare @.Prepolicy_details_id uniqueidentifier

declare @.EffectDate datetime

declare @.CloseDate datetime

declare @.historyid int

DECLARE DATECur CURSoR FOR

select distinct(policy_details_id),effectdate,closedate,historyid from SM_Cust_policy_Details

where closedate = '2079-06-06 00:00:00.000' and derivative = 0

order by policy_details_id,effectdate

OPEN DATECur

FETCH NEXT FROM DATECur INTO @.policy_details_id, @.effectdate,@.CloseDate,@.historyid

WHILE @.@.FETCH_STATUS = 0

BEGIN

DELETE SM_Cust_policy_details where policy_details_id=@.policy_details_id

and historyid <> @.historyid

and closedate <> '2079-06-06 00:00:00.000'

and CONVERT(DATETIME,CONVERT(CHAR(10),effectdate,103)) >= CONVERT(DATETIME,CONVERT(CHAR(10),@.effectdate,103))

and policy_status_id = 30240084

and derivative = 0

FETCH NEXT FROM DATECur INTO @.policy_details_id, @.effectdate,@.CloseDate,@.historyid

END

CLOSE DATECur

DEALLOCATE DATECur

-- ***********************************************************************

Once again thanks for your help..

Cheers

Praveen

|||Can you explain what the query is trying to do in english rather than trying to decode your criteria. The potential query may differ depending the nature of the data in your table e.g. uniqueness and nullability of the columns.|||

Hi

I need to delete the table data based on the following criteria.

1) "policy_details_id" should be same

2) "effectdate" is greater than or equal to effectdate

3) "HistoryId" should not be same

4) "closedate" is not equal to ' 2079-06-06 00:00:00.000 '

5) "policy_status_id" should be 30240084

6) "Derivative" should be 0

Cheers

Praveen

|||

I can read your code. I got that much from the stored proc you posted. I'm trying to understand what the stored procedure is trying to achieve.

Should just one row per policy_details_id remain after the delete? Why are you doing distinct of the policy_details_id in you cursor query? why delete where effectdate is greater than @.effectdate? Wouldn't that delete more recent rows.

Like I said, I'm trying to understand what this query is doing "in english" and not "in SQL". An explanation similar to your original posting is what I'm after.

|||

Hi Adam,

Thanks for your reply and here are the answers for your queries.

******** Should just one row per policy_details_id remain after the delete?

Yes, I want to keep only one row per policy_details_id after the deletion.

****** Why are you doing distinct of the policy_details_id in you cursor query?

I used distinct caluse in order to reduce the cursor result set in stored proc. Anyway you can avoid distinct caluse in the sql query.

******** why delete where effectdate is greater than @.effectdate? Wouldn't that delete more recent rows

Yes, it is going to delete more recent rows.

Cheers

Praveen

|||

Hello:

Please check this query :

DELETE c

FROM (SELECT * FROM (SELECT *, RANK() OVER (PARTITION BY policy_details_id ORDER BY historyid DESC, effectdate) as num

FROM policy_details WHERE (closedate<> '2079-06-06 00:00:00.000' OR closedate is NULL) AND Derivative=0 AND policy_status_id = 30240084 ) AS t

WHERE t.num>1) c

|||

Hi Limno,

Needs to change the query.

First i need to do the following query..

1) select policy_details_id,effectdate,closedate,historyid from SM_Cust_policy_Details where closedate = '2079-06-06 00:00:00.000' and derivative = 0

Based on the result set of this query i need to do the following delete query.

2) DELETE SM_Cust_policy_details where policy_details_id = resultset_policy_details_id and historyid <> resultset_historyid and closedate <> '2079-06-06 00:00:00.000' and CONVERT(DATETIME,CONVERT(CHAR(10),effectdate,103))>=CONVERT(DATETIME,CONVERT(CHAR(10),resultset_effectdate,103)) and policy_status_id=30240084 and derivative = 0.

Note: resultset_policy_details_id,resultset_historyid,resultset_effectdate are the result set query values of policy_details_id,historyid,effectdate in query number 1.

How can i integrate both of the above queries and make a single query?

Cheers

Praveen

|||

Could you post a set of your sample data in your table and the expected result? Thanks.

|||

The data consists like this..

policy_details_id policy_status_id historyid effectdate closedate

70C36E97-9564-A048-0000-9665018B81FF 30240084 9 2004-09-11 12:00:00.000 2004-10-11 11:34:00.000
70C36E97-9564-A048-0000-9665018B81FF 30240084 10 2004-10-11 11:34:00.000 2005-09-11 11:47:00.000
70C36E97-9564-A048-0000-9665018B81FF 30240084 11 2005-09-11 12:00:00.000 2005-09-11 12:00:00.000
70C36E97-9564-A048-0000-9665018B81FF 14075352 12 2005-09-11 11:47:00.000 2079-06-06 00:00:00.000

First i need to consider the effectdate where closedate is '2079-06-06 00:00:00.000'.In this case it is '2005-09-11 11:47:00.000'.

I need to delete the records where effectdate(only datepart) is equal or greater than '2005-09-11' and policy_status_id should be 30240084.

In the above case the third record i.e historyid = 11 is going to be deleted.

Let me know if you have any problems to understand.

Cheers

Praveen

Help with SQL

Hi, i hope i posted int he appropiated forum.
I hope you can help me with an sql sentence i dont know how to do,...

The problem is that i have a database with fields like this:

name, adress, etc.... , phone number

but sometimes, i get them like this:

paul, ... street... , etc... , 776509332
paul, ... street... , etc... , 234536445
paul, ... street... , etc... , 567847345

The client si the same, but he has more than one phone number....
I need to do, somehow, another table that makes this kind of duplicates to dissapear, and joins the phone numbers in an unique field for that person, so it will in the end look like this:

paul, ... street... , etc... , 776509332 234536445 567847345

Can anybody help me?

Thanks

Check back in a hour and I'll have something.|||Thanks!! i really need this code, i will wait patiently
|||

OK; First of all let's start by creating this function:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GETPHONENUMBER]
(
@.UserID int
)
RETURNS NVARCHAR(255)
AS
BEGIN
DECLARE @.STRINGNUMBER NVARCHAR(25)
SET @.STRINGNUMBER = ''
DECLARE @.GETNUMBER NVARCHAR(15)
DECLARE @.NUMBER NVARCHAR(15)
DECLARE USERNUMBERS CURSOR FOR
SELECT
PHONENUMBERS
FROM
TABLENUMBERS
WHERE
USERID = @.USERID

OPEN USERNUMBERS
FETCH NEXT FROM USERNUMBERS
INTO @.NUMBER

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.STRINGNUMBER = @.NUMBER + ',' + CAST(@.STRINGNUMBER AS NVARCHAR(200))
FETCH NEXT FROM USERNUMBERS
INTO @.NUMBER
END

CLOSE USERNUMBERS
DEALLOCATE USERNUMBERS

RETURN @.STRINGNUMBER
END

Now; you can call this function from inside your code like this;

SELECTDISTINCT [NAME],[MYDATABASE].[DBO].[GETPHONENUMBER](USERID)AS'MULTIPLE PHONE NUMBERS'

FROM TABLENUMBERS

WHERE USERID = 1

|||

SELECT t3.nameID, t3.Name,MAX(case t3.seq when 1 then t3.phone end)

+MAX(case t3.seq when 2 then' '+ t3.phone else''end)

+MAX(case t3.seq when 3 then' '+ t3.phone else''end)AS phone

FROM(SELECT nameID,Name, phone,(SELECTCOUNT(*)FROM phones AS t2 WHERE t2.nameID = t1.nameID and t2.phone <= t1.phone)AS seq

FROM phones AS t1

)as t3

GROUPBY t3.nameID, t3.Name

--For SQL Server 2005, you can also use this:

SELECT t3.nameID,coalesce(' '+ t3.[1],'')+coalesce(' '+ t3.[2],'')+coalesce(' '+ t3.[3],'')AS phone

FROM(SELECT nameID, phone, ROW_NUMBER()OVER(PARTITIONBY nameID ORDERBY phone)AS seq FROM phones)as t1

PIVOT(MAX(phone)for seq in([1], [2], [3]))AS t3

|||

If you are using SQL 2005, try this variation also

Createtable #tab( id int, val varchar(50))

insertinto #tab

select 1,'A'unionall

select 1,'B'unionall

select 1,'C'unionall

select 2,'D'unionall

select 2,'E'unionall

select 2,'F'unionall

select 3,'G'unionall

select 3,'H'

GO

with CTE (id,xyz)as

(

select id,xyz=val from #tab

unionall

select a.id,xyz=convert(varchar(24),xyz)+' '+convert(varchar(25),a.val)

from #tab a inner loop join CTE b

on a.id=b.id andpatindex('%'+a.val+'%',xyz)<1

)

selectdistinct id,reverse(max(xyz))from CTE

Groupby id

|||Thank you all for the help.. is for using it in access.. anyway, finally, is sql, and i think i will be able to use it as well.... thank you, really.. to all the people that has helped me
|||

For Access:

SELECT t3.nameID, t3.Name, MAX(IIF( t3.seq= 1, t3.phone+' ', ' ')) +MAX(IIF( t3.seq= 2, t3.phone+' ', ' ')) + MAX(IIF( t3.seq= 3, t3.phone, ' ')) AS phone
FROM [SELECT nameID, Name, phone, (SELECT COUNT(*) FROM phones AS t2 WHERE t2.nameID = t1.nameID and t2.phone <= t1.phone) AS seq
FROM phones AS t1]. AS t3
GROUP BY t3.nameID, t3.Name;

|||The access one will be the good one.. the only thing i need is you to clarify me what is each thing... i mean.. the table structure is just a table with 7 fields, the last one has the phones, what i want to group, and i only have that one table......
Thanks for all the help!!
|||

--For Access

SELECT t3.nameID, t3.Name, t3.Field1, t3.Field2,t3.Field3,t3.Field4, MAX(IIF( t3.seq= 1, t3.phone+' ', ' ')) +MAX(IIF( t3.seq= 2, t3.phone+' ', ' ')) + MAX(IIF( t3.seq= 3, t3.phone, ' ')) AS phone
FROM [SELECT nameID, Name, Field1, Field2,Field3,Field4, phone, (SELECT COUNT(*) FROM phones AS t2 WHERE t2.nameID = t1.nameID and t2.phone <= t1.phone) AS seq
FROM phones AS t1]. AS t3
GROUP BY t3.nameID, t3.Name, t3.Field1, t3.Field2,t3.Field3,t3.Field4;

--Assume nameID, Name, Field1, Field2,Field3,Field4, phone are your 7 fields in your table "phones".

|||Thanks i see it more clearly now...

So, t1, t2, t3 are tables, right? but i only have one....

|||

These three t1, t2, t3 are aliases for your only table. In my sample code, the only table is "phones".

You can replace your table name for "phones" and replace all fields with your table field names. HTH.

|||Ok, thanks, i hope it works!!
I will try it in my job and come back here if i find ny problem...

Thank you!

Help with SELECT please

I need to select 2 rows from a table for every SSN. DDL for the table:

CREATE TABLE [dbo].[tblResidentRotations] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ResidentProgram] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[RotationID] [int] NULL ,
[MonthName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RotationLocationID] [int] NULL ,
[CallLocationID] [int] NULL ,
[IMClinicDay] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[IMClinicDateLast] [datetime] NULL ,
[IMClinicDateFirst] [datetime] NULL ,
[PedsClinicDay] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Comments] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClinicScheduleComments] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LastFirstComments] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PGYLevel] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AcademicYear] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]

For a given MonthName, I pull SSN, IMClinicDay, IMClinicDateLast, and
IMClinicDateFirst. IMClinicDay contains data like "Monday AM",
"Tuesday PM". IMClinicDateLast and IMClinicDateFirst contains date
data in mm/dd/yy format. I need to pull IMClinicDay for the date given
in IMClinicDateFirst. For example, if my data looks like this:

SSN: 999999999
MonthName: July
IMClinicDay: Monday PM
IMClinicDateLast: 07/01/05
IMClinicDateFirst: 09/01/05

then I need to pull what the IMClinicDay would be for this SSN in
September.

Thanks for any help and advice.Stop using that silly redundant "tbl-" prefix; If nobody told you
yet, SQL only has one data structure. Then you might want to read a
basic book on data modeling - you always name a data element for what
it is, not for how it is stored, where it is used, etc.

Next, we need keys to have proper tables. An IDENTITY is **never** a
key by definition. SSN is never VARCHAR() but it is fixed length, so
all you did was invite a loss of data integrity. Ditto when you pulled
oversize numbers out of the air for the other column sizes. Since there
are no non-NOT NULL columns, you cannot ever have a key!! Think about
VARCHAR(1) and what it means.

Do you know of a month name that is CHAR(15)? Why are you using
strings for temporal data in SQL? Why are you using vague strings like
"Monday PM" for temporal data? Why did you violate ISO-8601 formats
for the bad dates?

What does the resident's academic year have to do with rotations?
The whole mess looks denormalized. Just based on a few decades of
prior experience, I would guess this ought o reduce down to something
like this:

CREATE TABLE ResidentRotations
(ssn VARCHAR(9) NOT NULL
REFERENCES Residents(ssn),
resident_program VARCHAR(20) NOT NULL,
rotation_loc INTEGER NOT NULL
REFERENCES LocationsCodes(loc_nbr),
rotation_start_time DATETIME NOT NULL,
rotation_end_time DATETIME NOT NULL,
CHECK (rotation_start_time < rotation_end_time),
call_loc INTEGER NOT NULL
REFERENCES LocationsCodes(loc_nbr),
PRIMARY KEY (ssn, rotation_start_time));

>> I need to pull what the IMClinicDay would be for this SSN in September. <<

You need to use a Calendar table and insert the scheduled shifts in
advance for the known duration. You can take care of holidays,
re-scheduling, etc. with this approach.

You might want to read Rick Snodgrass' s book on Temporal queries in
SQL after you get thru a basic data modeling book and a few ISO
standards. Pretty much everything you did was fundamentally wrong.|||(manning_news@.hotmail.com) writes:
> For a given MonthName, I pull SSN, IMClinicDay, IMClinicDateLast, and
> IMClinicDateFirst. IMClinicDay contains data like "Monday AM",
> "Tuesday PM". IMClinicDateLast and IMClinicDateFirst contains date
> data in mm/dd/yy format.

No, they are declared as datetime, which means that they are in a
binary format. If you say

SELECT * FROM tbl WHERE datecol = '07/01/05'

You could get rows from from 2007-01-05, 2005-01-07 or any other
of the six possible permutations, depending on the current settings.
On the other hand:

SELECT * FROM tbl WHERE datecol = '20070105'

will always give the same set of data.

OK, so that is not what you asked about, but since you had an apparent
misunderstanding about datetime, I figured I should point it out.

> I need to pull IMClinicDay for the date given
> in IMClinicDateFirst. For example, if my data looks like this:
> SSN: 999999999
> MonthName: July
> IMClinicDay: Monday PM
> IMClinicDateLast: 07/01/05
> IMClinicDateFirst: 09/01/05
> then I need to pull what the IMClinicDay would be for this SSN in
> September.

If I understand this correctly, you should have a look at the datename()
function in Books Online.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here's a sample of the data I have:

SSN: 999999999 (first row)
MonthName: July
IMClinicDay: Monday PM
IMClinicDateLast: 07/01/05
IMClinicDateFirst: 09/01/05

SSN: 999999999 (nth row)
MonthName: September
IMClinicDay: Wednesday PM
IMClinicDateLast: 09/01/05
IMClinicDateFirst: 10/01/05

With a SELECT statement, I want to return all of the first row and only
the IMClinicDay (Wednesday PM) of the nth row using IMClinicDateFirst
in the first row to get this data.

Thanks for any help.

Erland Sommarskog wrote:
> (manning_news@.hotmail.com) writes:
> > For a given MonthName, I pull SSN, IMClinicDay, IMClinicDateLast, and
> > IMClinicDateFirst. IMClinicDay contains data like "Monday AM",
> > "Tuesday PM". IMClinicDateLast and IMClinicDateFirst contains date
> > data in mm/dd/yy format.
> No, they are declared as datetime, which means that they are in a
> binary format. If you say
> SELECT * FROM tbl WHERE datecol = '07/01/05'
> You could get rows from from 2007-01-05, 2005-01-07 or any other
> of the six possible permutations, depending on the current settings.
> On the other hand:
> SELECT * FROM tbl WHERE datecol = '20070105'
> will always give the same set of data.
> OK, so that is not what you asked about, but since you had an apparent
> misunderstanding about datetime, I figured I should point it out.
> > I need to pull IMClinicDay for the date given
> > in IMClinicDateFirst. For example, if my data looks like this:
> > SSN: 999999999
> > MonthName: July
> > IMClinicDay: Monday PM
> > IMClinicDateLast: 07/01/05
> > IMClinicDateFirst: 09/01/05
> > then I need to pull what the IMClinicDay would be for this SSN in
> > September.
> If I understand this correctly, you should have a look at the datename()
> function in Books Online.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||I just want to know if I can get to the row I want. If it's impossible
with the structure I have then just say so.

--CELKO-- wrote:
> Stop using that silly redundant "tbl-" prefix; If nobody told you
> yet, SQL only has one data structure. Then you might want to read a
> basic book on data modeling - you always name a data element for what
> it is, not for how it is stored, where it is used, etc.
> Next, we need keys to have proper tables. An IDENTITY is **never** a
> key by definition. SSN is never VARCHAR() but it is fixed length, so
> all you did was invite a loss of data integrity. Ditto when you pulled
> oversize numbers out of the air for the other column sizes. Since there
> are no non-NOT NULL columns, you cannot ever have a key!! Think about
> VARCHAR(1) and what it means.
> Do you know of a month name that is CHAR(15)? Why are you using
> strings for temporal data in SQL? Why are you using vague strings like
> "Monday PM" for temporal data? Why did you violate ISO-8601 formats
> for the bad dates?
> What does the resident's academic year have to do with rotations?
> The whole mess looks denormalized. Just based on a few decades of
> prior experience, I would guess this ought o reduce down to something
> like this:
> CREATE TABLE ResidentRotations
> (ssn VARCHAR(9) NOT NULL
> REFERENCES Residents(ssn),
> resident_program VARCHAR(20) NOT NULL,
> rotation_loc INTEGER NOT NULL
> REFERENCES LocationsCodes(loc_nbr),
> rotation_start_time DATETIME NOT NULL,
> rotation_end_time DATETIME NOT NULL,
> CHECK (rotation_start_time < rotation_end_time),
> call_loc INTEGER NOT NULL
> REFERENCES LocationsCodes(loc_nbr),
> PRIMARY KEY (ssn, rotation_start_time));
> >> I need to pull what the IMClinicDay would be for this SSN in September. <<
> You need to use a Calendar table and insert the scheduled shifts in
> advance for the known duration. You can take care of holidays,
> re-scheduling, etc. with this approach.
> You might want to read Rick Snodgrass' s book on Temporal queries in
> SQL after you get thru a basic data modeling book and a few ISO
> standards. Pretty much everything you did was fundamentally wrong.|||(manning_news@.hotmail.com) writes:
> Here's a sample of the data I have:
> SSN: 999999999 (first row)
> MonthName: July
> IMClinicDay: Monday PM
> IMClinicDateLast: 07/01/05
> IMClinicDateFirst: 09/01/05
> SSN: 999999999 (nth row)
> MonthName: September
> IMClinicDay: Wednesday PM
> IMClinicDateLast: 09/01/05
> IMClinicDateFirst: 10/01/05
> With a SELECT statement, I want to return all of the first row and only
> the IMClinicDay (Wednesday PM) of the nth row using IMClinicDateFirst
> in the first row to get this data.

Since your table definition did not include any information about keys,
I cannot be sure that this query works:

SELECT a.SSN, a.ResidentProgram, ..., b.IMClinicDay
FROM tblResidentRotations a
LEFT JOIN b tblResidentRotations
ON a.SSN = b.SSN
AND a.IMClinicDateLast = b.IMClinicDateFirst
WHERE a.IMClinicDateFirst >= @.yearmonth + '01' AND
a.IMClincDateFirst < dateadd(MONTH, 1, @.yearmonth + '01')

I assume that @.yearmonth holds the month you are looking for on the
form YYYYMM.

--
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 Select Minimum Value

I need help in doing a select statment with a minimum
here is the statement
CREATE TABLE [Test] (
[rIndex] [int] IDENTITY (1, 1) NOT NULL ,
[Defaul_] [int] NULL ,
[FilterType] [int] NULL ,
[ProtScr] [int] NULL ,
[ProtRank] [int] NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ageStart] [int] NULL ,
[ageStop] [int] NULL ,
[Sex] [int] NULL ,
CONSTRAINT [PK_Test_1] PRIMARY KEY CLUSTERED
(
[rIndex]
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
AgeStart, AgeStop,Sex)
values
(1,1,1,5,'FOBT',50, 90,1)
insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
AgeStart, AgeStop,Sex)
values
(1,1,1,3,'Sigmoidoscopy',50, 90,1)
insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
AgeStart, AgeStop,Sex)
values
(1,1,1,5,'Colonoscopy',65, 90,1)
I have a subquery to try and get the minimum ProtScr and ProtRank
When I do the subquery
Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1)
I get 2 values
ProtScr ProtRank
1 3
1 5
if possible I want the subquery to only pick the lower value only. The main
select statement that I tried to do is located below to select the values
based on the subquery.
Select rIndex,Defaul_, FilterType, ProtScr,ProtRank, Description,
ageStart,AgeStop,sex from Test
where ProtRank EXISTS in
(Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1))
Where (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65) AND
(Sex IN (1, 2)) AND (Defaul_ = 1))
Thanks
Stephen K. MiyasatoHi
SELECT ProtScr,MIN(ProtRank) AS ProtRank
FROM
(
Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1)
) AS Der
GROUP BY ProtScr
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:OkzZlunTGHA.5496@.TK2MSFTNGP11.phx.gbl...
>I need help in doing a select statment with a minimum
> here is the statement
> CREATE TABLE [Test] (
> [rIndex] [int] IDENTITY (1, 1) NOT NULL ,
> [Defaul_] [int] NULL ,
> [FilterType] [int] NULL ,
> [ProtScr] [int] NULL ,
> [ProtRank] [int] NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ageStart] [int] NULL ,
> [ageStop] [int] NULL ,
> [Sex] [int] NULL ,
> CONSTRAINT [PK_Test_1] PRIMARY KEY CLUSTERED
> (
> [rIndex]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,5,'FOBT',50, 90,1)
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,3,'Sigmoidoscopy',50, 90,1)
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,5,'Colonoscopy',65, 90,1)
> I have a subquery to try and get the minimum ProtScr and ProtRank
> When I do the subquery
> Select ProtScr, MIN(ProtRank) AS ProtRank from test
> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1)
> I get 2 values
> ProtScr ProtRank
> 1 3
> 1 5
> if possible I want the subquery to only pick the lower value only. The
> main select statement that I tried to do is located below to select the
> values based on the subquery.
> Select rIndex,Defaul_, FilterType, ProtScr,ProtRank, Description,
> ageStart,AgeStop,sex from Test
> where ProtRank EXISTS in
> (Select ProtScr, MIN(ProtRank) AS ProtRank from test
> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1))
> Where (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1))
> Thanks
> Stephen K. Miyasato
>|||Thanks that helped
I get
protScr ProtRank
1 3
The subquery works but I could not get the main query to work
Select rIndex,Defaul_, FilterType, ProtScr,ProtRank, Description,
ageStart,AgeStop,sex from Test
where protScr, ProtRank EXISTS in -- need some help here
( -- subquery begins
SELECT ProtScr,MIN(ProtRank) AS ProtRank
FROM
(
Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1)
) AS Der
GROUP BY ProtScr
) -- subquery ends
Where (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65) AND
(Sex IN (1, 2)) AND (Defaul_ = 1))
Thanks
Stephen K. Miyasato
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e8bbKynTGHA.776@.TK2MSFTNGP09.phx.gbl...
> Hi
> SELECT ProtScr,MIN(ProtRank) AS ProtRank
> FROM
> (
> Select ProtScr, MIN(ProtRank) AS ProtRank from test
> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1)
> ) AS Der
> GROUP BY ProtScr
>
> "Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
> news:OkzZlunTGHA.5496@.TK2MSFTNGP11.phx.gbl...
>

help with query!

I have 2 tables

tblStyles
StyleID int (pk)
XMLfilename nvarchar(50)

tblRules
RuleID int (pk)
Usercode int
StyleID int (fk)
ruleindex tinyint

Now in a stored proc I want to retreive XMLfilename for the first rule that matches certain criteria.
So if Usercode 5 would have 3 rules defined with ruleindex: 8, 6 and 7 (they might be in that order in the table)
and both rule 6 and 8 would match my criteria, I would like to have the xmlfilename related to the rulerecord with ruleindex 6 (as 6 is the lowest ruleindex).

Here's what I have so far:
----------
declare @.style nvarchar(50)

set @.style=(

select XMLfileid FROM (

select us.XMLfileid,r.ruleindex from tblRules r
inner join tblUserStyles us on us.StyleID=r.StyleID
wherer.usercode=@.Usercode
order by ruleindex

) as tmp

WHERE <allmycriteria>

)
----------

I get this error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

declare @.style nvarchar(50)

set @.style=(

select XMLfileid FROM (

select Top 1 us.XMLfileid,r.ruleindex from tblRules r
inner join tblUserStyles us on us.StyleID=r.StyleID
wherer.usercode=@.Usercode
order by ruleindex asc

) as tmp

WHERE <allmycriteria>

)

Coz u need only one file name of the samlest index matched . You cannot use Order By in a Sub Query without using Top

alternative to your query you can use

1Create Procedure GetXmlFile2(3@.UserCodevarchar(10)4)5AS67BEGIN89declare @.stylenvarchar(50)1011set @.style=(1213select XMLfileidFROM tblStyleswhere StyleIdin(14Select Top 1 TR.StyleIdFROM15tblRulesINNERJOIN tblStyle TSon TS.StyleId=TR.StyleId16Where TR.usercode=@.UserCode17order by Tr.ruleindexasc18)1920WHERE21Select @.Style22END23

|||

It seems to me that having the scalar value assigned in the stored procedure is overkill. Review this and let me know if it works for you:

Create Procedure GetXmlFile( @.UserCodevarchar(10))ASBEGIN SELECT ts.XMLfileidFROM tblStyles tsWHERE ts.StyleId = (select min(tr.StyleId)from tblRules trwhere tr.usercode = @.UserCodegroup by tr.usercode )END

Help with Query

Hello I have a table simular to the following:
ID = int (Key)
Temp1 = nChar
Temp2 = nChar
Temp3 = nChar
The goal is to return something like this:
SELECT Temp1+ ' ' + Temp2+ ' ' +Temp3 AS MyValue
If Temp1 = "Hello" and Temp2 = "Mr." and Temp3 = "Chuck" it would return
"Hello Mr. Chuck".
The problem is, if Temp2 equals Nothing or DBNull I don't want it included
in MyValue.
I just want it to return "Hello Chuck".
Any assistance will be greatly appreciated,
Chuck
Use the coalesce function something like this:
SELECT COALESCE(Temp1, '')+' "+COALESCE(Temp2, '')...
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:%23qrgNRPXHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hello I have a table simular to the following:
> ID = int (Key)
> Temp1 = nChar
> Temp2 = nChar
> Temp3 = nChar
> The goal is to return something like this:
> SELECT Temp1+ ' ' + Temp2+ ' ' +Temp3 AS MyValue
> If Temp1 = "Hello" and Temp2 = "Mr." and Temp3 = "Chuck" it would return
> "Hello Mr. Chuck".
> The problem is, if Temp2 equals Nothing or DBNull I don't want it included
> in MyValue.
> I just want it to return "Hello Chuck".
> Any assistance will be greatly appreciated,
> Chuck
>

Help with Query

Hello I have a table simular to the following:
ID = int (Key)
Temp1 = nChar
Temp2 = nChar
Temp3 = nChar
The goal is to return something like this:
SELECT Temp1+ ' ' + Temp2+ ' ' +Temp3 AS MyValue
If Temp1 = "Hello" and Temp2 = "Mr." and Temp3 = "Chuck" it would return
"Hello Mr. Chuck".
The problem is, if Temp2 equals Nothing or DBNull I don't want it included
in MyValue.
I just want it to return "Hello Chuck".
Any assistance will be greatly appreciated,
ChuckUse the coalesce function something like this:
SELECT COALESCE(Temp1, '')+' "+COALESCE(Temp2, '')...
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:%23qrgNRPXHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hello I have a table simular to the following:
> ID = int (Key)
> Temp1 = nChar
> Temp2 = nChar
> Temp3 = nChar
> The goal is to return something like this:
> SELECT Temp1+ ' ' + Temp2+ ' ' +Temp3 AS MyValue
> If Temp1 = "Hello" and Temp2 = "Mr." and Temp3 = "Chuck" it would return
> "Hello Mr. Chuck".
> The problem is, if Temp2 equals Nothing or DBNull I don't want it included
> in MyValue.
> I just want it to return "Hello Chuck".
> Any assistance will be greatly appreciated,
> Chuck
>

Help with Query

I have the following table:
TableName: Customers
ID - Integer
CustomerID - VarChar
Name - VarChar
LoanNo= Int
Amount = Money
ID CustomerID Name LoanNo Amount
1 Chuck1 Chuck 1 2.00
2 Mike1 Mike 1 4.00
3 Dinah1 Dinah 1 6.00
4 James1 James 1 1.00
5 James1 James 2 3.00
6 Chuck1 Chuck 2 5.00
What I want to do is return all the Customers but only their highest LoanNo
(like MAX(LoanNo)).
Example:
ID CustomerID Name LoanNo Amount
2 Mike1 Mike 1 4.00
3 Dinah1 Dinah 1 6.00
5 James1 James 2 3.00
6 Chuck1 Chuck 2 5.00
Thanks,
Chuck> What I want to do is return all the Customers but only their highest
> LoanNo
> (like MAX(LoanNo)).
One method is with a derived table. Untested example:
SELECT
Customers.ID,
Customers.Name,
Customers.LoanNo,
Customers.Amount
FROM Customers
JOIN
(SELECT
CustomerID,
MAX(LoanNo) AS LoanNo
FROM dbo.Customers
GROUP BY CustomerID) AS MaxLoanNos ON
MaxLoanNos.CustomerID = Customers.CustomerID AND
MaxLoanNos.LoanNo = Customers.LoanNo
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:eBja%23rl7GHA.3760@.TK2MSFTNGP02.phx.gbl...
>I have the following table:
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> Name - VarChar
> LoanNo= Int
> Amount = Money
> ID CustomerID Name LoanNo Amount
> 1 Chuck1 Chuck 1 2.00
> 2 Mike1 Mike 1 4.00
> 3 Dinah1 Dinah 1 6.00
> 4 James1 James 1 1.00
> 5 James1 James 2 3.00
> 6 Chuck1 Chuck 2 5.00
> What I want to do is return all the Customers but only their highest
> LoanNo
> (like MAX(LoanNo)).
> Example:
> ID CustomerID Name LoanNo Amount
> 2 Mike1 Mike 1 4.00
> 3 Dinah1 Dinah 1 6.00
> 5 James1 James 2 3.00
> 6 Chuck1 Chuck 2 5.00
> Thanks,
> Chuck
>

help with query

I have a table UserData which amongst others contains a field UserCode (int) and Name and Country.

I also have a table UsersAndSports which contains a field UserCode and a field SportID (both int).
This table can have multiple rows for the same usercode:
UserCode SportID
12 7
12 9
12 4
15 9
15 8

I want a user on my site to search for users with an interest for the same sports (say SportID 7 OR 9).
In this case that would return UserCode 12 two times and UserCode 15 once. I just need the matching UserCode once.

I want to have a query that returns the UserCode, Name, Country AND the SportID.
What would that query be?

I tried something like this (which doesnt work ofcourse :) ):

SELECT tblUserData.UserName,UserCode,Country FROM aspnet_Users
INNER JOIN tblUserData ON aspnet_Users.UserId = tblUserData.UserID
INNER JOIN tblUserData ON tblUserData.UserCode IN (SELECT DISTINCT UserCode FROM tblUsersAndSports WHERE SportID=7 OR SportID=9)
WHERE tblUserData.Username<>''

I don't really understand. You say:

I want to have a query that returns the UserCode, Name, Country AND the SportID

But you also only want to return one user? But if a user has more than one SportID than which SportID should it be returning?|||Excuse me..I just want to return a user which has sportid x or y. I dont need to retreive the sportid's.

So when a user searches for other users who have sportid 7 OR 9, and user 1843 has sportID 7 and 9, then just usercode 1843 is returned.

I hope this clarifies my problem?|||OK, I think I understand now. In that case you can just do this:

SELECT DISTINCT UserData.UserCode
FROM UserData INNER JOIN
UsersAndSports ON UserData.UserCode = UsersAndSports.UserCode
WHERE (SportID = 7 OR SportID = 9)|||now I see it it's actually quite easy...:)
Thanks!

Help with query

Hi all,
I have the following table:
CREATE TABLE [dbo].[Company](
[CompanyID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [varchar](100) NOT NULL,
[CompanyTypeID] [int] NOT NULL,
[Active] [bit] NOT NULL)
These companies can have a variety of CompanyTypeID's. I want to
retrieve a different number of records for each CompanyTypeID in one
call.
Ex.:
DECLARE @.countType1 int
DECLARE @.countType2 int
DECLARE @.countType3 int
DECLARE @.countType4 int
DECLARE @.countType5 int
SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
UNION
SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
UNION
SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
UNION
SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
At this point, Im doing it all with a UNION and getting what I want.
IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
HAVING and use the @.count variables to return the desired number of
records per companytype.
Thanks,
VictorWhat's wrong with using the union?
you'll want to change it to a UNION ALL in the interim, though, since
there won't be any duplicates to eliminate.
ViLo wrote:
> Hi all,
>
> I have the following table:
> CREATE TABLE [dbo].[Company](
> [CompanyID] [int] IDENTITY(1,1) NOT NULL,
> [CompanyName] [varchar](100) NOT NULL,
> [CompanyTypeID] [int] NOT NULL,
> [Active] [bit] NOT NULL)
>
> These companies can have a variety of CompanyTypeID's. I want to
> retrieve a different number of records for each CompanyTypeID in one
> call.
>
> Ex.:
>
> DECLARE @.countType1 int
> DECLARE @.countType2 int
> DECLARE @.countType3 int
> DECLARE @.countType4 int
> DECLARE @.countType5 int
>
> SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
> UNION
> SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
> UNION
> SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
> UNION
> SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
>
> At this point, Im doing it all with a UNION and getting what I want.
> IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
> HAVING and use the @.count variables to return the desired number of
> records per companytype.
>
> Thanks,
> Victor
>|||ViLo (victor@.lamovidarecords.com) writes:
> These companies can have a variety of CompanyTypeID's. I want to
> retrieve a different number of records for each CompanyTypeID in one
> call.
>
> Ex.:
>
> DECLARE @.countType1 int
> DECLARE @.countType2 int
> DECLARE @.countType3 int
> DECLARE @.countType4 int
> DECLARE @.countType5 int
>
> SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
> UNION
> SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
> UNION
> SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
> UNION
> SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
>
> At this point, Im doing it all with a UNION and getting what I want.
> IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
> HAVING and use the @.count variables to return the desired number of
> records per companytype.
First of all, your query looks funny, as you say TOP 100 but as
there is no ORDER BY, you will get the 100, 200, etc as the optimizer
sees fit. Which will be neither random, nor deterministic.
If you are on SQL 2005 you can use TOP (@.count) if the arbitrary
choice is OK to you.
On SQL 2000, you probably need to use a temp table, particularly if you
want the TOP 200 by alphabet or so.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Help with Query

Hello I have a table simular to the following:
ID = int (Key)
Temp1 = nChar
Temp2 = nChar
Temp3 = nChar
The goal is to return something like this:
SELECT Temp1+ ' ' + Temp2+ ' ' +Temp3 AS MyValue
If Temp1 = "Hello" and Temp2 = "Mr." and Temp3 = "Chuck" it would return
"Hello Mr. Chuck".
The problem is, if Temp2 equals Nothing or DBNull I don't want it included
in MyValue.
I just want it to return "Hello Chuck".
Any assistance will be greatly appreciated,
ChuckUse the coalesce function something like this:
SELECT COALESCE(Temp1, '')+' "+COALESCE(Temp2, '')...
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:%23qrgNRPXHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hello I have a table simular to the following:
> ID = int (Key)
> Temp1 = nChar
> Temp2 = nChar
> Temp3 = nChar
> The goal is to return something like this:
> SELECT Temp1+ ' ' + Temp2+ ' ' +Temp3 AS MyValue
> If Temp1 = "Hello" and Temp2 = "Mr." and Temp3 = "Chuck" it would return
> "Hello Mr. Chuck".
> The problem is, if Temp2 equals Nothing or DBNull I don't want it included
> in MyValue.
> I just want it to return "Hello Chuck".
> Any assistance will be greatly appreciated,
> Chuck
>

Help with optimizing query

if I have the following tables:

Table Customer_tbl

cust_id as int (PK), ...

Table Item_tbl

item_id as int (PK), ...

Table Selected_Items_tbl

selected_item_id as int (PK), cust_id as int (FK), item_id as int (FK), ...

-

With the following query:

select cust_ID from selected_items_tbl WHERE item_id in (1, 2, n) GROUP BY cust_id, item_id HAVING cust_id in (select cust_id from selected_items_tbl where item_id = 1) AND cust_id in (select cust_id from selected_items_tbl where item_id = 2) AND cust_id in (select cust_id from selected_items_tbl where item_id = n)

-

Each of these tables has other items included. Selected_Items_tbl holds zero to many of the items from the item_tbl for each customer. If I am searching for a customer who has item 1 AND item 2 AND item n, what would be the most efficient query for this? Currently, the above query is what I am working with. However, it seems that we should be able to do this type of search in a single query (without subqueries).

E:

Maybe something like:

select cust_id
from ( select cust_id,
count (distinct item_id) as itemCount
from selected_items_tbl
where item_id in (1,2,n)
group by cust_id
having count (distinct item_id) = 3
) x

|||You shoul try this here:

select cust_ID

from selected_items_tbl tbl

WHERE EXISTS

(

select cust_id

from selected_items_tbl t1

where item_id = 1 AND t1.cust_id = tbl.cust_id

)

AND EXISTS

(

select cust_id

from selected_items_tbl t1

where item_id = 1 AND t1.cust_id = tbl.cust_id

)

AND EXISTS

(

select cust_id

from selected_items_tbl t1

where item_id = 1 AND t1.cust_id = tbl.cust_id

)

Group by cust_ID

Let me know if that helped you.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

help with Interactive Sort on a column...

I have been selecting the column name textbox at the top of a column
with an int value in sql server.
When I go to Interactive Sort/Sort Expression, I just put the same
expression as the one below the column name in the details "=Fields!
qtySold.Value".
The results is not what I was hoping for:
981
90
9
876
800
8
777
76
7
Instead of:
981
876
800
777
90
76
9
8
7
Any help is appreciated.
Thanks,
Trinttrint wrote:
> I have been selecting the column name textbox at the top of a column
> with an int value in sql server.
> When I go to Interactive Sort/Sort Expression, I just put the same
> expression as the one below the column name in the details "=Fields!
> qtySold.Value".
> The results is not what I was hoping for:
> 981
> 90
> 9
> 876
> 800
> 8
> 777
> 76
> 7
> Instead of:
> 981
> 876
> 800
> 777
> 90
> 76
> 9
> 8
> 7
> Any help is appreciated.
> Thanks,
> Trint
Just a guess, but it looks like it's sorting it as if it's text and not
a number. Hope I'm not pointing out the obvious. Have you tried
converting the value that you're sorting on to an integer explicitly?
James
--

Help with inserting multiple records using a CSV value.

I have the Temporary table:

ItemDetailID (int)
FieldID (int)
FieldTypeID (int)
ReferenceName (Varchar(250))
[Value] (varChar(MAX))

in one instance Value might equal: "1, 2, 3, 4"

This only happens when FieldTypeID = 5.

So, I need an insert query for when FieldTypeID = 5, to insert 5 rows into the TableFieldListValues(ItemDetailID, [value])

I have created a function to split the [Value] into a table of INTs

Any Advice?

If your function returns a table type data, loop through the table and do an INSERT for each row.

|||

I would love to do that... but... I can program my way out of a box using C#... with SQL.. i could probably take a baby step to the bathroom :\

Do you know of any links/resources/source that could show me how? I've googled like crazy, but no luck :(

|||

You could do an :

(1) Declare a table variable with an additional column Processed tinyint.

(2) INSERT INTO @.table

SELECT dbo.someFunction

(3) Loop through the table.

WHILE EXISTS (SELECT * FROM @.table Where Procesed = 0)

Begin

Get the values from the @.table

Insert into the Original table

update @.table set processed = 1 Where Condition

End

|||

I think I understand...

While Loops, So when you do the:
WHILE EXISTS(SELECT * FROM @.Table WHERE Processed = 0)
BEGIN

END

It goes through it row by row, sort of like a Foreach(DataROw row in DataTable) in C#?

|||

RTernier:

I think I understand...

While Loops, So when you do the:
WHILE EXISTS(SELECT * FROM @.Table WHERE Processed = 0)
BEGIN

END

It goes through it row by row, sort of like a Foreach(DataROw row in DataTable) in C#?

Yes.

|||

That would work. Now another question (Yea, I'm not that strong in SQL :P )

While I go through the WHILE loop,

Is there a way I can grab the values of the loop I'm going through?

Example:

WHILE EXISTS(SELECT * FROM @.Table WHERE Processed = 0)
BEGIN

END

====

I could do this right:

WHILE EXISTS(SELECT * FROM @.Table T WHERE Processed = 0)
BEGIN

PRINT T.MyColumn
END

===

if not, how can I directly access the values from T?

|||

If the values returned by your function are unique, then you can use a MIN(Id) to get each id, else you can add an IDENTITY column to your table variable and use that to navigate through each row.

Decare @.rowid int

WHILE ...

Begin

SELECT @.rowid = MIN(id) FROM @.Table Where Processed = 0

INSERT INTO ...original table

Update @.t Set Processed = 1 Where Id = @.Rowid

End

Help with insert query

TblA TblB
---
ColA int Identity ColB char(5)
ColB char(5) FK
ColC char(6)
ColD char(50)
I would like to add rows to TblA where TblA/ColB would be populated
from TblB and ColC and ColD would be literals for each row added. ColC
= 'XXXX' and ColD = 'Suspense'
ColB and ColC in TblA combine to make a referenced key. There are
already some rows which are in TblA.
For example,
INSERT INTO TblA
(ColB, ColC, ColD)
VALUES ((
SELECT ColB
FROM TblB
WHERE ColB NOT IN (
SELECT ColB
FROM TblA
WHERE ColC = 'XXXX'
)), 'XXXX', 'Suspense')
Is there any way to do this?
TIA Lars> TblA TblB
> ---
> ColA int Identity ColB char(5)
> ColB char(5) FK
> ColC char(6)
> ColD char(50)
> I would like to add rows to TblA where TblA/ColB would be populated
> from TblB and ColC and ColD would be literals for each row added. ColC
> = 'XXXX' and ColD = 'Suspense'
> ColB and ColC in TblA combine to make a referenced key. There are
> already some rows which are in TblA.
> For example,
> INSERT INTO TblA
> (ColB, ColC, ColD)
> VALUES ((
> SELECT ColB
> FROM TblB
> WHERE ColB NOT IN (
> SELECT ColB
> FROM TblA
> WHERE ColC = 'XXXX'
> )), 'XXXX', 'Suspense')
>
INSERT INTO TblA(ColB, ColC, ColD)
SELECT ColB, 'XXXX', 'Suspense'
FROM TblB
WHERE ColB NOT IN (
SELECT ColB
FROM TblA
WHERE ColC = 'XXXX')
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Hope i understood you right:
Insert Into TblA
Select 'SomethingforA',
ColB,
'XXXX',
'Suspense'
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
news:df286192dm1u9468c1i6bqfkulc5pfmoh5@.
4ax.com...
> TblA TblB
> ---
> ColA int Identity ColB char(5)
> ColB char(5) FK
> ColC char(6)
> ColD char(50)
> I would like to add rows to TblA where TblA/ColB would be populated
> from TblB and ColC and ColD would be literals for each row added. ColC
> = 'XXXX' and ColD = 'Suspense'
> ColB and ColC in TblA combine to make a referenced key. There are
> already some rows which are in TblA.
> For example,
> INSERT INTO TblA
> (ColB, ColC, ColD)
> VALUES ((
> SELECT ColB
> FROM TblB
> WHERE ColB NOT IN (
> SELECT ColB
> FROM TblA
> WHERE ColC = 'XXXX'
> )), 'XXXX', 'Suspense')
> Is there any way to do this?
> TIA Lars|||Try,
INSERT INTO TblA (ColB, ColC, ColD)
SELECT ColB, 'XXXX', 'Suspense'
FROM TblB
WHERE
ColB NOT IN (
SELECT ColB
FROM TblA
WHERE ColC = 'XXXX'
);
AMB
"larzeb" wrote:

> TblA TblB
> ---
> ColA int Identity ColB char(5)
> ColB char(5) FK
> ColC char(6)
> ColD char(50)
> I would like to add rows to TblA where TblA/ColB would be populated
> from TblB and ColC and ColD would be literals for each row added. ColC
> = 'XXXX' and ColD = 'Suspense'
> ColB and ColC in TblA combine to make a referenced key. There are
> already some rows which are in TblA.
> For example,
> INSERT INTO TblA
> (ColB, ColC, ColD)
> VALUES ((
> SELECT ColB
> FROM TblB
> WHERE ColB NOT IN (
> SELECT ColB
> FROM TblA
> WHERE ColC = 'XXXX'
> )), 'XXXX', 'Suspense')
> Is there any way to do this?
> TIA Lars
>