How to encorporate Date and Time comparison to pull a field

I'm trying to pull this data in my first inner join trying to pull the rmsbalance based on the latest date/time combination but not having any luck with my syntax because sql is throwing errors due to my syntax below and I can't figure how how to do the check on the date and time combo:

SELECT rf.RMSTRANCDE,

rm.rmsacctnum,

SUM(rf.rmstranamt) AS [Sum Tran Amt],

rf10.rmsbalance

FROM RMASTER rm

INNER JOIN <-- Problem starts in this inner join

(

SELECT RMSFILENUM,

rmsbalance

FROM RFINANL a

where rmstrandte + rmstrantim = (select max(rmstrandte) + max(rmstrantm) from RFINANL)

) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(

SELECT RMSFILENUM,

RMSTRANCDE,

SUM(rmstranamt) AS rmstranamt

FROM RFINANL

GROUP BY RMSFILENUM, RMSTRANCDE

) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

GROUP BY rm.rmsacctnum, rf.RMSTRANCDE, rf10.rmsbalance

HAVING rf10.rmsbalance <> SUM(rf.rmstranamt)

AND rf10.rmsbalance <> 0.00

What is the error message? Syntax of the SELECT statement seems fine.|||

thanks, but I actually figured it out on my own

sql

How To Dynamically Pull Stored Procedure Arguments

I'm sure there is a way using the system tables or maybe on the stored
procedures of the master db to pull the arguments for a given stored
procedure, and their datatypes.
So, for a stored proc like this:
CREATE StoredProc1
OrderNumber AS BIGINT,
OrderName AS VARCHAR(50)
AS ...
I would, using this magical query I am hoping exists, get back:
OrderNumber, BIGINT, 4
OrderName, VARCHAR, 50
or something in that order. Anyone have any ideas?Try,
use northwind
go
exec sp_procedure_params_rowset 'SalesByCategory'
go
AMB
"David Samson" wrote:

> I'm sure there is a way using the system tables or maybe on the stored
> procedures of the master db to pull the arguments for a given stored
> procedure, and their datatypes.
> So, for a stored proc like this:
> CREATE StoredProc1
> OrderNumber AS BIGINT,
> OrderName AS VARCHAR(50)
> AS ...
> I would, using this magical query I am hoping exists, get back:
> OrderNumber, BIGINT, 4
> OrderName, VARCHAR, 50
> or something in that order. Anyone have any ideas?|||From MS SQL Books On Line, you'll read about querying INFORMATION_SCHEMA.PAR
AMETERS:
"Contains one row for each parameter of a user-defined function or stored pr
ocedure accessible to the current user in the current
database. For functions, this view also returns one row with return value in
formation.
The INFORMATION_SCHEMA.PARAMETERS view is based on the sysobjects and syscol
umns system tables.
To retrieve information from these views, specify the fully qualified name o
f INFORMATION_SCHEMA view_name."
"David Samson" <CaptainSlock@.nospam.nospam> wrote in message news:54296B5C-F33F-426A-95A3-7
7366FF14200@.microsoft.com...
> I'm sure there is a way using the system tables or maybe on the stored
> procedures of the master db to pull the arguments for a given stored
> procedure, and their datatypes.
> So, for a stored proc like this:
> CREATE StoredProc1
> OrderNumber AS BIGINT,
> OrderName AS VARCHAR(50)
> AS ...
> I would, using this magical query I am hoping exists, get back:
> OrderNumber, BIGINT, 4
> OrderName, VARCHAR, 50
> or something in that order. Anyone have any ideas?|||David Samson (CaptainSlock@.nospam.nospam) writes:
> I'm sure there is a way using the system tables or maybe on the stored
> procedures of the master db to pull the arguments for a given stored
> procedure, and their datatypes.
> So, for a stored proc like this:
> CREATE StoredProc1
> OrderNumber AS BIGINT,
> OrderName AS VARCHAR(50)
> AS ...
> I would, using this magical query I am hoping exists, get back:
> OrderNumber, BIGINT, 4
> OrderName, VARCHAR, 50
> or something in that order. Anyone have any ideas?
SELECT c.name, t.name, c.length, c.precision, c.scale
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t ON c.xtype = t.xtype
WHERE o.name = @.name
ORDER BY c.colid
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi There,
Why not simply try
sp_help 'procedure name'
OR
Select the name of Procedure and press (Alt+F1) If your QA is
customized for that
With warm regards
Jatinder Singh

How to dynamically pull data for the past month?

I have a query that I want to schedule as a DTS package and have it run on
the first of every month to pull data for the previous month. How can I set
the SQL statement to determine what the last month was and use that for the
query parameters?
Thanks in advance for your help!
Isaac WeathersThe last day of the previous month is
select dateadd(dd, -(datepart(dd,getdate()) ), getdate())
I didn't test this but it gets the current day of the month (say the
12th, ) , then subtracts that many days from the current date, leaving you
at the last day of the prior month...You can then take that date and
subtract the the day -1 from that date, giving you the first day of the
month...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Isaac Weathers" <Isaac@.DrivenHosting.com> wrote in message
news:ecUJFsOqFHA.156@.TK2MSFTNGP11.phx.gbl...
>I have a query that I want to schedule as a DTS package and have it run on
> the first of every month to pull data for the previous month. How can I
> set
> the SQL statement to determine what the last month was and use that for
> the
> query parameters?
> Thanks in advance for your help!
> Isaac Weathers
>|||'====yyyymm format
if len(Month(DateAdd("M", -1, Date()))) = 1 then
DateString = DatePart("YYYY", DateAdd("M", -1, Date())) & "0" &
Month(DateAdd("M", -1, Date()))
Else
DateString = DatePart("YYYY", DateAdd("M", -1, Date())) &
Month(DateAdd("M", -1, Date()))
End If
'=====mm/dd/yyy format
DateString = Month(DateAdd("M", -1, Date()) ) & "/01/" &
DatePart("YYYY", DateAdd("M", -1, Date()))|||This will do the trick:
DateAdd("m",-1,
CAST(CONVERT(nvarchar(2), Month(GetDate()))+ '/1/' +
CONVERT(nvarchar(4), Year(GetDate()))
AS SmallDateTime))
AS FirstDayOfLastMonth,
CAST(CONVERT(nvarchar(12), GetDate() - Day(GetDate())) AS SmallDateTime)
AS LastDayOfLastMonth,
GeoSynch
"Isaac Weathers" <Isaac@.DrivenHosting.com> wrote in message
news:ecUJFsOqFHA.156@.TK2MSFTNGP11.phx.gbl...
>I have a query that I want to schedule as a DTS package and have it run on
> the first of every month to pull data for the previous month. How can I set
> the SQL statement to determine what the last month was and use that for the
> query parameters?
> Thanks in advance for your help!
> Isaac Weathers
>

how to do replication from access 2000 to sql server 2000

My requirement i want to create publisher in access 2000 and subscribe the
data from sql server by using pull replication
Thanks in advance for any reply
Try creating a merge publication in sql server and set up the access
database as a heterogenous subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)