Max Date Select Stmt Problem

Jan 11, 2007

Hello Everybody,

I have a problem, with select stmt:

SELECT TOP 15 *
FROM oaVIEW_MainData AS TOP_VIEW,
oaLanguageData_TAB AS RwQualifierJoin with (nolock)
WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.10
23:59:59')
AND RwQualifierJoin.text_id = c_cfgRegPoint
AND (((RwQualifierJoin.local1 LIKE N'Position of any bubu')))
AND TOP_VIEW.c_dateTime=(SELECT MAX(SUB_VIEW.c_dateTime)
FROM oaVIEW_MainData AS SUB_VIEW,oaLanguageData_TAB AS
RwQualifierJoin1 with (nolock)
WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.10
23:59:59')
AND RwQualifierJoin1.text_id = c_cfgRegPoint
AND (((RwQualifierJoin1.local1 LIKE N'Position of any bubu')))
AND TOP_VIEW.c_dsmIdent=SUB_VIEW.c_dsmIdent)
order by c_dateTime desc


Please consider:
- top doesn't metter, if I will use one or 10000 result is always the
same.
- oaVIEW_MainData, is a view on major big table, holding lot of records
joinden with small table containing configuration data, over left outer
join; both tables are with nolock option,
- quersy supose to return last record from major table/view, in given
time, additionaly, with other where conditions (like in this case with
text),
- on major table, are indexes which one is on id field (not used in
this query at all), which is a pk clustered, and other is on dateEvt
(c_dateTime) which is a desc index with fill level 90%
- table has also other indexes, on three different fields, one of
theses is dsmIdent,

Now, if I'm using max(id) works very fast, and ok for me, but the
problem is, I should not use id, because might be, that the records
will be written in the table with random order, so the only one saying
which is newest, will be dateEvt.

Using dateEvt as max(), dramaticly slows query, so I'm acctualy unable
to get result. What is much more funny, server is totaly busy with this
query, and it's procesor jumps on 100%.

Now, because the query is builded dynamicly, by a user selections,
that's why we decided on such a parser ... problem is, it is not
working :(

Can I change index on dateEvt somehow, to sped this up?
Maybe construct query somehow different, to get this over max() date?

Please help

Matik

View 1 Replies


ADVERTISEMENT

Select STMT

Oct 18, 2000

Hi

Can anyone help me in modifying this select statement in order to add two more columns to see if the column is identity and if the column is indexed.

SELECT SUBSTRING(SYSOBJECTS.NAME, 1, 45) AS TABLE_NAME,
SUBSTRING(SYSCOLUMNS.NAME, 1, 40) AS COLUMN_NAME,
SUBSTRING(SYSCOLUMNS.NAME, 1, 15) AS DATA_TYPE,
SYSCOLUMNS.LENGTH,
SYSCOLUMNS.XPREC AS PRECISIONS,
SYSCOLUMNS.XSCALE AS SCALE,
SYSCOLUMNS.ISNULLABLE AS ALLOW_NULLS

FROM SYSREFERENCES
RIGHT OUTER JOIN
SYSCOLUMNS INNER JOIN SYSTYPES ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE
INNER JOIN
SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID ON
SYSREFERENCES.CONSTID = SYSOBJECTS.ID
WHERE SYSOBJECTS.XTYPE <> 'S' and SYSOBJECTS.XTYPE <> 'V' AND SYSOBJECTS.XTYPE <> 'P'
ORDER BY TABLE_NAME


thanks
Venu

View 1 Replies View Related

Select Stmt

Mar 27, 2008

How do I use the select statement to create a blank field in a temp table, so I can use the update statement to populate the data later.

I tried [Select ' ' as field] and then tried the update statement to later populate data and I am getting the following error:
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

Please help

View 3 Replies View Related

Why Won't This Work? Select Stmt

Sep 20, 2006

select uid, OrderID, Count(OrderID)As DupCnt
from OrdDetails
group by uid, OrderID
having count(OrderID) > 1

this returns no rows, can't I show another column to identify which uid goes with the dups, I did it before and now it doesn't work, probably something silly I'm missing.

thx,

Kat

View 9 Replies View Related

Number Of Rows In A Select Stmt

Aug 2, 2004

hey all,

I am writing a sproc:

select @strCourseNameRegFor = sal.CourseName , @strSectionNoRegFor = sal.SectionNo,
@dteStartDateRegFor = sal.StartDate, @dteEndDateRegFor = sal.EndDate,
@dteStartTimeRegFor = sal.StartTime, @dteEndTimeRegFor = sal.EndTime,
@strDaysOfWeekRegFor = sal.DaysOfWeek
from lars.dbo.tblSalesCourse as sal, lars.dbo.tblCourseCatalog as cat
where sal.SchoolYr = @intRegForYear and rtrim(sal.SchoolTerm) = rtrim(@strRegForTerm) and upper(rtrim(sal.CourseName)) = upper(rtrim(@strCourseNamePrev))
and cat.NewStuAllowed = 0 and sal.CourseName = cat.CourseName and sal.Cancelled <> 1 and cat.SchoolYr = sal.SchoolYr
and sal.MaxNoStudents > sal.CurrNoStudents

I want to check if the select returned an empty set or not. I cannot use @@rowcount because i am assigning the values to the local vars. I tried

if @strCourseNameRegFor is null
begin
set @err = 'No courses';
end

but for some reason even if there are any records in the set, the if condition is getting satisfied. Can anyone help?

View 3 Replies View Related

How To Get Recordset From Only Last SELECT Stmt Executed???

Feb 14, 2005

How to get recordset from only last SELECT stmt executed???

this is part of my code.. in T-Sql


Create proc some mySp
...
AS
...
set nocount on
if (@SelUserID is null)
select 0
else if (@SelUserID = @userID)

select 1

else
begin

select * -- select a.
from dms_prsn_trx_log
where @incNo = ult_incid_no and
prsn_trx_no = 10 and
trx_log_txt = @logText
if (@@rowcount != 0)
set @isForwardedByUser = 1
select 2 -- select b. I NEED This value.

end
set nocount off
GO

here it executes select a, b.
But, I want mySp return last executed select result. which is here "select 2"

I thought set nocount ON does that, but it doesn't.
How can I do???

View 2 Replies View Related

Generalized SP For Select Stmt : Better Option

Mar 24, 2006

Hi Everyone,

For my application, i am writing a generalized Sp for the Select Stmt.

I have started with as shown below,

Create procedure proSelect
@TabName varchar(1000),
@ColName varchar(1000),
@ConName varchar(1000)
As
Begin
Declare @str Varchar(8000)
If @ColName = '' Set @ColName = '* '
else Set @ColName = @ColName + ' '
Set @str = 'Select ' +@ColName+ 'From ' +@tabname
If @ConName <> ''
Set @str = @str + ' Where '+ @ConName
exec (@str)
End;


I wan to more generalize it.. so that all the functionalities of select stmt can be accomplished with this sp...

Can anyone help ???

Thanks in advance...

one more small doubt(personal): All these days i was a starting member of this forum,but today it has changed to 'Yak Veteran Posting'.. What does that mean.. If not to be disclosed in forum, Do mail me at
satishr@kggroup.com

Regards,
satish.r
"Known is a drop, Unknown is an Ocean"

View 8 Replies View Related

Answered - Sum The Same Field Twice In Select Stmt

Mar 17, 2008

Hello friends , I have table (MoneyTrans) with following structure
[Id] [bigint] NOT NULL,
[TransDate] [smalldatetime] NOT NULL,
[TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw'
[Amount] [money] NOT NULL
I need to write a query to generate following output <br>
Trans Date, total deposits, total withdrawls, closing balance <br>
i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate )
I am working on this for past two days with out getting a right solution. Any help is appreciated
Sara

View 2 Replies View Related

@@RowCount To Display Rows From Select Stmt

Feb 7, 2008

I've created a Stored Procedure which performs a Select against my table, and displays the rows returned via these stmts -
@RowCount int Output
SELECT @rowcount = @@RowCount
This Works fine when Executed from SQL Server, but when trying to invoke the SP from my ASP page it complains that the SP expects parameter '@RowCount' which was not supplied.
I don't need to supply it when invoking the SP directly, why do I need to supply it from ASP?
I tried defining it as NULL within my SP, but can't seem to get it to accept both the NULL & Output parms.
And while I'm at it, how do I get my ASP page to display this @RowCount value?
 
Many Thanks.
 
 

View 21 Replies View Related

SELECT Query Stmt Inside Stored Procedure

Nov 21, 2005

Friends,

What are the possible usuages of a SELECT query stmt inside a stored procedure ??

How can we process the results of the SELECT query other than for documentation/Reporting purposes(Correct me if i'm wrong in this) ??

can any one throw some lite on this ..

Thanks,
SqlPgmr

View 1 Replies View Related

SQL 2012 :: Use Date Trunc Or Date Function To Select Date Range For Month On Month View

Jul 29, 2015

My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see

Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table

View 9 Replies View Related

How To Select All Dates Upto Todays Date And Include The First Next Future Date

Jan 11, 2006

hello
how can i select all dates upto todays date and include the first next future date in a given data base

say todays date was the 01/06/2006 (MM,DD,YYYY)

below is a mock data base
id date (MM,DD,YYYY)
1 01/02/2006
2 01/04/2006
3 01/06/2006
4 01/09/2006
5 01/20/2006

i want to select all dates equal or less that 01/06/2006 and include the first next future date .. and in this case it would be 01/09/2006

so the results would return

1 01/02/2006
2 01/04/2006
3 01/06/2006
4 01/09/2006

View 2 Replies View Related

Transact SQL :: How To Select Rows From Table Where DATE Column Is Today's Date

Aug 31, 2015

So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.

SELECT*
FROM[dbo].[EODPosting]
WHERE[EODPosting].[MatchDate]=GETDATE()

Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?

View 2 Replies View Related

Date Format - Column Which Select First Day Of Given Month Of Converted Date

Oct 21, 2013

Aim – Convert the following field ”[INSTALLATION_DATE]” date format from “20090709” Into this “2009-07-09” ,

Also create a new column called “BegMonth” which selects first day of the given month of the converted date column

The table is ;

SELECT
[FDMSAccountNo],
[INSTALLATION_DATE]
FROM [FDMS].[dbo].[stg_LMPAB501]

Results
FDMSAccountNoINSTALLATION_DATE
87800000088420030521

Required Results
FDMSAccountNoINSTALLATION_DATEBegMonth
8780000008842003-05-212003-05-01

View 3 Replies View Related

Select A Date Minus Whatever Todays Date Is?

Feb 15, 2008

At the moment i have a query that is dependent on a date which is 42 days before whatever the date may be today.

The statement in my query I have to use is:

MailDate <= '2008-01-05'

I am wondering if i could make that statement automatically take off 42 days from todays date?

View 8 Replies View Related

Date Select Query - Select Between Two Dates

Aug 22, 2006

have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003.

i have tried with the following query, i need help putting my queries together
select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004')

when i run this query i get this message

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15

please help
mustfa

View 6 Replies View Related

EXECUTE A SQL Stmt From SQL 7.0 To 6.5

Jan 3, 2001

I have a need to execute a SQL stmt on SQL 6.5 from a 7.0 box and vice
versa. I know that in 7.0, you can create a remote server and execute
statements across 7.0 servers, but can it be done from 6.5 to 7.0 and
7.0 to 6.5?

It's for a conversion project and time is the essence.

TIA,
Mike

View 1 Replies View Related

Stmt Text From Profiler

Nov 2, 2005

does anyone have a script that cleans up profiler traces by removing the variables from tsql. thereby giving you the procedure text as written.
I saw this a few months ago and havent had any luck in finding it.

anyone?

View 1 Replies View Related

How To Retrieve Value From Exec Stmt?

May 10, 2004

hi, all..
the following is part of my sp
I want to know how to assign result of stmt.1 to @tp

declare @tp datetime
declare @tableName varchar(100)
SET @tableName = 'tblState'
Exec ('SELECT MAX(UpdateTime) FROM ' + @tableName) -- stmt.1

thank you..

View 6 Replies View Related

No Have Permission To Use The KILL Stmt

Apr 1, 2008

I am using the kill statement to terminate a process, and this is done through my VB.net program. But I get the error

User does not have permission to use the KILL statement

This is the store procedure to call for kill statement..Is there any problem to execute it on dinamic sql?


CREATE PROCEDURE Kill_Process
@DBName VARCHAR(100),
@TableName VARCHAR(1000)
AS
SET NOCOUNT ON;
DECLARE @spid smallint;
DECLARE @spid2 smallint;
DECLARE @loginame nchar(128);
DECLARE @nsql NVARCHAR(4000);
SET @loginame = 'xxxx'

EXEC Find_Lock_Info @DBName, @TableName

DECLARE ProcessCursor CURSOR FOR
SELECT spid FROM master.dbo.sysprocesses
WHERE dbid = db_id(@dbname) AND loginame = rtrim(@loginame) AND spid <> @@spid
AND spid IN (SELECT spid FROM dbo.tbl_Lock_Info where dbid = db_id(@DBName) AND OBJECT_NAME(ObjId) = @TableName)


OPEN ProcessCursor;
FETCH NEXT FROM ProcessCursor INTO @spid;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @nsql = 'KILL ' + CONVERT(nvarchar,@spid) + '';
EXEC sp_executesql @nsql;
FETCH NEXT FROM ProcessCursor INTO @spid;
END
CLOSE ProcessCursor;
DEALLOCATE ProcessCursor;
GO


CREATE PROCEDURE Find_Lock_Info
@DBName VARCHAR(100),
@TableName VARCHAR(1000)
AS

SET NOCOUNT ON

BEGIN
CREATE TABLE #tmpLockInfo
(
spid SMALLINT,
dbid SMALLINT,
ObjId INT,
IndIdSMALLINT,
Type NCHAR(4),
ResourceNCHAR(32),
ModeNVARCHAR(8),
StatusNVARCHAR(5)
)

INSERT INTO #tmpLockInfo
(
spid, dbid, ObjId, IndId, Type, Resource, Mode, Status
)
EXEC Lock_Info

TRUNCATE TABLE tbl_Lock_Info

INSERT tbl_Lock_Info
SELECT
spid, dbid, ObjId, Type, Status
FROM #tmpLockInfo WHERE dbid = db_id(@DBName) AND ObjId <> 0 AND OBJECT_NAME(ObjId) = @TableName


END
GO

View 5 Replies View Related

Update And Delete Stmt.

May 11, 2007

Hi,
I have two tables:

1. RubricReport
2. RubricReportDetail

How can I code this step in my stored procedure:

If @ReportID is NULL, insert a row into RubricReport table, and set @ReportID=@@IDENTITY; otherwise, update table RubricReport for columns LastUpdate and LastUpdateBy, and delete table RubricReportDetail where ReportID=@ReportID.

Table RubricReport has columns ReportID, County,Dsitrict, DataYears, LastUpdate and LastUpdateBy

Table RubricReportDetail has columns ReportID, IndicatorID, LocalPerf

Kindly help me.

Thanks in advance

View 3 Replies View Related

SQL Stmt To Exit From A Job In The ELSE Block

Mar 5, 2008

Hi experts,

I have a job that has several steps.
One of the steps is of type T-SQL. I have an IF...ELSE block in it. If the if statement is true...perform an action.
Else exit from the job reporting failure.

I would like to know what sql stmts should i use to exit from the job (in the ELSE block)
I tried to use EXIT in the else stmt but it is not exiting from the job step.


Thanks in advance

View 4 Replies View Related

Select Where Date = Todays Date

Apr 20, 2001

I have a table with a field 'service_date' held as datetime. I would like to select from the table where the service date is equal to today. The input is dd/mm/yy.

Cheers

Nathan

View 2 Replies View Related

How To Select The Date Part Of A Date

Sep 5, 2006

I use smalldatetime for a datetime and i just display the date part
i'd like to compare the date part of the smalldatetime and the date i have
how can i do that ?
I know we can select the day,month or year ...
If you know a link where i could find different kinds of example about sql command it would be great to

thanks

View 3 Replies View Related

Error Comming At Insert Stmt

Oct 16, 2006

assume connection is established and photo cloumn as data type as image in sql table.str_insert = "INSERT INTO SIS_TeachingStaff VALUES('TSMT2','V.RAJANIKANTH','LECTURER','TEACHING STAFF','Msc(Maths)','" & Emp_pic.Image &"' " cmd = New SqlCommand(str_insert, conn)cmd.Connection = conncmd.ExecuteNonQuery()Error at insert stmt:   Operator '&' is not defined for types 'string' and 'system.drawing.image'    

View 1 Replies View Related

Incorrect Syntax On Uery Stmt

May 4, 2008

I'm trying to execute the following stmt within a Stored Procedure on SQL Server 2005 -
SELECT DISTINCT PkgActions.[PKG ID] AS PKG_ID, [APPR STATUS] AS Status, [END EXEC DATE] AS "Exec Date", [COMMENT] AS Comment FROM [PkgActions]
JOIN PkgApprovers ON PkgActions.[PKG ID] = PkgApprovers.[PKG ID]
WHERE 1=1 and ([APPR STATUS] = 'approved' OR [APPR STATUS] = 'Denied')
 
It fails with the message - Incorrect syntax near 'approved'.
However when I run the same stmt within the Query editor it works properly.
I can't see what the SP doesn't like about this. Any thoughts?
 
Thanks

View 4 Replies View Related

Can Is Pass Tablename In SQL Stmt As Variable

Jul 1, 1999

I am trying to create a stored procedure for automating Bulk inserting into tables
for one database to another.
Is there any way i can pass the table name as variable to insert and select stmt


thanks in advance

View 1 Replies View Related

Update Stmt Takes Forever

Nov 26, 1998

We have a MS SQL Server 6.5 database table with 643,000 records.
There are several indexes including some clustered indexes.

We do a statement: update wo set udf3 = '1234567890123456' where woid = '123'

this returns immediately.

Then we try the same statement where the string is 1 character longer and it
takes 45 minutes to return. There is no indication of what the server is doing
during this time.

There is no index on UDF3 and WOID is the primary key.

Any suggestions what is happening? What can we do to correct it?
DBCC CheckTable finds no errors.

name rows reserved data index_size unused
-------------------- ----------- ------------------ ------------------ ------------------ ------------------
WO 643124 493418 KB 321580 KB 169824 KB 2014 KB

View 1 Replies View Related

Update Stmt Timesout In VBScript

May 5, 2004

I have a relatively simple update statement that runs fine from Query Analyzer and Enterprise Manager (two rows updated in less than 1 sec) but times out when I run it from a VBScript file.

Any ideas?

Thavalai

View 8 Replies View Related

I'm Baffled By The Single Quotes With STMT

Jul 20, 2005

Hi,Don't worry about the vars, they are defined,the following line give me an err of "Incorrect syntax near '.'."Goal: to rename nonstardard column name.EXEC sp_rename '+@tbuffer+'.['+@cbuffer+']','+Replace(+@cbuffer+','%[^A-Za-z0-9_#$@]%','')','COLUMN';Thanks.

View 6 Replies View Related

Update Stmt With An Table Alias?

Jul 20, 2005

Hi all,I am doing the change from having worked in Oracle for a long time toMS SQL server and am frustrated with a couple of simple SQL stmt's. Orat least they have always been easy.The SQL is pretty straightforward. I am updating a field with a Maxeffective dated row criteria. (PepopleSoft app)update PS_JOB as A set BAS_GROUP_ID = ' 'where EMPL_STATUS in ('D', 'L', 'R', 'S', 'T')and EFFDT = (select max(EFFDT) from PS_JOB where EMPLID = A.EMPLID)This stmt is not working. I am getting an error on the keyword 'as'. Ihave tried:update PS_JOB A set...update PS_JOB from PS_JOB A set...Same result, error on 'A' or error on 'from'.I also tried to add the table alias to the sub query, whichtechnically worked, but with wrong data result.So my question comes down to: How do I use a table alias in an updatestatement in MS SQL server?I worked around this by creating a temp table. But that does notfulfill my curiosity, nor is it an ideal solution.Thanks a lot,-OK

View 14 Replies View Related

ALTER Stmt - Column Default Values

Aug 30, 2001

I'd like to alter a table and add a column:

add_date datetime

Is there a way, in the ALTER statement, to have the value default to the current date -- GETDATE() -- anytime a row is inserted w/out an explicit value for the column.

Thx

View 1 Replies View Related

Case Stmt Returns Duplicate Result

Nov 28, 2007

Hi there,

The following is my table whereby i have joined projects table with issue table (this is 1 to many relationship).



I have the following query:
SELECT
odf.mbb_sector sectorid,

SUM(case when odf.mbb_projecttype = 'lkp_val_appl' then 1 else 0 end) total_appl,
SUM(case when odf.mbb_projecttype = 'lkp_val_infrastructure' then 1 else 0 end) total_infra,
SUM(case when odf.mbb_projecttype = 'lkp_val_eval' then 1 else 0 end) total_eval,
SUM(case when odf.mbb_projecttype = 'lkp_val_subproject' then 1 else 0 end) total_subprj,
SUM(case when odf.mbb_projecttype = 'lkp_val_nonit' then 1 else 0 end) total_nonit,
SUM(case when odf.mbb_projecttype = 'lkp_val_adhocrptdataextract' or
odf.mbb_projecttype = 'lkp_val_productionproblem' or
odf.mbb_projecttype = 'lkp_val_maintwoprogchange' then 1 else 0 end) total_others,
COUNT(distinct prj.prid) total_prj

FROM
PRJ_PROJECTS AS PRJ,
SRM_PROJECTS AS SRM,
ODF_CA_PROJECT AS ODF

LEFT JOIN RIM_RISKS_AND_ISSUES AS RRI ON RRI.pk_id = odf.id

WHERE
prj.prid = srm.id
AND srm.id = odf.id
AND srm.is_active =1
AND odf.mbb_projecttype not in ('lkp_val_budget','lkp_val_itpc')
AND odf.mbb_funcunit = 'lkp_val_operation'

GROUP BY
odf.mbb_sector
which returns me the following result :
.

The problem is at the lkp_val_infosystem where it returns 3 instead of 1 in the total_infra column. How do I correct my case stmt to return the correct no of projects breakdown by different project type? Currently, only the total_prj which returns correct data.

Thanks

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved