Select Max Value From Prior Where Conditions

Apr 7, 2006

I can't figure this out for the life of me. Wanted to know if it's possible to select certain date conditions in a query, then later reference those conditions and to only select the max of them.

I need to do this dynamically as I do not know what the max value is. I've provided an example below:

Select var1
From table1
where
(
(Date1 = '11/30/2005')
OR
(Date1 = '12/31/2005')
)
and Date1 = (Max of previously selected values e.g. '12/31/2005')

What I can't figure out is how to dynamically retrieve the max of 11/31/2005 and 12/31/2005. Any ideas are greatly appreciated.

View 1 Replies

Trying To Get The Value From A Select Prior To An Insert

Dec 29, 2003

I'm trying to find out if a certain row exists in my table, if it does exist then I do nothing if it doesn't exist I will insert a value. Only problem is i'm quite new and not sure how to get the values from the insert to correctly check if they already exist. Heres my attempt using query analyser

DECLARE @FK_UserID int, @NoteID int, @BeenRead NVARCHAR(10), @TMPuserID INT, @TMPnoteID INT

SET @FK_UserID = 1
SET @NoteID = 254
SET @BeenRead = 'MyTestHere'

SELECT DISTINCT Backup_UserNotes.BackUp_UserID, Backup_UserNotes.BackUp_NoteID
FROM User_Notes INNER JOIN
Note ON User_Notes.FK_UN_NoteID = Note.NoteID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID

WHERE BackUp_UserID = @FK_UserID AND BackUp_NoteID = @NoteID

SELECT @NoteID = @@IDENTITY
SELECT @FK_UserID = @@IDENTITY

IF @FK_UserID = NULL AND @NoteID = NULL

BEGIN
INSERT BackUp_UserNotes (
BackUp_NoteID,
BackUp_UserID,
BackUp_Read)

SELECT @NoteID, @FK_UserID, @BeenRead

END

-- RETURN 0
------------------------------------------------
GO

Anybody know the best solution? Thanks

View 1 Replies View Related

Prior Omint Using Select

Feb 29, 2008

One more question.

I need to use select using some type of a prior omit.

for example, I have 2 selects:

select * from table abc where zip in ('07661') and name like 'A%'

go

select * from table abc where zip in ('07661') and company like 'ABC&'

there could be common records in both select statements, but I want to exclude the same records from the first select when I run my second one.

I could say

select * from table abc where zip in ('07661') and company like 'ABC&' and name not like 'A%'

in my second select, but I rather not do that, since my queries has many select criterias and its not just 2 selects.

So is there another option? a subsitution for "go"?

thanks

View 3 Replies View Related

Using IF ElSE Like Conditions In An Select Statement

Mar 17, 2006

Hi All,
This is my problem. I need the out put of a sql select statement to be "true" or "false" depending on the actual columns value is positive or negative. Does any one how to do this.
Thanks in advance,
-VJ

View 1 Replies View Related

Select With Multiple Conditions

May 12, 2008

Select c.Plan, c.ClaimNumber
from tbFLags c inner join tbMembers m
On c.Claim = m.HistoryNum
where c.Plan = 'J318' and c.Paymon = c.Rmon and c.Rmon = '2008-03-01'

Now I want to add these into this statement, what should be done.

Members meeting any of the 3 sets of criteria should not be selected

1) tbFlags.Hosp='1'

2) tbFlags.RD='1' OR tbCMSFlags.RAType in ('D', 'I2')

3) Deceased = tbMembers.DOD is not null.

View 27 Replies View Related

1. Select A Field Twice With Different (special) Conditions

Sep 12, 2007

Hi guys,

My challenge is really 3 problems in 1.

I have Table1 and Table2 which are inner joined with an ID.
Each record in Table1 may have up to two corresponding values in Table2, which are Type and Name.

req1- I want to select Table2.Name twice (or more), with each selection conditioned on a specific Type.
req2- I also want all information pertaining to the same ID to be returned in each row
req3- I want all IDs to be returned, even if Table2.Name are empty for Type1 and/or Type2.

So, the source tables look something like this:

Table1.ID | ��
-----------------------
ID001
ID002
ID003
ID004

Table2.ID | Table2.Type | Table2.Name
--------------------------------------------------------
ID001 | Type1 | NameA
ID001 | Type2 | NameB
ID002 | Type1 | NameC
ID003 | Type2 | NameD

Ideally, my results should look like this:

Table1.ID | Table2.Name (Type=1) | Table2.Name (Type=2)
----------------------------------------------------------
ID1 | NameA for Type1 | NameB for Type2
ID2 | NameC for Type1 | (empty)
ID3 | (empty) | NameD for Type2
ID4 | (empty) | (empty)

Right now, I can only get Table2.Name to display once, and it won�t return the empty values, even with outer joins. The structure that I�m using is:

SELECT �
FROM �
WHERE Table2.Name IN
(SELECT Table2.Name
FROM Table1 INNER JOIN Table2 �
WHERE Table2.Name = �Type1�)

Sorry the abstractness, but this is the simplest way for me to express the problem.

Any help will be appreciated, thanks!

View 4 Replies View Related

Inserting Conditions In A Select Statement

Jun 12, 2007

How can i insert a if-else condition in a select statement or is there a way to specify a condition within a select statement?
Any reply is really appreciated..thank you..

shemay

View 11 Replies View Related

Transact SQL :: Select Seems To Be Ignoring WHERE Conditions

Apr 23, 2015

SQL Version: 2008 (not r2)
Problem: My Select statement seems to be unaffected by some of the conditions in the WHERE Clause. For instance

JCCD.Mth >= cutoffs.FiscalYear_FirstMonth (value '20130101') AND JCCD.Mth <= @WIPMonthCurrent (value '20130101')AND LTRIM(RTRIM(JCCD.Job)) = '71-' (see output and code below)
SQL Code:
declare @WIPMonthCurrent date = '20130101'
SELECT
JCCD.JCCo, JCCD.Job, JCCD.Mth, JCCD.Source, sum(JCCD.ActualCost) AS CostToDate

[code]....

View 5 Replies View Related

T-SQL (SS2K8) :: Select Query With Multiple Where Conditions And OR Operator

Feb 6, 2015

I have the got below T SQL query to fetch the matching record

DECLARE @MappingTable TABLE
(
Productname nvarchar(10),
identification_key1 int,
identification_key2 int,
identification_key3 int

[Code] .....

-- result - 'Orange'

This is an exact matching record and straight forward

Is it possible to identify the record using T SQL query based on the following scenarios

1) return the record - If all the three where conditions match
2) if record not found check and return the record where 2 columns values in the where condition match

-- Expected Result for below query: 'Orange', because 2 of the columns in where condition have matching values

SELECT Productname
FROM @MappingTable where identification_key1=1 or identification_key2 =2 or identification_key3 =1

-- result - 'Orange'

View 4 Replies View Related

SQL Server 2008 :: Join Another Table But With Select Conditions?

Mar 24, 2015

I have this sql....

Select
DISTINCT p.dbPatID, p.dbpatfirstname, p.dbPatLastName,
s.dbSchTypeCnt as SchDetailType, t.dbSchTypeCnt as SchTypeType,
ISNULL(r.dbStatusDesc, 'No Ref') AS dbStatusDesc,
ISNULL(t.dbSchTypeCode, 'No Ref') AS dbSchTypeCode,
ISNULL(t.dbSchTypeDesc, 'No Ref') AS dbSchTypeDesc,

[code]....

however, I only want the lastest a.dbPatApptTime and only when a.dbPFStatus = 1 and a.ClientRef = 'EPS'

So the stand alone sql could be....

Select Top(1) dbPatApptTime as LastVisitDate, dbSchTypeDesc as LastVisitDesc
from appointments
where dbPFStatus = 1 and clientref = 'EPS'
order by dbPatApptTime desc

I'm just not sure how to incorporate that into my sql or whether there is a better way,

View 9 Replies View Related

Select Rows Based On Multiple Date Conditions

Jun 5, 2008

Hi,
I have 1 table with 5 rows. One of the rows has dateTime values. I want to know how many rows there are with a value in that column < today AND how many rows there are with a value in that column > today.
I'm not sure how to do this.

SELECT Count(*) WHERE dateColumn <= today AND dateColumn > today gives me everything.
I like to end up with a column containing the count of rows <= today
and a column with rows where date > today.

Is this possible in SQL or do I have to retrieve all rows and then loop over the resultset and check each row?

Thanks,
Marc

View 2 Replies View Related

JOIN Efficiency Using Multiple ON Conditions Versus WHERE Conditions

Jan 10, 2008

My question is fairly simple. When I join between two tables, I always use the ON syntax. For example:

SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId)

No problems there. However, if I then decide to further filter the selection based on some trait of the UserRole, I have two options: I can add the condition as a WHERE statement, or I can add the condition within the ON block.

--Version 1:

SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId)
WHERE

UserRoles.Active = 'TRUE'

-- Version 2

SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId

AND UserRoles.Active = 'TRUE')

So, the question is, which is faster/better, if either? The Query Analyzer shows the two queries have the exact same execution plan, which makes sense, since they're both joining the same tables. However, I'm wondering if adding the condition in the ON statement results in fewer rows the JOIN statement initially needs to join up, thus reducing the overall initial size of the results table before the WHERE conditions are applied.

So is there a difference, performance wise? I imagine that if Users had a thousand records, and UserRoles had 10 records, then the JOIN would create a cartesian product of the two tables, resulting in 10,000 records in the table before the WHERE conditions are applied. However, if only three of the UserRoles is set to Active, would that mean that the resulting table, before applying WHERE conditions, would only contain 3000 records?

Thanks for whatever information you can provide.

View 7 Replies View Related

Connect By Prior

Aug 4, 2004

Hello,
I have to migrate an application from oracle to sqlserver but i habe query with
connect by prior
What is the equivalent in sqlserver 2000.
Please help me.

View 2 Replies View Related

Prior Sql Queries

Mar 1, 2006

Is there any way to check 1-2 days prior sql queries executed..??

However, i am going for auditing now.

Thanks,
Pavan

View 1 Replies View Related

Prior Omit

Apr 13, 2006

Hello, Is there a way to do a prior omit in a SELECT?

I currently have multiple selections separated by a GO, but I need to prior Omit the rows I selected prior

so I have

select * from mytabe where
Address <> ' ' and
Zip4 <> ' ' and
zip in ('12345')
Go

select * from mytabe where
Address <> ' ' and
Zip4 <> ' ' and
Purchcode in ('A','C')
Go

This continues for another 10 selections I need to constantly omit the selections made above.

Is there a way to do this in SQL?

Thanks!

View 2 Replies View Related

Using Date From Prior Record

Apr 8, 2004

Hello gurus, I have a table of data containing stop and start times of equipment, such as this:

StartTime....................EndTime
12/01/01 15:44...........12/01/01 18:44
12/02/01 3:44............12/02/01 14:44
12/02/01 15:22...........12/02/01 15:33

etc.

With this, I can build a query that subtracts the start and end dates to give me the total differences between each record... But, how do I write a query that can count the elapsed time between the current record's "start time" and the prior record's "end time" to give me the elapsed time between each record?

View 14 Replies View Related

Dates Prior To 1753-01-01

Jul 23, 2005

Hello,I have an Access DB with a column [date] holding dates from present day backto around year 1702, and I am trying to run DTS Import with this MDB, whichfails due to a conversion error.MSDN says the datetime data type only allows 1753-01-01 to 1900-01-01, thusI am under the impression having the [date] field as varchar is the only wayto store the dates.My questions are:a) Can dates prior to 1753-01-01 be stored in a date column?b) If dates are in a column of type varchar (or similar) is it possible tosort them chronologically with T-SQL ?c) 1753-01-01 seems particularly arbitrary, any reason for this?Many thanks for any help, it's driving me mad!John

View 6 Replies View Related

Killing Processes Prior To Restore

May 13, 2002

I have a scheduled job that will do a database restore at given time every day. Sometimes I run into a situation where some people leave themselves logged on to the database, which prevents the job from running.

Is there a way that I can set up my job to include killing any open processes against the database that I'm restoring prior to the restore being done?

View 3 Replies View Related

Dates In Years Prior To 1900?

Jan 13, 2000

I've set up a SQL7 database with MSAccess97 as a front end. I'm trying to enter a person with a birthdate prior to 1900, get an ODBC call error, "Datetime field overflow". How to enter dates prior to year 1900?
Thanks.

View 1 Replies View Related

Send Email Prior To A Datetime

Aug 11, 2003

This is quite a tricky one for me to figure out.

I have a datetime field in a databse for when a tender closes. More often then not its 4pm - for what its worth. And I want to send an email informing the manager 6hrs, 30mins prior and when the tender closes. So I need to somehow check that field either once a day and store those times somewhere or quite regularly ... like every hour.

I would be interested to hear how people would approach such a concept. I havn't got a great deal of experience in triggers or SQL mail.

It is in an asp file that I am doing it.

Any ideas would be hugely appreciated.

Cheers

tom

View 1 Replies View Related

Ask For Password Prior To Allowing Access

Nov 13, 2003

I am trying to make a connection to sql server using sspi (windows authentication), however, I do not wish to use the built in tokenauthentication system. Administrators are worried about somebody using an unattended logged on computer to gain access to the database. So I am left with using sql server authentication (which I am not fond of doing, more password administration) or what I would like to do is use windows authentication but make the user enter thier windows password prior to logging into sql server. Is there a way? Thanks in Advance.

Kent

View 2 Replies View Related

How To Script Off Indexes Prior To Dropping Them?

Oct 10, 2007

This is for SQL 2005 and I know how to right click an index and do a "script index as create to new query window"

Basically, the one of the Microsoft scripts or views will tell us that we have 100+ indexes that exist for a database but that they are not being used by SQL server.

I will probably take them "offline" for a while and then drop them later on.

Before dropping them, I would like to be able to recreate them within minutes if system performance is degraded after this happens.

I was working on a script to pull this info out of the sys. tables like below but was wondering if anyone already has a script or an easier way to do this. (...and I don't want to right click 100 indexes within 137 tables and script to a new window and then compile a big script)

SELECT

'Create ' +

i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS +

' Index [' +

i.name +

'] ON ' +

t.name +

CHAR(10) +

c.name +

' ASC'

FROM sys.index_columns ic

Join sys.indexes i ON i.index_id = ic.index_id

JOIN sys.tables t ON ic.object_id = t.object_id

Join sys.columns c ON ic.column_id = c.column_id

WHERE

i.name = 'IX_Sellers_StatusID' and

c.object_id = t.object_id and

i.object_id = ic.object_id

Thanks,
Brian

View 3 Replies View Related

Need A Task That Will Zip A File Prior To Upload Via FTP

Mar 28, 2006

I have a text file I need to zip (compress) before uploading it via an FTP task. Do I have to use a third-party tool to do this? Thanks.

View 15 Replies View Related

T-SQL (SS2K8) :: First And Last Day Prior Month As Input Parameters

Apr 16, 2014

I have to create a report and I want all activity for the previous month.

I need to calculate the First and Last Day Prior Month to be used as Input Parameters.

Would something like this be the case or is there a better solution?

[code="sql"]
SELECT DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0) as FirstDayPreviousMonthWithTimeStamp,
DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) as LastDayPreviousMonthWithTimeStamp
[/code]

I was thinking get the first day of the previous and current month to exclude the Timestamp and use a less then first day of current month?

View 3 Replies View Related

SQL 2012 :: Checking Existence Of Row Prior To Insert?

Aug 11, 2015

I have a situation where our stored procedure inserts records from table_1 to table_2 when they don't already exist (uses the EXIST statement) on that table. If table_1 contains multiple records that are the same, it appears after the 1st record has been inserted, it does not recognize it as being there when it checks the existence when attempting to insert record 2.

Here's an example of the script:

insert into table_2 (col1,col2,col3)
select col1,col2,col3
from table_1 t1
where not exists (select '1'
from table_2 t2
where t1.col1 = t2.col1
and t1.col2 = t2.col2
and t1.col3 = t2.col3)

Data from Table_1 -- Assume that table_2 does not contain these records

col1 col2 col3
AA 11 A1
AA 11 A1
BB 22 B2

All 3 records would be inserted to table_2 in this example.

View 7 Replies View Related

Transact SQL :: Get Rows Not Matching Prior Year

Apr 25, 2015

We were asked to fix a query to get rows from a prior year history table that did not match to rows in the current year to show a variance from one year to the next. Rows must match on [corpnbr],[plincd],[pgrpcd] and [pitmcd]. If the combination has rows in the current and prior year ([hstyr]) then everything is fine. However, if they have rows in the prior year (e.g. [hstyr]='2014') but not in the current year (e.g. [hstyr]='2015') then they do not show in the result. Below is how they designed the table and below that is the stored procedure to pull the records.

CREATE TABLE [dbo].[BillingHistory](
[BillingHistoryID] [int] IDENTITY(1,1) NOT NULL,
[entity] [varchar](4) NULL,
[plincd] [varchar](3) NULL,
[pgrpcd] [varchar](4) NULL,
[pitmcd] [varchar](4) NULL,
[newplincd] [varchar](2) NULL,

[Code] ....

View 13 Replies View Related

SQL Server - Oracle Differences - Connect By Prior

Dec 5, 2007

Hello ,

I'm facing a complicated problem and I don't think that the solution will be an easy one.

I have an SQL statement in Oracle which I need to translate it in Sql Server 2005.

select lpad(' ',5*(orderid)) || to_char(descr) as menui
from <table _name>
where MENU_ITEM not in ('test1','test2','test3') and item_parent not in ('test4,'test5,'test6')
start with <item_parent='item_parent' >
connect by prior <menu_item = item_parent and menu_name='ADR_m_adr_frame'; > ?(condition)

Somewhere I have read that SQL server does not support Hierarchical Sql statements. Is this true ? How am I going to do that ?

Any help will be appreciated.

Thank you

View 7 Replies View Related

Synchronizing Data Prior To Initial Snapshot

Aug 23, 2007

Hello folks,

I am trying to set up replication between a central office and multiple sites. This will be merge repl, since either central or satellites can be modified. However, one issue I ran into is setting up subscribers. I don't always have the option of starting with a publisher's snapshot; some of the subscribers have their own data already, which may or may not overlap with central site, and which needs to be synchronized into the central site somehow.

So far, I haven't seen any solution for this kind of scenario, at least nothing out of the box. Am I doomed to having to roll out custom data transfers (I don't think I can use DTS since all sites are running Express), or is there some standard way of doing this?

Thanks so much!

Sonya

View 6 Replies View Related

Transact SQL :: DATEADD - Determine Prior Month

Jul 8, 2015

My overall problem is to do some string manipulation and then DATEADD, but I still don't know why I getting what SQL is giving me.

To get the prior month, I use the following code;

SELECT
(
SELECT
RIGHT('00' + CAST(
CASE CAST(RIGHT(MAX(Eff_Period), 2) AS int) - 1
WHEN 0 THEN 12
ELSE CAST(RIGHT(MAX(Eff_Period), 2) AS int) - 1
END AS varchar(2)), 2)
)--, -- Month
--CAST(CAST(LEFT(MAX(Eff_Period), 4) AS int) - 1 AS varchar(4)) -- Year
FROM
Current_Membership;

This correctly gives me the proper month number for the prior month, in this case, '06' since the current month is '07.'
But, for debugging, I wanted to test to make sure it would properly adjust for January - '01' - by replacing MAX(Eff_Period) with '201501.'

It did. Worked fine. Then, to make really sure, I put in '201502.'

Instead of the single record of '01,' it shot out 177,209 rows of all '02's!

I put back in the MAX(Eff_Period) instead of the date string constant and, worked fine. Swapped back in the date string - boom! - 177,209 rows again.

Like I said, I can use DATEADD - Now I'm really confused. I changed the date string to '201501' just to verify before posting this, and it spit out 177,209 rows of '01's! So, to keep from altering things and to maintain the edit history, I started up another query and C&P'd everything to it. Then, on the original, I backed up to an earlier version, one that worked. That listing is the same as above, but the commented out comma in line 9 and the commented CAST in the line after it - to get the year - were uncommented.

Now, it works with both '201501' and '201502.' But, if I replace the comments, basically removing the CAST with the year, it slams back with 177,209 rows!

View 6 Replies View Related

Maintenance Back Up Plan Not Deleting Prior Backup

Apr 25, 2001

I have a maintenance plan set to back up my 2 primary databases, one about 1 GB, the other about 2GB. I have about 4 GB available on the back up drive. I have set it to delete old backups more than 1 minute old. Problem is, the first night it works fine, the second night it sometimes it refuses to delete the old and therefore does not have enough space to complete the new back-up. Does anyone run into this before? Does it complete both back-ups THEN erase the old, in which case I need to have about 6GB available? Sometimes it works and sometimes it doesn't. It can be going along fine for weeks then stop working. Any info or links to how this is supposed to work would help.

TIA

View 3 Replies View Related

The Timeout Period Elapsed Prior To Completion Of The Operation

Sep 10, 2006

Hi

I'm using two different server for application(.net version 1.1) and database(sqlserver200) with win2k3 environment. I'm getting the below error message all the times. I've veryfied the communication between the two server is fine.

"The timeout period elapsed prior to completion of the operation or the server is not responding"

Any help is greatly appreciated

Cheers

Veeru

View 1 Replies View Related

SQL Server 2012 :: Find Changed Rows (and Row Prior To Changes)

Jul 22, 2014

I have a table (represented by #Events) that holds modifications made to another table. I do have some control over the table structure and indexing. I want to pull all of the change records that were made between two dates.

The tricky part is to include the previous version of each record, which will usually be found prior to the start date in question.

The code that I have provided below works. So you can use it to easily see what should be returned. But it's very slow in production.

Any better method to pull this data together?

-- Production version of this table has 4.5 million rows (roughly 1,000 rows per day)
-- Primary key is on L4Ident (clustered)
-- nonclustered index on ProcessDate, LinkRL4
DROP TABLE dbo.#Events;
DROP TABLE dbo.#Results;
CREATE TABLE dbo.#Events (
L4Ident int IDENTITY(1,1) NOT NULL,

[Code] ....

View 4 Replies View Related

T-SQL (SS2K8) :: Finding The Item Filled In Prior To Current One

May 13, 2015

I have data similar to the below

CREATE TABLE #TEMP
(
TYPE VARCHAR(10),
SEQ INT,
SUB_TYPE VARCHAR(10))

[Code] ....

Now for each type the seq is very important. Effectively by order of seq the subtype stays the same until another subtype changes it. So for TYPE1 100,110 and 150 are A. 170, 200,220 are B. 230 and 250 are C and so on.

However as you can see the data isnt actually stored in the row. I need a select statement that shows this data.

I have done this:

SELECT t1.*,t3.SUB_TYPE FROM #TEMP t1
CROSS APPLY
(SELECT MAX(SEQ) SEQ FROM #TEMP AS t2 WHERE t1.SEQ >= t2.seq AND t2.SUB_TYPE <>'' AND t1.TYPE = t2.TYPE
GROUP BY t2.TYPE) t2
INNER JOIN
#TEMP t3
ON t3.TYPE = t1.TYPE AND t2.SEQ = t3.SEQ

And it seems to work. Is this the easiest way to do it or am i missing something?

View 3 Replies View Related