Absurd project: select

Showing posts with label select. Show all posts

Friday, March 30, 2012

HELP in "Where" clause

Hi,
I would like to pass a stream of varchar as my "where" clause as follow.
Select * from test where + @.Filters
I can't get this work until I declare the SQL as varchar and then execute th
e query as follow:
DECLARE @.SQL VARCHAR(2000)
SET @.SQL = 'Select * from test where ' + @.Filters
EXEC (@.SQL)
Is there a way that I can implement the SP in my first example?Hi,
You can do like this;
declare @.filters varchar(30)
set @.filters='1 =1'
exec('Select * from test where '+ @.Filters)
You can change the where clause based on ur requirement.
Thanks
Hari
MCDBA
"Abi" <anonymous@.discussions.microsoft.com> wrote in message
news:4E5BACA6-9C0B-4643-A3DD-C65375830D37@.microsoft.com...
> Hi,
> I would like to pass a stream of varchar as my "where" clause as follow.
> Select * from test where + @.Filters
> I can't get this work until I declare the SQL as varchar and then execute
the query as follow:
> DECLARE @.SQL VARCHAR(2000)
> SET @.SQL = 'Select * from test where ' + @.Filters
> EXEC (@.SQL)
> Is there a way that I can implement the SP in my first example?|||No, you need dynamic SQL for that. But please consider below first:
http://www.sommarskog.se/dynamic_sql.html
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Abi" <anonymous@.discussions.microsoft.com> wrote in message
news:4E5BACA6-9C0B-4643-A3DD-C65375830D37@.microsoft.com...
> Hi,
> I would like to pass a stream of varchar as my "where" clause as follow.
> Select * from test where + @.Filters
> I can't get this work until I declare the SQL as varchar and then execute
the query as follow:
> DECLARE @.SQL VARCHAR(2000)
> SET @.SQL = 'Select * from test where ' + @.Filters
> EXEC (@.SQL)
> Is there a way that I can implement the SP in my first example?

Help in "Limit"

we all know
mysql: select * from table limit ?1, ?2

equals

sqlserver: SELECT TOP ?2 *
FROM table WHERE (IDENTITYCOL NOT IN
(SELECT TOP ?1 IDENTITYCOL
FROM table order by IDENTITYCOL))
order by IDENTITYCOL

but the below SQL in mysql,how to convert?I enmesh.........
select pageid,pagename,pageaddr,pageauditflag,pageartaudi tflag,startplaytime
from pageinfo where entryid= ?1 and startplaytime= ?2
limit ?3, ?4

thanks!do it the same way -- pretend ?1 and ?2 don't exist, put ?3 where ?1 is and ?4 where ?2 is in the TOP query|||do it the same way -- pretend ?1 and ?2 don't exist, put ?3 where ?1 is and ?4 where ?2 is in the TOP query
you mean if the query is:
select * from AselectPageInfo_view where entryid=1 and startplaytime='2006-03-08' limit 0,1
I can convert it to below:
SELECT TOP 1 * FROM (select * from AselectPageInfo_view where
entryid=1 and startplaytime='2006-03-08' ) WHERE (pageid NOT IN (SELECT
TOP 0 pageid FROM (select * from AselectPageInfo_view where entryid='1'
and startplaytime='2006-03-08' ) order by pageid)) order by pageid

right?
but it can't run!
what's wrong?
Please help!thanks!|||oh,May be this is right!
SELECT TOP 1 * FROM AselectPageInfo_view WHERE
(pageid NOT IN (SELECT TOP 0 pageid FROM
AselectPageInfo_view where
entryid='1' and startplaytime='2006-03-08' order by pageid))
and (entryid='1' and startplaytime='2006-03-08') order by pageid

Expert,Please check it,is it right?
Thank you from bottom of my heart!|||SELECT TOP 0 is not going to return anything, is it?

:)|||no，it return result( one line )
expert, the query that i write in #4 is right?|||i mean
select * from AselectPageInfo_view where entryid=1 and
startplaytime='2006-03-08' limit 0,1
whether equals
SELECT TOP 1 * FROM AselectPageInfo_view WHERE
(pageid NOT IN (SELECT TOP 0 pageid FROM
AselectPageInfo_view where
entryid='1' and startplaytime='2006-03-08' order by pageid))
and (entryid='1' and startplaytime='2006-03-08') order by pageid|||please tell me how many rows you get for this --

SELECT TOP 0 pageid FROM
AselectPageInfo_view where
entryid='1' and startplaytime='2006-03-08' order by pageid|||oh,it's zero|||do you know why?|||i change the 0 to 1
that can get one row
the 0 is reason?|||hello,expert,why we need know the result? 1 and 0 we can change to another parameter,
i just want to know the two query whether equals?|||no, they are not the same|||oh,expert,can you tell me which query can equals
select * from AselectPageInfo_view where entryid=1 and
startplaytime='2006-03-08' limit 0,1
because the limit can't be used in mssql|||that query just gets 1 row -- and since there's no ORDER BY, it could be any row

so the equivalent in microsoft sql server isselect top 1 * from AselectPageInfo_view where entryid=1 and
startplaytime='2006-03-08'|||and please stop calling me "expert"

i have a name|||ok,I know,Rudy Limeback :)|||that query just gets 1 row -- and since there's no ORDER BY, it could be any row

so the equivalent in microsoft sql server isselect top 1 * from AselectPageInfo_view where entryid=1 and
startplaytime='2006-03-08'

well,if i change the reference variables, your query is not right
i mean if the query like this:

select * from AselectPageInfo_view where entryid=1 and
startplaytime='2006-03-08' limit 5,10
depend on your opinion,I should write the query like below,right?
select top 10 * from AselectPageInfo_view where entryid=1 and
startplaytime='2006-03-08'|||well,if i change the reference variables, your query is not right i did not say you could change my query and still get similar results

all you asked was the equivalence to LIMIT 1, and i gave you that equivalence

if you want the equivalent to LIMIT 5,10, you need to first ask yourself what is the ORDER BY that determines the sequence of rows?|||i did not say you could change my query and still get similar results

all you asked was the equivalence to LIMIT 1, and i gave you that equivalence

if you want the equivalent to LIMIT 5,10, you need to first ask yourself what is the ORDER BY that determines the sequence of rows?
of course,it ispageid|||please show your entire query, i do not see pageid anywhere in there|||ok,i want to change a SQL which in mysql to mssql

select pageinfo.pageid,pageinfo.pagename,pageinfo.pageadd r,
pageinfo.pageauditflag,pageinfo.pageartauditflag,p ageinfo.startplaytime
from pageinfo where pageinfo.entryid=?1 and pageinfo.startplaytime=?2
limit ?3,?4"

first, i creat a view

CREATE VIEW dbo.AselectPageInfo_view
AS
SELECT pageid, pagename, pageaddr, pageauditflag, pageartauditflag,
startplaytime, entryid
FROM appsdb.pageinfo

second, i need change the query

select * from AselectPageInfo_view where entryid=?1 and
startplaytime=?2 limit ?3,?4

but i don't know whether the query which i changed is right?|||thank you for your kindly help:)|||i am going to say this only one more time

where is your ORDER BY clause?

without an ORDER BY clause, nothing you do will turn out correctly|||Rydy, I applaud your patience!|||Rydy, I applaud your patience!
My bad! .. Rudy|||thanks for the kind words, GDMI

i do like to give people lots of opportunity to catch up, eh

;)|||oh，I know
thank you！|||<<< quoted text and followup removed >>>

and rudy, you are my favorite expert.|||thanks, sean, this thread has been quite a challenge for me, and i'm not completely happy with my performance (you can see in a couple of places where my exasperation shows through)

it's nice to get positive feedback :)|||Rudy - you are my favourite Database Guru of the Year (2005) too :)|||Rudy - you are my favourite Database Guru of the Year (2005) too :)

he's been my favorite since a long time... :D

help how to create a row progressive..

Hi to everybody, I'd like to know how to extract a progressive number in a
select.
Example..

Select * from employers

Name Surname Position Age Hire Date Progressive
Mario Rossi Consultant 25 26/07/2003 1 (this
field doeas not exists in table)
fredrick Bauman Secretary 30 15/06/1999 2
Liana Orfei Director 45 15/01/1970 3
...................................... .....................
...........n

Thanks to every bodyHere's an example from the Pubs database:

SELECT
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id)
,*
FROM Authors AS A

--
David Portas
SQL Server MVP
--|||If you can afford to use an intermediate temp table:

select IDENTITY(int,1,1) as ProgressiveNumber into #Employers
select * from #Employers

MSSQL requires a table (even a temp table) on which to hang the identity
state info.

" Pecos Bill" <pecos.bill@.yahoo.it> wrote in message
news:6KlCc.15581$c_1.447390@.twister1.libero.it...
> Hi to everybody, I'd like to know how to extract a progressive number in a
> select.
> Example..
> Select * from employers
> Name Surname Position Age Hire Date Progressive
> Mario Rossi Consultant 25 26/07/2003 1 (this
> field doeas not exists in table)
> fredrick Bauman Secretary 30 15/06/1999 2
> Liana Orfei Director 45 15/01/1970 3|||> SELECT
> (SELECT COUNT(*)
> FROM Authors
> WHERE au_id <= A.au_id)
> ,*
> FROM Authors AS A

thanks...I'll try soon.|||"Mischa Sandberg" <mischa_sandberg@.telus.net> ha scritto nel messaggio
news:72pCc.6022$E84.827@.edtnps89...
> If you can afford to use an intermediate temp table:
> select IDENTITY(int,1,1) as ProgressiveNumber into #Employers
> select * from #Employers
> MSSQL requires a table (even a temp table) on which to hang the identity
> state info.

thanks...I'll try soon.
alessandro|||>> I'd like to know how to extract a progressive number in a select.
<<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access
or ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. In SQL and tiered architectures, things that deal with a
PHYSICAL ordering are done in the front end, never in the database.

Wednesday, March 28, 2012

Help for Newbie!

I have a table with 10 records: 10, 9, 8, 7 ... and so on. I want to SELECT the top 3 records starting with the second record on the list (which in this case is 9.) So the result should be 9, 8, and 7. What would be the correct SQL statement for this?

I tried "SELECT TOP 3 * FROM tableXXX WHERE (FieldID<>(SELECT TOP 1 * FROM tableXXX ORDER BY FieldID DESC) ORDER BY FieldID DESC" but sure as heck it didn't work. Can anyone help?drop the * on the 2nd select and only qry for the pkval :

SELECT Top 3 *
FROM Contacts
WHERE idCont <> (SELECT Top 1 idCont FROM Contacts)|||Thank you, Mike. It works beautifully! The complete statement is:

SELECT Top 3 *
FROM tbl_XXX
WHERE (idXXX <>(SELECT Top 1 idXXX FROM tbl_XXX ORDER BY idXXX DESC))
ORDER BY idXXX DESC

Again, thank you. You are great help.

Help for INTRO function

Hello.
I need to create a new table from a query result, then I've written this SQL
command:
"SELECT Name, Number FROM Friends INTRO Result"
BUT
SQL give me an error!!
Please Help Me
Bye
CrisI assume you mean INTO not INTRO
SELECT Name, Number INTO result FROM Friends
http://sqlservercode.blogspot.com/|||Cristian wrote:
> Hello.
> I need to create a new table from a query result, then I've written this S
QL
> command:
> "SELECT Name, Number FROM Friends INTRO Result"
> BUT
> SQL give me an error!!
> Please Help Me
> Bye
> Cris
It's INTO not INTRO.
However, the resulting table won't have any keys or constraints.
Although SELECT INTO is handy as a quick and dirty way to create
tables, it may be better to do something like:
CREATE TABLE Result (name VARCHAR(50) NOT NULL, number INTEGER NOT
NULL, PRIMARY KEY ...)
INSERT INTO Result (name, number)
SELECT name, number
FROM Friends ;
David Portas
SQL Server MVP
--|||Also,
If you need an autonumber in the destination table and need the data ordered
you must create the table first.
Good code:-
Create table myTable ( id int identity(1,1) not null, name varchar(50) not
null )
INSERT INTO myTable ( name ) SELECT name FROM friends ORDER BY name
if you use "select identity(int,1,1) AS id , name into MyTable from ..."
the order is not guaranteed and so it is advised against.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1134382059.521678.242420@.g44g2000cwa.googlegroups.com...
> Cristian wrote:
SQL
> It's INTO not INTRO.
> However, the resulting table won't have any keys or constraints.
> Although SELECT INTO is handy as a quick and dirty way to create
> tables, it may be better to do something like:
> CREATE TABLE Result (name VARCHAR(50) NOT NULL, number INTEGER NOT
> NULL, PRIMARY KEY ...)
> INSERT INTO Result (name, number)
> SELECT name, number
> FROM Friends ;
> --
> David Portas
> SQL Server MVP
> --
>

Monday, March 26, 2012

Help finding the Max Total

Hi,

I have the following code

SELECT
PR.WBS2,
SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR'
THEN Ledgerar.amount * - 1
ELSE '0' END) AS BillExt
FROM PR
LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND
PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND
pr.wbs1 = '001-298'
GROUP BY PR.WBS2

It prints out:
Wbs2 BillExt
0141 0
0143 0
1217 20580

I want the code to return the wbs2 code '1217' because it has the highest amount in BillExt '20580'.

Can someone help me with this?

Thanks.
lauraThe quick and dirty version...

select top 1 a.WBS2, a.BillExt
from
(SELECT
PR.WBS2,
SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR'
THEN Ledgerar.amount * - 1
ELSE '0' END) AS BillExt
FROM PR
LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND
PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND
pr.wbs1 = '001-298'
GROUP BY PR.WBS2) a
order by BillExt desc|||Nothing dirty about it...|||That would be nice if there was only 1 record that needed to be returned.
select a.WBS2, BillExt=max(a.BillExt)
from
(SELECT
PR.WBS2,
SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR'
THEN Ledgerar.amount * - 1
ELSE '0' END) AS BillExt
FROM PR
LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND
PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND
pr.wbs1 = '001-298'
GROUP BY PR.WBS2) a
group by a.WBS2
order by BillExt desc|||Now going a little further once I find the maximum total what if I then have to insert a value in that record. Is there a way to do this without using subqueries and instead using case statements?

For instance :

Wbs2 BillExt MaxValue
0141 0 0
0143 0 0
1217 20580 1

Thanks,
Laura|||That would be nice if there was only 1 record that needed to be returned.

Which is wat she asked for...I left my mind reading hat at home...

Laura....INSERT What to Where?

Insert into that record?

I don't understand.|||I'm sorry it's hard to describe what I am asking.

If I have the following information in a table:

WBS1 Amount MAX
0141 0
1217 2
1222 200

I first want to find the maximum of amount which is 200. Next, I want to put a 1 in the MAX column where the largest sum appears in amount.

Ending result:

WBS1 Amount MAX
0141 0
1217 2
1222 200 1

Is this possible?|||Well, once you know the ID of the record with the highest value you can issue and UPDATE statement to set its MAX field value to 1. But you will also need to issue an UPDATE statement to reset all other MAX field values that might have been the largest value before.

I can't recommend marking a record as the "Maximum value" when that status can change at any time. It is better to have a function or view or stored procedure that finds the maximum record dynamically.

Why do you want to mark this record and what are you going to do with it?|||Well, I simplified this example quite a bit to generate ideas for myself. But What I am supposed to do is create a report for accounting.

Accounting bills there clients based on services provided. Each service is designated codes. What is supposed to happen in one of the reports is to print the total reimbursable amount into the service that was billed the most.

There is no insertion are update that can be done it is just for display purposes only.

One of the restrictions that I have been under is that I cannot use subqueries only case statements which makes it even more difficult.

So I may have to go an entirely different path.

Thanks for your help,
Laura|||You can't use subqueries? What kind of lunacy is that?

You could still use a stored proc that first loads the MAX value's primary key into a variable and then uses the variable in subsequent queries.

Can't use subqueries? Kindly direct the person who gave you that directive to this informative website:

http://www.hov-hov.dk/you.htm|||Well, I simplified this example quite a bit to generate ideas for myself. But What I am supposed to do is create a report for accounting.

Accounting bills there clients based on services provided. Each service is designated codes. What is supposed to happen in one of the reports is to print the total reimbursable amount into the service that was billed the most.

There is no insertion are update that can be done it is just for display purposes only.

One of the restrictions that I have been under is that I cannot use subqueries only case statements which makes it even more difficult.

So I may have to go an entirely different path.

Thanks for your help,
LauraCan you "cheat" and JOIN a virtual table? Technically that isn't a sub-query.

-PatP

Side note to Blindman, I'm going to have to bookmark that site!

Help extracting data from second row of result set

How do I select a field from the second row of the top two rows of data selected from a table.

(SELECT TOP(2) scp.DisplayName

FROM dbo.StudentContact sc

INNER JOIN dbo.Person scp on scp.PersonID = sc.ContactID

WHERE sc.StudentID = stu.StudentID AND sc.IsCustodian = 1 AND sc.ReceivesMailings = 1

Order By scp.LastName,

scp.FirstName

I want to select the top two rows and select the name of the person in the second row to appear in my result.

Try:

select top 1 DisplayName

from

(

SELECT TOP(2) scp.DisplayName, scp.LastName, scp.FirstName

FROM dbo.StudentContact sc

INNER JOIN dbo.Person scp on scp.PersonID = sc.ContactID

WHERE sc.StudentID = stu.StudentID AND sc.IsCustodian = 1 AND sc.ReceivesMailings = 1

Order By scp.LastName,

scp.FirstName

) as t

order by LastName DESC, FirstName DESC

If you are using SQL Server 2005, then use:

;with cte

(

SELECT scp.DisplayName, row_number() over(order by scp.LastName, scp.FirstName) as rn

FROM dbo.StudentContact sc

INNER JOIN dbo.Person scp on scp.PersonID = sc.ContactID

WHERE sc.StudentID = stu.StudentID AND sc.IsCustodian = 1 AND sc.ReceivesMailings = 1

)

select DisplayName

from cte

where rn = 2;

AMB

|||

Thanks for your reply, I am using SQL Server 2005 so I opted for the second option. I have incorporated it into my query but I am getting a null value for the second guardian. I am including the entire query this time, sorry I didn't do this the first time. If you can give any help it would be greatly appreciated.

IF OBJECT_ID('dbo.p_Export') IS NOT NULL

DROP PROCEDURE [dbo].[p_Export]

<Purpose>This query produces a list student information with their lunch codes for import into Comalex Cafe.</Purpose>

Coded against version history file 7/16/07 12:25pm

EXEC p_Export @.XmlAcadSessionList = '<root><item ID="18" /><item ID="282" /><item ID="9" /><item ID="10" /><item ID="11" /><item ID="12" /></root>'

CREATE PROCEDURE p_Export

(

@.XmlAcadSessionList XML,

@.StudentFilterID INT = NULL

)

;with Guardian

(

SELECT sc.StudentID,scp.DisplayName, row_number() over(order by sc.StudentID,scp.LastName, scp.FirstName) as rn

FROM dbo.StudentContact sc

INNER JOIN dbo.Person scp on scp.PersonID = sc.ContactID

WHERE sc.IsCustodian = 1 AND sc.ReceivesMailings = 1

)

SELECT

Snum = Substring(stu.StudentNumber,2,14),

Lname = stu.LastName,

Fname = stu.FirstName,

DOB = REPLICATE('0', 2 - LEN(CONVERT(VARCHAR(2),DATEPART(mm,stu.DateOfBirth)))) + CONVERT(VARCHAR(2),DATEPART(mm,stu.DateOfBirth))

+ REPLICATE('0', 2 - LEN(CONVERT(VARCHAR(2),DATEPART(dd,stu.DateOfBirth)))) + CONVERT(VARCHAR(2),DATEPART(dd,stu.DateOfBirth))

+ (CONVERT(VARCHAR(4),DATEPART(yyyy,stu.DateOfBirth))),

[Status] = CASE WHEN

lp.StateCode = '1'

THEN 'D'

WHEN lp.StateCode = '2'

THEN 'R'

WHEN lp.StateCode = '3'

THEN 'F'

WHEN lp.StateCode NOT IN ('2', '3')

THEN 'D'

END,

Fill3 = space(20),

GradeLevel = sas.GradeLevelName,

Home = (SELECT stf.DisplayName FROM dbo.Staff stf

INNER JOIN dbo.Homeroom hm ON hm.HomeroomID = sas.HomeroomID

WHERE stf.StaffID = hm.TeacherID),

Race = (SELECT TOP(1) (CASE WHEN e.StateCode = 'H' THEN 'O' WHEN e.StateCode NOT IN ('W', 'B', 'A', 'I') THEN 'O' Else e.StateCode END)--need to add code to set code to O for H

FROM dbo.Person p

LEFT JOIN dbo.PersonEthnicity pe on pe.PersonID = p.PersonID

INNER JOIN dbo.Ethnicity e on e.EthnicityID = pe.EthnicityID

WHERE p.StudentNumber = stu.StudentNumber

ORDER BY pe.IsPrimary DESC

Asnum = stu.AltStudentNumber,

[Language] = CASE WHEN lan.[Name] Like 'Spanish'

THEN 'S'

ELSE 'E'

END,

Addr1 = RTRIM(adr.StreetNumber) + ' ' +RTRIM(adr.AddressLine1),

City = adr.City,

[State] = adr.State,

Zip = adr.PostalCode,

Phone = pt.AreaCode+pt.LocalNumber,

Fill4 = space(20),

[Gname1]= (SELECT TOP(1) scp.DisplayName

FROM dbo.StudentContact sc

INNER JOIN dbo.Person scp on scp.PersonID = sc.ContactID

WHERE sc.StudentID = stu.StudentID AND sc.IsCustodian = 1 AND sc.ReceivesMailings = 1

Order By scp.LastName,

scp.FirstName

Gname2= (Select g.DisplayName from dbo.studentacadsession sas1 INNER JOIN Guardian g ON g.StudentID = sas1.StudentID where g.StudentID = sas.StudentID AND g.rn = 2),

Fill5 = space(20),

Fill6 = space(20),

Fill7 = space(20),

Fill8 = space(20),

Fill9 = space(20),

Fill10 = space(20),

Fill11 = space(20),

Fill12 = space(20),

Fill13 = space(20),

Fill14 = space(20),

Fill15 = space(20),

Fill16 = space(20),

Fill17 = space(20),

Fill18 = space(20),

Fill19 = space(20),

Fill20 = space(20),

Fill21 = space(20),

Fill22 = space(20),

Fill23 = space(20),

Fill24 = space(20),

Fill25 = space(20),

Fill26 = space(20),

Fill27 = space(20),

Fill28 = space(20),

Fill29 = space(20),

Active = CASE When sas.CurrentEnrollmentStatus = 'C'

THEN 'T'

ELSE 'F'

END

FROM

dbo.StudentAcadSession sas

INNER JOIN dbo.AcadSession asn ON asn.AcadSessionID = sas.AcadSessionID

INNER JOIN dbo.Student stu ON stu.StudentID = sas.StudentID

LEFT JOIN dbo.LunchProgram lp ON lp.LunchProgramID = sas.LunchProgramID

LEFT JOIN dbo.PersonLanguage pl ON pl.PersonID = sas.StudentID

LEFT JOIN dbo.Language lan ON lan.LanguageID = pl.LanguageID

LEFT JOIN dbo.Address adr on adr.AddressID = stu.MailingAddressID

LEFT JOIN dbo.PersonTelephone pt ON pt.PersonID = sas.StudentID

AND pt.IsPrimary = 1

WHERE

(EXISTS

(

SELECT

FROM

@.XmlAcadSessionList.nodes('/root/item') X(a)

WHERE

X.a.value('@.ID','INT') = asn.AcadSessionID

)

AND

asn.IsClosed = 0)

AND

( ( @.StudentFilterID IS NULL ) OR ( EXISTS( SELECT * FROM dbo.[FilterKeySet] fks WHERE fks.[FilterID] = @.StudentFilterID AND fks.[ItemID] = sas.[StudentID] ) ) )

ORDER BY stu.StudentNumber

|||

I would like to help, but how can I reproduce your problem?

Can you post the estructure of the tables involved and sample data?

AMB

|||

tbl 1

IF OBJECT_ID('dbo.Person') IS NOT NULL

DROP TABLE [dbo].[Person]

CREATE TABLE [dbo].[Person] (

[PersonID] INT IDENTITY (3,1) NOT NULL

CONSTRAINT [CPK_Person] PRIMARY KEY CLUSTERED ( [PersonID] ),

[PersonGUID] UNIQUEIDENTIFIER NOT NULL

CONSTRAINT [UNCC_Person_PersonGUID] UNIQUE NONCLUSTERED ( [PersonGUID] ),

[PersonNumber] VARCHAR(15) NOT NULL

CONSTRAINT [UNCC_Person_PersonNumber] UNIQUE NONCLUSTERED ( [PersonNumber] ),

[StudentNumber] VARCHAR(15) NOT NULL

CONSTRAINT [DF_Person_StudentNumber] DEFAULT(''),

[StaffNumber] VARCHAR(15) NOT NULL

CONSTRAINT [DF_Person_StaffNumber] DEFAULT(''),

[AltPersonNumber] VARCHAR(15) NULL,

[DisplayName] VARCHAR(255) NOT NULL,

[FirstName] VARCHAR(50) NOT NULL

CONSTRAINT [DF_Person_FirstName] DEFAULT(''),

[MiddleName] VARCHAR(50) NOT NULL

CONSTRAINT [DF_Person_MiddleName] DEFAULT(''),

[LastName] VARCHAR(75) NOT NULL,

[PreferredName] VARCHAR(75) NOT NULL

CONSTRAINT [DF_Person_PreferredName] DEFAULT(''),

[DateOfBirth] DATETIME NULL,

[SocialSecurityNumber] CHAR(9) NOT NULL

CONSTRAINT [DF_Person_SocialSecurityNumber] DEFAULT(''),

[ProperTitleID] SMALLINT NULL,

[GenerationID] SMALLINT NULL,

[GenderID] SMALLINT NULL,

[GenderDescriptor] VARCHAR(50) NULL,

[EducationLevelID] SMALLINT NULL,

[MaritalStatusID] SMALLINT NULL,

[ReligionID] SMALLINT NULL,

[ResidencyStatusID] SMALLINT NULL,

[PhotographID] INT NULL,

[MailingAddressID] INT NULL,

[PhysicalAddressID] INT NULL,

[UserID] INT NULL,

[CountryOfResidence] CHAR(3) NOT NULL

CONSTRAINT [DF_Person_CountryOfResidence] DEFAULT(''),

[EmployerName] VARCHAR(150) NOT NULL

CONSTRAINT [DF_Person_EmployerName] DEFAULT(''),

[IsHomeless] BIT NOT NULL

CONSTRAINT [DF_Person_IsHomeless] DEFAULT (0),

[RowVersion] UNIQUEIDENTIFIER NOT NULL

CONSTRAINT [DF_Person_RowVersion] DEFAULT (NEWID())

) ON [PRIMARY]

exec sp_addextendedproperty N'MS_Description', N'The person table contains common information about students, teachers and other staff members, parents, emergency contacts, and other persons in the system.',

N'user', N'dbo', N'table', N'Person'

exec sp_addextendedproperty N'MS_Description', N'The primary key of the table.',

N'user', N'dbo', N'table', N'Person', N'column', N'PersonID'

exec sp_addextendedproperty N'MS_Description', N'Identifier that may be used to identify a unique person across multiple databases or systems. Unlike the PersonID, the PersonGUID should be kept with the original record if the data is transferred to another database.',

N'user', N'dbo', N'table', N'Person', N'column', N'PersonGUID'

exec sp_addextendedproperty N'MS_Description', N'An easy to read identifier of the person.',

N'user', N'dbo', N'table', N'Person', N'column', N'PersonNumber'

exec sp_addextendedproperty N'MS_Description', N'The full name of the person as it should be displayed by the system.',

N'user', N'dbo', N'table', N'Person', N'column', N'DisplayName'

exec sp_addextendedproperty N'MS_Description', N'The person''s first name. Empty quotes if unknown.',

N'user', N'dbo', N'table', N'Person', N'column', N'FirstName'

exec sp_addextendedproperty N'MS_Description', N'The person''s middle name. Empty quotes if unknown.',

N'user', N'dbo', N'table', N'Person', N'column', N'MiddleName'

exec sp_addextendedproperty N'MS_Description', N'The person''s last name. Last name is required for all persons.',

N'user', N'dbo', N'table', N'Person', N'column', N'LastName'

exec sp_addextendedproperty N'MS_Description', N'The name that the person prefers to be called. The person''s full name should not be used. Empty quotes indicates that the person has no preferred name; the first name is assumed.',

N'user', N'dbo', N'table', N'Person', N'column', N'PreferredName'

exec sp_addextendedproperty N'MS_Description', N'The date on which the person was born, not including time. Null indicates an unknown date of birth.',

N'user', N'dbo', N'table', N'Person', N'column', N'DateOfBirth'

exec sp_addextendedproperty N'MS_Description', N'The person''s unformatted social security number. Null represents not known or not applicable.',

N'user', N'dbo', N'table', N'Person', N'column', N'SocialSecurityNumber'

exec sp_addextendedproperty N'MS_Description', N'The person''s title. Examples: {Mr, Ms, Dr}. Null represents not known or not applicable.',

N'user', N'dbo', N'table', N'Person', N'column', N'ProperTitleID'

exec sp_addextendedproperty N'MS_Description', N'The person''s generational suffix. Examples: {Jr, Sr, III}. Null represents not known or not applicable.',

N'user', N'dbo', N'table', N'Person', N'column', N'GenerationID'

exec sp_addextendedproperty N'MS_Description', N'The person''s gender. Examples: {male, female}. Null represents not known.',

N'user', N'dbo', N'table', N'Person', N'column', N'GenderID'

exec sp_addextendedproperty N'MS_Description', N'The person''s one character gender descriptor according to the GenderID; Cached for quick access. F indicates Female, M indicates Male, Null represents not known.',

N'user', N'dbo', N'table', N'Person', N'column', N'GenderDescriptor'

exec sp_addextendedproperty N'MS_Description', N'The highest level of education completed by the person. Null represents not known.',

N'user', N'dbo', N'table', N'Person', N'column', N'EducationLevelID'

exec sp_addextendedproperty N'MS_Description', N'The current marital status of the person. Null represents not known.',

N'user', N'dbo', N'table', N'Person', N'column', N'MaritalStatusID'

exec sp_addextendedproperty N'MS_Description', N'The person''s self-identified religion. Null represents not known.',

N'user', N'dbo', N'table', N'Person', N'column', N'ReligionID'

exec sp_addextendedproperty N'MS_Description', N'The residency status of the person from the perspective of the country of the owner of the data.',

N'user', N'dbo', N'table', N'Person', N'column', N'ResidencyStatusID'

exec sp_addextendedproperty N'MS_Description', N'An image of the person.',

N'user', N'dbo', N'table', N'Person', N'column', N'PhotographID'

exec sp_addextendedproperty N'MS_Description', N'The ID of the person''s mailing address.',

N'user', N'dbo', N'table', N'Person', N'column', N'MailingAddressID'

exec sp_addextendedproperty N'MS_Description', N'The ID of the person''s physical address.',

N'user', N'dbo', N'table', N'Person', N'column', N'PhysicalAddressID'

exec sp_addextendedproperty N'MS_Description', N'The name of the country of the person''s primary residence.',

N'user', N'dbo', N'table', N'Person', N'column', N'CountryOfResidence'

exec sp_addextendedproperty N'MS_Description', N'The name of the person''s employer. Null if the person''s employee is not known or not applicable.',

N'user', N'dbo', N'table', N'Person', N'column', N'EmployerName'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether the person is currently homeless. 1 = homeless, 0 = not homeless.',

N'user', N'dbo', N'table', N'Person', N'column', N'IsHomeless'

exec sp_addextendedproperty N'MS_Description', N'A uniqueidentifier used to manage optimistic concurrency.',

N'user', N'dbo', N'table', N'Person', N'column', N'RowVersion'

tbl 1 sample data

PersonID PersonGUID PersonNumber StudentNumber StaffNumber AltPersonNumber DisplayName FirstName MiddleName LastName PreferredName DateOfBirth SocialSecurityNumber ProperTitleID GenerationID GenderID GenderDescriptor EducationLevelID MaritalStatusID ReligionID ResidencyStatusID PhotographID MailingAddressID PhysicalAddressID UserID CountryOfResidence EmployerName IsHomeless RowVersion

-- -- -- -- -- - -- -- - - -- - -- -- -

3 6A6A8238-D35D-483D-A2BB-1223E3C2830F P1001 S1001 NULL Allenton, Alice Alice Allenton Alice 1990-10-10 00:00:00.000 367296330 NULL NULL 2 F NULL NULL NULL NULL NULL 1 2 NULL 0 1D551B0E-F4C1-483C-B974-AFE7B2C8C2D5

4 A614FB9F-B4A6-487F-9A95-D7822CF90921 P1002 S1002 NULL Daniels, Darrell Darrell Daniels Darrell 1992-02-14 00:00:00.000 434257090 NULL NULL 1 M NULL NULL NULL NULL NULL 3 3 NULL 0 83B4EFAD-4E3E-4AA2-A77F-7E4FEE7433BD

5 FE955667-D634-47EC-B0C4-434CEE3C4B7B P1003 S1003 NULL Aventon, Arlene Arlene Aventon Arlene 1990-11-24 00:00:00.000 881807495 NULL NULL 2 F NULL NULL NULL NULL NULL 4 5 NULL 0 F34C80CD-1AE3-46FE-9CE4-F00142685E05

6 23D316DD-3100-4A56-9F36-9D8B34D21121 P1004 S1004 NULL Brown, Betty Betty Brown Betty 1990-10-10 00:00:00.000 460805371 NULL NULL 2 F NULL NULL NULL NULL NULL 6 7 NULL 0 5344672C-F50F-4574-8DD7-3A61B5A2F7B2

7 DF49BECF-76AA-40E4-AF30-210A9C03BFAC P1005 S1005 NULL Edwards, Ethan Ethan Edwards Ethan 1992-02-14 00:00:00.000 504931759 NULL NULL 1 M NULL NULL NULL NULL NULL 8 8 NULL 0 BACC7CE4-6C1C-4FC4-9591-CC66AA89EF3A

8 6F7B664C-AC44-4379-BE23-694B8A86A07E P1006 S1006 NULL Buck, Barbara Barbara Buck Barbara 1990-11-24 00:00:00.000 515164939 NULL NULL 2 F NULL NULL NULL NULL NULL 9 10 NULL 0 94951FD2-831A-44E5-8494-ACB0D6368EC3

9 577E4F3C-F674-47C0-9A21-1860952F0102 P1007 ST1001 NULL AStaff, Alpha Alpha AStaff Alpha NULL 111998888 NULL NULL 1 M NULL NULL NULL NULL NULL NULL NULL NULL 0 4402F5FC-23D8-4FDC-A32B-C694287E4171

Tbl 2

IF OBJECT_ID('dbo.StudentContact') IS NOT NULL

DROP TABLE [dbo].[StudentContact]

CREATE TABLE [dbo].[StudentContact]

(

[StudentID] INT NOT NULL,

[ContactID] INT NOT NULL,

[RelationshipID] SMALLINT NOT NULL,

[Description] VARCHAR(255) NOT NULL

CONSTRAINT [DF_StudentContact_Description] DEFAULT (''),

[ReceivesMailings] BIT NOT NULL

CONSTRAINT [DF_StudentContact_ReceivesMailings] DEFAULT (0),

[CanPickUp] BIT NOT NULL

CONSTRAINT [DF_StudentContact_CanPickUp] DEFAULT (0),

[IsRelated] BIT NOT NULL

CONSTRAINT [DF_StudentContact_IsRelated] DEFAULT(0),

[IsCustodian] BIT NOT NULL

CONSTRAINT [DF_StudentContact_IsCustodian] DEFAULT(0),

[IsEmergencyContact] BIT NOT NULL

CONSTRAINT [DF_StudentContact_IsEmergencyContact] DEFAULT(0),

CONSTRAINT [CPK_StudentContact] PRIMARY KEY CLUSTERED ( [StudentID], [ContactID] )

) ON [PRIMARY]

exec sp_addextendedproperty N'MS_Description', N'The StudentContact table contains student-person relationships in the system.',

N'user', N'dbo', N'table', N'StudentContact'

exec sp_addextendedproperty N'MS_Description', N'The student for whom the person is a contact.',

N'user', N'dbo', N'table', N'StudentContact', N'column', N'StudentID'

exec sp_addextendedproperty N'MS_Description', N'The person who is the contact.',

N'user', N'dbo', N'table', N'StudentContact', N'column', N'ContactID'

exec sp_addextendedproperty N'MS_Description', N'Defines the relationship between the student and the contact.',

N'user', N'dbo', N'table', N'StudentContact', N'column', N'RelationshipID'

exec sp_addextendedproperty N'MS_Description', N'Free text description of how the contact relates to the student.',

N'user', N'dbo', N'table', N'StudentContact', N'column', N'Description'

exec sp_addextendedproperty N'MS_Description', N'Indicates the contact receives mail-outs from the school for this student.',

N'user', N'dbo', N'table', N'StudentContact', N'column', N'ReceivesMailings'

exec sp_addextendedproperty N'MS_Description', N'Indicates the contact can pick up this student from school.',

N'user', N'dbo', N'table', N'StudentContact', N'column', N'CanPickUp'

exec sp_addextendedproperty N'MS_Description', N'Indicates the contact is a blood relation to the student.',

N'user', N'dbo', N'table', N'StudentContact', N'column', N'IsRelated'

exec sp_addextendedproperty N'MS_Description', N'Indicates the contact is the legal custodian of the student.',

N'user', N'dbo', N'table', N'StudentContact', N'column', N'IsCustodian'

exec sp_addextendedproperty N'MS_Description', N'Indicates the contact should be notified in an emergency.',

N'user', N'dbo', N'table', N'StudentContact', N'column', N'IsEmergencyContact'

Tbl 2 Sample Data

StudentID ContactID RelationshipID Description ReceivesMailings CanPickUp IsRelated IsCustodian IsEmergencyContact

-- -- -- - --

55 5 3 0 0 0 1 0

69 91 1 1 1 1 1

82 4 1 1 1 1 1 1

8842 0 0 0 1 0

91 3 1 1 1 1 1 1

tbl 3

IF OBJECT_ID('dbo.Student') IS NOT NULL

DROP TABLE [dbo].[Student]

CREATE TABLE [dbo].[Student] (

[StudentID] INT IDENTITY (3,1) NOT NULL

CONSTRAINT [CPK_Student] PRIMARY KEY CLUSTERED ( [StudentID] ),

[StudentGUID] UNIQUEIDENTIFIER NOT NULL

CONSTRAINT [UNCC_Student_StudentGUID] UNIQUE NONCLUSTERED ( [StudentGUID] ),

[StudentNumber] VARCHAR(15) NOT NULL

CONSTRAINT [UNCC_Student_StudentNumber] UNIQUE NONCLUSTERED ( [StudentNumber] ),

[DisplayName] VARCHAR(255) NOT NULL,

[FirstName] VARCHAR(50) NOT NULL

CONSTRAINT [DF_Student_FirstName] DEFAULT(''),

[MiddleName] VARCHAR(50) NOT NULL

CONSTRAINT [DF_Student_MiddleName] DEFAULT(''),

[LastName] VARCHAR(75) NOT NULL,

[PreferredName] VARCHAR(75) NOT NULL

CONSTRAINT [DF_Student_PreferredName] DEFAULT(''),

[DateOfBirth] DATETIME NULL,

[SocialSecurityNumber] CHAR(9) NOT NULL

CONSTRAINT [DF_Student_SocialSecurityNumber] DEFAULT(''),

[NextYearSchoolID] INT NULL,

[ProperTitleID] SMALLINT NULL,

[GenerationID] SMALLINT NULL,

[GenderID] SMALLINT NULL,

[GenderDescriptor] CHAR(1) NULL,

[MaritalStatusID] SMALLINT NULL,

[ReligionID] SMALLINT NULL,

[ResidencyStatusID] SMALLINT NULL,

[PhotographID] INT NULL,

[MailingAddressID] INT NULL,

[MailingAddressMultiLine] VARCHAR(230) NULL,

[MailingAddressSingleLine] VARCHAR(225) NULL,

[MailingAddressIsListed] BIT NULL,

[PhysicalAddressID] INT NULL,

[ResidesOutOfDistrict] BIT NOT NULL

CONSTRAINT [DF_Student_ResidesOutOfDistrict] DEFAULT( 0 ),

[LimitedEnglishID] SMALLINT NULL,

[MigrantFamilyID] SMALLINT NULL,

[BirthCertNumber] VARCHAR(30) NOT NULL

CONSTRAINT [DF_Student_BirthCertNumber] DEFAULT(''),

[BirthCertVerifyNumber] VARCHAR(30) NOT NULL

CONSTRAINT [DF_Student_BirthCertVerifyNumber] DEFAULT(''),

[Section504Qualification] CHAR(3) NOT NULL

CONSTRAINT [DF_Student_Section504Qualification] DEFAULT ('NA'),

[AltStudentNumber] VARCHAR(15) NOT NULL

CONSTRAINT [DF_Student_AltStudentNumber] DEFAULT(''),

[StateIDNumber] VARCHAR(30) NOT NULL

CONSTRAINT [DF_Student_StateIDNumber] DEFAULT(''),

[CountryOfResidence] CHAR(3) NOT NULL

CONSTRAINT [DF_Student_CountyOfResidence] DEFAULT(''),

[EmployerName] VARCHAR(150) NOT NULL

CONSTRAINT [DF_Student_EmployerName] DEFAULT(''),

[IsHomeless] BIT NOT NULL

CONSTRAINT [DF_Student_IsHomeless] DEFAULT (0),

[IsImmigrant] BIT NOT NULL

CONSTRAINT [DF_Student_IsImmigrant] DEFAULT (0),

[IsForeignExchange] BIT NOT NULL

CONSTRAINT [DF_Student_IsForeignExchange] DEFAULT (0),

[IsAllowedInetAccess] BIT NOT NULL

CONSTRAINT [DF_Student_IsAllowedInetAccess] DEFAULT (0),

[HasMedicalAlert] BIT NOT NULL

CONSTRAINT [DF_Student_HasMedicalAlert] DEFAULT (0),

[IsGraduate] BIT NOT NULL

CONSTRAINT [DF_Student_IsGraduate] DEFAULT (0),

[IsHomeSchooled] BIT NOT NULL

CONSTRAINT [DF_Student_IsHomeSchooled] DEFAULT (0),

[IsTuition] BIT NOT NULL

CONSTRAINT [DF_Student_IsTuition] DEFAULT (0),

[TelephonyPIN] VARCHAR(8) NOT NULL,

[InternetPassword] VARCHAR(20) NOT NULL,

--REVIEW IIB: Schema change document says this should be renamed to FormattedTelephoneNumber.

[TelephoneNumber] VARCHAR(21) NULL,

[IsListed] BIT NULL,

[EmailAddress] VARCHAR(128) NULL,

[OriginalEnrollmentDate] DATETIME NULL,

[ExpectedGraduationDate] DATETIME NULL,

[CachedSchoolList] TEXT NULL,

[SpecialInstructions] VARCHAR(4096) NOT NULL

CONSTRAINT [DF_Student_SpecialInstructions] DEFAULT(''),

[SpEdStatusID] SMALLINT NULL,

[PrimaryExcepID] SMALLINT NULL,

[SecondaryExcepID] SMALLINT NULL,

[LeastRestEnvID] SMALLINT NULL,

[CaseManagerID] INT NULL,

[EligibilityDate] DATETIME NULL,

[ReevaluationDate] DATETIME NULL,

[IEPBeginDate] DATETIME NULL,

[IEPEndDate] DATETIME NULL,

[DateEnrolledInLEA] DATETIME NULL,

[ReportingSchoolID] INT NULL,

[IntegratedWithSETS] BIT NOT NULL

CONSTRAINT [DF_Student_IntegratedWithSETS] DEFAULT(0),

[UserID] INT NOT NULL,

[RowVersion] UNIQUEIDENTIFIER NOT NULL

CONSTRAINT [DF_Student_RowVersion] DEFAULT (NEWID())

) ON [PRIMARY]

exec sp_addextendedproperty N'MS_Description', N'The Student table contains district-wide information about students in the system.',

N'user', N'dbo', N'table', N'Student'

exec sp_addextendedproperty N'MS_Description', N'The primary key of the table.',

N'user', N'dbo', N'table', N'Student', N'column', N'StudentID'

exec sp_addextendedproperty N'MS_Description', N'Identifier that may be used to identify a unique student across multiple databases or systems. Unlike the StudentID, the StudentGUID should be kept with the original record if the data is transferred to another database.',

N'user', N'dbo', N'table', N'Student', N'column', N'StudentGUID'

exec sp_addextendedproperty N'MS_Description', N'An easy to read identifier of the student.',

N'user', N'dbo', N'table', N'Student', N'column', N'StudentNumber'

exec sp_addextendedproperty N'MS_Description', N'The full name of the student as it should be displayed by the system.',

N'user', N'dbo', N'table', N'Student', N'column', N'DisplayName'

exec sp_addextendedproperty N'MS_Description', N'The student''s first name. Empty quotes if unknown.',

N'user', N'dbo', N'table', N'Student', N'column', N'FirstName'

exec sp_addextendedproperty N'MS_Description', N'The student''s middle name. Empty quotes if unknown.',

N'user', N'dbo', N'table', N'Student', N'column', N'MiddleName'

exec sp_addextendedproperty N'MS_Description', N'The student''s last name. Last name is required for all students.',

N'user', N'dbo', N'table', N'Student', N'column', N'LastName'

exec sp_addextendedproperty N'MS_Description', N'The name that the student prefers to be called. The student''s full name should not be used. Empty quotes indicates that the Student has no preferred name; the first name is assumed.',

N'user', N'dbo', N'table', N'Student', N'column', N'PreferredName'

exec sp_addextendedproperty N'MS_Description', N'The date on which the student was born, not including time. Null indicates an unknown date of birth.',

N'user', N'dbo', N'table', N'Student', N'column', N'DateOfBirth'

exec sp_addextendedproperty N'MS_Description', N'The student''s unformatted social security number. Null represents not known.',

N'user', N'dbo', N'table', N'Student', N'column', N'SocialSecurityNumber'

exec sp_addextendedproperty N'MS_Description', N'ID (FK) of the school this student will attend next year.',

N'user', N'dbo', N'table', N'Student', N'column', N'NextYearSchoolID'

exec sp_addextendedproperty N'MS_Description', N'The student''s title. Examples: {Mr, Ms, Dr}. Null represents not known or not applicable.',

N'user', N'dbo', N'table', N'Student', N'column', N'ProperTitleID'

exec sp_addextendedproperty N'MS_Description', N'The student''s generational suffix. Examples: {Jr, Sr, III}. Null represents not known or not applicable.',

N'user', N'dbo', N'table', N'Student', N'column', N'GenerationID'

exec sp_addextendedproperty N'MS_Description', N'The student''s gender. Examples: {male, female}. Null represents not known.',

N'user', N'dbo', N'table', N'Student', N'column', N'GenderID'

exec sp_addextendedproperty N'MS_Description', N'The student''s one character gender descriptor according to the GenderID; Cached for quick access. F indicates Female, M indicates Male, Null represents not known.',

N'user', N'dbo', N'table', N'Student', N'column', N'GenderDescriptor'

exec sp_addextendedproperty N'MS_Description', N'The current marital status of the student. Null represents not known.',

N'user', N'dbo', N'table', N'Student', N'column', N'MaritalStatusID'

exec sp_addextendedproperty N'MS_Description', N'The student''s self-identified religion. Null represents not known.',

N'user', N'dbo', N'table', N'Student', N'column', N'ReligionID'

exec sp_addextendedproperty N'MS_Description', N'The residency status of the student from the perspective of the country of the owner of the data.',

N'user', N'dbo', N'table', N'Student', N'column', N'ResidencyStatusID'

exec sp_addextendedproperty N'MS_Description', N'An image of the student.',

N'user', N'dbo', N'table', N'Student', N'column', N'PhotographID'

exec sp_addextendedproperty N'MS_Description', N'The ID of the person''s mailing address.',

N'user', N'dbo', N'table', N'Student', N'column', N'MailingAddressID'

exec sp_addextendedproperty N'MS_Description', N'The ID of the person''s physical address.',

N'user', N'dbo', N'table', N'Student', N'column', N'PhysicalAddressID'

exec sp_addextendedproperty N'MS_Description', N'The level of written and verbal proficiency that the student possesses in the English language. Null represents that the student''s primary language, or mother-tongue, is English. Any other value represents a limited English proficiency.',

N'user', N'dbo', N'table', N'Student', N'column', N'LimitedEnglishID'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether a student belongs to a migrant family. Null represents that the student''s family is not migrant. Any other value represents the cateogory of migrant family to which the student belongs.',

N'user', N'dbo', N'table', N'Student', N'column', N'MigrantFamilyID'

exec sp_addextendedproperty N'MS_Description', N'The birth certificate number from the student''s birth certificate.',

N'user', N'dbo', N'table', N'Student', N'column', N'BirthCertNumber'

exec sp_addextendedproperty N'MS_Description', N'A reference to the method used to verify the birth certificate. The data usually represents the name or identifier of the person who performed a visual verification of the physical document.',

N'user', N'dbo', N'table', N'Student', N'column', N'BirthCertVerifyNumber'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether a student has a metnal or physical disability that qualifies under Section 504 of the Rehabilitation Act of 1973. (Empty Quotes) Indicates no qualified disability. (504) Indicates a qualified disability. (IEP) Indicates a qualified disability for a student with an IEP.',

N'user', N'dbo', N'table', N'Student', N'column', N'Section504Qualification'

exec sp_addextendedproperty N'MS_Description', N'Used to identify a student to another agency or organization.',

N'user', N'dbo', N'table', N'Student', N'column', N'AltStudentNumber'

exec sp_addextendedproperty N'MS_Description', N'The number assigned by the State Dept of Education.',

N'user', N'dbo', N'table', N'Student', N'column', N'StateIDNumber'

exec sp_addextendedproperty N'MS_Description', N'The abbreviation of the country of the student''s primary residence.',

N'user', N'dbo', N'table', N'Student', N'column', N'CountryOfResidence'

exec sp_addextendedproperty N'MS_Description', N'The name of the student''s employer. Null if the student''s employer is not known or not applicable.',

N'user', N'dbo', N'table', N'Student', N'column', N'EmployerName'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether the student is currently homeless. (1) homeless. (0) not homeless.',

N'user', N'dbo', N'table', N'Student', N'column', N'IsHomeless'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether the student is an immigrant. (1) immigrant. (0) not an immigrant.',

N'user', N'dbo', N'table', N'Student', N'column', N'IsImmigrant'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether the student is a foreign exchange student. (1) foreign exchange student. (0) not a foreign exchange student.',

N'user', N'dbo', N'table', N'Student', N'column', N'IsForeignExchange'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether the student is allowed internet access at the school. (1) The student is allowed internet access. (0) The student is not allowed internet access.',

N'user', N'dbo', N'table', N'Student', N'column', N'IsAllowedInetAccess'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether the student is a graduate. (1) The student is a graduate. (0) The student is not a graduate.',

N'user', N'dbo', N'table', N'Student', N'column', N'IsGraduate'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether the student is home schooled. (1) The student is home schooled. (0) The student is not home schooled.',

N'user', N'dbo', N'table', N'Student', N'column', N'IsHomeSchooled'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether the students parents pay tution. (1) The students parents pay tuition. (0) The students parents do not pay tuition.',

N'user', N'dbo', N'table', N'Student', N'column', N'IsTuition'

exec sp_addextendedproperty N'MS_Description', N'The person''s primary telephone number. Cached for quick access. Additional telephone numbers are stored in the PersonTelephone table.',

N'user', N'dbo', N'table', N'Student', N'column', N'TelephoneNumber'

exec sp_addextendedproperty N'MS_Description', N'Boolean indicating if the telephone number is listed on external lists.',

N'user', N'dbo', N'table', N'Student', N'column', N'IsListed'

exec sp_addextendedproperty N'MS_Description', N'The person''s primary email address. Cached for quick access. Additional email addresses are stored in the PersonEmail table.',

N'user', N'dbo', N'table', N'Student', N'column', N'EmailAddress'

exec sp_addextendedproperty N'MS_Description', N'The date the student was originally enrolled in the district. NULL indicates that the student has never been enrolled in the district. This value is maintained by the system.',

N'user', N'dbo', N'table', N'Student', N'column', N'OriginalEnrollmentDate'

exec sp_addextendedproperty N'MS_Description', N'Date that the student is expected to graduate. NULL indicates that this information is unknown.',

N'user', N'dbo', N'table', N'Student', N'column', N'ExpectedGraduationDate'

exec sp_addextendedproperty N'MS_Description', N'An XML formatted cached list of the schools in which the student has been enrolled.',

N'user', N'dbo', N'table', N'Student', N'column', N'CachedSchoolList'

EXEC sp_addextendedproperty N'MS_Description', N'Indicator used to determine if a student is receiving Special Education Services.',

N'user', N'dbo', N'table', N'Student', N'column', N'SpEdStatusID'

EXEC sp_addextendedproperty N'MS_Description', N'The primary reason why the student is reciving Special Education Services.',

N'user', N'dbo', N'table', N'Student', N'column', N'PrimaryExcepID'

EXEC sp_addextendedproperty N'MS_Description', N'The secondary reason why the student is reciving Special Education Services.',

N'user', N'dbo', N'table', N'Student', N'column', N'SecondaryExcepID'

EXEC sp_addextendedproperty N'MS_Description', N'Represents the student least restrictive environment for Special Education Services.',

N'user', N'dbo', N'table', N'Student', N'column', N'LeastRestEnvID'

EXEC sp_addextendedproperty N'MS_Description', N'Reference to a staff member that is in chanrge of the students Special Education Services.',

N'user', N'dbo', N'table', N'Student', N'column', N'CaseManagerID'

EXEC sp_addextendedproperty N'MS_Description', N'Date in which a student has become eligible for an Exceptionality.',

N'user', N'dbo', N'table', N'Student', N'column', N'EligibilityDate'

EXEC sp_addextendedproperty N'MS_Description', N'Date in which to reevaluate a student to see if they still need Special Education Services for there exceptionality.',

N'user', N'dbo', N'table', N'Student', N'column', N'ReevaluationDate'

EXEC sp_addextendedproperty N'MS_Description', N'Date that represents when a student has begon an Individulized Education Program.',

N'user', N'dbo', N'table', N'Student', N'column', N'IEPBeginDate'

EXEC sp_addextendedproperty N'MS_Description', N'Date that represents when a student has ended an Individulized Education Program.',

N'user', N'dbo', N'table', N'Student', N'column', N'IEPEndDate'

EXEC sp_addextendedproperty N'MS_Description', N'Date in which a student first started receiving Special Education Services.',

N'user', N'dbo', N'table', N'Student', N'column', N'DateEnrolledInLEA'

EXEC sp_addextendedproperty N'MS_Description', N'A refrence to the school that a student is considered to report from.',

N'user', N'dbo', N'table', N'Student', N'column', N'ReportingSchoolID'

EXEC sp_addextendedproperty N'MS_Description', N'Indicator to determine if a student has been Integrated with SETSWeb.',

N'user', N'dbo', N'table', N'Student', N'column', N'IntegratedWithSETS'

exec sp_addextendedproperty N'MS_Description', N'The user ID of the student, for use with the Home Module.',

N'user', N'dbo', N'table', N'Student', N'column', N'UserID'

exec sp_addextendedproperty N'MS_Description', N'A uniqueidentifier used to manage optimistic concurrency.',

N'user', N'dbo', N'table', N'Student', N'column', N'RowVersion'

Tbl 3 Sample Data

StudentID StudentGUID StudentNumber DisplayName FirstName MiddleName LastName PreferredName DateOfBirth SocialSecurityNumber NextYearSchoolID ProperTitleID GenerationID GenderID GenderDescriptor MaritalStatusID ReligionID ResidencyStatusID PhotographID MailingAddressID MailingAddressMultiLine MailingAddressSingleLine MailingAddressIsListed PhysicalAddressID ResidesOutOfDistrict LimitedEnglishID MigrantFamilyID BirthCertNumber BirthCertVerifyNumber Section504Qualification AltStudentNumber StateIDNumber CountryOfResidence EmployerName IsHomeless IsImmigrant IsForeignExchange IsAllowedInetAccess HasMedicalAlert IsGraduate IsHomeSchooled IsTuition TelephonyPIN InternetPassword TelephoneNumber IsListed EmailAddress OriginalEnrollmentDate ExpectedGraduationDate CachedSchoolList SpecialInstructions SpEdStatusID PrimaryExcepID SecondaryExcepID LeastRestEnvID CaseManagerID EligibilityDate ReevaluationDate IEPBeginDate IEPEndDate DateEnrolledInLEA ReportingSchoolID IntegratedWithSETS UserID RowVersion

-- -- -- -- -- - - -- - - -- - -- - -- -- - -- - - -- -- - - -- -- -- -- -- -- - - -- - -- - -- -- -- -- -- -- --

3 6A6A8238-D35D-483D-A2BB-1223E3C2830F S1001 Allenton, Alice Alice Allenton Alice 1990-10-10 00:00:00.000 367296330 NULL NULL NULL 2 F NULL NULL NULL NULL 1 PO Box 59

148 Starling Road

Vienna, MO 65582 PO Box 59148 Starling Road Vienna, MO 65582 0 2 0 NULL NULL NA 0 0 0 0 0 0 0 0 (573) 690-1234 1 NULL 2006-08-10 00:00:00.000 NULL <cache><c asID="6" asName="0607 Regular" schID="6" schName="Academic Session View Year Calendar" schNumber="3000" status="C" /></cache> NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 3 1D551B0E-F4C1-483C-B974-AFE7B2C8C2D5

4 A614FB9F-B4A6-487F-9A95-D7822CF90921 S1002 Daniels, Darrell Darrell Daniels Darrell 1992-02-14 00:00:00.000 434257090 NULL NULL NULL 1 M NULL NULL NULL NULL 3 81 Darby Hollow Street

Apt 29

Richland, MO 65556 81 Darby Hollow StreetApt 29 Richland, MO 65556 0 3 0 NULL NULL NA 0 0 0 0 0 0 0 0 (573) 690-4321 1 NULL 2006-08-10 00:00:00.000 NULL <cache><c asID="6" asName="0607 Regular" schID="6" schName="Academic Session View Year Calendar" schNumber="3000" status="C" /></cache> NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 4 83B4EFAD-4E3E-4AA2-A77F-7E4FEE7433BD

5 FE955667-D634-47EC-B0C4-434CEE3C4B7B S1003 Aventon, Arlene Arlene Aventon Arlene 1990-11-24 00:00:00.000 881807495 NULL NULL NULL 2 F NULL NULL NULL NULL 4 PO Box 300

815 West Eubanks

Vienna, MO 65582 PO Box 300815 West Eubanks Vienna, MO 65582 0 5 0 NULL NULL NA 0 0 0 0 0 0 0 0 (573) 690-4333 1 NULL 2006-08-10 00:00:00.000 NULL <cache><c asID="6" asName="0607 Regular" schID="6" schName="Academic Session View Year Calendar" schNumber="3000" status="C" /></cache> NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 5 F34C80CD-1AE3-46FE-9CE4-F00142685E05

6 23D316DD-3100-4A56-9F36-9D8B34D21121 S1004 Brown, Betty Betty Brown Betty 1990-10-10 00:00:00.000 460805371 NULL NULL NULL 2 F NULL NULL NULL NULL 6 PO Box 806

187 West Franklin

Vienna, MO 65582 PO Box 806187 West Franklin Vienna, MO 65582 0 7 0 NULL NULL NA 0 0 0 0 0 0 0 0 (573) 690-1234 1 NULL 2006-08-10 00:00:00.000 NULL <cache><c asID="6" asName="0607 Regular" schID="6" schName="Academic Session View Year Calendar" schNumber="3000" status="C" /></cache> NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 6 5344672C-F50F-4574-8DD7-3A61B5A2F7B2

7 DF49BECF-76AA-40E4-AF30-210A9C03BFAC S1005 Edwards, Ethan Ethan Edwards Ethan 1992-02-14 00:00:00.000 504931759 NULL NULL NULL 1 M NULL NULL NULL NULL 8 3331 East Broadway

Apt 93

Stoutland, MO 65560 3331 East BroadwayApt 93 Stoutland, MO 65560 0 8 0 NULL NULL NA 0 0 0 0 0 0 0 0 (573) 690-4321 1 NULL 2006-08-10 00:00:00.000 NULL <cache><c asID="6" asName="0607 Regular" schID="6" schName="Academic Session View Year Calendar" schNumber="3000" status="C" /></cache> NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 7 BACC7CE4-6C1C-4FC4-9591-CC66AA89EF3A

8 6F7B664C-AC44-4379-BE23-694B8A86A07E S1006 Buck, Barbara Barbara Buck Barbara 1990-11-24 00:00:00.000 515164939 NULL NULL NULL 2 F NULL NULL NULL NULL 9 PO Box 9196

3331 Hwy JJ

Stoutland, MO 65560 PO Box 91963331 Hwy JJ Stoutland, MO 65560 0 10 0 NULL NULL NA 0 0 0 0 0 0 0 0 (573) 690-4333 1 NULL 2006-08-10 00:00:00.000 NULL <cache><c asID="6" asName="0607 Regular" schID="6" schName="Academic Session View Year Calendar" schNumber="3000" status="C" /></cache> NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 8 94951FD2-831A-44E5-8494-ACB0D6368EC3

|||

We appreciate your effort in posting the code and sample data.

However, in order to help us help you, we really need the sample data in a form that does not require that we spend much time creating and populating the tables.

Please view this link which should help you understand how to prepare the data so we can easily use it.

(Keep in mind, the less time we have to spend in 'setting up' the problem, the more likely you will get folks here to volunteer their time to help you..)

|||

Thanks for the link, this makes it much easier for me also......

INSERT INTO [student] ([StudentID],[StudentGUID],[StudentNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[NextYearSchoolID],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[MailingAddressMultiLine],[MailingAddressSingleLine],[MailingAddressIsListed],[PhysicalAddressID],[ResidesOutOfDistrict],[LimitedEnglishID],[MigrantFamilyID],[BirthCertNumber],[BirthCertVerifyNumber],[Section504Qualification],[AltStudentNumber],[StateIDNumber],[CountryOfResidence],[EmployerName],[IsHomeless],[IsImmigrant],[IsForeignExchange],[IsAllowedInetAccess],[HasMedicalAlert],[IsGraduate],[IsHomeSchooled],[IsTuition],[TelephonyPIN],[InternetPassword],[TelephoneNumber],[IsListed],[EmailAddress],[OriginalEnrollmentDate],[ExpectedGraduationDate],[CachedSchoolList],[SpecialInstructions],[SpEdStatusID],[PrimaryExcepID],[SecondaryExcepID],[LeastRestEnvID],[CaseManagerID],[EligibilityDate],[ReevaluationDate],[IEPBeginDate],[IEPEndDate],[DateEnrolledInLEA],[ReportingSchoolID],[IntegratedWithSETS],[UserID],[RowVersion])VALUES(5,'45847C59-0EEC-4F0A-B3A7-74B8B8F877BE','S1001','Burr, Aaron','Aaron','','Burr','','Feb 6 1989 12:00:00:000AM','902061756',5,NULL,NULL,2,'M',NULL,NULL,NULL,4,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,'','','NA','','','','',0,0,0,0,1,0,0,0,'','',NULL,NULL,NULL,NULL,NULL,'<cache><c asID="18" asName="0506 Regular" schID="4" schName="Greene High School" schNumber="001" status="C" /></cache>','Not allowed to associate with Alexander Hamilton.',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,3,'2D1228D2-650D-4831-8269-7D5B42F66995')
INSERT INTO [student] ([StudentID],[StudentGUID],[StudentNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[NextYearSchoolID],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[MailingAddressMultiLine],[MailingAddressSingleLine],[MailingAddressIsListed],[PhysicalAddressID],[ResidesOutOfDistrict],[LimitedEnglishID],[MigrantFamilyID],[BirthCertNumber],[BirthCertVerifyNumber],[Section504Qualification],[AltStudentNumber],[StateIDNumber],[CountryOfResidence],[EmployerName],[IsHomeless],[IsImmigrant],[IsForeignExchange],[IsAllowedInetAccess],[HasMedicalAlert],[IsGraduate],[IsHomeSchooled],[IsTuition],[TelephonyPIN],[InternetPassword],[TelephoneNumber],[IsListed],[EmailAddress],[OriginalEnrollmentDate],[ExpectedGraduationDate],[CachedSchoolList],[SpecialInstructions],[SpEdStatusID],[PrimaryExcepID],[SecondaryExcepID],[LeastRestEnvID],[CaseManagerID],[EligibilityDate],[ReevaluationDate],[IEPBeginDate],[IEPEndDate],[DateEnrolledInLEA],[ReportingSchoolID],[IntegratedWithSETS],[UserID],[RowVersion])VALUES(6,'A9B6D8C2-8D95-42E2-92E6-02F496A764BB','S1002','Franklin, Benjamin','Benjamin','','Franklin','Ben','Jan 17 1966 12:00:00:000AM','901171706',5,NULL,NULL,2,'M',NULL,NULL,NULL,5,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,'','','NA','','','','The Philadelphia Inquirer',0,0,0,0,0,0,0,0,'','',NULL,NULL,NULL,NULL,NULL,'<cache><c asID="18" asName="0506 Regular" schID="4" schName="Greene High School" schNumber="001" status="C" /></cache>','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,4,'5B4F017C-1A9C-4ABF-9F93-C57AD53DED5F')
INSERT INTO [student] ([StudentID],[StudentGUID],[StudentNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[NextYearSchoolID],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[MailingAddressMultiLine],[MailingAddressSingleLine],[MailingAddressIsListed],[PhysicalAddressID],[ResidesOutOfDistrict],[LimitedEnglishID],[MigrantFamilyID],[BirthCertNumber],[BirthCertVerifyNumber],[Section504Qualification],[AltStudentNumber],[StateIDNumber],[CountryOfResidence],[EmployerName],[IsHomeless],[IsImmigrant],[IsForeignExchange],[IsAllowedInetAccess],[HasMedicalAlert],[IsGraduate],[IsHomeSchooled],[IsTuition],[TelephonyPIN],[InternetPassword],[TelephoneNumber],[IsListed],[EmailAddress],[OriginalEnrollmentDate],[ExpectedGraduationDate],[CachedSchoolList],[SpecialInstructions],[SpEdStatusID],[PrimaryExcepID],[SecondaryExcepID],[LeastRestEnvID],[CaseManagerID],[EligibilityDate],[ReevaluationDate],[IEPBeginDate],[IEPEndDate],[DateEnrolledInLEA],[ReportingSchoolID],[IntegratedWithSETS],[UserID],[RowVersion])VALUES(7,'50DD3F56-2882-4223-B993-53FB765E1B2F','S1004','Gerry, Elbridge','Elbridge','','Gerry','','Jul 17 1989 12:00:00:000AM','907171744',5,NULL,NULL,2,'M',NULL,NULL,NULL,6,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,'','','NA','','','','',0,0,0,0,0,0,0,0,'','',NULL,NULL,NULL,NULL,NULL,'<cache><c asID="18" asName="0506 Regular" schID="4" schName="Greene High School" schNumber="001" status="C" /></cache>','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,6,'C87C1F1B-B27F-444F-B7B3-0E565652A0BA')
INSERT INTO [student] ([StudentID],[StudentGUID],[StudentNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[NextYearSchoolID],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[MailingAddressMultiLine],[MailingAddressSingleLine],[MailingAddressIsListed],[PhysicalAddressID],[ResidesOutOfDistrict],[LimitedEnglishID],[MigrantFamilyID],[BirthCertNumber],[BirthCertVerifyNumber],[Section504Qualification],[AltStudentNumber],[StateIDNumber],[CountryOfResidence],[EmployerName],[IsHomeless],[IsImmigrant],[IsForeignExchange],[IsAllowedInetAccess],[HasMedicalAlert],[IsGraduate],[IsHomeSchooled],[IsTuition],[TelephonyPIN],[InternetPassword],[TelephoneNumber],[IsListed],[EmailAddress],[OriginalEnrollmentDate],[ExpectedGraduationDate],[CachedSchoolList],[SpecialInstructions],[SpEdStatusID],[PrimaryExcepID],[SecondaryExcepID],[LeastRestEnvID],[CaseManagerID],[EligibilityDate],[ReevaluationDate],[IEPBeginDate],[IEPEndDate],[DateEnrolledInLEA],[ReportingSchoolID],[IntegratedWithSETS],[UserID],[RowVersion])VALUES(8,'7C02B0FC-D46E-43BF-A19A-6BD30301BC37','S1003','Hamilton, Alexander','Alexander','','Hamilton','','Jan 11 1990 12:00:00:000AM','901111755',5,NULL,NULL,2,'M',NULL,NULL,NULL,7,1,'287 Convent Ave New York, NY 10031','287 Convent Ave New York, NY 10031',0,1,0,NULL,NULL,'','','NA','','','','US Treasury Dept',0,0,0,0,0,0,0,0,'','','(212) 283-5154 x34555',0,NULL,NULL,NULL,'<cache><c asID="18" asName="0506 Regular" schID="4" schName="Greene High School" schNumber="001" status="C" /></cache>','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,5,'29707034-4806-4F6E-A00C-B3E174FCE297')
INSERT INTO [student] ([StudentID],[StudentGUID],[StudentNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[NextYearSchoolID],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[MailingAddressMultiLine],[MailingAddressSingleLine],[MailingAddressIsListed],[PhysicalAddressID],[ResidesOutOfDistrict],[LimitedEnglishID],[MigrantFamilyID],[BirthCertNumber],[BirthCertVerifyNumber],[Section504Qualification],[AltStudentNumber],[StateIDNumber],[CountryOfResidence],[EmployerName],[IsHomeless],[IsImmigrant],[IsForeignExchange],[IsAllowedInetAccess],[HasMedicalAlert],[IsGraduate],[IsHomeSchooled],[IsTuition],[TelephonyPIN],[InternetPassword],[TelephoneNumber],[IsListed],[EmailAddress],[OriginalEnrollmentDate],[ExpectedGraduationDate],[CachedSchoolList],[SpecialInstructions],[SpEdStatusID],[PrimaryExcepID],[SecondaryExcepID],[LeastRestEnvID],[CaseManagerID],[EligibilityDate],[ReevaluationDate],[IEPBeginDate],[IEPEndDate],[DateEnrolledInLEA],[ReportingSchoolID],[IntegratedWithSETS],[UserID],[RowVersion])VALUES(12,'6194B294-F02E-41CB-8494-57B1DD290E45','S1005','Washington, Martha','Martha','','Washington','','May 15 1991 12:00:00:000AM','902104557',NULL,NULL,NULL,1,'F',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,'','','NA','','','','',0,0,0,0,0,1,0,0,'','',NULL,NULL,NULL,NULL,NULL,'<cache><c asID="18" asName="0506 Regular" schID="4" schName="Greene High School" schNumber="001" status="C" /></cache>','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,7,'48601CC4-5162-42DE-8B79-45CB9FCCDFDA')

INSERT INTO [studentcontact] ([StudentID],[ContactID],[RelationshipID],[Description],[ReceivesMailings],[CanPickUp],[IsRelated],[IsCustodian],[IsEmergencyContact])VALUES(5,8,69,'Dueling Partner',0,0,0,0,0)
INSERT INTO [studentcontact] ([StudentID],[ContactID],[RelationshipID],[Description],[ReceivesMailings],[CanPickUp],[IsRelated],[IsCustodian],[IsEmergencyContact])VALUES(5,10,69,'Guardian',0,0,0,1,0)
INSERT INTO [studentcontact] ([StudentID],[ContactID],[RelationshipID],[Description],[ReceivesMailings],[CanPickUp],[IsRelated],[IsCustodian],[IsEmergencyContact])VALUES(7,6,69,'',0,0,0,0,0)
INSERT INTO [studentcontact] ([StudentID],[ContactID],[RelationshipID],[Description],[ReceivesMailings],[CanPickUp],[IsRelated],[IsCustodian],[IsEmergencyContact])VALUES(7,10,69,'',0,0,0,0,0)

INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(3,'E742AD65-ADA7-4D50-87B4-18BEDF2946C0','P1001','','ST1002',NULL,'Adams, John','John','','Adams','','Oct 30 1989 12:00:00:000AM','911311735',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,2,NULL,NULL,NULL,'','',0,'71F9A6CC-9002-4E0B-8E0B-C6B46EE3D6E6')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(4,'5A3AE32D-7511-4272-BDF8-AFE9F6C4A0AA','P1002','','ST1005',NULL,'Adams, John Quincy','John','Quincy','Adams','','Jul 11 1989 12:00:00:000AM','907111767',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,3,NULL,NULL,NULL,'','',0,'0BB294F5-0843-40F3-9BB7-8429C4E04AED')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(5,'45847C59-0EEC-4F0A-B3A7-74B8B8F877BE','P1003','S1001','',NULL,'Burr, Aaron','Aaron','','Burr','','Feb 6 1989 12:00:00:000AM','902061756',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,4,NULL,NULL,NULL,'','',0,'2D1228D2-650D-4831-8269-7D5B42F66995')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(6,'A9B6D8C2-8D95-42E2-92E6-02F496A764BB','P1004','S1002','',NULL,'Franklin, Benjamin','Benjamin','','Franklin','Ben','Jan 17 1966 12:00:00:000AM','901171706',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,5,NULL,NULL,NULL,'','The Philadelphia Inquirer',0,'5B4F017C-1A9C-4ABF-9F93-C57AD53DED5F')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(7,'50DD3F56-2882-4223-B993-53FB765E1B2F','P1005','S1004','',NULL,'Gerry, Elbridge','Elbridge','','Gerry','','Jul 17 1989 12:00:00:000AM','907171744',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,6,NULL,NULL,NULL,'','',0,'C87C1F1B-B27F-444F-B7B3-0E565652A0BA')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(8,'7C02B0FC-D46E-43BF-A19A-6BD30301BC37','P1006','S1003','',NULL,'Hamilton, Alexander','Alexander','','Hamilton','','Jan 11 1990 12:00:00:000AM','901111755',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,7,1,1,NULL,'','US Treasury Dept',0,'29707034-4806-4F6E-A00C-B3E174FCE297')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(9,'AF52AF69-8645-416F-AD46-5288F3977202','P1007','','ST1003',NULL,'Jefferson, Thomas','Thomas','','Jefferson','','Apr 13 1990 12:00:00:000AM','104131743',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,8,2,3,NULL,'','',0,'AAAC30E6-551A-4349-B3B0-B16EFABCAAB7')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(10,'397F411E-F501-4454-894C-EA751F9DE7E4','P1008','','ST1004',NULL,'Madison, James Jr','James','','Madison','','Mar 16 1990 12:00:00:000AM','903161751',NULL,1,2,'M',NULL,NULL,NULL,NULL,9,4,NULL,8,'','',0,'42DFC849-1989-41EB-8AE2-E2A108F6053F')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(11,'3B8D0472-B748-4F4F-BA67-F0E9A023BC28','P1009','','ST1001',NULL,'Washington, George','George','','Washington','','Feb 22 1990 12:00:00:000AM','902221732',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,10,NULL,NULL,NULL,'','',0,'E63B1391-E7EC-4921-BABB-8825C82A7A70')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(12,'6194B294-F02E-41CB-8494-57B1DD290E45','P1010','S1005','',NULL,'Washington, Martha','Martha','','Washington','','May 15 1991 12:00:00:000AM','902104557',NULL,NULL,1,'F',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'','',0,'48601CC4-5162-42DE-8B79-45CB9FCCDFDA')

|||Actually, you should use the TOP query since that will give better performance than the ROW_NUMBER one. And you don't seem to be filtering on the row number value in your query also. So it should produce more rows than the TOP 2.

Friday, March 23, 2012

Help explain statement start offset and end offset

SELECT TOP 5
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
statement_end_offset end -statement_start_offset)/2) FROM
sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I want
to understand this statement within the query above(SELECT
SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1
then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
query_text
Where can i learn more about what that statement is actually trying to do ?
Hassan
The view sys.dm_exec_sql_text(sql_handle)) is showing you the currently
executing batch, which may be a block of many commands or could be a stored
procedure. The offsets are showing you which exact statement in the code is
actually running at the moment. This way you are not looking at the whole
batch of commands and trying to figure out which statement is actually
running.
So, picture a stored procedure with 1000 lines. Suppose you run this query
several times and you see the same value being returned, which we might say
are equip to lines 400 to 407, then you may have found the slow point in the
code that could use some extra work in tuning.
RLF
"Hassan" <hassan@.test.com> wrote in message
news:uonSdUVJIHA.5208@.TK2MSFTNGP04.phx.gbl...
> SELECT TOP 5
> total_worker_time/execution_count AS [Avg CPU Time],
> (SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
> statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
> statement_end_offset end -statement_start_offset)/2) FROM
> sys.dm_exec_sql_text(sql_handle)) AS query_text
> FROM sys.dm_exec_query_stats
> ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I
> want to understand this statement within the query above(SELECT
> SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset
> = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
> end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
> query_text
> Where can i learn more about what that statement is actually trying to do
> ?
>

Help explain statement start offset and end offset

SELECT TOP 5
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
statement_end_offset end -statement_start_offset)/2) FROM
sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I
want
to understand this statement within the query above(SELECT
SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1
then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
query_text
Where can i learn more about what that statement is actually trying to do ?Hassan
The view sys.dm_exec_sql_text(sql_handle)) is showing you the currently
executing batch, which may be a block of many commands or could be a stored
procedure. The offsets are showing you which exact statement in the code is
actually running at the moment. This way you are not looking at the whole
batch of commands and trying to figure out which statement is actually
running.
So, picture a stored procedure with 1000 lines. Suppose you run this query
several times and you see the same value being returned, which we might say
are equip to lines 400 to 407, then you may have found the slow point in the
code that could use some extra work in tuning.
RLF
"Hassan" <hassan@.test.com> wrote in message
news:uonSdUVJIHA.5208@.TK2MSFTNGP04.phx.gbl...
> SELECT TOP 5
> total_worker_time/execution_count AS [Avg CPU Time],
> (SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
> statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
> statement_end_offset end -statement_start_offset)/2) FROM
> sys.dm_exec_sql_text(sql_handle)) AS query_text
> FROM sys.dm_exec_query_stats
> ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and
I
> want to understand this statement within the query above(SELECT
> SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset
> = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
> end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
> query_text
> Where can i learn more about what that statement is actually trying to do
> ?
>

Help explain statement start offset and end offset

SELECT TOP 5
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
statement_end_offset end -statement_start_offset)/2) FROM
sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I want
to understand this statement within the query above(SELECT
SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1
then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
query_text
Where can i learn more about what that statement is actually trying to do ?Hassan
The view sys.dm_exec_sql_text(sql_handle)) is showing you the currently
executing batch, which may be a block of many commands or could be a stored
procedure. The offsets are showing you which exact statement in the code is
actually running at the moment. This way you are not looking at the whole
batch of commands and trying to figure out which statement is actually
running.
So, picture a stored procedure with 1000 lines. Suppose you run this query
several times and you see the same value being returned, which we might say
are equip to lines 400 to 407, then you may have found the slow point in the
code that could use some extra work in tuning.
RLF
"Hassan" <hassan@.test.com> wrote in message
news:uonSdUVJIHA.5208@.TK2MSFTNGP04.phx.gbl...
> SELECT TOP 5
> total_worker_time/execution_count AS [Avg CPU Time],
> (SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
> statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
> statement_end_offset end -statement_start_offset)/2) FROM
> sys.dm_exec_sql_text(sql_handle)) AS query_text
> FROM sys.dm_exec_query_stats
> ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I
> want to understand this statement within the query above(SELECT
> SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset
> = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
> end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
> query_text
> Where can i learn more about what that statement is actually trying to do
> ?
>

Help expanding a query .. group By

Im using vb code to generate a SQL query to return a recordset. The follwing
query returns 2 records as follows.
Select a.BHYEAR_MOVEDATE,
Case b.BTYear_TransCode
WHEN '17' THEN 'DD'
WHEN '01' THEN 'DD'
WHEN '18' THEN 'DD'
Else 'AUD' END AS 'TYPE'
from dbo.BacsHdrYearly as a
LEFT JOIN dbo.BacsTrnYear as b
on a.BHYear_LedgerKey = b.BTYear_LedgerKey
Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
BHYEAR_MOVEDATE TYPE
--- --
2005-04-21 00:00:00 DD
2005-04-21 00:00:00 DD
I need it to only return one record as for each date and type ( may be
multiple dates and types ).
BHYEAR_MOVEDATE TYPE
--- --
2005-04-21 00:00:00 DD
any suggestions on how to rephrase this query ?What ABout DISTINCT ?

> Select DISTINCT a.BHYEAR_MOVEDATE,
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Peter Newman" <PeterNewman@.discussions.microsoft.com> schrieb im
Newsbeitrag news:5AEEE5BB-67B2-4DEC-A918-F6C32E79312C@.microsoft.com...
> Im using vb code to generate a SQL query to return a recordset. The
> follwing
> query returns 2 records as follows.
> Select a.BHYEAR_MOVEDATE,
> Case b.BTYear_TransCode
> WHEN '17' THEN 'DD'
> WHEN '01' THEN 'DD'
> WHEN '18' THEN 'DD'
> Else 'AUD' END AS 'TYPE'
> from dbo.BacsHdrYearly as a
> LEFT JOIN dbo.BacsTrnYear as b
> on a.BHYear_LedgerKey = b.BTYear_LedgerKey
> Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
> OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
> GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
>
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> 2005-04-21 00:00:00 DD
>
> I need it to only return one record as for each date and type ( may be
> multiple dates and types ).
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> any suggestions on how to rephrase this query ?|||or
GROUP BY clause
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OoaGSMNRFHA.204@.TK2MSFTNGP15.phx.gbl...
> What ABout DISTINCT ?
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:5AEEE5BB-67B2-4DEC-A918-F6C32E79312C@.microsoft.com...
>|||Of couse ;-)
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:e$TxrUNRFHA.3076@.TK2MSFTNGP14.phx.gbl...
> or
> GROUP BY clause
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:OoaGSMNRFHA.204@.TK2MSFTNGP15.phx.gbl...
>|||Peter,
Your statement is correct, except that in the GROUP BY clause you have to us
e:
...
GROUP BY
a.BHYEAR_MOVEDATE,
Case b.BTYear_TransCode
WHEN '17' THEN 'DD'
WHEN '01' THEN 'DD'
WHEN '18' THEN 'DD'
Else 'AUD' END;
AMB
"Peter Newman" wrote:

> Im using vb code to generate a SQL query to return a recordset. The follwi
ng
> query returns 2 records as follows.
> Select a.BHYEAR_MOVEDATE,
> Case b.BTYear_TransCode
> WHEN '17' THEN 'DD'
> WHEN '01' THEN 'DD'
> WHEN '18' THEN 'DD'
> Else 'AUD' END AS 'TYPE'
> from dbo.BacsHdrYearly as a
> LEFT JOIN dbo.BacsTrnYear as b
> on a.BHYear_LedgerKey = b.BTYear_LedgerKey
> Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
> OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
> GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
>
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> 2005-04-21 00:00:00 DD
>
> I need it to only return one record as for each date and type ( may be
> multiple dates and types ).
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> any suggestions on how to rephrase this query ?sql

Help creating a SELECT statement for "today"

Hello,

I am attempting to build a MS SQL query that will return data from
"today"; today being current day 8:00AM-10:00PM today. My goal is to
return the data from a table that is written to throughout the day, the
query will provide the current grade of service in our call center.

I am having difficulty defining my where clause:

- I can accomplish my goal my statically defining my 'date between' as
the actual date and time (not ideal)

- I can accomplish the second part of my date using CURRENT_TIMESTAMP;
but I am unable to define the starting point

Here is where I am thus far:

/* We are going to count the total calls into each queue from start of
business today (8:00AM) to now */

select COUNT(Result) as "Total Sales Calls Offered" from
dbo.QueueEncounter
where Direction='0'
and
QueueID='1631'
and
/* This is where I get lost */
Time between DATEPART(day, GETDATE()) and DATEPART(day, GETDATE())

Clearly the last line returns zero as there are no calls between the
same date range. How can I add to that line, or write this to work?

Any thoughts?

Thanks for the help.

-ChrisOn 25 Jan 2006 10:03:53 -0800, Chris wrote:

>Hello,
>I am attempting to build a MS SQL query that will return data from
>"today"; today being current day 8:00AM-10:00PM today. My goal is to
>return the data from a table that is written to throughout the day, the
>query will provide the current grade of service in our call center.
>I am having difficulty defining my where clause:
>- I can accomplish my goal my statically defining my 'date between' as
>the actual date and time (not ideal)
>- I can accomplish the second part of my date using CURRENT_TIMESTAMP;
>but I am unable to define the starting point
>Here is where I am thus far:
>/* We are going to count the total calls into each queue from start of
>business today (8:00AM) to now */
>select COUNT(Result) as "Total Sales Calls Offered" from
>dbo.QueueEncounter
>where Direction='0'
>and
>QueueID='1631'
>and
>/* This is where I get lost */
>Time between DATEPART(day, GETDATE()) and DATEPART(day, GETDATE())
>Clearly the last line returns zero as there are no calls between the
>same date range. How can I add to that line, or write this to work?
>Any thoughts?
>Thanks for the help.
>-Chris

Hi Chris,

You say you want rows for today, 8:00AM-10:00PM. Does this imply that
the table also contains rows outside the 8:00AM-10:00PM time frame that
you don't want to include?

AND Time BETWEEN DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP),
'8:00AM')
AND DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP),
'10:00PM')

Note that this will include a row with time exactly equal to 10 PM, but
exclude a row with time 3 milliseconds after 10PM.

If you want all rows for the whole day (0:00 - 24:00), use
AND Time >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
AND Time < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 1)

--
Hugo Kornelis, SQL Server MVP|||Awesome Hugo, thanks so much for the help - My query is now nearly
complete; with one last problem....

declare @.today datetime,
@.tomorrow datetime,
@.offered smallint,
@.answeredin120 smallint,
@.GOS smallint

set @.today = convert(char(8), GETDATE ( ), 112)
set @.tomorrow = @.today + 1

-- Find total calls offered
set @.offered = (select COUNT(Result) from dbo.QueueEncounter
where Direction='0' and QueueID='1438' and Time >= @.today and Time <
@.tomorrow)

-- Find total calls answered in 120 seconds
set @.answeredin120 =(select COUNT(Result) from dbo.QueueEncounter
where Direction='0' and QueueID='1438' and Time >= @.today and Time <
@.tomorrow and WaitTime <= 120)

-- Divide the total calls offered by the total calls answered in X
multiplied by 100 to find current GOS ??

set @.GOS = (@.offered)/(@.answeredin120)*100

select @.GOS

The problem is my GOS is being returned as 100 when it is really apprx
77%.
Where did I go wrong?

-Thanks!|||On 25 Jan 2006 14:12:48 -0800, Chris wrote:

(snip)
>-- Divide the total calls offered by the total calls answered in X
>multiplied by 100 to find current GOS ??
>set @.GOS = (@.offered)/(@.answeredin120)*100
>select @.GOS
>The problem is my GOS is being returned as 100 when it is really apprx
>77%.
>Where did I go wrong?

Hi Chris,

Integer division: divide two integers, the result is integer too.

SELECT 1/3
SELECT 1.0/3
SELECT 1/3.0
SELECT 1.0/3.0

The above show that forcing at least one operand to non-integer suffices
to get a result with fraction. In your case, one possible way would be

SET @.GOS = CAST(@.offered AS decimal(10,2)) / @.answeredin120 * 100

Or even

SET @.GOS = 100.0 * @.offerec / @.answeredin120

--
Hugo Kornelis, SQL Server MVP|||Hugo, Again many thanks... I will try this at the office tomorrow.

Cheers.

Friday, March 30, 2012

HELP in "Where" clause

Help in "Limit"

help how to create a row progressive..

Wednesday, March 28, 2012

Help for Newbie!

Help for INTRO function

Monday, March 26, 2012

Help finding the Max Total

Help extracting data from second row of result set

Friday, March 23, 2012

Help explain statement start offset and end offset

Help explain statement start offset and end offset

Help explain statement start offset and end offset

Help expanding a query .. group By

Help creating a SELECT statement for "today"

Absurd project

Blog Archive

About Me