How to get todays date in format YY/MM/DD and to compare it to another date passed into th

I need to do the following and am hoping someone can help me out.

I have C#(asp.net app) that will call a stored procedure. The C# will pass in a date to the
stored procedure. The date is in the format YY/MM/DD. Once inside of the stored procedure, the date
passed into the stored proc needs to be compared to todays date. Todays date must be determined in
the SQL.

So basically here is my pseudo code for what I am trying to accomplish. Basically I just am after
the comparison of the two values:

If @.BeginDate < TodaysDate

The difficult part is how to obtain the value for "TodaysDate"

Taking into consideration that "TodaysDate" should probably be in the format of YY/MM/DD considering that is how the date it is to be compared with is being passed in.

Can someone please code this out for me in Microsoft SQL. I would be forever grateful.

I figured out what I needed to know, but will have further questions and will need help. Thanks to all.

how to get this into a table?

Hi all,

Can any body please suggest me how to get the result from the following procedure into a table.

sp_help_job @.Execution_status=1

I just cannot get the result from this procedure into a table

Thanking in advance

Jacx

To get the o/p of a sp to a table the table should already be existing. The output columns should match the table structure. if the table exists

then

Insert into Tablename Exec yoursp

Madhu

|||

i will give u a simple solution. now ifu see the o./p of sp_help_job @.Execution_status=1 then u can see that there are many column. So the simple method is , first findout the script for sp_Hlep_job. for that run sp_helptext sp_help_job.When u run this query, u will get the source code of sp_help_job, and u can see the data type or you can make a wrapper which will automatically create a table for u

Madhu

|||

this is the script for the table i created for geting the results

CREATE TABLE [Tbl_JobDET] (
[Job_id] [uniqueidentifier] NULL ,
[Originating_server] [varchar] (255) NULL ,
[Name] [varchar] (255) NULL ,
[enabled] [int] NULL ,
[description] [varchar] (255) NULL ,
[start_step_id] [int] NULL ,
[Category] [varchar] (255) NULL ,
[owner] [varchar] (255) NULL ,
[notify_level_email] [int] NULL ,
[notify_level_netsend] [int] NULL ,
[notify_level_page] [int] NULL ,
[notify_email_operator] [int] NULL ,
[notify_netsend_operator] [int] NULL ,
[delete_level] [int] NULL ,
[date_created] [datetime] NULL ,
[date_modified] [datetime] NULL ,
[Version_number] [int] NULL ,
[last_run_date] [int] NULL ,
[last_run_time] [int] NULL ,
[last_run_outcome] [int] NULL ,
[next_run_date] [int] NULL ,
[next_run_time] [int] NULL ,
[next_run_schedule_id] [int] NULL ,
[current_execution_status] [int] NULL ,
[current_execution_step] [int] NULL ,
[current_retry_attempt] [int] NULL ,
[has_step] [int] NULL ,
[hsa_schedule] [int] NULL ,
[has_target] [int] NULL ,
[type] [int] NULL
) ON [PRIMARY]
GO

and i tried geting the results using this

insert tbl_jobDET
EXEC sp_help_job @.execution_status=1

and this is the Error i got

Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67
An INSERT EXEC statement cannot be nested.

Any suggestions on how can i resolve this error and get the result into the table ?

|||

Jacx,

A possible solution could be creating a linked server pointing to itself (loopback) and using:

select *

into #t

from openquery(Loopback, 'set fmtonly off; exec msdb.dbo.sp_help_job @.execution_status = 1')

go

select *

from #t

go

drop table #t

go

AMB

|||Jacx,

I was looking for the same, and came across this page:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=259078

It contains this suggestion, which works great:

SELECT * INTO #JobInfo
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
, 'set fmtonly off exec msdb.dbo.sp_help_job @.execution_status=4')

You may have to change your connection string or sp_help_job parameters...

how to get this into a table?

Hi all,

Can any body please suggest me how to get the result from the following procedure into a table.

sp_help_job @.Execution_status=1

I just cannot get the result from this procedure into a table

Thanking in advance

Jacx

To get the o/p of a sp to a table the table should already be existing. The output columns should match the table structure. if the table exists

then

Insert into Tablename Exec yoursp

Madhu

|||

i will give u a simple solution. now ifu see the o./p of sp_help_job @.Execution_status=1 then u can see that there are many column. So the simple method is , first findout the script for sp_Hlep_job. for that run sp_helptext sp_help_job.When u run this query, u will get the source code of sp_help_job, and u can see the data type or you can make a wrapper which will automatically create a table for u

Madhu

|||

this is the script for the table i created for geting the results

CREATE TABLE [Tbl_JobDET] (
[Job_id] [uniqueidentifier] NULL ,
[Originating_server] [varchar] (255) NULL ,
[Name] [varchar] (255) NULL ,
[enabled] [int] NULL ,
[description] [varchar] (255) NULL ,
[start_step_id] [int] NULL ,
[Category] [varchar] (255) NULL ,
[owner] [varchar] (255) NULL ,
[notify_level_email] [int] NULL ,
[notify_level_netsend] [int] NULL ,
[notify_level_page] [int] NULL ,
[notify_email_operator] [int] NULL ,
[notify_netsend_operator] [int] NULL ,
[delete_level] [int] NULL ,
[date_created] [datetime] NULL ,
[date_modified] [datetime] NULL ,
[Version_number] [int] NULL ,
[last_run_date] [int] NULL ,
[last_run_time] [int] NULL ,
[last_run_outcome] [int] NULL ,
[next_run_date] [int] NULL ,
[next_run_time] [int] NULL ,
[next_run_schedule_id] [int] NULL ,
[current_execution_status] [int] NULL ,
[current_execution_step] [int] NULL ,
[current_retry_attempt] [int] NULL ,
[has_step] [int] NULL ,
[hsa_schedule] [int] NULL ,
[has_target] [int] NULL ,
[type] [int] NULL
) ON [PRIMARY]
GO

and i tried geting the results using this

insert tbl_jobDET
EXEC sp_help_job @.execution_status=1

and this is the Error i got

Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67
An INSERT EXEC statement cannot be nested.

Any suggestions on how can i resolve this error and get the result into the table ?

|||

Jacx,

A possible solution could be creating a linked server pointing to itself (loopback) and using:

select *

into #t

from openquery(Loopback, 'set fmtonly off; exec msdb.dbo.sp_help_job @.execution_status = 1')

go

select *

from #t

go

drop table #t

go

AMB

|||Jacx,

I was looking for the same, and came across this page:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=259078

It contains this suggestion, which works great:

SELECT * INTO #JobInfo
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
, 'set fmtonly off exec msdb.dbo.sp_help_job @.execution_status=4')

You may have to change your connection string or sp_help_job parameters...

How to Get the SQL Affinity Mask value in Sql 2005

Hi All

Can u pls tell me How to Get the SQL Affinity Mask value in Sql 2005.

for SQL 2000 , we were using following query:

SQLStatement.printf("select c.value from master..sysconfigures c, master..spt_values v,"
"master..syscurconfigs r where v.type = 'C' and v.number = c.config and v.number >= 0 "
"and v.number = r.config and v.name ='affinity mask'");

lErrCode=SQLExecDirect(hStmt,(SQLCHAR*)pszSQLStatement,strlen(pszSQLStatement));

lSQLBindCol(nAffMask);

But With SQL 2005 , we are getting ZERO(0) ROWS SELECTED.

you could take a look at the following link in SQL 2005 book online to set the affinity mask

http://msdn2.microsoft.com/en-us/library/ms187104.aspx

Thanks

Sethu Srinivasan [MSFT]

SQL Server Manageability Dev Team

How to Get the SQL Affinity Mask value in Sql 2005

Hi All

Can u pls tell me How to Get the SQL Affinity Mask value in Sql 2005.

for SQL 2000 , we were using following query:

SQLStatement.printf("select c.value from master..sysconfigures c, master..spt_values v,"
"master..syscurconfigs r where v.type = 'C' and v.number = c.config and v.number >= 0 "
"and v.number = r.config and v.name ='affinity mask'");

lErrCode=SQLExecDirect(hStmt,(SQLCHAR*)pszSQLStatement,strlen(pszSQLStatement));

lSQLBindCol(nAffMask);

But With SQL 2005 , we are getting ZERO(0) ROWS SELECTED.

you could take a look at the following link in SQL 2005 book online to set the affinity mask

http://msdn2.microsoft.com/en-us/library/ms187104.aspx

Thanks

Sethu Srinivasan [MSFT]

SQL Server Manageability Dev Team

How to get the Sequence number without using temp table

Here is my problem:
I have a table with following columns:
PersonID FirstName LastName
102 John Ben
103 Josh Parker
104 Mark Ben
Now if I type SELECT * FROM Person WHERE LastName = 'Ben' these two records will be displayed
PersonID FirstName LastName
102 John Ben
104 Mark Ben
But I want this to return with one additional Sequence column like this:
New Column PersonID FirstName LastName
1 102 John Ben
2 104 Mark Ben
How can I add this so called "New Column" ?


Here's one way:
SELECT
COUNT(*) AS [New Column],
P1.PersonId,
P1.FirstName,
P1.LastName
FROM Person AS P1
JOIN Person AS P2 ON
P2.LastName = P1.LastName
AND P2.PersonId <= P1.PersonId
WHERE P1.LastName = 'Ben'
GROUP BY
P1.PersonId,
P1.FirstName,
P1.LastName
|||You are AWESOME !

How to get the returning value of a stored procedure?

Hi everyone!
I am new to sql server 2005 and visual studio 2005.

I have the following simple stored procedure that checks if a user exists:
-------------------------------
ALTER PROCEDURE [dbo].[sp_Users_AlreadyExists]

@.UserName varchar(256)
AS
BEGIN
SET NOCOUNT ON;

IF (EXISTS (SELECT UserName FROM dbo.Users WHERE LOWER(@.UserName) = LoweredUserName ))
RETURN(1)
ELSE
RETURN(0)
END
-------------------------------

I use the following code to execute the procedure on visual studio:
-------------------------------
.
.
.
cmdobj As SqlCommand
cmdobj = New SqlCommand(sp_Users_AlreadyExists, connobj)
cmdobj.CommandType = CommandType.StoredProcedure
cmdobj.Parameters.AddWithValue("@.UserName", "blablalala")
cmdobj.ExecuteNonQuery()
cmdobj.Dispose()
connobj.Close()
.
.
.
-------------------------------

I expected that cmdobj.ExecuteNonQuery() would return 1 if the userblablab exists or 0 if the user doesnt, but it just return -1 (i thinkbecause no row was affected)

Does anyone knows how to retrieve the value that my stored procedure returns?

Thanx in advance!

cmdobj.parameters.add("@.RETURN_VALUE",sqldbtype.int).direction=returnvalue

Then check the parameter value after the execute like:

dim val as integer=cmdobj.parameters("@.RETURN_VALUE").value

sql

HOW TO GET THE RESULTS IN THE SAME SEQUENCE IN ORACLE 9i AND SQL SERVER 2005?

In ORACLE 9i, I created the table test that show the tree structure of an organizaion with the following SQL statement:

CREATE TABLE TEST(
PARFOLDERNO NUMBER(8,0),
FOLDERNO NUMBER(8,0)
)

And select the data using the following SQL Statement:
SELECT PARFOLDERNO,FOLDERNO FROM TEST

The result is:
PARFOLDERNO FOLDERNO
0 2461
2461 2463
2461 2462
2462 2465
2462 2466
2463 2469
2463 2470

To show the subnodes of the root node 2461, the following SQL Statement is used:

SELECT PARFOLDERNO,FOLDERNO FROM TEST START WITH FOLDERNO=2461 CONNECT BY PRIOR FOLDERNO=PARFOLDERNO

the results:

PARFOLDERNO FOLDERNO
0 2461
2461 2463
2463 2469
2463 2470
2461 2462
2462 2465
2462 2466

I have created the table test with the same structure and the same data in SQL Server 2005. To show the subnodes of the root node 2461, the following SQL Statement is used:

WITH CTE_TEST(PARFOLDERNO,FOLDERNO)
AS
(
SELECT PARFOLDERNO,FOLDERNO FROM TEST WHERE FOLDERNO=2461
UNION ALL

SELECT TEST.PARFOLDERNO,TEST.FOLDERNO FROM TEST, CTE_TEST
WHERE TEST.PARFOLDERNO=CTE_TEST.FOLDERNO
)

SELECT PARFOLDERNO,FOLDERNO FROM CTE_TEST

PARFOLDERNO FOLDERNO

0 2461
2461 2463
2461 2462
2462 2465
2462 2466
2463 2469
2463 2470

The results are shown again in Oracle 9i and SQL Server 2005 as follwos:

Oracle 9i SQL Server 2005

PARFOLDERNO FOLDERNO PARFOLDERNO FOLDERNO
0 2461 0 2461
2461 2463 2461 2463
2463 2469 2461 2462
2463 2470 2462 2465
2461 2462 2462 2466
2462 2465 2463 2469
2462 2466 2463 2470

How can I get the result with the same sequence in SQL Server 2005?

Thanks!In ORACLE 9i, I created the table test that show the tree structure of an organizaion with the following SQL statement:

CREATE TABLE TEST(
PARFOLDERNO NUMBER(8,0),
FOLDERNO NUMBER(8,0)
)

And select the data using the following SQL Statement:
SELECT PARFOLDERNO,FOLDERNO FROM TEST

The result is:
PARFOLDERNO FOLDERNO
0 2461
2461 2463
2461 2462
2462 2465
2462 2466
2463 2469
2463 2470

To show the subnodes of the root node 2461, the following SQL Statement is used:

SELECT PARFOLDERNO,FOLDERNO FROM TEST START WITH FOLDERNO=2461 CONNECT BY PRIOR FOLDERNO=PARFOLDERNO

the results:

PARFOLDERNO FOLDERNO
0 2461
2461 2463
2463 2469
2463 2470
2461 2462
2462 2465
2462 2466

I have created the table test with the same structure and the same data in SQL Server 2005. To show the subnodes of the root node 2461, the following SQL Statement is used:

WITH CTE_TEST(PARFOLDERNO,FOLDERNO)
AS
(
SELECT PARFOLDERNO,FOLDERNO FROM TEST WHERE FOLDERNO=2461
UNION ALL

SELECT TEST.PARFOLDERNO,TEST.FOLDERNO FROM TEST, CTE_TEST
WHERE TEST.PARFOLDERNO=CTE_TEST.FOLDERNO
)

SELECT PARFOLDERNO,FOLDERNO FROM CTE_TEST

PARFOLDERNO FOLDERNO

0 2461
2461 2463
2461 2462
2462 2465
2462 2466
2463 2469
2463 2470

The results are shown again in Oracle 9i and SQL Server 2005 as follwos:

Oracle 9i SQL Server 2005

PARFOLDERNO FOLDERNO PARFOLDERNO FOLDERNO
0 2461 0 2461
2461 2463 2461 2463
2463 2469 2461 2462
2463 2470 2462 2465
2461 2462 2462 2466
2462 2465 2463 2469
2462 2466 2463 2470

How can I get the result with the same sequence in SQL Server 2005?

Thanks!|||Just to make it clear: Oracle result is correct, SQL Server 2005 result is wrong?

If that's so, I guess you should have asked this question on SQL Server forum, not Oracle.|||I'd use an ORDER BY clause.

-PatP|||Pat Phelan ,thank you very much.

Where is the clause "Order by " added?Please give me some example.

Anxoix to get your reply.|||You will have to use an ORDER BY somewhere. Not even Oracle guarantees that the results will always be returned in the same order if you don't use an ORDER BY.
Remember you are dealing with (mathematical) sets which do not have an implicit order. A DBMS is not a spreadsheet.

Using CONNECT BY does impose an implicit order, that's why in your statement it's very likely that the sorting will stay that way in Oracle, but if you want the SQL Server result sorted, tell the server to do so.|||If that's so, I guess you should have asked this question on SQL Server forum, not Oracle.he did, he posted in both, and the duplicate was removed, so he re-posted another duplicate, and now they are merged

jp7234, please do not cross-post|||both platforms same syntax for this

select blah
from table
where 1=1
order by blah

select blah
from table
where 1=1
union
select blah
from table1
where 1=1
order by blah

Or order by can use column number to order

IE order by 1, 2|||r937,thanks.

Thank you for your care. I wll not give the same questions in different forums.This time, for I am anxious to get help as soon as possible I do so.|||Thank you,all of above friends.

I have used the Clause "Order by",but I failed.

The results in Oracle 9i shows a tree-like structure.

PARFOLDERNO FOLDERNO
0 2461
2461 2463
2463 2469
2463 2470

2461 2462
2462 2465
2462 2466

We can find four branches:
1.the first 4 records show two branches
(1) 2461--2463--2469
(2) 2461--2463--2470

2.the first record and No. 5-7 record show other two branches
(3) 2461--2462--2465
(4) 2461--2462--2466

We can find that Parent node(0,2461) have two direct children node(2461,2463).(2461,2462). And the node information of the children node(2461,2463).(2461,2462) is sequencecd in different block.

But the results in SQL Server 2005 can not show these brances.And the results can not be sequenced in different block. I want to get the results with tree-like structure (the first branche,the second branche,and ....)in SQL Server 2005.

Please help me. If possible ,please give me your emails so that I can describe what I need in the results.

Thank a lots.|||I think that what you are describing here are analtic functions native to Oracle
CONNECT BY PRIOR

To my knowledge Sql Server does not have this functionallity at this time.

The only way to accomplish what I think that you are tring to accomplish is within a SP and build your result while cursoring through recordsets.

If you have a defined/finite number og related items
ie grand parent
Parent
child

You can accomplish your task using you table joined to itself.

select lvl1.Parent_node
, lvl2.Parent_node
, lvl3.Parent_node
from FROM TEST lvl1
left outer join test lvl2 on lvl1.folder_node =lvl2.Parent_node
left outer join test lvl3 on lvl2.folder_node =lvl1.Parent_node|||This can be done in TSQL, without using cursors, and without using recursion.

But before I go to the (not inconsiderable) trouble of explaining how, please explain WHY it is so important that the data be returned in that particular order. Data ordering is normally irrelevant to the database server, whether Oracle or MSSQL. Oracle just "happens" to return the data in that order for this particular non-standard function, so don't expect it to be a simple matter to force MSSQL to follow another engine's internal logic.

How to get the record created date?

How do I delete rows that are older than 180 days if the table does not
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.

> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.

> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
add[vbcol=seagreen]
>

How to get the record created date?

How do I delete rows that are older than 180 days if the table does not
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
--
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful, but
>> in most applications it isn't necessary on every single table.
>> A
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> Very True, but I prefer to drive home the point for the generalization,
>> and then once the idea has taken hold, start dealing with the exceptions.
>> And of course, not needed at all in some databases.
>> But for general business data ...
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful,
>> but in most applications it isn't necessary on every single table.
>> A
>>
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.
> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> How do I delete rows that are older than 180 days if the table does not
>> contain a column of timestamp? The following statement works fine if
>> parameter startdate is known:
>> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
>> Thanks in advance.
>> -tc
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.
> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> > SQL Server does not track this information, so you're going to have to
add
> > a column like:
> >
> > ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> > CURRENT_TIMESTAMP;
> >
> > Of course, you won't be able to take advantage of the values for this
> > specifc task until 6 more months have passed.
> >
> > A
> >
> >
> > "tcw" <tcwangs@.msn.com> wrote in message
> > news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> >> How do I delete rows that are older than 180 days if the table does not
> >> contain a column of timestamp? The following statement works fine if
> >> parameter startdate is known:
> >>
> >> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> >>
> >> Thanks in advance.
> >>
> >> -tc
> >>
> >
> >
>sql

How to get the record created date?

How do I delete rows that are older than 180 days if the table does not
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
--
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful, but
>> in most applications it isn't necessary on every single table.
>> A
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> Very True, but I prefer to drive home the point for the generalization,
>> and then once the idea has taken hold, start dealing with the exceptions.
>> And of course, not needed at all in some databases.
>> But for general business data ...
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful,
>> but in most applications it isn't necessary on every single table.
>> A
>>
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.
> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> How do I delete rows that are older than 180 days if the table does not
>> contain a column of timestamp? The following statement works fine if
>> parameter startdate is known:
>> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
>> Thanks in advance.
>> -tc
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.
> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> > SQL Server does not track this information, so you're going to have to
add
> > a column like:
> >
> > ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> > CURRENT_TIMESTAMP;
> >
> > Of course, you won't be able to take advantage of the values for this
> > specifc task until 6 more months have passed.
> >
> > A
> >
> >
> > "tcw" <tcwangs@.msn.com> wrote in message
> > news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> >> How do I delete rows that are older than 180 days if the table does not
> >> contain a column of timestamp? The following statement works fine if
> >> parameter startdate is known:
> >>
> >> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> >>
> >> Thanks in advance.
> >>
> >> -tc
> >>
> >
> >
>

How to get the parameter type in VB.net?

I am currently using the following embedded code to get the selected values from a Parameter and display in a textbox. I however have a number of reports and I don't want to include this in all of them. I have created a dll with some other functions but when i try to include the following function it throws an error on the Parameter type. What am I doing wrong, I am not all that familiar with dot net?

Public Function ShowParameterValues(ByVal parameter as Parameter) as String
Dim s as String

If parameter.count <= 5 then
If parameter.IsMultiValue and parameter.count > 1 then
s = " "
For i as integer = 0 to parameter.Count-1
if i = parameter.count - 1
s = s + CStr(parameter.Label(i))
else
s = s + CStr(parameter.Label(i)) + ", "
end if

Next
Else
s = " " + CStr(parameter.Label(0))
End If
else
s = " Only a maximum of 5 selection values can be displayed. "
End if
Return s
End Function

What's the error message?|||I am sorry..it says the type Parameter is not defined.|||

A multi-valued parameter is a 0-based array of objects, I'm pretty sure. But you should be passing the *name* of the parameter you want to reference, as a string, not the object. No?

Then you can say Parameters(tsParamName).Label(i) and Parameters(tsParamName).Count in your code...

HTH,

>L<

|||

It simply means it cannot find the type Parameter, either you need to reference the dll that contains the definition of the type Parameter or make to sure to include the CS file where that type Parameter is defined.

In your code,you are passing parameter as Parameter in the signature of the function, this is where it's complaining, it cannot find the Assembly where the type Parameter is defined.

|||

>>either you need to reference the dll that contains the definition of the type Parameter or make to sure to include the CS file where that type Parameter is defined.

Jonel, I honestly don't think that's correct... rather than including the reference to Parameter, I think the actual type he would need is ReportParameter here ... but in this case he could actually be passing the name of the parameter from the textbox expression (or wherever he's using the function), as I said...

>L<

|||

Hi, Lisa,

From within a custom assembly, you need to provide a fully qualified reference for the parameters type:

Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Parameters

If you use this reference, then when you install new releases of Reporting Services, you may need to recompile and redeploy the custom assembly.

|||

My apologies, Mary, Jonel, and everybody reading -- I misread the original post (saw the "embedded code" part but not that the poster was moving the code into a DLL!). Had a braino...

>L<

|||Thanks guys for your responses. After searching the net last week I found the answer. Thanks much for all your help.

How to get the null values in my stored procedure i am getting error dbnullvalue on front

I have the following stored proc. which i am using on the front end to get all the record from table:

if there are any fields it has anynull values in it i am getting error dbnull value error.
i have null value for ReviewerComment field, can you please tell me how to pass a "" if it is null, in the store proc only, to get all the fresh dat to front end before bnding it to the datagrid control.

CREATE PROCEDURE [dbo].[sp_displayrevws]
AS
select r.RevID,
rtrim(f.revwfunction) as revwfunction,
rtrim(u.uname) as uname,
CONVERT(varchar(10),r.Issued,101) as Issued,
r.ReviewerComment,
r.Response,
r.ModuleID,
rtrim(r.ModuleName) as modulename,
r.ReviewerUserID,r.RevFunctionid,r.Dispositionid
from TAB_ccsNetReviewers r, tabuname u, ccsfunctions f, ccsdisposition d where u.id = r.ReviewerUserID and r.RevFunctionid = f.id and r.Dispositionid = d.id and r.ModuleID = 1 order by r.RevID ASC
GO

Thank you very much.Note use of IsNull() function.

CREATE PROCEDURE [dbo].[sp_displayrevws]
AS
select r.RevID,
rtrim(f.revwfunction) as revwfunction,
rtrim(u.uname) as uname,
CONVERT(varchar(10),r.Issued,101) as Issued,
IsNull(r.ReviewerComment,''),
r.Response,
r.ModuleID,
rtrim(r.ModuleName) as modulename,
r.ReviewerUserID,r.RevFunctionid,r.Dispositionid
from TAB_ccsNetReviewers r, tabuname u, ccsfunctions f, ccsdisposition d where u.id = r.ReviewerUserID and r.RevFunctionid = f.id and r.Dispositionid = d.id and r.ModuleID = 1 order by r.RevID ASC
GO

|||You can use the case statement in sql to look at a field before its returned. For example, you could do this (uses the pubs db)

select au_lname, au_fname,
AuthorCity = Case when (city is null) then '' else city end
from authors

if any city values are null, they'll come back as a '' empty string.

Hope this helps,

Scott

How to get the missing dates

Hi Everyone,
I was wondering how I could get the missing dates. I have the following tabl
e:
--Attendance--
ClientId
AttendDate
--Patients--
PatientId
LastName
FirstName
ect...
This table holds dates that our client has been in our office. But I need to
find out what dates the clinet was not there given a date range. What is the
best way to do this? I have the following query. It will work for only one
client. The problem is that I may need to do it for all patients or just one
patient.
CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
@.PatientId varchar(20),
@.ToDate smalldatetime,
@.FromDate smalldatetime
AS
create table #range1(dt smalldatetime)
while (@.ToDate <= @.FromDate)
begin
insert into #range1 values(@.ToDate)
Set @.ToDate = dateadd(dd,1,@.ToDate)
end
Select dt from #range1
where dt not in (Select AttendDate from Attendance
where Patientid = @.PatientId)
drop table #range1
GO
The also only returns the dates themself, while I still need other data to
be included in the query like the first and last name of the Patient. I
played with the query some but could not get the results I was looking for.
Thanks for any comments.
MichaelYou may benefit from a calendar table. Try this resource:
http://www.aspfaq.com/show.asp?id=2519
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:81388BE6-50B5-459A-8919-C4C949097FB1@.microsoft.com...
> Hi Everyone,
> I was wondering how I could get the missing dates. I have the following
> table:
> --Attendance--
> ClientId
> AttendDate
> --Patients--
> PatientId
> LastName
> FirstName
> ect...
> This table holds dates that our client has been in our office. But I need
> to
> find out what dates the clinet was not there given a date range. What is
> the
> best way to do this? I have the following query. It will work for only one
> client. The problem is that I may need to do it for all patients or just
> one
> patient.
> CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
> @.PatientId varchar(20),
> @.ToDate smalldatetime,
> @.FromDate smalldatetime
> AS
> create table #range1(dt smalldatetime)
> while (@.ToDate <= @.FromDate)
> begin
> insert into #range1 values(@.ToDate)
> Set @.ToDate = dateadd(dd,1,@.ToDate)
> end
> Select dt from #range1
> where dt not in (Select AttendDate from Attendance
> where Patientid = @.PatientId)
> drop table #range1
> GO
> The also only returns the dates themself, while I still need other data to
> be included in the query like the first and last name of the Patient. I
> played with the query some but could not get the results I was looking
> for.
> Thanks for any comments.
> Michael
>sql

How to get the missing dates

Hi Everyone,
I was wondering how I could get the missing dates. I have the following table:
--Attendance--
ClientId
AttendDate
--Patients--
PatientId
LastName
FirstName
ect...
This table holds dates that our client has been in our office. But I need to
find out what dates the clinet was not there given a date range. What is the
best way to do this? I have the following query. It will work for only one
client. The problem is that I may need to do it for all patients or just one
patient.
CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
@.PatientId varchar(20),
@.ToDate smalldatetime,
@.FromDate smalldatetime
AS
create table #range1(dt smalldatetime)
while (@.ToDate <= @.FromDate)
begin
insert into #range1 values(@.ToDate)
Set @.ToDate = dateadd(dd,1,@.ToDate)
end
Select dt from #range1
where dt not in (Select AttendDate from Attendance
where Patientid = @.PatientId)
drop table #range1
GO
The also only returns the dates themself, while I still need other data to
be included in the query like the first and last name of the Patient. I
played with the query some but could not get the results I was looking for.
Thanks for any comments.
MichaelYou may benefit from a calendar table. Try this resource:
http://www.aspfaq.com/show.asp?id=2519
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:81388BE6-50B5-459A-8919-C4C949097FB1@.microsoft.com...
> Hi Everyone,
> I was wondering how I could get the missing dates. I have the following
> table:
> --Attendance--
> ClientId
> AttendDate
> --Patients--
> PatientId
> LastName
> FirstName
> ect...
> This table holds dates that our client has been in our office. But I need
> to
> find out what dates the clinet was not there given a date range. What is
> the
> best way to do this? I have the following query. It will work for only one
> client. The problem is that I may need to do it for all patients or just
> one
> patient.
> CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
> @.PatientId varchar(20),
> @.ToDate smalldatetime,
> @.FromDate smalldatetime
> AS
> create table #range1(dt smalldatetime)
> while (@.ToDate <= @.FromDate)
> begin
> insert into #range1 values(@.ToDate)
> Set @.ToDate = dateadd(dd,1,@.ToDate)
> end
> Select dt from #range1
> where dt not in (Select AttendDate from Attendance
> where Patientid = @.PatientId)
> drop table #range1
> GO
> The also only returns the dates themself, while I still need other data to
> be included in the query like the first and last name of the Patient. I
> played with the query some but could not get the results I was looking
> for.
> Thanks for any comments.
> Michael
>

How to get the in Quarterly

Hi All,

I want to select the data from the table in Quarterly
i have the following query but it is suming all the
months and giving
in following query i want to show sum of 01 to
03months in row and o4 to 06 in one row...like
that if i give 200101 to 200112...it has to give me 4
quaters...how to do that..

select CONVERT(CHAR(4),period,112), sum(composite)
from cdh_price_gap
where CONVERT(CHAR(6),period,112) between '200101' and
'200106'
group by CONVERT(CHAR(4),period,112)
order by CONVERT(CHAR(4),period,112)

i used to convert to compare the date in this formate
200101 thats it...

thanksselect cast( (month(period)+2) / 3 as integer ) as quarter
, sum(composite)
from cdh_price_gap
group by cast( (month(period)+2) / 3 as integer )

rudy
http://r937.com/

How to get the first occurrence of a record from a table?

I have the following query:

SELECTNotifications.[TimeStamp])

FROMdbo.vwGrantsMaster LEFT OUTER JOIN

dbo.CoFundNotifications ON dbo.vwGrantsMaster.GrantFMBId = dbo.CoFundNotifications.GrantsFMBId

It is returning multiple records from Notifications table since it allows multiple entries under a single GrantFMBId. For example for a single GrantFMBId there can be multiple TimeStamp. When retrieved all are appearing even distinct key word is used.

What I am looking is that to get only the first occurrence of a record for a GrantFMBId from Notifications table.

Any help?

Thank you in advance.

Am I in the wrong place...?|||

If I understand you correctly, you wish the FIRST row entered for a particular GrantsFMBId, [TimeStamp] combination.

Perhaps something like this will produce your desired results:

Code Snippet

SELECT dt.TS
FROM (SELECT
GrantFMBId,
TS = min( [TimeStamp] )
FROM dbo.vwGrantsMaster
GROUP BY GrantFMBId
) dt
LEFT JOIN dbo.CoFundNotifications cf
ON dt.GrantFMBId = cf.GrantFMBId

How to get the default data path of a SQL Server?

Could I get the default data path of a SQL Server if under following condition:
1) I have only known the SQL Server Name in my network.
2) I am using a workstation computer that connects to a SQL Server through network.
3) I can use store procedure.

The result I wanted is the default data path of a SQL Server like:
"[SQL Server Installed path]\MSSQL\Data", ie. most common case "C:\Program Files\Microsoft SQL Server\MSSQL\Data"

Does any method or store procedure could do the above case?I forget to state the following point:
1) I also need to handle the case if a SQL Server in network has multiple instances.
2) I use a SQL Server 2000.|||Hi,

One easy method is,

SELECT * FROM master..sysdatabases WHERE name LIKE '<database name>'

You can also find information @. http://support.microsoft.com/default.aspx?scid=kb;en-us;Q272705&SD=MSKB&

Thanks
Yogish

Originally posted by king
Could I get the default data path of a SQL Server if under following condition:
1) I have only known the SQL Server Name in my network.
2) I am using a workstation computer that connects to a SQL Server through network.
3) I can use store procedure.

The result I wanted is the default data path of a SQL Server like:
"[SQL Server Installed path]\MSSQL\Data", ie. most common case "C:\Program Files\Microsoft SQL Server\MSSQL\Data"

Does any method or store procedure could do the above case?|||by Alexander Chigrik
USE master
GO
DECLARE @.install_path NVARCHAR(260)
EXEC sp_MSgettools_path @.install_path OUTPUT
SELECT @.install_path
GO

... its an undocumented XP available.

How to get the count difference between two tables

Hi,
I need to calcualte the count difference between two tables. I will
apply the method to 6 count comparisons. The following is my sample
code, which did not work. Could anyone help me out? Thanks!
create table one
(x float)
insert into one
values(1)
insert into one
values(2)
create table two
(x float)
insert into two
values(1)
insert into two
values(2)
insert into two
values(3)
declare @.one int, @.two int, @.diff int
set nocount on
select count(*) from one
set @.one=@.@.rowcount
select count(*) from two
set @.two=@.@.rowcount
set @.diff=(@.two-@.one)
print @.diff
Thanks a lot!
MikeMichael -
Do this instead:
declare @.one int, @.two int, @.diff int
set nocount on
select @.one=count(*) from one
select @.two=count(*) from two
set @.diff=(@.two-@.one)
print @.diff
@.@.rowcount returns the number of rows returned by a query. So "Select
count(*) from any_table_here" will always have an @.@.rowcount = 1, because
there is only 1 value returned from a "Select count(*)" query.|||On Feb 8, 2:20 pm, apf <a...@.discussions.microsoft.com> wrote:
> Michael -
> Do this instead:
> declare @.one int, @.two int, @.diff int
> set nocount on
> select @.one=count(*) from one
> select @.two=count(*) from two
> set @.diff=(@.two-@.one)
> print @.diff
> @.@.rowcount returns the number of rows returned by a query. So "Select
> count(*) from any_table_here" will always have an @.@.rowcount = 1, because
> there is only 1 value returned from a "Select count(*)" query.
Hi,
Thanks for your help! It works perfect. Could you tell me when the
output goes to GRID or when the output goes to MESSAGE? Because I used
PRINT statement, the output goes to different places in different
runs.
Thanks,
Mike|||I'm not completely sure what you're asking.
When you use Query Analyzer, there is a drop down in the top navigation that
lets you choose whether the results are returned in a grid, in text, or
output to a file.sql

How to get the count difference between two tables

Hi,
I need to calcualte the count difference between two tables. I will
apply the method to 6 count comparisons. The following is my sample
code, which did not work. Could anyone help me out? Thanks!
create table one
(x float)
insert into one
values(1)
insert into one
values(2)
create table two
(x float)
insert into two
values(1)
insert into two
values(2)
insert into two
values(3)
declare @.one int, @.two int, @.diff int
set nocount on
select count(*) from one
set @.one=@.@.rowcount
select count(*) from two
set @.two=@.@.rowcount
set @.diff=(@.two-@.one)
print @.diff
Thanks a lot!
MikeMichael -
Do this instead:
declare @.one int, @.two int, @.diff int
set nocount on
select @.one=count(*) from one
select @.two=count(*) from two
set @.diff=(@.two-@.one)
print @.diff
@.@.rowcount returns the number of rows returned by a query. So "Select
count(*) from any_table_here" will always have an @.@.rowcount = 1, because
there is only 1 value returned from a "Select count(*)" query.|||On Feb 8, 2:20 pm, apf <a...@.discussions.microsoft.com> wrote:
> Michael -
> Do this instead:
> declare @.one int, @.two int, @.diff int
> set nocount on
> select @.one=count(*) from one
> select @.two=count(*) from two
> set @.diff=(@.two-@.one)
> print @.diff
> @.@.rowcount returns the number of rows returned by a query. So "Select
> count(*) from any_table_here" will always have an @.@.rowcount = 1, because
> there is only 1 value returned from a "Select count(*)" query.
Hi,
Thanks for your help! It works perfect. Could you tell me when the
output goes to GRID or when the output goes to MESSAGE? Because I used
PRINT statement, the output goes to different places in different
runs.
Thanks,
Mike|||I'm not completely sure what you're asking.
When you use Query Analyzer, there is a drop down in the top navigation that
lets you choose whether the results are returned in a grid, in text, or
output to a file.