This May Be A Tough One: Contains With Inflectional And NOT Logic...Can It Be Done?

Aug 10, 2007

Thank you in advance!

I know from other posts that this works:
CONTAINS(column, 'formsof(inflectional,sport) AND formsof(inflectional,award)'

But I have an advanced googlesque search that includes "Without the Words:". I want to include inflectionals in the list of words to be omitted. Can this be done?

So far I have not had any success with:
'NOT(formsof(inflectional,sport)) AND NOT(formsof(inflectional, award))'

Any help would be greatly appreciated!

View 1 Replies

Using Contains To Find Inflectional Variations Of Multi Words With AND Logic

Jan 23, 2007

what if you want to search with AND logic using the FORMSOF(inflectional,...) methodology.

if my search phrase is "sport award" I can easily do an OR search using the following in my where clause:
CONTAINS(Colname,'formsof(INFLECTIONAL,sport,award).
but the and is far more tricky....

does anyone know how to do this without having multiple Contains statements (which greatly increases overhead)?

I know that I can use AND in a straight contains like so:
CONTANS(column, '"sport" AND "award"') but this does not allow me to explore inflectional variations on the words...

nesting multiple FORMSOF's doesn't seem to work either like so:
contains(column,'"formsof(inflectional,sport)"' AND 'formsof(inflectional,award)"')

anyone else found how to do this?

View 4 Replies View Related

HOW!!! Do You Use Paramaters With FormsOf(INFLECTIONAL, @xxx)

Aug 14, 2006

I'm having trouble using paramaters in a full-text search with FormsOf(INFLECTIONAL:DECLARE @SearchWord nvarchar(4000)
SET @SearchWord = 'tax'
SELECT listing_id, RANK, name, address, city, zip, heading, phone
FROM listings a,
FREETEXTTABLE(listings, *, 'FormsOf(INFLECTIONAL, @SearchWord')
WHERE [KEY] = a.listing_id
ORDER BY RANK DESC, name

This returns no results. It treats the paramater as a NULL value. But this...

SELECT listing_id, RANK, name, address, city, zip, heading, phone
FROM listings a,
FREETEXTTABLE(listings, *, 'FormsOf(INFLECTIONAL, tax')
WHERE [KEY] = a.listing_id
ORDER BY RANK DESC, name

returns over 500 results. I've been banging my head against this for 2+hours. Google has been no help as every example shows the result hard-coded in like the second example.PLEASE HELP!!!!!!

????????

View 4 Replies View Related

SQL Sever 2005 - Full Text FormsOf Inflectional

Feb 11, 2006

We have 2 SQL Server 2005 machines. One has the CTP version installed,the other is the RTM.When running the query below, results are returned for the CTP versionbut not the RTM version.select * from table t1inner join containstable(table, column, 'formsof(inflectional,''b�cher'')') t2on t1.[id] = t2.[key]I have checked that the same data is present in the tables on bothmachines and also that the FTI has been fully populated with a statusof "idle".Please note that the word used "b�cher" is a German word and shouldreturn "buch" as a match.Is this a bug with the RTM version of SQL Server 2005??

View 1 Replies View Related

This Is A Tough One

Jun 27, 2007

The following table is counts of the patients that are currently in these statuses. For example, there are 4 people in Triage/Greenhttp://www.helixpoint.com/sql/TriageStatusGrid.jpgIf you look at a screen shot of the tables below, Red/Yellow/Green... These are in the Priority table. Red Being "Immediate or ID of 1http://www.helixpoint.com/sql/db.gifNow the grid and the db do not match...but here is a scenario from the db screen shot.. Patient 1, 2, 3, and 4 are currently in Sector 2 (transport)Patient 1, 3, and 4 are currently in Priority 1(Immediate)Here is a kicker. Patient 4 can not be counted because he has a dischargeDateTime in the Patient table.So here are the counts I need to get:So there are 3 patients in the transport column ( Patient 1, 2, 3) “4 has been discharged�?2 patients in the immediate/Red column ( Patient 1, 3) “4 has been discharged�?I would need to return a number 2 for the Transport/Red columnHow can I do this sql? Would I do this in multiple sql calls? Can you give me an example?This is what I triedIt does not seem to use the current status SELECT COUNT(*) AS Expr1FROM Patient LEFT OUTER JOINSectorHistory ON Patient.PatientID = SectorHistory.PatientID LEFT OUTER JOINPriorityHistory ON Patient.PatientID = PriorityHistory.PatientIDWHERE (Patient.DischargeDateTime IS NULL) AND (PriorityHistory.PriorityID = 1) AND (SectorHistory.SectorID = 2)

View 1 Replies View Related

Tough Question

Jun 25, 2004

I have an employee table, which i have to join with itself because I have to findout someones primary boss. An employee can have several bosses...

This is an example of the table.

Emp_Id 1
Emp_name John
Boss 'n/a'

Emp_Id 2
Emp_name Peter
Boss 1

Emp_Id 3
Emp_name Mary
Boss_Id 1

Emp_Id 3
Emp_name Mary
Boss_Id 2

I know this isnt a normalized table, but anyway... for each boss that an employee has there is one record depicting the employes boss.

In this case Mary has 2 bosses, John and Peter.

What I need is a query that returns the employee information and the primary boss( in this case the boss with the lowest id)

So for mary the query would return

Emp_iD, Emp_name, Boss_Id
3 Mary 1

Anyone know how could i do this?

View 2 Replies View Related

Tough Problem...

Jun 29, 2004

I need a query that will return data in the following format.

Col1 Col2 Count
---------------
A | B | 1
A | C | 2
A | D | 3
A | E | 4
B | A | 1
B | B | 2
B | C | 3
--------------

In other words I want to group the results by col1 and when col1 one changes I want to restart my rowcount. I also want to return the row count for each record.

I have tried many different methods, but I am starting to think that this is not even possible.

Any ideas?

Thanks...

View 13 Replies View Related

Tough Question

Apr 21, 2008

Ok, I'm needing to write a view for a 3rd party scripting tool to call. Apparently this scrpting tool cannot call a function or SP.

Here is the problem. The view needs to take a notes col and a date col
and concat them together as one col and the cus acct # as 2nd col.
There will be several rows of notes to one cus acct #.

So if a normal select against this returns

note date acct
================================
txt1 1/1/00 1
txt2 1/2/00 2
txt3 1/3/00 1

I need to return this through a view

notes acct
===========================
txt1 1/1/00, txt3 1/3/00 1
txt2 1/2/00 2

I realize there are several ways to do this in a SP or function
but through a view?

View 2 Replies View Related

Tough Query

Feb 13, 2004

Hi,

I have a table with a couple of million rows. Each row as its datetime field spilt between numerous columns (year, month, day, hour... it's a datawarehouse fact table).

Here's what I'd like to do in a query:

If a value is missing (in the column value) it's set to -999. What I'd like to do is to set this column to the value of the hour before this one. For example, here's some data before and after an update:

BEFORE:
MONTH DAY HOUR VALUE
==================
01 31 21 33.5
01 31 22 -999
03 07 24 87.6
03 08 01 -999

AFTER:
MONTH DAY HOUR VALUE
==================
01 31 21 33.5
01 31 22 33.5
03 07 24 87.6
03 08 01 87.6

Here's the complete design of my table
IDENT nvarchar 50
THEDATE datetime
THEDAY int
THEMONTH int
THEYEAR int
THEVALUE real
SOURCE nvarchar 255

How can I do this?

Thanks,

Skip.

View 2 Replies View Related

Tough Little Trigger

Jul 23, 2005

Given the following 3 Tables:CREATE TABLE [Company] ([CompanyID] [int] NOT NULL ,[DateTimeCreated] [datetime] NOT NULL CONSTRAINT[DF_Company_DateTimeCreated] DEFAULT (getdate()),[DateTimeModified] [datetime] NULL ,CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED([CompanyID]) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [CompanyOffice] ([CompanyID] [int] NOT NULL ,[OfficeID] [int] NOT NULL ,[IsActive] [bit] NOT NULL ,CONSTRAINT [PK_CompanyOffice] PRIMARY KEY CLUSTERED([CompanyID],[OfficeID]) ON [PRIMARY] ,CONSTRAINT [FK_CompanyOffice_Company] FOREIGN KEY([CompanyID]) REFERENCES [Company] ([CompanyID]),CONSTRAINT [FK_CompanyOffice_Office] FOREIGN KEY([OfficeID]) REFERENCES [Office] ([OfficeID])) ON [PRIMARY]GOCREATE TABLE [Office] ([OfficeID] [int] NOT NULL ,[DateTimeCreated] [datetime] NOT NULL CONSTRAINT[DF_Office_DateTimeCreated] DEFAULT (getdate()),[DateTimeModified] [datetime] NULL ,[FullOfficeName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL ,CONSTRAINT [PK_Office] PRIMARY KEY CLUSTERED([OfficeID]) ON [PRIMARY]) ON [PRIMARY]GOThe CompanyOffice.dbo.IsActive bit field is supposed to be marked"true" for 1 record per a given Office (i.e. there can only be a single"Active" Company for any given Office). I decided the best way toenforce is through a trigger...My initial thoughts were a toggling effect (similar to the behaviorthat a radio button exhibits)... which would work like a champ for aSingle Row Insert or Update but for a Multi Row Insert/Update not thatstaight forward... I fooled around a little with some complicatedsub-queries that did not pan out. The only other way to do this is toutilize a cursor (at least that I can think of). Because of theoverhead with a cursor, I find this incredibly undesirable.My secondary thought was to just restrict an Insert or Update Statementthat leaves the Table in an "error" state (2 or 0 Active Companies peran Office). Then I realized that if the "Toggling Trigger" did notexist from above, it will often be the case that the Table would haveto be left in an "error" state for a short while, until a second updatestatement is run. (example, I insert a new active Company in theCompanyOffice table for an Office, then I go to the other activeCompany record for this Office and set the IsActive flag to false...for that short period of time between the 2 statement the DB is an"error" state, because there are 2 Active Companies for that singleOffice.) That makes this solution very undesirable.Any suggestions?Thanks in Advance --Rich

View 7 Replies View Related

Tough Sql Query

Feb 18, 2006

I am going mad with this Query. I need to join 3 Tables. Their FormatsareVouchers[VoucherID] [uniqueidentifier] NOT NULL ,[VoucherTypeID] [int] NOT NULL ,[VoucherNo] [int] NULL ,[VoucherDate] [datetime] NOT NULL ,[VoucherNarration] [varchar] (255)CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED([VoucherID]) ON [PRIMARY]Ledgers[LedgerID] [int] IDENTITY (1, 1) NOT NULL ,[LedgerName] [varchar] (50) COLLATECONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED([LedgerID]) ON [PRIMARY]CREATE TABLE [Transactions] ([TransactionID] [uniqueidentifier] NOT NULL ,[VoucherID] [uniqueidentifier] NOT NULL ,[ByTo] [char] (1)[LedgerID] [int] NOT NULL ,[Credit] [money] NOT NULL ,[Debit] [money] NOT NULL ,CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED([TransactionID]) ON [PRIMARY] ,CONSTRAINT [FK_Transactions_Ledgers] FOREIGN KEY([LedgerID]) REFERENCES [Ledgers] ([LedgerID]),CONSTRAINT [FK_Transactions_Vouchers] FOREIGN KEY([VoucherID]) REFERENCES [Vouchers] ([VoucherID])) ON [PRIMARY]GOThe Required Output isID VoucherNo VoucherDate LedgerName Amount1 1 2001-09-03 Bank-1 2400.002 2 2001-09-03 Cash 600.003 3 2001-09-03 TAX A/C 0.004 4 2001-09-03 Bank-1 4000.005 5 2001-09-03 Bank-10.00But, I am getting More than One row from the transactions table. I justneed the first matching rowID VoucherNo VoucherDate LedgerName Amount1 1 2001-09-03 Bank-1 2400.002 2 2001-09-03 Cash 600.003 3 2001-09-03 TAX A/C 0.004 4 2001-09-03 Bank-1 4000.005 4 2001-09-03 Cash 400.006 5 2001-09-03 Bank-1 0.007 5 2001-09-03 Cash 5035.00The Query I am using isSELECTdbo.Vouchers2001.VoucherID,dbo.Vouchers2001.VoucherNo,dbo.Vouchers2001.VoucherDate,dbo.Ledgers.LedgerName,SUM(dbo.Transactions2001.Debit) AS AmountFROM dbo.Vouchers2001 INNER JOINdbo.Transactions2001ON dbo.Vouchers2001.VoucherID =dbo.Transactions2001.VoucherID INNER JOINdbo.Ledgers ON dbo.Transactions2001.LedgerID =dbo.Ledgers.LedgerIDWHERE (dbo.Vouchers2001.VoucherTypeID = 1)GROUP BY dbo.Vouchers2001.VoucherID,dbo.Ledgers.LedgerName,dbo.Vouchers2001.VoucherDate,dbo.Vouchers2001.VoucherNo,dbo.Vouchers2001.VoucherTypeIDORDER BY dbo.Vouchers2001.VoucherID,dbo.Ledgers.LedgerName,dbo.Vouchers2001.VoucherDate,dbo.Vouchers2001.VoucherNoPlz help Out*** Sent via Developersdex http://www.developersdex.com ***

View 12 Replies View Related

Tough Query?

Aug 5, 2006

The following data set is building inspection visits. It consists ofmultiple visits (2+) made to the same building on the same day.I want to get a list of visits made to the same building on the same day,but by different employees, and for different visit codes (eg records 5-6,or 9-11)Here's the table=====================================CREATE TABLE VISITS(VISITID NUMBER(5,0) NOT NULL ,BLDGCODE VARCHAR2(10) NOT NULL ,VISITDATE DATE NOT NULL ,EMPID NUMBER(5,0) NOT NULL ,VISITCODE VARCHAR2(5) NOT NULL);ALTER TABLE VISITSADD CONSTRAINT PK_VISITS PRIMARY KEY(VISITID);CREATE UNIQUE INDEX UIDX_VISITS ON VISITS(BLDGCODE,VISITDATE,EMPID,VISITCODE);=====================================And here's the data:=====================================VISITID,BLDGCODE,VISITDATE,EMPID,VISITCODE1, BLDG1, 10/18/2005, 128, V62, BLDG1, 10/18/2005, 128, V93, BLDG2, 1/24/2006, 128, V84, BLDG2, 1/24/2006, 165, V225, BLDG3, 2/15/2006, 13, V146, BLDG3, 2/15/2006, 143, V87, BLDG4, 8/1/2006, 319, V98, BLDG4, 8/1/2006, 390, V99, BLDG4, 8/2/2006, 319, V910, BLDG4, 8/2/2006, 390, V911, BLDG4, 8/2/2006, 390, V812, BLDG5, 8/28/2006, 318, V1113, BLDG5, 8/28/2006, 376, V1114, BLDG5, 8/29/2006, 318, V1115, BLDG5, 8/29/2006, 334, V1116, BLDG5, 8/29/2006, 376, V1117, BLDG5, 8/30/2006, 318, V1118, BLDG5, 8/30/2006, 376, V1119, BLDG5, 8/30/2006, 334, V1120, BLDG5, 8/31/2006, 318, V1121, BLDG5, 8/31/2006, 376, V1122, BLDG5, 8/31/2006, 334, V1123, BLDG6, 10/11/2005, 323, V1924, BLDG6, 10/11/2005, 323, V2725, BLDG6, 11/8/2005, 323, V826, BLDG6, 11/8/2005, 323, V2727, BLDG7, 10/18/2005, 323, V328, BLDG7, 10/18/2005, 323, V2729, BLDG7, 11/14/2005, 14, V330, BLDG7, 11/14/2005, 323, V331, BLDG7, 11/14/2005, 143, V332, BLDG7, 12/15/2005, 143, V333, BLDG7, 12/15/2005, 323, V334, BLDG8, 3/8/2006, 15, V2335, BLDG8, 3/8/2006, 120, V2336, BLDG9, 5/22/2006, 25, V237, BLDG9, 5/22/2006, 391, V1438, BLDG10, 11/3/2005, 310, V639, BLDG10, 11/3/2005, 310, V840, BLDG10, 3/15/2006, 139, V2841, BLDG10, 3/15/2006, 310, V2842, BLDG10, 3/16/2006, 139, V2843, BLDG10, 3/16/2006, 310, V2844, BLDG11, 11/3/2005, 323, V2245, BLDG11, 11/3/2005, 323, V2746, BLDG12, 4/18/2006, 71, V247, BLDG12, 4/18/2006, 337, V1348, BLDG12, 4/19/2006, 71, V249, BLDG12, 4/19/2006, 337, V1350, BLDG13, 10/3/2005, 142, V2251, BLDG13, 10/3/2005, 142, V2752, BLDG14, 5/23/2006, 32, V2353, BLDG14, 5/23/2006, 139, V2354, BLDG14, 5/24/2006, 32, V2355, BLDG14, 5/24/2006, 139, V2356, BLDG15, 5/30/2006, 141, V1757, BLDG15, 5/30/2006, 141, V2258, BLDG16, 6/1/2006, 71, V1859, BLDG16, 6/1/2006, 336, V1860, BLDG16, 6/1/2006, 123, V1861, BLDG17, 2/21/2006, 34, V862, BLDG17, 2/21/2006, 34, V1963, BLDG18, 12/14/2005, 141, V764, BLDG18, 12/14/2005, 141, V1765, BLDG19, 10/18/2005, 320, V1466, BLDG19, 10/18/2005, 320, V1667, BLDG20, 3/6/2006, 141, V868, BLDG20, 3/6/2006, 141, V2269, BLDG21, 10/11/2005, 324, V670, BLDG21, 10/11/2005, 324, V771, BLDG22, 7/10/2006, 38, V2372, BLDG22, 7/10/2006, 252, V1173, BLDG22, 7/11/2006, 38, V2374, BLDG22, 7/11/2006, 252, V1175, BLDG22, 7/11/2006, 142, V2276, BLDG23, 11/10/2005, 308, V777, BLDG23, 11/10/2005, 308, V878, BLDG23, 5/11/2006, 308, V879, BLDG23, 5/11/2006, 391, V280, BLDG24, 3/23/2006, 143, V2481, BLDG24, 3/23/2006, 155, V282, BLDG24, 3/24/2006, 143, V2483, BLDG24, 3/24/2006, 155, V2584, BLDG25, 10/3/2005, 31, V1485, BLDG25, 10/3/2005, 31, V1986, BLDG26, 2/20/2006, 31, V1487, BLDG26, 2/20/2006, 31, V2288, BLDG27, 2/15/2006, 13, V1489, BLDG27, 2/15/2006, 143, V890, BLDG28, 10/12/2005, 141, V891, BLDG28, 10/12/2005, 141, V1792, BLDG29, 10/4/2005, 32, V2293, BLDG29, 10/4/2005, 310, V294, BLDG30, 9/12/2005, 53, V2395, BLDG30, 9/12/2005, 123, V2196, BLDG30, 9/12/2005, 141, V2397, BLDG30, 9/13/2005, 53, V2398, BLDG30, 9/13/2005, 141, V2399, BLDG30, 9/13/2005, 123, V21100, BLDG30, 9/14/2005, 53, V23101, BLDG30, 9/14/2005, 141, V23102, BLDG30, 9/14/2005, 123, V21103, BLDG31, 2/14/2006, 13, V14104, BLDG31, 2/14/2006, 143, V8105, BLDG32, 11/1/2005, 320, V3106, BLDG32, 11/1/2005, 320, V27107, BLDG33, 11/3/2005, 34, V7108, BLDG33, 11/3/2005, 34, V19109, BLDG34, 7/10/2006, 37, V23110, BLDG34, 7/10/2006, 62, V23111, BLDG34, 7/11/2006, 37, V23112, BLDG34, 7/11/2006, 62, V23113, BLDG34, 7/12/2006, 37, V23114, BLDG34, 7/12/2006, 62, V23115, BLDG35, 11/21/2005, 78, V27116, BLDG35, 11/21/2005, 334, V8117, BLDG36, 7/10/2006, 358, V1118, BLDG36, 7/10/2006, 358, V8119, BLDG37, 8/14/2006, 50, V14120, BLDG37, 8/14/2006, 71, V11121, BLDG37, 8/15/2006, 50, V14122, BLDG37, 8/15/2006, 71, V11123, BLDG38, 9/13/2005, 130, V6124, BLDG38, 9/13/2005, 130, V8125, BLDG39, 2/22/2006, 34, V8126, BLDG39, 2/22/2006, 34, V14127, BLDG40, 2/14/2006, 13, V14128, BLDG40, 2/14/2006, 143, V8129, BLDG41, 5/22/2006, 252, V17130, BLDG41, 5/22/2006, 326, V17131, BLDG41, 5/23/2006, 252, V17132, BLDG41, 5/23/2006, 326, V17133, BLDG42, 7/10/2006, 309, V2134, BLDG42, 7/10/2006, 318, V23135, BLDG42, 7/11/2006, 309, V2136, BLDG42, 7/11/2006, 318, V23137, BLDG42, 7/12/2006, 309, V2138, BLDG42, 7/12/2006, 318, V23139, BLDG43, 10/18/2005, 206, V8140, BLDG43, 10/18/2005, 206, V14141, BLDG44, 3/9/2006, 142, V24142, BLDG44, 3/9/2006, 233, V23143, BLDG44, 3/9/2006, 319, V24144, BLDG44, 3/10/2006, 142, V24145, BLDG44, 3/10/2006, 319, V24146, BLDG44, 3/10/2006, 233, V23147, BLDG45, 9/15/2005, 128, V6148, BLDG45, 9/15/2005, 128, V9149, BLDG46, 5/24/2006, 25, V2150, BLDG46, 5/24/2006, 391, V8151, BLDG47, 1/17/2006, 321, V6152, BLDG47, 1/17/2006, 321, V22153, BLDG48, 7/13/2006, 38, V18154, BLDG48, 7/13/2006, 318, V11155, BLDG49, 7/12/2006, 142, V23156, BLDG49, 7/12/2006, 263, V23157, BLDG50, 4/11/2006, 62, V24158, BLDG50, 4/11/2006, 142, V24159, BLDG50, 4/12/2006, 62, V24160, BLDG50, 4/12/2006, 142, V24161, BLDG51, 10/13/2005, 78, V13162, BLDG51, 10/13/2005, 325, V13163, BLDG52, 5/2/2006, 145, V9164, BLDG52, 5/2/2006, 390, V12165, BLDG52, 5/2/2006, 390, V9166, BLDG52, 5/3/2006, 145, V8167, BLDG52, 5/3/2006, 390, V9168, BLDG52, 5/3/2006, 390, V12169, BLDG53, 12/14/2005, 76, V9170, BLDG53, 12/14/2005, 322, V9171, BLDG53, 12/15/2005, 76, V9172, BLDG53, 12/15/2005, 322, V9173, BLDG53, 12/15/2005, 322, V22174, BLDG54, 9/6/2005, 323, V3175, BLDG54, 9/6/2005, 323, V27176, BLDG54, 12/13/2005, 323, V22177, BLDG54, 12/13/2005, 323, V27178, BLDG55, 9/6/2005, 129, V21179, BLDG55, 9/6/2005, 233, V23180, BLDG55, 9/7/2005, 38, V23181, BLDG55, 9/7/2005, 233, V23182, BLDG55, 9/7/2005, 142, V23183, BLDG55, 9/7/2005, 129, V21184, BLDG55, 9/8/2005, 38, V23185, BLDG55, 9/8/2005, 233, V23186, BLDG55, 9/8/2005, 142, V23187, BLDG55, 9/8/2005, 129, V21188, BLDG55, 9/9/2005, 129, V21189, BLDG55, 9/9/2005, 233, V23190, BLDG55, 6/20/2006, 142, V8191, BLDG55, 6/20/2006, 142, V29192, BLDG56, 6/28/2006, 131, V13193, BLDG56, 6/28/2006, 319, V13194, BLDG56, 6/29/2006, 131, V13195, BLDG56, 6/29/2006, 319, V13196, BLDG57, 11/8/2005, 320, V22197, BLDG57, 11/8/2005, 320, V27198, BLDG58, 1/25/2006, 13, V3199, BLDG58, 1/25/2006, 14, V13200, BLDG59, 11/29/2005, 233, V9201, BLDG59, 11/29/2005, 233, V14202, BLDG60, 2/8/2006, 323, V22203, BLDG60, 2/8/2006, 323, V27204, BLDG61, 1/17/2006, 166, V3205, BLDG61, 1/17/2006, 166, V22206, BLDG62, 9/27/2005, 320, V3207, BLDG62, 9/27/2005, 320, V22208, BLDG62, 2/21/2006, 115, V9209, BLDG62, 2/21/2006, 320, V9210, BLDG62, 2/22/2006, 115, V9211, BLDG62, 2/22/2006, 320, V9212, BLDG63, 11/14/2005, 87, V11213, BLDG63, 11/14/2005, 129, V27214, BLDG63, 11/14/2005, 323, V27215, BLDG63, 11/15/2005, 129, V11216, BLDG63, 11/15/2005, 143, V11217, BLDG63, 11/16/2005, 129, V11218, BLDG63, 11/16/2005, 143, V11219, BLDG63, 11/17/2005, 129, V11220, BLDG63, 11/17/2005, 143, V11221, BLDG63, 11/18/2005, 129, V27222, BLDG63, 11/18/2005, 143, V11223, BLDG64, 6/7/2006, 253, V2224, BLDG64, 6/7/2006, 391, V6225, BLDG65, 6/7/2006, 253, V2226, BLDG65, 6/7/2006, 391, V14227, BLDG66, 1/11/2006, 39, V25228, BLDG66, 1/11/2006, 141, V25229, BLDG66, 1/12/2006, 39, V25230, BLDG66, 1/12/2006, 141, V25231, BLDG66, 3/20/2006, 39, V23232, BLDG66, 3/20/2006, 76, V23233, BLDG66, 3/21/2006, 39, V23234, BLDG66, 3/21/2006, 115, V23235, BLDG66, 3/21/2006, 76, V23236, BLDG66, 3/22/2006, 39, V23237, BLDG66, 3/22/2006, 115, V23238, BLDG66, 3/22/2006, 76, V23239, BLDG67, 5/26/2006, 141, V7240, BLDG67, 5/26/2006, 141, V17241, BLDG68, 12/21/2005, 141, V8242, BLDG68, 12/21/2005, 141, V17243, BLDG69, 5/23/2006, 50, V3244, BLDG69, 5/23/2006, 50, V8245, BLDG70, 2/1/2006, 114, V17246, BLDG70, 2/1/2006, 114, V22247, BLDG71, 10/11/2005, 131, V8248, BLDG71, 10/11/2005, 334, V8249, BLDG71, 3/10/2006, 334, V8250, BLDG71, 3/10/2006, 334, V22251, BLDG72, 7/31/2006, 398, V2252, BLDG72, 7/31/2006, 398, V22253, BLDG73, 11/30/2005, 129, V23254, BLDG73, 11/30/2005, 326, V24255, BLDG74, 11/29/2005, 143, V22256, BLDG74, 11/29/2005, 143, V23257, BLDG75, 4/26/2006, 12, V18258, BLDG75, 4/26/2006, 17, V18259, BLDG76, 6/6/2006, 320, V14260, BLDG76, 6/6/2006, 320, V15261, BLDG77, 1/10/2006, 78, V2262, BLDG77, 1/10/2006, 325, V9263, BLDG77, 1/11/2006, 78, V2264, BLDG77, 1/11/2006, 325, V8265, BLDG77, 1/11/2006, 325, V6266, BLDG78, 5/17/2006, 141, V17267, BLDG78, 5/17/2006, 141, V22268, BLDG79, 9/13/2005, 37, V19269, BLDG79, 9/13/2005, 318, V19270, BLDG80, 12/20/2005, 34, V13271, BLDG80, 12/20/2005, 250, V13272, BLDG81, 4/19/2006, 25, V22273, BLDG81, 4/19/2006, 391, V2274, BLDG82, 5/3/2006, 108, V14275, BLDG82, 5/3/2006, 391, V2276, BLDG83, 6/19/2006, 36, V8277, BLDG83, 6/19/2006, 393, V8278, BLDG84, 2/13/2006, 13, V14279, BLDG84, 2/13/2006, 143, V8280, BLDG85, 5/8/2006, 308, V8281, BLDG85, 5/8/2006, 391, V2282, BLDG86, 4/25/2006, 322, V8283, BLDG86, 4/25/2006, 322, V22284, BLDG87, 7/14/2006, 322, V13285, BLDG87, 7/14/2006, 322, V15286, BLDG88, 2/2/2006, 322, V8287, BLDG88, 2/2/2006, 322, V22288, BLDG89, 4/13/2006, 390, V2289, BLDG89, 4/13/2006, 390, V8290, BLDG90, 10/12/2005, 131, V8291, BLDG90, 10/12/2005, 334, V8292, BLDG91, 6/26/2006, 131, V22293, BLDG91, 6/26/2006, 319, V22294, BLDG91, 6/27/2006, 131, V22295, BLDG91, 6/27/2006, 319, V22296, BLDG92, 3/1/2006, 39, V23297, BLDG92, 3/1/2006, 141, V23298, BLDG92, 3/2/2006, 39, V23299, BLDG92, 3/2/2006, 115, V23300, BLDG92, 3/2/2006, 141, V23301, BLDG92, 3/3/2006, 39, V23302, BLDG92, 3/3/2006, 141, V23303, BLDG92, 3/3/2006, 115, V23304, BLDG92, 7/20/2006, 115, V23305, BLDG92, 7/20/2006, 141, V25306, BLDG92, 7/21/2006, 115, V23307, BLDG92, 7/21/2006, 141, V25308, BLDG93, 5/8/2006, 78, V2309, BLDG93, 5/8/2006, 325, V9310, BLDG93, 5/9/2006, 78, V2311, BLDG93, 5/9/2006, 78, V9312, BLDG93, 5/9/2006, 325, V9313, BLDG94, 6/19/2006, 128, V9314, BLDG94, 6/19/2006, 358, V9315, BLDG94, 6/20/2006, 128, V9316, BLDG94, 6/20/2006, 358, V9317, BLDG95, 6/6/2006, 253, V2318, BLDG95, 6/6/2006, 391, V14319, BLDG96, 6/6/2006, 253, V2320, BLDG96, 6/6/2006, 391, V14321, BLDG97, 6/5/2006, 253, V7322, BLDG97, 6/5/2006, 391, V7323, BLDG98, 1/24/2006, 322, V9324, BLDG98, 1/24/2006, 322, V22325, BLDG99, 10/12/2005, 323, V7326, BLDG99, 10/12/2005, 323, V15327, BLDG100, 12/21/2005, 320, V14328, BLDG100, 12/21/2005, 320, V22329, BLDG100, 2/23/2006, 115, V9330, BLDG100, 2/23/2006, 320, V9331, BLDG100, 2/24/2006, 115, V9332, BLDG100, 2/24/2006, 320, V9333, BLDG101, 2/22/2006, 115, V9334, BLDG101, 2/22/2006, 320, V9335, BLDG101, 2/23/2006, 115, V9336, BLDG101, 2/23/2006, 320, V9337, BLDG102, 10/13/2005, 131, V8338, BLDG102, 10/13/2005, 334, V8339, BLDG103, 1/12/2006, 119, V7340, BLDG103, 1/12/2006, 119, V22341, BLDG104, 5/17/2006, 233, V23342, BLDG104, 5/17/2006, 243, V23343, BLDG104, 5/18/2006, 233, V23344, BLDG104, 5/18/2006, 243, V23345, BLDG105, 11/22/2005, 309, V6346, BLDG105, 11/22/2005, 309, V22347, BLDG106, 1/12/2006, 166, V8348, BLDG106, 1/12/2006, 166, V22349, BLDG107, 9/27/2005, 206, V7350, BLDG107, 9/27/2005, 206, V20351, BLDG108, 4/12/2006, 322, V14352, BLDG108, 4/12/2006, 322, V22353, BLDG109, 3/27/2006, 17, V11354, BLDG109, 3/27/2006, 358, V11355, BLDG109, 3/27/2006, 127, V11356, BLDG109, 3/27/2006, 142, V11357, BLDG109, 3/27/2006, 144, V11358, BLDG109, 3/27/2006, 318, V11359, BLDG109, 3/27/2006, 129, V11360, BLDG109, 3/28/2006, 17, V11361, BLDG109, 3/28/2006, 115, V11362, BLDG109, 3/28/2006, 358, V11363, BLDG109, 3/28/2006, 334, V11364, BLDG109, 3/28/2006, 323, V11365, BLDG109, 3/28/2006, 318, V11366, BLDG109, 3/28/2006, 144, V11367, BLDG109, 3/28/2006, 142, V11368, BLDG109, 3/28/2006, 129, V11369, BLDG109, 3/28/2006, 127, V11370, BLDG109, 3/29/2006, 17, V11371, BLDG109, 3/29/2006, 323, V11372, BLDG109, 3/29/2006, 358, V11373, BLDG109, 3/29/2006, 334, V11374, BLDG109, 3/29/2006, 318, V11375, BLDG109, 3/29/2006, 144, V11376, BLDG109, 3/29/2006, 142, V11377, BLDG109, 3/29/2006, 129, V11378, BLDG109, 3/29/2006, 127, V11379, BLDG109, 3/29/2006, 115, V11380, BLDG109, 3/30/2006, 17, V11381, BLDG109, 3/30/2006, 129, V11382, BLDG109, 3/30/2006, 358, V11383, BLDG109, 3/30/2006, 334, V11384, BLDG109, 3/30/2006, 323, V11385, BLDG109, 3/30/2006, 318, V11386, BLDG109, 3/30/2006, 144, V11387, BLDG109, 3/30/2006, 142, V11388, BLDG109, 3/30/2006, 127, V11389, BLDG109, 3/30/2006, 115, V11390, BLDG109, 3/31/2006, 17, V11391, BLDG109, 3/31/2006, 318, V11392, BLDG109, 3/31/2006, 358, V11393, BLDG109, 3/31/2006, 144, V11394, BLDG109, 3/31/2006, 142, V11395, BLDG109, 3/31/2006, 129, V11396, BLDG109, 3/31/2006, 127, V11397, BLDG109, 3/31/2006, 115, V11398, BLDG110, 11/7/2005, 320, V22399, BLDG110, 11/7/2005, 320, V27400, BLDG111, 10/12/2005, 146, V8401, BLDG111, 10/12/2005, 146, V22402, BLDG112, 2/14/2006, 141, V8403, BLDG112, 2/14/2006, 141, V22404, BLDG113, 2/15/2006, 145, V9405, BLDG113, 2/15/2006, 233, V9406, BLDG113, 2/16/2006, 145, V8407, BLDG113, 2/16/2006, 233, V9408, BLDG114, 1/26/2006, 310, V6409, BLDG114, 1/26/2006, 310, V8410, BLDG115, 5/5/2006, 36, V9411, BLDG115, 5/5/2006, 376, V9412, BLDG115, 7/5/2006, 36, V9413, BLDG115, 7/5/2006, 376, V9414, BLDG115, 7/6/2006, 36, V9415, BLDG115, 7/6/2006, 376, V9416, BLDG116, 4/12/2006, 34, V4417, BLDG116, 4/12/2006, 34, V13418, BLDG117, 12/27/2005, 323, V13419, BLDG117, 12/27/2005, 323, V27420, BLDG117, 2/17/2006, 14, V3421, BLDG117, 2/17/2006, 323, V3422, BLDG118, 1/31/2006, 308, V17423, BLDG118, 1/31/2006, 308, V22424, BLDG119, 5/9/2006, 308, V8425, BLDG119, 5/9/2006, 391, V2426, BLDG120, 11/8/2005, 233, V28427, BLDG120, 11/8/2005, 233, V29428, BLDG121, 12/15/2005, 141, V8429, BLDG121, 12/15/2005, 141, V17430, BLDG122, 5/23/2006, 25, V2431, BLDG122, 5/23/2006, 391, V22432, BLDG123, 4/20/2006, 25, V14433, BLDG123, 4/20/2006, 391, V2434, BLDG124, 4/17/2006, 25, V22435, BLDG124, 4/17/2006, 391, V2436, BLDG125, 4/18/2006, 25, V22437, BLDG125, 4/18/2006, 391, V2438, BLDG126, 10/18/2005, 13, V19439, BLDG126, 10/18/2005, 13, V22440, BLDG127, 5/10/2006, 308, V8441, BLDG127, 5/10/2006, 391, V2442, BLDG128, 1/10/2006, 78, V11443, BLDG128, 1/10/2006, 233, V23444, BLDG129, 11/8/2005, 78, V9445, BLDG129, 11/8/2005, 325, V9446, BLDG129, 11/9/2005, 78, V9447, BLDG129, 11/9/2005, 325, V9448, BLDG130, 10/18/2005, 325, V28449, BLDG130, 10/18/2005, 334, V9450, BLDG130, 11/9/2005, 78, V9451, BLDG130, 11/9/2005, 334, V9452, BLDG130, 11/9/2005, 325, V9453, BLDG130, 11/10/2005, 78, V9454, BLDG130, 11/10/2005, 325, V9455, BLDG130, 11/10/2005, 334, V6456, BLDG131, 5/30/2006, 50, V3457, BLDG131, 5/30/2006, 50, V8458, BLDG132, 5/24/2006, 31, V6459, BLDG132, 5/24/2006, 31, V22460, BLDG133, 2/7/2006, 114, V17461, BLDG133, 2/7/2006, 114, V22462, BLDG134, 6/21/2006, 62, V9463, BLDG134, 6/21/2006, 309, V28464, BLDG135, 5/10/2006, 78, V9465, BLDG135, 5/10/2006, 325, V9466, BLDG135, 5/11/2006, 78, V9467, BLDG135, 5/11/2006, 325, V9468, BLDG136, 12/15/2005, 129, V23469, BLDG136, 12/15/2005, 233, V23470, BLDG137, 5/17/2006, 129, V6471, BLDG137, 5/17/2006, 129, V8472, BLDG138, 3/8/2006, 336, V8473, BLDG138, 3/8/2006, 336, V22474, BLDG139, 3/20/2006, 129, V14475, BLDG139, 3/20/2006, 129, V19476, BLDG140, 3/22/2006, 52, V29477, BLDG140, 3/22/2006, 334, V29478, BLDG140, 3/23/2006, 52, V29479, BLDG140, 3/23/2006, 334, V29480, BLDG141, 1/4/2006, 78, V22481, BLDG141, 1/4/2006, 334, V22482, BLDG142, 9/7/2005, 52, V8483, BLDG142, 9/7/2005, 52, V10484, BLDG143, 5/4/2006, 78, V28485, BLDG143, 5/4/2006, 334, V29486, BLDG144, 5/11/2006, 50, V3487, BLDG144, 5/11/2006, 50, V8488, BLDG144, 5/12/2006, 50, V7489, BLDG144, 5/12/2006, 50, V8490, BLDG145, 10/4/2005, 119, V16491, BLDG145, 10/4/2005, 119, V19492, BLDG146, 6/6/2006, 34, V7493, BLDG146, 6/6/2006, 34, V8494, BLDG147, 9/14/2005, 36, V28495, BLDG147, 9/14/2005, 324, V5496, BLDG148, 9/14/2005, 322, V14497, BLDG148, 9/14/2005, 322, V15498, BLDG149, 6/20/2006, 123, V8499, BLDG149, 6/20/2006, 123, V22500, BLDG150, 5/16/2006, 12, V18501, BLDG150, 5/16/2006, 318, V18502, BLDG151, 4/17/2006, 62, V23503, BLDG151, 4/17/2006, 142, V23504, BLDG151, 4/17/2006, 318, V23505, BLDG151, 4/17/2006, 154, V2506, BLDG151, 4/18/2006, 62, V23507, BLDG151, 4/18/2006, 318, V23508, BLDG151, 4/18/2006, 142, V23509, BLDG151, 4/18/2006, 154, V2510, BLDG151, 4/19/2006, 62, V22511, BLDG151, 4/19/2006, 318, V23512, BLDG151, 4/19/2006, 154, V2513, BLDG151, 4/19/2006, 142, V23514, BLDG152, 10/20/2005, 320, V14515, BLDG152, 10/20/2005, 320, V15516, BLDG153, 7/11/2006, 334, V8517, BLDG153, 7/11/2006, 334, V9518, BLDG154, 2/1/2006, 53, V23519, BLDG154, 2/1/2006, 323, V23520, BLDG154, 2/2/2006, 53, V23521, BLDG154, 2/2/2006, 323, V23522, BLDG154, 2/3/2006, 53, V23523, BLDG154, 2/3/2006, 323, V27524, BLDG154, 2/3/2006, 323, V23525, BLDG154, 7/12/2006, 53, V24526, BLDG154, 7/12/2006, 139, V26527, BLDG154, 7/12/2006, 141, V25528, BLDG154, 7/13/2006, 53, V24529, BLDG154, 7/13/2006, 139, V26530, BLDG154, 7/13/2006, 141, V25531, BLDG154, 7/14/2006, 53, V24532, BLDG154, 7/14/2006, 141, V25533, BLDG154, 7/14/2006, 139, V26534, BLDG155, 9/14/2005, 323, V7535, BLDG155, 9/14/2005, 323, V27536, BLDG156, 9/12/2005, 165, V6537, BLDG156, 9/12/2005, 165, V8538, BLDG157, 10/27/2005, 32, V13539, BLDG157, 10/27/2005, 310, V13540, BLDG158, 11/2/2005, 320, V14541, BLDG158, 11/2/2005, 320, V15542, BLDG158, 2/20/2006, 115, V9543, BLDG158, 2/20/2006, 320, V9544, BLDG158, 2/21/2006, 115, V9545, BLDG158, 2/21/2006, 320, V9546, BLDG158, 4/26/2006, 320, V3547, BLDG158, 4/26/2006, 320, V17548, BLDG159, 3/14/2006, 336, V14549, BLDG159, 3/14/2006, 336, V22550, BLDG160, 3/15/2006, 336, V8551, BLDG160, 3/15/2006, 336, V14552, BLDG160, 8/24/2006, 336, V8553, BLDG160, 8/24/2006, 336, V27554, BLDG161, 1/5/2006, 253, V6555, BLDG161, 1/5/2006, 253, V17556, BLDG162, 3/13/2006, 141, V9557, BLDG162, 3/13/2006, 141, V22558, BLDG163, 6/29/2006, 78, V6559, BLDG163, 6/29/2006, 78, V9560, BLDG164, 12/12/2005, 76, V9561, BLDG164, 12/12/2005, 322, V9562, BLDG164, 12/13/2005, 76, V9563, BLDG164, 12/13/2005, 322, V22564, BLDG164, 12/13/2005, 322, V9565, BLDG165, 1/11/2006, 166, V8566, BLDG165, 1/11/2006, 166, V22567, BLDG166, 5/24/2006, 141, V17568, BLDG166, 5/24/2006, 141, V22569, BLDG167, 1/11/2006, 165, V8570, BLDG167, 1/11/2006, 165, V22571, BLDG168, 10/18/2005, 244, V14572, BLDG168, 10/18/2005, 309, V14573, BLDG169, 1/31/2006, 144, V8574, BLDG169, 1/31/2006, 144, V22575, BLDG170, 6/6/2006, 123, V18576, BLDG170, 6/6/2006, 383, V18577, BLDG171, 1/17/2006, 263, V6578, BLDG171, 1/17/2006, 263, V9579, BLDG172, 8/10/2006, 233, V9580, BLDG172, 8/10/2006, 376, V9581, BLDG172, 8/11/2006, 233, V9582, BLDG172, 8/11/2006, 376, V9583, BLDG173, 4/4/2006, 131, V13584, BLDG173, 4/4/2006, 144, V13585, BLDG174, 7/4/2006, 383, V4586, BLDG174, 7/4/2006, 383, V8=====================================Thanks

View 9 Replies View Related

Tough Problem, Need Help

Jul 20, 2005

I have a very strange database with a very strange problem.Consider 4 tables:Table1:----------------Table1ID INT PKTable2ID INT FKTable3ID INT FKOrderNo VARCHAR(50)Table2----------------Table2ID INT PKTable4ID INT FKTable3----------------Table3ID INT PKTable2ID INT FKTable4----------------Table4ID INT PKOrderTotal VARCHAR(50)With Data:Table1:------------1 1 NULL 900012 2 NULL 900023 NULL 1 900034 NULL 2 90004Table2:------------1 12 1Table3:------------1 12 2Table4:------------1 5002 1000Table1 can have either a Table2ID OR a Table3ID but not both.This is the query I'm attempting:---------------------------SELECT dbo.Table1.OrderNo, dbo.Table4.OrderTotalFROM dbo.Table1 LEFT OUTER JOINdbo.Table4 INNER JOINdbo.Table2 ON dbo.Table4.Table4ID =dbo.Table2.Table4ID INNER JOINdbo.Table3 ON dbo.Table2.Table2ID =dbo.Table3.Table3ID ON dbo.Table1.Table2ID = dbo.Table2.Table2ID ANDdbo.Table1.Table3ID = dbo.Table3.Table3IDWhich gives me:---------------------------90001 NULL90002 NULL90003 NULL90004 NULLWhen I really want:----------------------------90001 50090002 50090003 50090003 1000 (NOT 500)I don't know how to do this. Are any of you sql guru's up to thechallenge?Thanks in advance-Mattp.s. sql to recreate tables includedCREATE TABLE [dbo].[Table1] ([Table1ID] [int] IDENTITY (1, 1) NOT NULL ,[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Table2ID] [int] NULL ,[Table3ID] [int] NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Table2] ([Table2ID] [int] IDENTITY (1, 1) NOT NULL ,[Table4ID] [int] NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Table3] ([Table3ID] [int] IDENTITY (1, 1) NOT NULL ,[Table2ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Table4] ([Table4ID] [int] IDENTITY (1, 1) NOT NULL ,[OrderTotal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOALTER TABLE [dbo].[Table1] WITH NOCHECK ADDCONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED([Table1ID]) ON [PRIMARY]GOALTER TABLE [dbo].[Table2] WITH NOCHECK ADDCONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED([Table2ID]) ON [PRIMARY]GOALTER TABLE [dbo].[Table3] WITH NOCHECK ADDCONSTRAINT [PK_Table3] PRIMARY KEY CLUSTERED([Table3ID]) ON [PRIMARY]GOALTER TABLE [dbo].[Table4] WITH NOCHECK ADDCONSTRAINT [PK_Table4] PRIMARY KEY CLUSTERED([Table4ID]) ON [PRIMARY]GOALTER TABLE [dbo].[Table1] ADDCONSTRAINT [FK_Table1_Table2] FOREIGN KEY([Table2ID]) REFERENCES [dbo].[Table2] ([Table2ID]),CONSTRAINT [FK_Table1_Table3] FOREIGN KEY([Table3ID]) REFERENCES [dbo].[Table3] ([Table3ID])GOALTER TABLE [dbo].[Table3] ADDCONSTRAINT [FK_Table3_Table2] FOREIGN KEY([Table3ID]) REFERENCES [dbo].[Table2] ([Table2ID])GO

View 6 Replies View Related

Please Help With This Tough SQL Query

Jul 20, 2005

I've been trying this one for 2-3 hours and can't figure it out. I'deappreciate any help or pointers in the right direction. Thanks.QueryI need the query to return me all the lottery names and results thathave the latest date in the database for that particular game and forthe state [AZ]. So the return data from the data below data would be:Result:--------------------------AZ Atlantic 6/49 2004-08-07 3-6-8-12-19-24 18AZ Atlantic PayDay 2004-08-05 15-51-59-75AZ Atlantic Tag 2004-08-08 4-6-1-6-7-6Example Table "Lottery":----------------------------------------------------State|Game | Date | ResultsAZ Atlantic 6/49 2004-08-04 5-16-17-26-38-44 46AZ Atlantic 6/49 2004-08-07 3-6-8-12-19-24 18AZ Atlantic PayDay 2004-07-29 2-23-62-77AZ Atlantic PayDay 2004-08-05 15-51-59-75AZ Atlantic Tag 2004-08-04 5-8-9-1-2-3AZ Atlantic Tag 2004-08-08 4-6-1-6-7-6

View 3 Replies View Related

A Tough Situation To Get ID....

Apr 17, 2008

Hello All

I have a table called Tax Act

In that I have Tax Act ID. Which is supposed to be NOT NULL.

I have to create IDs by my self using some MAX and MAX+1 incrementing function.

Please guide me on how to

View 3 Replies View Related

Connection Timeouts - A Tough One!

Dec 16, 2003

I'm having major problems with SqlClient Data provider connections in my application. I'm using Win 2003 Server with SQL Server 2000 (both on the same machine), and my application is developed in VB.NET.

I'm using the following db connection string:
Data Source=(local);Initial Catalog=database;Uid=user;Pwd=password;max pool size=100;

As you can see max pool size is set to 100. The SQL Database has it's MAX_CONNECTIONS property set to >32000.

The symptoms are that at seemingly random times, not nessecarily during peak hours, the application becomes extremely slow and stops responding. The error message that is beeing generated is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

After monitoring and debugging, I've found two things.

1) At random times, zero-three times a day, the number of database connections (according to performance monitor) rises from normally 2-8 up to 20-25. After a while, usually 10-60 minutes, it drops back to normal. This is directly related to when the timeout errors occur. It doesn't seem to happen more often during peak load, for some reason. I suspect there is connection leakage in the application, but I've done extensive debugging, and I just can't find any. Also, since the peaks occur randomly and I can't reproduce the error in my development environment, I have no solution to this problem. A few weeks ago, the application ran during heavy load without this problem for 7 days. Before and after that, there have been errors at least every second day. That confuses me even more.

2) The number of connections (monitored in perf mon) never reach above 25. Why? I'm using SQL Server enterprise edition, it's connection limit is over 32000. ADO pool size is 100, and I can't find anything else that would limit the amount of concurrent connections. I'm totally confused. Why does number of connections never rise above 25? Is there a setting hidden somewhere that I just can't find? Also, according to Performance Monitor, I don't get any failed connects, I just get failed commands. Maybe that's a clue? Could the problem some kind of deadlocks in the database that causes timeout?

I realize this entire post is rather confusing. It reflects the state of my mind, I guess, after working around the clock on this issue for a month :)

Any help is deeply appreciated!

View 2 Replies View Related

Tough SQL Server/ASP Problem

Jun 28, 2000

This one has me stumped!!!

I have a stored procedure that moves data from three tables to three other tables that are exactly like the first three. When I call the SP from the Query Analyzer, it works great and moves all the records over. When I call it from ASP, the SP inserts two identical records in each table. Has anyone seen this kind of behavior? The ASP code is not in a loop my the way. I placed a response.write after I executed the SP and it only printed to the screen once. SO I know that the SP is only being called once from ASP.
I don't have any loops within the SP either.

Thanks in advance

View 3 Replies View Related

Tough Query-Please Help!!! URGENT!!!

Nov 21, 2001

Hello,

I have a table with 3 cols--- id1,id2 and id3
Total no.of rows-- 18

id1id2id3
11
25
35
45
51
6100
7100
845
944
1045
11100
126
136
141
152
163
17111
18123

id1 is an identity column.
I have to populate third column which is id3 based on the data in id2.

I have to group similar values in id2 together
and insert 1,2,3 accordingly in id3.
For eg: we have three 100's in id2, so we should have values 1,2,3 for id3.
we have two 45's in id2, we should have values 1,2 for id3.

so final output should be:

id1id2id3
111
251
352
453
512
61001
71002
8451
9441
10452
111003
1261
1362
1413
1521
1631
171111
181231

Is it possible?
Please help!!!!!!!

Thanks,
cheryl

View 3 Replies View Related

Some Tough SQL Server Queries

Oct 4, 2006

1) Given the name of an index how to find the columns associated with the particular index in SQL Server?

2) Given the name of a constraint, how to find whether it is enabled or disabled?

3) Is there any equivalent of the Oracle 'valid' state in SQL Server? e.g. In Oracle 'valid' state is defined for views, indexes. Are there equivalent concepts in SQL Server and if there are, how to find if a particular index/view is 'valid' or not?

View 2 Replies View Related

Aggregate Function - Tough Query For Me...

Oct 17, 2006

I currently have the following query:

Quote:
select distinct a.memberFirstName, a.memberLastName, c.ChapterName, d.divisionName,
count(f.memberID) as numMembers
FROM Members a
INNER JOIN groupLeaders b
ON a.memberID = b.memberID
Inner JOIN Chapters c
ON c.chapterID = b.chapterID
LEFT JOIN divisions d
ON d.divisionID = c.divisionID
Inner Join groupsOfEight e
ON e.groupLeaderID = b.groupLeaderID
Inner Join groupOfEightMembers f
ON f.groupOfEightID = e.groupOfEightID
Group BY a.memberFirstName, a.memberLastName, c.chapterName, d.divisionName
Order By divisionName, numMembers

This query returns me the names of all of my Group Leaders, their Chapter, Division, and the number of members they have selected to be in their group.

Now, instead of the number of members in each Group I would like to know the total number of Members in each division to appear in the count.

[NOTE: All chapters have a division, linked by a divisionID in the "Chapters" table -- I need to get a count of all the "ChapterMembers" [chaptermembers is a table also] that are in the Division.

Here is the query I started to build before I ran into serious trouble:

Quote:
select a.divisionName, count('c.memberID') as numMembers
From Divisions a
Inner Join Chapters b
On b.divisionID = a.divisionID
Inner Join chapterMembers c
ON c.chapterID = b.chapterID
Left Join Members d
ON d.memberID = c.memberID
LEFT Join groupLeaders e
On e.memberID = d.memberID
Group By a.divisionName

This particular query returns only the DivisonName and the number of Members in the division as expected. However, when I try to select the information for the GroupLeader (first & last name) I am forced to add memberFirstName to the Group By statement which changes my Count...

Have I done an okay job of explaining the problem?

The goal here is to select all of the GroupLeaders first & last name, their chapterName, divisionName, and the total number of members in the division.

Thanks for any advice!

Zoop

View 3 Replies View Related

Express Server - Tough Query

Jun 10, 2008

I have tables [CustomerData] ([CD]) and [MembershipData] ([MD]).

[CD] has
ID - Primary key
FirstName
LastName
Birthdate
Etc

[MD] has
ID - Primary key
Customer - The ID of the customer from [CustomerData]
StartDate - Membership start date
EndDate - Membership end date

Each [CD] can have multiple [MD].

Memberships can be looked at in one of four ways. They are also prioritized (see next paragraph) in this order
Current: Membership started on or before today and ends on or after today.
Future: Membership starts after today.
Past: Membership ended before today and start and end dates do not fall on 1/1/1900
None: Membership starts and ends on 1/1/1900

I have created four Views that return all memberships for each type. I am trying to create a query that selects all [CD] and includes the highest matching membership for each [CD]

I have a working query that will return the highest priority [MD] that a [CD] has when I specify a [CD] in my WHERE.

That query looks like this. (CurrentMembership, FutureMembership, PastMembership and NoMembership are my Views)

IF (EXISTS(SELECT * FROM CurrentMembership WHERE Customer = 452))
(SELECT TOP 1 * FROM CurrentMembership WHERE Customer = 452)
ELSE
IF (EXISTS(SELECT * FROM FutureMembership WHERE Customer = 452))
(SELECT TOP 1 * FROM FutureMembership WHERE Customer = 452)
ELSE
IF (EXISTS(SELECT * FROM PastMembership WHERE Customer = 452))
(SELECT TOP 1 * FROM PastMembership WHERE Customer = 452)
ELSE
(SELECT TOP 1 * FROM NoMembership WHERE Customer = 452)

I have it working for one specified individual. I'm at a loss as to how to extend it to work for all individuals.

Any ideas?

TIA

View 7 Replies View Related

Tough Date Manipulation Issue.

Jul 23, 2005

Greetings All, I was hoping that someone might be able to help me withthe following issue:table ddl:create table exchange(exchangefrom varchar(6),exchangeto varchar(6),exchangecode varchar(6),datemfrom datetime,dateto datetime,exchangerate decimal(28,10))The data in this table under normal conditons will look like:select *from exchangeUSD EURO GL 01/01/2004 01/31/2004 .7523453111USD GBP GL 01/01/2004 01/31/2004 .5384966123USD EURO GL 02/01/2004 02/29/2004 .7523492111USD GBP GL 02/01/2004 02/29/2004 .6004972023My task is to calculate the days delimeted by the start and end date ofthe period which is simple enough:select exchangefrom, exchangeto, exchangecode, datemfrom, dateto,datediff(d, datemfrom, dateto)from exchangeHowever due to circumstances beyond my control the dateto field maycontain a null instead of a valid end date!! YIKES:select *from exchangeUSD EURO GL 01/01/2004 01/31/2004 .7523453111USD GBP GL 01/01/2004 NULL .5384966123USD EURO GL 02/01/2004 02/29/2004 .7523492111USD GBP GL 02/01/2004 02/29/2004 .6004972023My solution to correct the data is to populate the missing end datewith the (start date -1 day) of the next period. However, I am notsure how to do this with SQL? E.g) from the example directly above therow: USD GBP GL 01/01/2004 NULL needs to be updated to:USD GBP GL 01/01/2004 01/31/2004 and this can be done bylooking for the next period (USD GBP GL 02/01/200402/29/2004) that follows and subtracting from its start date 1 day (02/01/2004 - 1 day = 01/31/2004) and that will give me the appropriateend date.If anyone has any insight into solving this problem I would be verythankful.Regards, TFD.

View 7 Replies View Related

Tough Correlated Subquery Issue

Aug 29, 2006

I am running 2 versions of a correlated subquery. The two versiondiffer slightly in design but differ tremendously in performance....ifanyone can answer this, you would be awesome.The "bad" query attempts to build a result set using a correlatedsubquery. The part causing the error is that the correlated subqueryis part of a derived table (joining 3 tables). Trying to run the querytakes a long time and the more records in the source table, theperformance is exponentially worse. When i change the derived table toa fixed table, the query runs fast.I look at the Execution Plan in Query Analyzer and the majority of timeis taken by the Clustered Index Seek and by the Nested Loops/InnerJoin.************************************************** ************************************************** ******here is the "bad" query:************************************************** ************************************************** ******SELECT licenseKey, (SELECT TOP 1 mi.id FROM messages miINNER JOIN identities i ON i.id=mi.identityidINNER JOIN licenses l on i.licenseid=l.idWHERE l.licenseKey = t1.licenseKey AND category = 'usage'ORDER BY mi.created DESC) as messageidFROM licenses T1************************************************** ************************************************** ******here is the "good" query************************************************** ************************************************** ******SELECT licenseKey, (SELECT TOP 1 t2.id FROM temptable T2WHERE t2.licenseKey = t1.licenseKeyORDER BY t2.created DESC) as messageidFROM licenses T1************************************************** ************************************************** ******Thank you in advance

View 5 Replies View Related

Trace And 6.5---simple Question--Tough Answers

Mar 23, 2004

I want to trace just sql statements, ie insert delete and update
On d drive the file should be l dropped

and have a fixed size say 1 mg

How do i set up

View 7 Replies View Related

Tough One - 28000 (18452) Not Associated With A Trusted Connection

Sep 6, 2007

Here's a real puzzler. I have a remote SQL Server running on a different domain than the one I am logged in to. From my own PC, logged in as myself, I can create an ODBC connection to this remote server (using integrated authentication) with no problem. When I log in to any other PC as myself, I get the "28000 (18452) Not associated with a trusted connection" error. This would therefore seem to be a PC-specific problem, but I can't see any difference between the PCs. All PC's are running the same version of MDAC.

Firewall is down, no proxy is involved, and I have given myself Administrative privelege on the PCs.

Can anyone explain this? Thanks in advance.

View 9 Replies View Related

Tough 2 Year Comparison Sales Query

Dec 17, 2007

I'm developing a 2 year comparison rolling 12 month Sales report but am having problems with the query I'm using. Because I need to include every month (whether no sales or not) I have a calendar table named metaDates and each table gets outer joined as a result. (Forgive the long query) Please see below.

SELECT Customer.country, Order_Line_Invoice.prodcatid, MetaDates.[Month], MetaDates.[Year], isNull(SUM(Order_Line_Invoice.Sales),0) AS Sales , SUM(Order_Line_Invoice.Cost) AS Cost,
( isNull(SUM(Order_Line_Invoice.Sales),0) - isNull(SUM(Order_Line_Invoice.Cost),0) )AS GM,
'Current 12 Months' AS yearNum
FROM MetaDates
LEFT OUTER JOIN Order_Line_Invoice ON (MetaDates.Date = Order_Line_Invoice.InvoiceDate AND (Order_Line_Invoice.prodcatid IN (@GroupByFieldFilter)) )
LEFT OUTER JOIN Customer ON (Order_Line_Invoice.CustId = Customer.CustId and Customer.country IN (@country) )
LEFT OUTER JOIN Product ON (Order_Line_Invoice.ProdId = Product.ProdId)
WHERE (MetaDates.Date BETWEEN dateadd(m, datediff(m, 0, DATEADD(month,-11,@EndDate) ), 0) AND @EndDate)

GROUP BY MetaDates.[Year], MetaDates.[Month], Customer.country, Order_Line_Invoice.prodcatid

UNION
SELECT Customer.country, Order_Line_Invoice.prodcatid, MetaDates.[Month], MetaDates.[Year], isNull(SUM(Order_Line_Invoice.Sales),0) AS Sales , SUM(Order_Line_Invoice.Cost) AS Cost,
( isNull(SUM(Order_Line_Invoice.Sales),0) - isNull(SUM(Order_Line_Invoice.Cost),0) ) AS GM,
'Previous 12 Months' AS yearNum
FROM MetaDates
LEFT OUTER JOIN Order_Line_Invoice ON (MetaDates.Date = Order_Line_Invoice.InvoiceDate AND (Order_Line_Invoice.prodcatid IN (@GroupByFieldFilter)) )
LEFT OUTER JOIN Customer ON (Order_Line_Invoice.CustId = Customer.CustId and Customer.country IN (@country) )
LEFT OUTER JOIN Product ON (Order_Line_Invoice.ProdId = Product.ProdId)
WHERE (MetaDates.Date BETWEEN dateadd(m, datediff(m, 0, DATEADD(month,-23,@EndDate) ), 0) AND dateadd(m, datediff(m, 0, DATEADD(month,-11,@EndDate) ), -1))

GROUP BY MetaDates.[Year], MetaDates.[Month], Customer.country, Order_Line_Invoice.prodcatid
ORDER BY MetaDates.[Year], MetaDates.[Month]

That said the second outer join (Customer.country IN (@country)) never gets executed. It's really a simple concept. "Give me last 2 year sales by month (every previous and last month even if no sales) but only for the country the user is in"

Can someone help me out with this sql statement. Any help would be much appreciated.

View 11 Replies View Related

Final Attemp -Sql Stored Procedure Tough Question

Mar 17, 2004

I have a procedure I need to get the values out of..I am using outputs...I have no idea why it wont work......I need all values listed in the select part of procedure....

CREATE procedure dbo.Appt_Login_NET
(
@LoginName nvarchar(15),
@Password NvarChar(15),
@UserName nvarchar(15)Output,
@UserPassword nvarchar(15)Output,
@UserClinic nvarchar(3)Output,
@UserTester bit Output
)
as
select
UserName,
UserPassword,
UserClinic,
UserTester
from
Clinic_users
where
UserName = @LoginName
and
UserPassword = @Password

GO

my vb.net code to retrive this info is

Private Sub Button1_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.ServerClick
Dim con As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmd As New SqlCommand
Dim parmuser As SqlParameter
Dim parmus As SqlParameter
Dim parmpass As SqlParameter
Dim parmtest As SqlParameter
Dim struser As String
Dim strpass As String
Dim strclinic As String
Dim strnames As String
Dim tester As String
strpass = txtPass.Value
struser = txtUser.Value
cmd = New SqlCommand("Appt_Login_NET", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@LoginName", struser)
cmd.Parameters.Add("@Password", strpass)
parmus = cmd.Parameters.Add("@UserName", SqlDbType.NVarChar)
parmus.Size = 15
parmus.Direction = ParameterDirection.Output
parmuser = cmd.Parameters.Add("@UserClinic", SqlDbType.NVarChar)
parmuser.Size = 3
parmuser.Direction = ParameterDirection.Output
parmpass = cmd.Parameters.Add("@UserPassword", SqlDbType.NVarChar)
parmpass.Size = 15
parmpass.Direction = ParameterDirection.Output
parmtest = cmd.Parameters.Add("@UserTester", SqlDbType.Bit)
parmtest.Size = 1
parmtest.Direction = ParameterDirection.Output

con.Open()
cmd.ExecuteNonQuery()
If Not IsDBNull(cmd.Parameters("@UserName").Value) Then
Label1.Text = cmd.Parameters("@UserName").Value()
Else
Label1.Text = "No Results Found"
End If

con.Close()
End Sub

Why does this always show as "DBNUll" I get nothing when I debug any of my parm variables.I searched the SQl Server and in Query analyzer instead of the output variables in the procedure being just outputs they are input/outputs...................What does it take to get this working??? Do I need a conversion datatype I would prefer I gain the values and store them in variables......

View 7 Replies View Related

TOUGH INSERT: Copy Sale Record/Line Items For Duplicate Record

Jul 20, 2005

I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria

View 6 Replies View Related

Need Some Logic Help

May 10, 2008

Can anyone see where my logic may have gone a stray?
A user would be attempting to update a gridview row, I'm storing the amount of an item and the qty they purchased. If the order was for $150 and the grid has two item 1 x $50 and 2 x $50 the order already totals $150, so if the user tries to change the first item to a qty of 2 x $50 the total would be $200 and this would be more than the total payment or $150.
I want to then tell them they cannot do this.
@AP_ID Int,@AI_ID int,@PurchaseAmount money,@PurchaseQTY int,@LastUpdate datetime,@LastUpdateBy nvarchar(50),@Receipt_ID intASdeclare @current_item_total decimal(18,2)declare @new_item_total decimal(18,2)declare @total_payments decimal(18,2)declare @current_purchase table(receipt_id int,purchase_amt money)INSERT INTO @current_purchase SELECT tblPurchase.Receipt_ID,(tblPurchase.PurchaseAmount * tblPurchase.PurchaseQty) as purchase_amtFROM tblPurchase INNER JOIN tblReceipts ON tblPurchase.Receipt_ID = tblReceipts.Receipt_IDWHERE tblReceipts.Receipt_ID=@receipt_id--Get total already saved to recordselect @current_item_total = sum(purchase_amt) from @current_purchase--Get total amount of payment saved to recordselect @total_payments = sum(tblReceipts.AmountPaid) from tblReceipts where tblReceipts.receipt_id=@receipt_id--Get total amount user is trying to save to recordset @new_item_total = (@PurchaseAmount * @PurchaseQTY)--If current total plus new total is greater than total payment, tell user they cannot do this.IF ((@current_item_total + @new_item_total) > @total_payments)BEGINSELECT 'You are attempting to add more than your total payment.' AS MESSAGEENDELSEBEGIN--UPDATE ROWUPDATE tblPurchase SET [AI_ID]=@AI_ID,[PurchaseAmount]=@PurchaseAmount,PurchaseQTY=@PurchaseQTY,LastUpdate=@LastUpdate,LastUpdateBy=@LastUpdateBy WHERE [AP_ID]=@AP_ID SELECT 'Item was updated.' AS MESSAGEEND

View 2 Replies View Related

Need Some Logic Help

Jan 6, 2004

Trying to bind some data to a datalist for a report.

User selects an Industry from a dropdown list and then I dump all records for that industry. However in order to parse some of the record field values into names (I.E. from a 1 to the actual company name) for some records I have to read TABLE_ONE and for other records I might have to read TABLE_TWO depending on the value of FIELD_ONE.

If FIELD_ONE = "A" then I get the NAME from TABLE_ONE.
If FIELD_ONE = "B" then I get the NAME from TABLE_TWO.
If FIELD_ONE = "C" then I get the NAME from TABLE_THREE.

I'm lost at how to get started on this. I thought about adding IF statements to my query but these won't work because I'm not passing in the value of FIELD_ONE ahead of time - it's part of the query. So I thought maybe I could do a pre-read and store all FIELD_ONE values in an ArrayList and pass these in as parameters, but the stored proc is only being called once - so that won't work.

Any thoughts on how I can do this?

View 2 Replies View Related

Help With Logic...

Apr 27, 2006

tbl_one hv 8mil rows, tbl_2 have 8k rows...

if
select count(*) from tbl_one
where sub_col1 = 2

return 3mil rows

and

select count(*) from tbl_2
where ad_col1 = '000009'

return 4k rows

HOW COME..
select count(*) from tbl_one,tbl_2
where (sub_col1 = 2 and ad_col1 = '1234')

return more than 12 billion rowss?? helpp..

View 7 Replies View Related

Help The Logic

May 3, 2006

I have to write trigger to relate two table.

If I have made changes like insert, update and modify in one table1 automatically have to change the table2 and vice versa.

How this can be done, do we need to point the common key fields in both table while inserting

View 8 Replies View Related

Is This Right Logic

Jun 12, 2007

What i'm trying to do is if this column dt.IN_DIV_NO is populated take that value first if it's null than rr.AIQ_R_DIVISION_NO and if that column is null than rr.F_DIVISION_NO.

This is what i came up with, will it evaluate the way i need it to?

case When dt.IN_DIV_NO is not null then dt.IN_DIV_NO else case when rr.AIQ_R_DIVISION_NO is null then rr.F_DIVISION_NO end end as Current_DIV

Thank-you

View 4 Replies View Related