Query with date question

I need to return all records in which the date 'lastmodified' +
'responsible' which is a number (of days) is the same as today's date.
I have the following condition in my sql but even though it should return
some records it doesnt:
WHERE (DATEADD(day,Activities.responsible , Activities.Lastmodified ) =
getdate())
Activities.responsible = Its the number of days being added
Activities.lasmodified = Its a date say (01/01/2005)
If the SUM of both is todays date the recordset should be returned, I don't
know if this has to do with the fact that seconds and minutes might be
involved ?
Any help is appreciated.
AleksThis should do it:
WHERE Activities.LastModified BETWEEN (GETDATE() - Activities.Responsible)
AND GETDATE()
... Unfortunately, I believe this will force a table or index scan; I'm not
sure how to get around it given your current schema. If you can, instead of
keeping the number of days, keep the "end" date. Then you'll be able to
write a query that's capable of using an index.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:eWfhxe2IFHA.1476@.TK2MSFTNGP09.phx.gbl...
> I need to return all records in which the date 'lastmodified' +
> 'responsible' which is a number (of days) is the same as today's date.
> I have the following condition in my sql but even though it should return
> some records it doesnt:
>
> WHERE (DATEADD(day,Activities.responsible , Activities.Lastmodified ) =
> getdate())
>
> Activities.responsible = Its the number of days being added
> Activities.lasmodified = Its a date say (01/01/2005)
> If the SUM of both is todays date the recordset should be returned, I
don't
> know if this has to do with the fact that seconds and minutes might be
> involved ?
> Any help is appreciated.
> Aleks
>|||Much more efficient query (assuming an index on LastModified):
DECLARE @.d SMALLDATETIME
SET @.d = DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
SELECT ...
FROM Activities a
..
WHERE a.LastModified >= (@.d - a.responsible)
AND a.LastModified < (@.d +1 - a.responsible)
You always want the column with the index on its own on one side of the
equation. This will allow for an index s instead of a scan. And yes,
this is because LastModified has minutes and seconds, presumably, and
GETDATE() certainly does. The DATEADD/DATEDIFF trick I did up there
converted it to a time of midnight, which makes it easy to find date values
anywhere >= that day and < that day + 1. The query also takes advantage of
implicit integer math with datetime/smalldatetime values, but the purists
might want this instead:
SELECT ...
FROM Activities a
..
WHERE a.LastModified >= DATEADD(DAY, 0 - a.responsible, @.d)
AND a.LastModified < DATEADD(DAY, 1 - a.responsible, @.d)
http://www.aspfaq.com/
(Reverse address to reply.)
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:eWfhxe2IFHA.1476@.TK2MSFTNGP09.phx.gbl...
> I need to return all records in which the date 'lastmodified' +
> 'responsible' which is a number (of days) is the same as today's date.
> I have the following condition in my sql but even though it should return
> some records it doesnt:
>
> WHERE (DATEADD(day,Activities.responsible , Activities.Lastmodified ) =
> getdate())
>
> Activities.responsible = Its the number of days being added
> Activities.lasmodified = Its a date say (01/01/2005)
> If the SUM of both is todays date the recordset should be returned, I
don't
> know if this has to do with the fact that seconds and minutes might be
> involved ?
> Any help is appreciated.
> Aleks
>|||Did not work
The sum of lastworked and responsible should be today's date. I tried that
sql and returned nothing.
Aleks
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OULqxl2IFHA.588@.TK2MSFTNGP15.phx.gbl...
> This should do it:
> WHERE Activities.LastModified BETWEEN (GETDATE() - Activities.Responsible)
> AND GETDATE()
>
> ... Unfortunately, I believe this will force a table or index scan; I'm
> not
> sure how to get around it given your current schema. If you can, instead
> of
> keeping the number of days, keep the "end" date. Then you'll be able to
> write a query that's capable of using an index.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:eWfhxe2IFHA.1476@.TK2MSFTNGP09.phx.gbl...
> don't
>|||This seems to work though.
(Activities.LastModified+responsible) between (GETDATE()-1) and
(getdate())
Would that be alright ?
A
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OULqxl2IFHA.588@.TK2MSFTNGP15.phx.gbl...
> This should do it:
> WHERE Activities.LastModified BETWEEN (GETDATE() - Activities.Responsible)
> AND GETDATE()
>
> ... Unfortunately, I believe this will force a table or index scan; I'm
> not
> sure how to get around it given your current schema. If you can, instead
> of
> keeping the number of days, keep the "end" date. Then you'll be able to
> write a query that's capable of using an index.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:eWfhxe2IFHA.1476@.TK2MSFTNGP09.phx.gbl...
> don't
>|||"Aleks" <arkark2004@.hotmail.com> wrote in message
news:egjIvv2IFHA.3568@.TK2MSFTNGP10.phx.gbl...
> This seems to work though.
> (Activities.LastModified+responsible) between (GETDATE()-1) and
> (getdate())
> Would that be alright ?
Personally -- if you can't change the schema -- I would go with Aaron's
second query:
SELECT ...
FROM Activities a
..
WHERE a.LastModified >= DATEADD(DAY, 0 - a.responsible, @.d)
AND a.LastModified < DATEADD(DAY, 1 - a.responsible, @.d)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Alek,
To explain your problem, You must understand theat getDate() function
returns date and Time, so your query will only return records where the
expression
DATEADD(day,Activities.responsible , Activities.Lastmodified )
is EXACTLY equal to the current date AND time Value, It is very unlikely
that any records will satisfy that...
The only way to do this is to write your predicate as a between, (or as a >=
AND < ), where all records are returned where the value in the dataabase
table is between 2 calculated BOUNDARY values, based on the current date and
time...
so you would either have
Activities.Lastmodified Between @.LowDate And @.HighDate, or
Activities.Lastmodified > @.LowDate And Activities.Lastmodified <= @.HighDate
(NOTE: BETWEEN implies >= AND <= )
Two questions need to be answered, to determine what those
1) When you say "= getdate()" What, exactly do you mean? - do you want,
a) ALl the records that occurred on that specific Calendar DAY?, or
b) All the records that occurred within exactly 12/(24?) hours of a
specific Date and Time?
If it's the former (which I suspect is the case), than your Boundary dates
will be Midnight, in the am, on a specific calculated date :
DateAdd(day, - Responsible, getdate()), converted to strip off the time...
Convert(VarChar(8), DateAdd(day, - Responsible, getdate()), 112)
and the high date would be one day later, again at midnigjt...
Convert(VarChar(8), DateAdd(day, 1 - Responsible, getdate()), 112)
or.
Where Activities.Lastmodified >= Convert(VarChar(8), DateAdd(day, -
Responsible, getdate()), 112)
AND Activities.Lastmodified < Convert(VarChar(8), DateAdd(day, 1 -
Responsible, getdate()), 112)
You have to split off the time portion if you only want those records from a
specific calendar day...
cannot "Aleks" wrote:

> I need to return all records in which the date 'lastmodified' +
> 'responsible' which is a number (of days) is the same as today's date.
> I have the following condition in my sql but even though it should return
> some records it doesnt:
>
> WHERE (DATEADD(day ,Activities.responsible , Activities.Lastmodified ) =
> getdate())
>
> Activities.responsible = Its the number of days being added
> Activities.lasmodified = Its a date say (01/01/2005)
> If the SUM of both is todays date the recordset should be returned, I don'
t
> know if this has to do with the fact that seconds and minutes might be
> involved ?
> Any help is appreciated.
> Aleks
>
>

Query Where clause not working quite correct

My query below should only do an insert if these 2 evaluate to true:

a) The customer number is not currently in the DR table

b) The customer number is in the list check (where customer in)

a has to be true in order for b to be checked. The insert cannot happen for a customer that's already in the DCR whose number is in the IN clause

So far, it is close however I'm finding a few numbers being inserted that are already in the DR table.

I would also like to insert one record for the customer. It will find multiple entries per customer number since this is really a transactiosn table but I need to insert like the Top 1 o something since these inserts are going to be customers who have no transactions...thus is why you see blanks or zeros for all the values. These inserts are part of a larger picture but are needed...so there is no n eed to explain why I'd want to insert nothing ('' and 0 values) for those customers...just leave it at that

SELECT top 1 m.customer,

c.name,

c.customer,

'',

0,

m.Branch,

0,

'',

'',

'',

0,

'',

'',

0,

0,

0,

0,

'UI' AS Type,

1 AS Active,

m.number,

0,

0,

0,

0,

0,

0,

'',

0,

0,

'',

'',

(SELECT TotalPostingDays from TotalPostingDays),

(SELECT CurrentPostingDAy from CurrentPostingDay)

FROM dbo.Master m (NOLOCK)

INNER JOIN dbo.Customer c ON c.Customer = m.Customer

AND c.customer IN ( '0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113'

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133')

AND c.customer NOT IN (select customernumber FROM DR)

I think your problem lies in the fact that you are making your customer not in the DR table comparison as part or the join criteria. Try moving it to a WHERE clause instead or actually join the DR table using left join, see below

1st, instead of "AND c.customer NOT IN (select customernumber FROM DR)" Replace with WHERE c.customer NOT IN (SELECT customernumber FROM DR) Also move your check for customers in the provided list, into a where clause instead of the join criteria.

2nd, use the join as below with a where clause

Code Snippet

SELECT top 1 m.customer,

c.name,

c.customer,

'',

0,

m.Branch,

0,

'',

'',

'',

0,

'',

'',

0,

0,

0,

0,

'UI' AS Type,

1 AS Active,

m.number,

0,

0,

0,

0,

0,

0,

'',

0,

0,

'',

'',

(SELECT TotalPostingDays from TotalPostingDays),

(SELECT CurrentPostingDAy from CurrentPostingDay)

FROM dbo.Master m (NOLOCK)

INNER JOIN dbo.Customer c ON c.Customer = m.Customer

LEFT OUTER JOIN DR d ON c.customer = d.customernumber

WHERE d.customernumber is null

AND c.customer IN ( '0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113'

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133')

Either method should provide the results you need.

|||

Try re-writing your statement to use NOT EXISTS instead NOT IN to check for existence. If for any reason, there is a row in [DR] where customernumber is NULL, then you will have some trouble.

Example:

Code Snippet

select

*

from

(select 1 as c1 union all select 2) as a

where

c1 not in (1, NULL)

It should looks like:

...

where

c.customer IN (

'0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113',

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133'

)

AND NOT exists (

select *

from DR as d

where d.customernumber = c.customer

)

AMB

Query Where clause not working quite correct

My query below should only do an insert if these 2 evaluate to true:

a) The customer number is not currently in the DR table

b) The customer number is in the list check (where customer in)

a has to be true in order for b to be checked. The insert cannot happen for a customer that's already in the DCR whose number is in the IN clause

So far, it is close however I'm finding a few numbers being inserted that are already in the DR table.

I would also like to insert one record for the customer. It will find multiple entries per customer number since this is really a transactiosn table but I need to insert like the Top 1 o something since these inserts are going to be customers who have no transactions...thus is why you see blanks or zeros for all the values. These inserts are part of a larger picture but are needed...so there is no n eed to explain why I'd want to insert nothing ('' and 0 values) for those customers...just leave it at that

SELECT top 1 m.customer,

c.name,

c.customer,

'',

0,

m.Branch,

0,

'',

'',

'',

0,

'',

'',

0,

0,

0,

0,

'UI' AS Type,

1 AS Active,

m.number,

0,

0,

0,

0,

0,

0,

'',

0,

0,

'',

'',

(SELECT TotalPostingDays from TotalPostingDays),

(SELECT CurrentPostingDAy from CurrentPostingDay)

FROM dbo.Master m (NOLOCK)

INNER JOIN dbo.Customer c ON c.Customer = m.Customer

AND c.customer IN ( '0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113'

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133')

AND c.customer NOT IN (select customernumber FROM DR)

I think your problem lies in the fact that you are making your customer not in the DR table comparison as part or the join criteria. Try moving it to a WHERE clause instead or actually join the DR table using left join, see below

1st, instead of "AND c.customer NOT IN (select customernumber FROM DR)" Replace with WHERE c.customer NOT IN (SELECT customernumber FROM DR) Also move your check for customers in the provided list, into a where clause instead of the join criteria.

2nd, use the join as below with a where clause

Code Snippet

SELECT top 1 m.customer,

c.name,

c.customer,

'',

0,

m.Branch,

0,

'',

'',

'',

0,

'',

'',

0,

0,

0,

0,

'UI' AS Type,

1 AS Active,

m.number,

0,

0,

0,

0,

0,

0,

'',

0,

0,

'',

'',

(SELECT TotalPostingDays from TotalPostingDays),

(SELECT CurrentPostingDAy from CurrentPostingDay)

FROM dbo.Master m (NOLOCK)

INNER JOIN dbo.Customer c ON c.Customer = m.Customer

LEFT OUTER JOIN DR d ON c.customer = d.customernumber

WHERE d.customernumber is null

AND c.customer IN ( '0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113'

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133')

Either method should provide the results you need.

|||

Try re-writing your statement to use NOT EXISTS instead NOT IN to check for existence. If for any reason, there is a row in [DR] where customernumber is NULL, then you will have some trouble.

Example:

Code Snippet

select

*

from

(select 1 as c1 union all select 2) as a

where

c1 not in (1, NULL)

It should looks like:

...

where

c.customer IN (

'0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113',

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133'

)

AND NOT exists (

select *

from DR as d

where d.customernumber = c.customer

)

AMB

query using commas

Hi,

I have table Article(ID,Title,FAID)

I need a query that will select all the Article.ID records where the FAID contains the number of the article ID

For exapmle, Article Table content is:

1, "Title1","2,6"2, "Title2",""3, "Title3","6,1"

6, "Title6","2"

Lets say I want to get all titles of article ID 1. I am going to its FAID which is "2,6"

So the query will return : "Title2", "Title6"

Can you advice how to write this? I can do a walk around solution where I will open a new table nameFAbut I rather not to.

You could use the CHARINDEX function in T-SQL. Something like this:

WHERE CHARINDEX(FAID, ID) > 0

This is untested, but will probably work for you.

BUT, this looks an awful lot like a non-normalized table, since you have multiple items in the FAID field. You're likely to be able to write much cleaner and more efficient queries if you normalize it.

Don

|||

the FAID fiels containd a related article IDs. this mean I will use this field only in one query. the one that I am building right now.

although at the moment I have normlized table ReleatedArticle (FAID,ArticleID). for each FAID, I have multiple ArticleIDs, and this is normlize.

do you think I should leave it like this normlized, and not to change to the CHARINDEX solution?

I don't like to open a new table when it seems like unneccessary one. please advice.

|||

Well, the normalization rules are not absolute, although there are people who treat them as such. Sometimes there are good reasons to break the rules. If you truly will not use the information in any other way, in any other queries, this might possibly be efficient for you. But if you ever find yourself writing any convoluted T-SQL or client code to work with the related article ID data, consider putting it into a separate table.

Don

Query two databases on same machine

Hi
I have an SQL 2000 Server, with a number of Databases on it,
I would like to run a query which extracts data from one database and
stores it in another database, the databases have a column which is
the same in each called "AccNumber".
How does one link two databases in an SQL 2000 query.
Any information is appreciated.
Thanks
KimboINSERT dbname1.dbo.table1 (AccNumber)
SELECT AccNumber FROM dbname2.dbo.table2
HTH. Ryan
"Kimbo" <theozbuggaNOSPAM@.hotmail.com> wrote in message
news:6etpr1lqu3ji8h4cr01p59i2pmah3flmf8@.
4ax.com...
> Hi
> I have an SQL 2000 Server, with a number of Databases on it,
> I would like to run a query which extracts data from one database and
> stores it in another database, the databases have a column which is
> the same in each called "AccNumber".
> How does one link two databases in an SQL 2000 query.
> Any information is appreciated.
> Thanks
> Kimbo|||Hi,
You need to use the FQN for the tables
[ServerName].[DatabaseName].[Owner].[tableName]
regards LaRa
"Kimbo" <theozbuggaNOSPAM@.hotmail.com> wrote in message
news:6etpr1lqu3ji8h4cr01p59i2pmah3flmf8@.
4ax.com...
> Hi
> I have an SQL 2000 Server, with a number of Databases on it,
> I would like to run a query which extracts data from one database and
> stores it in another database, the databases have a column which is
> the same in each called "AccNumber".
> How does one link two databases in an SQL 2000 query.
> Any information is appreciated.
> Thanks
> Kimbo|||Kimbo
INSERT INTO DataBase_One.dbo.Table
SELECT <> FROM Database_Two.dbo.Table
"Kimbo" <theozbuggaNOSPAM@.hotmail.com> wrote in message
news:6etpr1lqu3ji8h4cr01p59i2pmah3flmf8@.
4ax.com...
> Hi
> I have an SQL 2000 Server, with a number of Databases on it,
> I would like to run a query which extracts data from one database and
> stores it in another database, the databases have a column which is
> the same in each called "AccNumber".
> How does one link two databases in an SQL 2000 query.
> Any information is appreciated.
> Thanks
> Kimbo|||use dbname1
go
insert dbname2.owner.table2(field)
select field from table1
Regards,
"Ryan" wrote:

> INSERT dbname1.dbo.table1 (AccNumber)
> SELECT AccNumber FROM dbname2.dbo.table2
> --
> HTH. Ryan
>
> "Kimbo" <theozbuggaNOSPAM@.hotmail.com> wrote in message
> news:6etpr1lqu3ji8h4cr01p59i2pmah3flmf8@.
4ax.com...
>
>|||Look up "distributed partitioned views" in Books Online. Based on your post
I
see no need for physically moving data from one database into another.
ML
http://milambda.blogspot.com/

Query total number of records in a schema

Please help out the neophyte!

I'm trying to query a database for the total number of records in each table owned by a particular schema in SQL Server 2000. I can query INFORMATION_SCHEMA.TABLES for a list of the tables in a particular schema, but how do I pass these table names to variable that can be used to count(*) the records in each? Can anyone show me how this should be done using simple TSQL?

Thanks.

Chris

Give this a shot:

Code Snippet

DECLARE @.TableName varchar(100)

DECLARE @.SQL nvarchar(MAX)

DECLARE SchemaCounter CURSOR FOR

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

OPEN SchemaCounter

FETCH NEXT FROM SchemaCounter INTO @.TableName

WHILE @.@.FETCH_STATUS = 0

BEGIN

SET @.SQL = 'SELECT COUNT(*) [' + @.TableName + ' COUNT] FROM [' + @.TableName + ']'

EXEC sp_executesql @.SQL

FETCH NEXT FROM SchemaCounter INTO @.TableName

END

CLOSE SchemaCounter

DEALLOCATE SchemaCounter

|||There is a couple of ways to get it through T-SQL, but I would not call them "simple" in neither case:

Code Snippet

select o.name, i.rowcnt
from sysindexes i
inner join sysobjects o on o.id = i.id
where o.type = 'U'
and i.indid in (0,1)

Or this one (still works in MSSQL2005):

Code Snippet

exec sp_MStablespace 'MyTableName'

The only drawback of these methods is that they used undocumented or deprecated features of SQL Server. Only hope you know what the word "undocumented" really means in MSSQL.


PS There is also a way to use sp_MSForEachTable system stored proc, but it's also undocumented .|||

Hey...

rowcnt in sysindexes

It is very dangerous to use. One can not assure it gives exact number of rows and I am not sure if we can use this or not?

Regards,

Subbu

|||

cannot trust rowcnt in sysindexes. what do u say ?

|||

Yes.. Here the stright forward query (without undocumented sps)

Code Snippet

Select

OBJ.NAME [TableName],

IND.RowCnt as [RowCount]

From

dbo.sysindexes IND

Join sysobjects OBJ on IND.ID = OBJ.ID and indid < 2 And OBJ.Type='U'

order By 1

|||

Hey, the neophyte thanks you for your help. A nice simple solution to my problem.

Chris

|||RowCnt is not updated in "real time". It not ever guaranteed to be 100% accurate. However, if you only want a very reliable "ballpark" number, it works really well. I use it all the time with the caviat that the number is a guidline, rather than "in-stone."

Query to sequentially number Null fields in a column

I'm trying to write a Query that will Update all the Null fields in Table1
column1 to 'P' and a 6 digit sequential number starting from 000001
including the leading zeros. Can someone help me figure out the correct
syntax? So far, nothing I've come up with is working right.
TIA
MattWell, if you don't want to add an IDENTITY column, and just want to add the
zero-padded char, you could:
1.) Create temp table with IDENTITY column and primary key from source table
1.) Generate identity values for all rows in target table in the temp table
2.) Update target table to include a zero-padded version of the identity
value
Example:
Let's say your table is called Customer and the primary key is CustomerKey
varchar(10)
BEGIN TRANSACTION
CREATE TABLE
#KeyGen
(
CustomerKey varchar(10) NOT NULL,
NewID int NOT NULL IDENTITY (1,1)
)
INSERT INTO KeyGen (CustomerKey) SELECT CustomerKey FROM Customer
WITH(TABLOCKX)
ALTER TABLE Customer ADD NewKey char(10) NOT NULL DEFAULT('')
UPDATE Customer SET NewKey = (SELECT RIGHT('000000' + CAST(NewID AS
varchar(6)), 6) FROM #KeyGen WHERE KeyGen.CustomerKey =
Customer.CustomerKey)
DROP TABLE #KeyGen
COMMIT TRANSACTION
Error handling is an exercise for the reader.
Cheers,
James Hokes
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:%23yx8L0oeGHA.4304@.TK2MSFTNGP05.phx.gbl...
> I'm trying to write a Query that will Update all the Null fields in Table1
> column1 to 'P' and a 6 digit sequential number starting from 000001
> including the leading zeros. Can someone help me figure out the correct
> syntax? So far, nothing I've come up with is working right.
> TIA
> Matt
>|||The problem is the source table doesn't have a primary key. That's what I'm
creating with this query.
I've been working with this code that I found in the archive, but I can't
get it to work
update temp_Reports tr1
set identifier_id = (select count(*) from temp_Reports tr2
where tr2.identifier_id <= tr1.identifier_id) + (select MAX(identifier_id)
FROM temp_Reports)
Where identifier_id is Null
I created this table as a temporary test
CREATE TABLE [temp_Reports] (
[identifier_id] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[somedata] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
And added these values:
1 | Test1
2 | Test2
3 | Test3
Null | Test4
Null | Test5
I get
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'tr1'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '+'.
but I'm not clear why.
Matt
"James Hokes" <noway@.nospamthanksanyway.com> wrote in message
news:eMW1O9oeGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Well, if you don't want to add an IDENTITY column, and just want to add
> the zero-padded char, you could:
> 1.) Create temp table with IDENTITY column and primary key from source
> table
> 1.) Generate identity values for all rows in target table in the temp
> table
> 2.) Update target table to include a zero-padded version of the identity
> value
> Example:
> Let's say your table is called Customer and the primary key is CustomerKey
> varchar(10)
> BEGIN TRANSACTION
> CREATE TABLE
> #KeyGen
> (
> CustomerKey varchar(10) NOT NULL,
> NewID int NOT NULL IDENTITY (1,1)
> )
> INSERT INTO KeyGen (CustomerKey) SELECT CustomerKey FROM Customer
> WITH(TABLOCKX)
> ALTER TABLE Customer ADD NewKey char(10) NOT NULL DEFAULT('')
> UPDATE Customer SET NewKey = (SELECT RIGHT('000000' + CAST(NewID AS
> varchar(6)), 6) FROM #KeyGen WHERE KeyGen.CustomerKey =
> Customer.CustomerKey)
> DROP TABLE #KeyGen
> COMMIT TRANSACTION
>
> Error handling is an exercise for the reader.
> Cheers,
> James Hokes
> "Matt Williamson" <ih8spam@.spamsux.org> wrote in message
> news:%23yx8L0oeGHA.4304@.TK2MSFTNGP05.phx.gbl...
>|||>> The problem is the source table doesn't have a primary key. That's what
Make sure, in the future, to declare a primary key at the time of table
definition itself. Also, unless you have at least one set of columns that
are unique in the table, you have no way out.
The error is due to the alias used in the UPDATE clause. Moreover the logic
does not take into account the rows are already NULL. Assuming the second
column is unique within the table here is a workaround:
UPDATE tbl
SET col1 = ( SELECT COUNT( * )
FROM tbl t
WHERE t.col2 <= tbl.col2
AND t.col1 IS NULL )
+ ( SELECT MAX( col1 )
FROM tbl )
WHERE col1 IS NULL ;
Anith|||Matt,
1 -

> update temp_Reports tr1
Can not use alias in this way. Try:
update temp_Reports
set identifier_id = (select count(*) from temp_Reports tr2
where tr2.identifier_id <= temp_Reports.identifier_id) + (select
MAX(identifier_id)
FROM temp_Reports)
Where identifier_id is Null
go
2 -
The code will not give the result you are expecting, because the update runs
in a transaction, so the rows updated will not be seen by the "select"
statement that is doing the counting.
AMB
"Matt Williamson" wrote:

> The problem is the source table doesn't have a primary key. That's what I'
m
> creating with this query.
> I've been working with this code that I found in the archive, but I can't
> get it to work
> update temp_Reports tr1
> set identifier_id = (select count(*) from temp_Reports tr2
> where tr2.identifier_id <= tr1.identifier_id) + (select MAX(identifier_id)
> FROM temp_Reports)
> Where identifier_id is Null
> I created this table as a temporary test
> CREATE TABLE [temp_Reports] (
> [identifier_id] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [somedata] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> And added these values:
> 1 | Test1
> 2 | Test2
> 3 | Test3
> Null | Test4
> Null | Test5
> I get
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'tr1'.
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '+'.
> but I'm not clear why.
> Matt
> "James Hokes" <noway@.nospamthanksanyway.com> wrote in message
> news:eMW1O9oeGHA.3364@.TK2MSFTNGP05.phx.gbl...
>
>

Query to see if an int field starts with a certain number

How would I write a query on a table containing a column of ints, where I want to retrieve the rows where that int value starts with a number? I know that you can do this with strings by using "....WHERE thisfield LIKE ('123%')", but if 'thisfield' is an int, how would I do this? Thanks!Convert it to string, perform a substring, and then do your comparison.

Perhaps: substring(cast([thisfield] as varchar(50)),1,1)|||I don't know how the performance of this will compare, but if thisfield is non-negative, this should work as well:

[thisfield] / power(10, cast(log10([thisfield] as int))

Cheers,
-Isaac
|||

I hate to ask this, but the giant pink elephant in the room is "how do you have an int that doesn't start with a number?" What it sounds like you have is a column of string values that may or may not be an integer, and you want to see if the first character of the string is a number, right? For this it is:

thisColumn like '[1234567890]%'

But if the column is supposed to only contain integers, the best way to make sure that they are integers is to create the column using an integer datatype.

query to return week number 1 for first week of year starting on a monday?

Hi,
I'm trying to put together a query that will return number 1 for the first
week of the year that starts on a monday. I've found the following query:
select datepart(week, DateAdd(day, -1 * DATEPART (dw, '06-JAN-2008') -1,
'06-JAN-2008' ))
This will successfully return 53 for '06-JAN-2008' and 1 for '07-JAN-2008'
However, it will return 53 for '01-JAN-2007' when it should be returning 1.
So basically, it's inconsistent.
Does anyone know of a query that can correctly return the week number given
that the first week of the year begins on the first monday of the year.
Also, it would be good if the query was able to adapt to a database who's
first date is set to Sunday, or Monday
ThanksRob
DECLARE @.today AS DATETIME;
SET @.today = CAST(CONVERT(CHAR(8), '20070101', 112) AS DATETIME);
SELECT
@.today AS today,
DATENAME(Week,@.today - DATEPART(weekday, @.today + @.@.DATEFIRST - 1) + 1)
AS week_start_date
"Rob" <r_miller@.ozemail.com.au> wrote in message
news:uxq8GXlkIHA.748@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'm trying to put together a query that will return number 1 for the first
> week of the year that starts on a monday. I've found the following query:
> select datepart(week, DateAdd(day, -1 * DATEPART (dw, '06-JAN-2008') -1,
> '06-JAN-2008' ))
> This will successfully return 53 for '06-JAN-2008' and 1 for '07-JAN-2008'
> However, it will return 53 for '01-JAN-2007' when it should be returning
> 1. So basically, it's inconsistent.
> Does anyone know of a query that can correctly return the week number
> given that the first week of the year begins on the first monday of the
> year. Also, it would be good if the query was able to adapt to a database
> who's first date is set to Sunday, or Monday
> Thanks

Query to obtain missing number

I've been trying to figure out how to create a query that would list the missing numbers between a high and low number for a field. For example, If I have the recordset below:

1
3
4
6
7
9

I'd like the resulting recordset to be:

2
5
8

Is there a way to achieve this? Thanks, Jason.Yes, there are several ways.

What have you covered so far in class?

-PatP|||In Class? I'm not taking a class. I know the programming language fairly well, I just cannot figure this one out. Can you give me a quick example? Thanks, Jason.|||There are multiple ways to do this. Probably the simplest is to create a "numbers" table with one row for every interesting (possible) value that a number might have. For a two byte integer, this range could be -32768 through 32767. Once you've got the numbers table, you can do a simple exists test, something like:SELECT n.val
FROM numbers AS n
WHERE NOT EXISTS (SELECT *
FROM myRecordset AS r
WHERE r.val = n.val)Of course you'd also need to limit the result to just the values of interest in this case (between the Min and Max values already in your recordset).

-PatP|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||Does Foxpro support recursive queries? If so, you could recursively increment an integer up to some limit and exclude the non-qualifying rows.

Query to extract number of duplicates

This query is driving me insane.
I need to extract the number of rows in tblUsers where the first 3
characters of "fldSurname" are the same and they where born "fldDOB" on the
same year and month.
Can anyone help ?Poppy,
Try this link from MS!
I had to use it the other w to remove 150,000 duplicate rows from a live
database and it worked a treat.
You need to modify the SQL slightly to accomodate your data, but the logic
is good and should help you out.
Thanks Microsoft! :)
Immy
"poppy" <poppy@.discussions.microsoft.com> wrote in message
news:F7BE4539-D96B-4FDF-AD48-188649EC4CF9@.microsoft.com...
> This query is driving me insane.
> I need to extract the number of rows in tblUsers where the first 3
> characters of "fldSurname" are the same and they where born "fldDOB" on
> the
> same year and month.
> Can anyone help ?|||if all you need is the count:
select left(fldSurname,3) as surname_start, fldDOB, count(*) as rows
from tblUsers
group by left(fldSurname,3), fldDOB
poppy wrote:
> This query is driving me insane.
> I need to extract the number of rows in tblUsers where the first 3
> characters of "fldSurname" are the same and they where born "fldDOB" on th
e
> same year and month.
> Can anyone help ?|||What link from MS '
"Immy" wrote:

> Poppy,
> Try this link from MS!
> I had to use it the other w to remove 150,000 duplicate rows from a liv
e
> database and it worked a treat.
> You need to modify the SQL slightly to accomodate your data, but the logi
c
> is good and should help you out.
> Thanks Microsoft! :)
> Immy
> "poppy" <poppy@.discussions.microsoft.com> wrote in message
> news:F7BE4539-D96B-4FDF-AD48-188649EC4CF9@.microsoft.com...
>
>|||DOH!!!! God damn CTRL+C! :)
Here it is...
Just note that this will help to remove the records also...
If you want to just see how many you have then a simple count as suggested
by Trey will show the results.
http://support.microsoft.com/defaul...kb;en-us;139444
Regards
Immy
"poppy" <poppy@.discussions.microsoft.com> wrote in message
news:AFCD89C6-2862-4304-B554-0581E085E78C@.microsoft.com...
> What link from MS '
> "Immy" wrote:
>

Query to display a field based on a parameter

I have a client table, with 8 fields, all of which contain a phone number.
Bad design, I know, but let's not get into that here. My problem now is
that in addition to those fields, we have a "Preferred" field, which simply
names the field of the preferred phone number. So we have fields like
HomePhone, BusinessPhone, Fax, and the Preferred field says "HomePhone"
How can I create a query so that the record displays only that field that is
preferred? I hope that makes sense.
For instance: John Doe, Home Phone 123-4567, Business Phone 765-4321,
Preferred "Home Phone", I want to the query to only display the name, and
home phone.
For Jane Doe, Home Phone 123-4567, Business Phone 765-4321, Preferred
"Business Phone", I want to the query to only display the name, and business
phone.
All of these records are displayed in a datagrid.
Thanks for your help.SELECT CASE Preferred
WHEN 'Home Phone' THEN HomePhone
WHEN 'Work Phone' THEN WorkPhone
..
WHEN 'Yet Another Phone' THEN YetAnotherPhone
End as PreferredPhone
FROM LotsOfPhones
Roy Harvey
Beacon Falls, CT
On Fri, 7 Apr 2006 16:29:47 -0600, "KatMagic" <SSKatMagic@.yahoo.com>
wrote:

>I have a client table, with 8 fields, all of which contain a phone number.
>Bad design, I know, but let's not get into that here. My problem now is
>that in addition to those fields, we have a "Preferred" field, which simply
>names the field of the preferred phone number. So we have fields like
>HomePhone, BusinessPhone, Fax, and the Preferred field says "HomePhone"
>How can I create a query so that the record displays only that field that i
s
>preferred? I hope that makes sense.
>For instance: John Doe, Home Phone 123-4567, Business Phone 765-4321,
>Preferred "Home Phone", I want to the query to only display the name, and
>home phone.
>For Jane Doe, Home Phone 123-4567, Business Phone 765-4321, Preferred
>"Business Phone", I want to the query to only display the name, and busines
s
>phone.
>All of these records are displayed in a datagrid.
>Thanks for your help.
>

Query to count physician encounters

I need to count the total number of patient visits accumulated by each
provider in a medical practice between selected dates. I am using the table
of charges. Each visit may include several individual charges during the
visit, but I want to count the visit only once. Therefore I used this
statement (the date range is not actually hard coded):
SELECT DISTINCT ProviderCode, PatUniqueID, service_date
FROM Charges
WHERE service_date Between '01/01/1995' And '12/31/2005' ;
What I get is:
Provider Code PatUniqueID service_date
-- -- --
RMB 25AAAAAA 01/01/2005
RMB 983AAAAA 02/01/2005
etc.
However, what I readlly want is a count. How many of these lines occur for
provider RMB and each provider. The desired table would be:
Provider Code Encounter Count
-- --
RMB 25
JDR 51
etc.
I am hoping to do this in a single SQL query statements, possibly with
imbedded SELECT statements. Can anyone help me to write this statement,
please.Richard,
If the query you show returns exactly the rows you want to count
(by Provider Code), you can build from this query:
select ProviderCode, count(*) as EncounterCount from (
SELECT DISTINCT ProviderCode, PatUniqueID, service_date
FROM Charges
WHERE service_date Between '01/01/1995' And '12/31/2005'
) as RowsToCount
group by ProviderCode
Steve Kass
Drew University
richardb wrote:

>I need to count the total number of patient visits accumulated by each
>provider in a medical practice between selected dates. I am using the table
>of charges. Each visit may include several individual charges during the
>visit, but I want to count the visit only once. Therefore I used this
>statement (the date range is not actually hard coded):
>SELECT DISTINCT ProviderCode, PatUniqueID, service_date
>FROM Charges
>WHERE service_date Between '01/01/1995' And '12/31/2005' ;
>What I get is:
>Provider Code PatUniqueID service_date
>-- -- --
>RMB 25AAAAAA 01/01/2005
>RMB 983AAAAA 02/01/2005
>etc.
>However, what I readlly want is a count. How many of these lines occur for
>provider RMB and each provider. The desired table would be:
>Provider Code Encounter Count
>-- --
>RMB 25
>JDR 51
>etc.
>I am hoping to do this in a single SQL query statements, possibly with
>imbedded SELECT statements. Can anyone help me to write this statement,
>please.
>
>|||Right! Got it and thanks...
"Steve Kass" wrote:

> Richard,
> If the query you show returns exactly the rows you want to count
> (by Provider Code), you can build from this query:
> select ProviderCode, count(*) as EncounterCount from (
> SELECT DISTINCT ProviderCode, PatUniqueID, service_date
> FROM Charges
> WHERE service_date Between '01/01/1995' And '12/31/2005'
> ) as RowsToCount
> group by ProviderCode
> Steve Kass
> Drew University
> richardb wrote:
>
>

Query syntax: select parent record, including number of children?

OK, I'm totally blanking on how to get my query to do the right thing. I have two tables, "SurveyQuestion" and "SurveyAnswer" with a one-to-many relationship: a Question has many Answers, and they're hooked up by including a field in Answer that specifies the Question ID.

What I want to select is: all the columns of SurveyQuestion, and a count of how many SurveyAnswer records are associated with the SurveyQuestion. What's the right way to do this?? Should I make a stored proc or something to run the row count? Is there an easier way? I think the complication stems from the fact that I want to return a GROUP column alongside regular records, but I'm not really sure. I'm using MySQL 4.x if that affects your answer.4.x is not specific enough, you must be on at least 4.1 to run this --select Q.foo
, Q.bar
, Q.qux
, ( select count(*)
from SurveyAnswer
where questionID = Q.ID ) as answers
from SurveyQuestion as Q