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
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 RelatedI 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?
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...
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?
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.
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 RelatedI 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 RelatedThe 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 RelatedI 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 RelatedI'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 RelatedHey all
I've got a DTS package with a script that does this:
Set xlApp = CreateObject("Excel.Application")
This fails though. Do I have to install MS Office on the machine the SQL Server is on? Or can I simply copy a few DLLs over and register them??
Thanks! I gotta get this solved within 5 hrs :(
Hi,
I am trying to get data as it is available in the table but every time I run sql statement it shows in asending order. How can I avoid this asending order situation?
Select distinct db_contract,title1 from titles where db_contract=39624
db_contract Title1
39624 HIGHWAY
39624 M-24-001
39624 M-24-002
39624 R-24-003
39624 M-24-006
39624 Z-24-007
Any help will be appreciated.
Hi everyone -
I am relatively new to this and trying to make my database as efficient as possible.
Here is the situation. In my database, there are multiple "stocks" that each have a "pricing history". When something happens that changes the price of the stock, an entry containing information about the time the change occured, who changed it, etc. is added.
I have many stocks. The information of these stocks is independant in the sense that I never really need to grab/compare information about two stocks at once. I only look at one stock at a time.
So, do I set up a "pricing history" table for every single stock (pricinghist1,pricinghist2,etc.)? Or do I set up one "pricing history" table it and index the table by stock number?
Are these equivilant? Is one more efficient than the other?
Thanks!
N
Here is the basic situation.
(1) I have 2 packages that run independently of eachother.
(2) Both are scheduled in SQLServer Agent Jobs as separate jobs.
(3) Job A is scheduled to run every 12 hours and Job B is scheduled to run every 10 minutes.
(4) However, I want to prevent Job B from running if Job A happens to be running.
(5) It is unknown exactly how long Job A will take to finish so I can't schedule Job B around it.
The way I wanted to approach this situation is as follows.
Within Job A's package, create a "marker" file when the package starts and delete it when the package finishes. So the existence of this marker file will tell Job B's package if it should run or not.
The concept is simple, but I'm not sure how to implement this.
For example, to create the marker file, I would use a File System Task, but I don't see an option in there to "create file". (However, I do see an option for "delete file".) Also, what task would I use in Job B's package to check if the marker file exists.
Lastly, If you have better approach, I would like to hear about it.
Thank you.
Hello everyone,
I'm creating a database for a new application and I'm currently facing a design problem, regarding a business requierment for the membership module.
The Membership Module of the application has several business requierments, specified by the client. One of them is the ability to add and remove details about their members. So far, I've created a schema, named Person, which will contain a number of tables responsible for everything related with the membership, as an individual.
To help you guys understand the design I'm trying to implement, I'll post the fields of two of the tables that belong to the Person schema, as follows:
Person.Base
Id (uniqueidentifier)
UniqueIdTypeId (uniqueidentifier)
UniqueId (uniqueidentifier)
Password (char(88))
PasswordSalt (char(10))
PasswordRecoveryQuestion (nvarchar(256)) [NULLABLE]
PasswordRecoveryAnswer (char(88)) [NULLABLE]
CreationDate (datetime)
AuthenticationWindowStartDate (datetime)
AuthenticationWindowAttemptCount (tinyint)
IsActive (bit)
IsBanned (bit)
IsLocked (bit)
Status (bit)
Person.Emails
PersonId (uniqueidentifier)
EmailAddress (nvarchar(256))
CreationDate (datetime)
ValidationCode (char(10))
ValidationDate (datetime) [NULLABLE]
IsValid (bit)
IsPrimary (bit)
IsRollbackTarget (bit)
Status (bit)
So far so good. This design works great to preserve the data integrety. Nonetheless, this is where the problems start. Now, imagin you need to let someone from that company add an item to the user (through the application). Let's say we want to allow the company application manager to add an item to the person called "PreviousEmployer". Such item would then be used for statics, thus would probably need to be indexed.
In order to meet this business requierment I would create some addicional tables. Let's get started:
Person.CustomFields
Id (uniqueidentifier)
Name (nvarchar(50))
Description (nvarchar(3000)) [NULLABLE]
Status (bit)
Person.CustomField_Value
FieldId (uniqueidentifier)
Value (nvarchar(450))
This could work just fine if both are indexed (that's why the nvarchar size is set to 450). But I'm guessing this is far from the optimal solution for many reasons, one of them being the efficiency of the index if the company decides to go and use this for a flag (true or false [bit]). Another "solution" breaking scenario would be if the company wants to add the CV of the person, situation in which we were unlikely to be able to add a file in this datafield.
How would you guys approach this issue? The bottom line is that the client needs to be able to add pretty mcuh any type of custom field and perform searches againts it. So, besides being a dynamic solution it needs to be efficient.
Best regards and thanks in advance.
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!
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
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
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!
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?
Hi, I have the following tables:
Categories {Category_ID, Column2, ...}
Articles { Article_ID, Category_FK, Column3, ...}
Discussions {Discussion_ID, Article_FK, Column3, ...}
Now, all what I have is just category_ID value (Let us say 3), how can I do cascade delete to delete category's record that its ID = 3 and delete all articles and all discussions that found in that category?
I have a very urgent situation and to do with SQL Server 6.5, cannot start and the error message I got was: Initdata: Suballocation for buffer pages failed (838860 Bytes requested. Can anyone help me urgent matter.
Regards,
I need help on reproducing a deadlock. The sample that I have so far is only creating blocking and it never deadlocks. It's because I'm not sure what order the system tables are being help when creating temp tables in a transaction, therefore, I can't tell which pages need to be requested from another transaction to cause the deadlock. Any help will be much appreciated.
Thanks,
Mike
We have 2 SQL tables being accessed through an Access form. The tables are an ORDER table and an ORDER-DETAIL table comprised of data regarding the Parts in any given Order. (Yes -- the classic Order-Entry situation.) The Access form is used to view/create new Orders, and shows ORDER data in fields, plus has a large field which presents a "spreadsheet"-like view of the related records from the ORDER-DETAIL table.
The users enter and modify data in the ORDER-DETAIL table directly through this "spreadsheet" in the Access form. However, because there is no PARTS table yet (that's part of what I'm working on), they have to enter part numbers and descriptions *manually* in each ORDER.
So... here's my question:
After I implement a PARTS table, I would like for users to be able to open an ORDER in the Access form, type in a Part # in a row of the ORDER-DETAIL "spreadsheet", and then have the rest of the row populate with the appropriate Part description and other data from the PARTS table. How do I go about making that a reality? Some kind of stored procedure triggered by a change in the Part # field? Ha ha – if so, I am clueless as to how to make that happen. ANY information would greatly appreciated!
Thanks!
whill96205 the Noob :confused:
Hi, all..
I by mistake Overwrites distribution DB.
Server1 and Server2 replicates each other.
Applications are using Server1, and Server2 is back up purpose.
When server1 fails application redirected SErver2, using same data since there is replication.
Server2 had distribution DB.
Now there is no distribution DB.. it's messed up...
I want to restore replication between Server1 and Server2.
I tried to make replication from Server1, I got errors..
How can I do this???
I have a Transaction replication running between 2 SQl 2000 Servers. The distributor is on seperate machine.
Server1 is an online Db which is sending replicated trn to Server2 which is a reporting Server. Because of space issues, I have to remove about 100 Million Rows from Server1 Table without affectiing Server2 Table (Server 2 Retains those rows)
Is there any Stored Proc available to disable the replication only during that time ?
Thanks
How would I write a query to produce all combinations for a situation such as the following?
Suppose I wanted to write a sentence, "This is [adjective] [noun]." where [adjective] comes from the Adjective table and [noun] come from the Noun table.
Adjective table looks like e.g.
ID Adj
1 good
2 so so
3 bad
Noun table looks like e.g.
ID Noun
1 apple
2 orange
3 banana
And the result set would look like
This is good apple.
This is so so apple.
This is bad apple.
This is good orange.
This is so so orange.
This is bad orange.
This is good banana.
This is so so banana.
This is bad banana.
I would take a stab at this myself and post even something that doesn't work...
OBJECTIVE:THE QUERY SHOULD GIVE ME THE FIELDS I MENTIONED IN THE FIRST QUERY WITH THE CONDITIONS BELOW.
CONDITION 1: RateReview field should have yesterday's date
CONDITION 2: Email will be send to customer only once so Customer_GUID is UniqueIdentifier
CONDITION 3: Customer shouldnt' have opted to get out from receiving any email so Termination field should be NULL
ONe Customer can have many transwactions
Is there any way i write the code specifying that no email should be sent more than once evereven if customer buys 10 tickets.
Only one email sent so i need to specify that if this email has gone to particulare CUSTOMER_GUID then Ignore that record and
do not send any email. This would be done by some tool known as StrongMail.
SELECT
CAST(a.Transaction_GUID AS varchar(36)) as Transaction_GUID,
CAST(a.Customer_GUID AS varchar(36)) as Customer_GUID,
Film_id as MovieId,
First_nm as FirstName,
Last_nm as LastName,
Email_nm as EmailAddress
FROm
Table1 where RateReview_dm >= dateadd(day, datediff(day, 0, getdate()), -1) -- Greater or same as Yesterday day
and RateReview_dm < dateadd(day, datediff(day, 0, getdate()), 0) -- Less than today's date
and
and Terminate_dm is null
(I don;t know what condition to give that same customer good should not be send email again if send once)
i don't know whether i need to create a derive table or it can work without drive table
Cananyone help me with this query please
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
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
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 RelatedI 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