I have a sitemapcache that caches nodes with a sqlcachedependency. Everything is working fine but one thing. Every time you visit a forum, the viewcount is changed, therefore raising the trigger and dropping my cache object. How do i make it so that the trigger is only fired if I update the Title or Description field?
ALTER TRIGGER [dbo].[sp_Forums_Topics_AspNet_SqlCacheNotification_Trigger] ON [dbo].[sp_Forums_Topics]
FOR INSERT, UPDATE,DELETE AS BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'sp_Forums_Topics'
END
I want to create a trigger that that is invoked when a certain condition is fulfilled. For instance:
I have 2 tables, PS and US with similar table structure. PS table contains a column called 'status'. Whenever the status column is updated from 2 to either 23, 24, 25 or 26, a new row, which contains the same data within the updated row of PS, will be inserted into the table US.
My goal is to create a trigger to automatically set the value for a status id on a table based on set criteria. Depending on the values of other fields will determine the value that the status id field is assigned. So far, I have written the trigger to update the status id field and I go through a seperate Update statement for each status id value. The problem is that I can't get this to work at the record level. The problem that I am getting is that if I have 50 records in TABLE1 and at least one of them satisfies the where clause of the update statement, all of the records get updated. So, using these two update statements, all of my records end up with a status value of '24' because that was the last update statement run in the trigger. Here is the code I have so far:
CREATE TRIGGER dbo.JulieTrigger1
ON dbo.Table1
AFTER INSERT,UPDATE
AS
BEGIN
BEGIN TRY
/*Update Table1.Status to POTENTIAL (id 23) status */
UPDATE TABLE1
SET status_id = 23
WHERE EXISTS (SELECT *
FROM TABLE1 a INNER JOIN TABLE2 b
ON b.order_id = a.order_id
WHERE a.start_dt IS NULL
AND b.current_status_ind = 1
AND b.lead_status_id NOT IN (15,16)
AND a.order_id = TABLE1.order_id)
/*Update Table1.Status to ACTIVE (id 24) status */
My question is fairly simple. When I join between two tables, I always use the ON syntax. For example:
SELECT
* FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId)
No problems there. However, if I then decide to further filter the selection based on some trait of the UserRole, I have two options: I can add the condition as a WHERE statement, or I can add the condition within the ON block.
--Version 1:
SELECT
* FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId) WHERE
UserRoles.Active = 'TRUE'
-- Version 2
SELECT
* FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId
AND UserRoles.Active = 'TRUE')
So, the question is, which is faster/better, if either? The Query Analyzer shows the two queries have the exact same execution plan, which makes sense, since they're both joining the same tables. However, I'm wondering if adding the condition in the ON statement results in fewer rows the JOIN statement initially needs to join up, thus reducing the overall initial size of the results table before the WHERE conditions are applied.
So is there a difference, performance wise? I imagine that if Users had a thousand records, and UserRoles had 10 records, then the JOIN would create a cartesian product of the two tables, resulting in 10,000 records in the table before the WHERE conditions are applied. However, if only three of the UserRoles is set to Active, would that mean that the resulting table, before applying WHERE conditions, would only contain 3000 records?
So, I have this SSIS package that basically moves records from one table into another table in the same DB. I€™m running it on the actual server and everything seems to work. The data-flow task lights up yellow and visual studio increments the number of records passed through as if they were running perfectly. However, when I go to look at the data, it€™s not in there! There are no errors reported ad the event log doesn€™t show anything that would lead me to think anything went wrong. Anyone ever run into this issue? I€™m wracking my brain on this one and am completely stumped. Any help would really be appreciated
This seems about the best place for my query however it does cross over a bit ... dont shoot me.
SQL 2000 sp4 on server 2003 dev env VB6 users XP Sp2 Mdac 2.8
I have a legacy app that required some simple ammendment, insert a single row of data into a simple table (Not rocket Science) Sample : insert into maintcon_mach (maintcon_id, mach_id, date) values (123, 456, getdate())
PK on maintcon_id, mach_id so that single relationship exists between maintcon_id and mach_id in the table.
The procedure above is followed by a verification procedure that checks to see if table maintcon_mach has a mach_id 456 against maintcon_id 123, this procedure returns true. So far so good, however if you step through the procedure till disconnecting from the database and then reconnect and do only the verification again, the data is missing.
The following steps to resolve have resulted in no improvement.
1. Verify MDAC in entire project and on users computers all 2.8 2 Remove transactions (just in case) and still the same. 3 Replace SQL insert with ADO (As below) and still the same.. tried this with and without transactions. 4 Tried referecing older MDACs 5 Installed SQLredist on sample machines and still the same.
rs.Open "maintcon_mach", conMach, adOpenForwardOnly, adLockOptimistic With rs .AddNew !maintcon_id = lngmaint !mach_id = lngMachID !Date = Format(Now(), "yyyy-mm-dd") .Update End With
Is the problem in SQL or MDAC??? Perhaps I've missed something (Obviously have) but have run out of places to look... Any idea's
I have a cursor inside a stored procedure that should identify rows based on certain criteria and update the columns on those records.
While I try to do so, the cursor goes into an infinite loop although I have only 1 record that matches my criteria. I think that the transaction is not commited and the cursor is picking up the same record for update.
Here is my sample record in table :tblMsg SenderRef <space> MsgStatusId <space> MsgType <space>Groupid 1281341<space> 1 <space>KBC-NON-ETC-MATCHED-BLIM <space>191902
DECLARE @blimGroupId AS INT DECLARE @admSenderRef AS VARCHAR(50)
DECLARE admCursor CURSOR FOR SELECT senderref FROMtblMsg WHERE msgstatusid = 18 AND msgtype = 'KBC-RCVD-ADM' FOR UPDATE
OPEN admCursor
FETCH NEXT FROM admCursor INTO @admSenderRef
WHILE @@FETCH_STATUS = 0 BEGIN --FIND CORRESPONDING BLIM RECORD AND GRAB ITS GROUPID TO UPDATE THE ADM SELECT @blimGroupId = GroupID FROM tblMsg WHERE msgType = 'KBC-NON-ETC-MATCHED-BLIM' AND SenderRef = @admSenderRef PRINT 'AFTER SELECT INSIDE WHILE: Senderref- >' + @admSenderRef; --UPDATE THE ADM RECORD WITH THE GROUPID AND MSGSTATUS = 3 BEGIN UPDATE tblMsg SET MsgStatusId = 3, GroupId = @blimGroupId WHERE CURRENT OF admCursor END PRINT 'AFTER UPDATE INSIDE WHILE'; END
CLOSE admCursor DEALLOCATEadmCursor
The update statement was included beteween a BEGIN TRAN and END TRAN. But no joy.
I have multiple questions and would appreciate any suggestions in resolving them. I'm a novice to these issues.
1) First of all, what is the exact command to setup the trunc. log on chkpt. option on for a transactional log of a SQL Server 2000 database? Is this option on by default? I have noticed for one of the databases I'm managing that the transaction log was over 12 GB, while the db was only 425 MB.
2) How's it possible to run a DBCC TRACEON to see the content of the transaction log to see if we are having any issues with any uncommitted transactions, i.e. updates, inserts, and/or deletes.
3) What are the commands to truncate inactive transactions and increasing the readbatchsize?
4) lastly, how do I validate transactional replication via checksum and find valid latency between a small number of changes that need to be committed between publisher and subscriber.
I have some code that should execute multiple statements and then commit. I am using the Microsoft SQL Server 2005 JDBC Driver 1.1.
The statements only commit ifI close the connection to the SQL Server 2005 instance. I've looked at the example given at http://msdn2.microsoft.com/en-us/library/ms378931.aspx and it doesn't require closing the connection in order to commit.
Example:
public void doCall(Connection con) { try { /*Turn off AutoCommit.*/ con.setAutoCommit(false);
I have a table CREATE TABLE [dbo].[CmnLanguage]( [Id] [char](2) NOT NULL CONSTRAINT PkCmnLanguage_Id PRIMARY KEY, [EnglishName] [varchar](26) NOT NULL, [NativeName] [nvarchar](26) NOT NULL, [DirectionType] [smallint] NOT NULL, [IsVisible] [bit] NOT NULL, [CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(), [ModifiedDateTime] [datetime] NULL) We will use these 3 queries select * from CmnLanguage where IsVisible = 0select * from CmnLanguage where IsVisible = 1select * from CmnLanguage I want to make a method which handles these queries. But at the back end on Stored Procedures We have to write 3 queries Which I don't want to do. I want to minimize the queries and conditions and want to just write one for these 3 Can any one do it?
I'm having some real problems using the OR condition in a very simple SQL statement and could use your help or insight on where the problem lies, or perhaps a workaround.
I have a large flat table in a SQL 7 database with 10 million + records called "HISTORY". I have not installed either service pack 1 or 2. I'm attempting to run a query that references the following four fields which are all non-clustered keys:
EQUIPMENT_NO TEXT 12 CHASSIS_IN TEXT 12 CHASSIS TEXT 12 SVC_DATE_TIME SMALLDATETIME
Here's the SQL statement:
SELECT * FROM HISTORY WHERE (HISTORY.EQUIPMENT_NO = 'XYZ123' OR HISTORY.CHASSIS = 'XYZ123' OR HISTORY.CHASSIS_IN = 'XYZ123') AND SVC_DATE_TIME >= '01/15/00 00:00:00 AM' AND SVC_DATE_TIME <= '02/28/00 23:59:59 PM' ORDER BY EQUIPMENT_NO
This query takes 11 min. 5 sec. inder the Query Analyzer and ultimately returns the 31 desired records.
If you remove the SVC_DATE_TIME criteria, about 350 records are returned in a matter of seconds. I've also tried variations on the date syntax such as '01/15/00', etc. with no change in the amount of time to execute.
Other queries such as a simple AND condition combining EQUIPMENT_NO and SVC_DATE_TIME are snappy.
Are there known problems/bugs with "OR" conditions in queries that anyone is aware of, particularly with parentheses; am I composing this query incorrectly? Is there some alternate syntax that would work as expected? I can't see where the query shouldn't execute quickly as expected, particularly with all indexed fields involved. I'm stumped! Lend me your expertise. Thanks much.
Clark R. Farabaugh, Jr. Financial Systems Analyst VIT Norfolk, VA
I have a table called sample and i have the following requirement. i.e i need sum(credit) group by ssn no.
One special condition is as follows:
For each distinct ssn if "flag" has the same CX value,then out of all the records with the same CX value, the highest "credit" value is added to the sum for that "ssn" and the rest are ignored. If while adding "credit" to the sum and if "credit" value is equal to zero then "sum" value is used for summing else "credit" value is used. Can any one help me out in trying this logic. I have tried but i could'nt able embed the conditions inbetween the Sql statetment.
Code: Drop table #table Drop table #table_with_groupid -- Prepare test data CREATE TABLE #table ([Admissions_key] bigint NOT NULL PRIMARY KEY, MRN nvarchar(10) NOT NULL,
[Code] ....
How can I compare dates with conditions. I only want to Mark C where the difference between adm_datetime and prevsep_datetime is <= 1 otherwise E as well
where datediff(MINUTE,tg.adm_datetime,tg.pre_sep_date)< =1 ??
is it correct ? where should I put this to implement correctly ?
MAX(Vernum) AS test, MAX(Case WHEN A2.AttrID = 2 AND A2.DefID = 10057945 THEN A2.ValStr END) AS TYPE_DOCUMENT
This works not perfect. In many cases I have more then one row and my query takes the max value of column Valstr. Thats is not exactly what I want. I'd like to have the value of Column Valstr of the row where column Vernum has the maximum value.
I've tried many things like:
MAX(Vernum) AS test, MAX(Case WHEN A2.AttrID = 2 AND A2.DefID = 10057945 AND A2.Vernum=test THEN A2.ValStr END) AS TYPE_DOCUMENT
OR
MAX(Vernum) AS test, MAX(Case WHEN A2.AttrID = 2 AND A2.DefID = 10057945 AND A2.Vernum=MAX(A2.Vernum) THEN A2.ValStr END) AS TYPE_DOCUMENT
pls: 1/ can we do it this way: inner join Table2 ON table1.fld1=table2.fld21 AND table1.fld12=table2.fld22 2/also: what s the difference between join , iner join and left join Thanks .
IF NOT EXISTS(SELECT * FROM CPA WHERE PrimaryEmail=@PrimaryEmail or PrimaryEmail=@SecondaryEmail or SecondaryEmail=@PrimaryEmail or SecondaryEmail=@SecondaryEmail ) BEGIN --select * from o_login Begin transaction InsCPA
INSERT INTO CPA(SupervisorId,BookmarkerId,PreparerId,FirmNo,FirmName,FirstName,MiddleName,LastName,TaxYear,TaxSoftware,HomePhone,WorkPhone,Fax,PrimaryEmail,SecondaryEmail,CountryId,State,Zipcode,Status) VALUES(@SupervisorId,@BookmarkerId,@PreparerId,@FirmNo,@FirmName,@FirstName,@MiddleName,@LastName,@TaxYear,@TaxSoftware,@HomePhone,@WorkPhone,@Fax,@PrimaryEmail,@SecondaryEmail,@CountryId,@State,@Zipcode,@Status)
--Error handling IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN InsCPA SET @RESULT = 0 END ELSE BEGIN --DECLARE @ID1 INTEGER -- Returns 1 to the calling program to indicate success. COMMIT TRAN InsCPA SET @RESULT = 1 END END ELSE BEGIN -- Return 2 to the calling program to indicate record already exists. set @RESULT = 2 END END
ELSE IF(@Operator='U') BEGIN declare @pemail as varchar(30) declare @semail as varchar(30) declare @firm as varchar(20) select @pemail=PrimaryEmail,@semail=SecondaryEmail,@firm=FirmNo from CPA WHERE Id = @Id --select * from CPA
if(@pemail=@PrimaryEmail) or(@semail=@PrimaryEmail)--or((@semail=@SecondaryEmail)and (@semail=@PrimaryEmail))) begin
print 'prim1' if(@semail=@SecondaryEmail)or (@pemail=@SecondaryEmail) begin print 'sec1' if(@firm=@FirmNo) begin print'firm' BEGIN TRANSACTION UpdateCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id UPDATE EMPLOYEE SET FirmNo=@FirmNo WHERE FirmNo=@firm
IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN UpdateCPA SET @RESULT = 0
END ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'1' COMMIT TRAN UpdateCPA SET @RESULT = 1
END END else begin IF NOT EXISTS(SELECT * FROM CPA WHERE FirmNo=@FirmNo) BEGIN print'fd' BEGIN TRANSACTION UpdateCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id UPDATE EMPLOYEE SET FirmNo=@FirmNo WHERE FirmNo=@firm IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN UpdateCPA SET @RESULT = 0
END ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'1' COMMIT TRAN UpdateCPA SET @RESULT = 1
END end ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'4' --COMMIT TRAN UpdateCPA SET @RESULT = 4
END end end else begin IF NOT EXISTS(SELECT * FROM CPA WHERE PrimaryEmail=@SecondaryEmail or SecondaryEmail=@SecondaryEmail) BEGIN if(@firm=@FirmNo) begin BEGIN TRANSACTION UpdateCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id UPDATE EMPLOYEE SET FirmNo=@FirmNo WHERE FirmNo=@firm
IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN UpdateCPA SET @RESULT = 0
END ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'1' COMMIT TRAN UpdateCPA SET @RESULT = 1
END END else begin IF NOT EXISTS(SELECT * FROM CPA WHERE FirmNo=@FirmNo) BEGIN BEGIN TRANSACTION UpdateCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id UPDATE EMPLOYEE SET FirmNo=@FirmNo WHERE FirmNo=@firm IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN UpdateCPA SET @RESULT = 0
END ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'1' COMMIT TRAN UpdateCPA SET @RESULT = 1
END end ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'44' --COMMIT TRAN UpdateCPA SET @RESULT = 4
END end /* --select * from o_login Begin transaction InsCPA
UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id --Error handling IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN InsCPA SET @RESULT = 0 END ELSE BEGIN --DECLARE @ID1 INTEGER -- Returns 1 to the calling program to indicate success. print'11' COMMIT TRAN InsCPA SET @RESULT = 1 END*/ END ELSE BEGIN print 'sec same' -- Return 2 to the calling program to indicate record already exists. set @RESULT = 3 END end end else begin IF NOT EXISTS(SELECT * FROM CPA WHERE PrimaryEmail=@PrimaryEmail or SecondaryEmail=@PrimaryEmail) BEGIN /*--select * from o_login Begin transaction InsCPA
UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id --Error handling IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN InsCPA SET @RESULT = 0 END ELSE BEGIN --DECLARE @ID1 INTEGER -- Returns 1 to the calling program to indicate success. print'111' COMMIT TRAN InsCPA SET @RESULT = 1 END*/ if(@firm=@FirmNo) begin BEGIN TRANSACTION UpdateCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id
UPDATE EMPLOYEE SET FirmNo=@FirmNo WHERE FirmNo=@firm
IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN UpdateCPA SET @RESULT = 0
END ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'1' COMMIT TRAN UpdateCPA SET @RESULT = 1
END END else begin IF NOT EXISTS(SELECT * FROM CPA WHERE FirmNo=@FirmNo) BEGIN BEGIN TRANSACTION UpdateCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id
UPDATE EMPLOYEE SET FirmNo=@FirmNo WHERE FirmNo=@firm IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN UpdateCPA SET @RESULT = 0
END ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'1' COMMIT TRAN UpdateCPA SET @RESULT = 1
END end ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'2' --COMMIT TRAN UpdateCPA SET @RESULT = 2
END end END ELSE BEGIN print 'prim same' -- Return 2 to the calling program to indicate record already exists. set @RESULT = 2 END end end
Above procedure has many if else conditions Is there any way to write procs other than this process
1. Are stored procedures WITH ENCRYPTION slower than the ones withoutencryption?2. Should i put most restrictive conditions first or last in WHERE? Inwhich order does MSSQL execute conditions? Or MSSQL determents whatwould be best and does not bother with the way i sorted conditions?for example:SELECT *FROM [users]WHERE[user_id] = 1 AND[baned] = 0Is "[user_id] = 1" or "[baned] = 0" going to be executed first?
I am trying to do a summary SQL query. I have 3 fields. If one filed isnull and the other is not null, I want to count how many records thereare. I also want to count the opposite way then count both fields ifthey are both not null. Can I do this within the same query? Helpappreciated.Thanks,Steve*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I am trying to do a select with the closest match.
Code Snippet
CREATE TABLE [#Person]
(
[PersonId] tinyint IDENTITY(1,1) NOT NULL
,[Height] char(2) NOT NULL
,[Weight] char(3) NOT NULL
,[Age] varchar(3) NOT NULL
,[HairColor] varchar(7) NOT NULL
,[EyeColor] varchar(7) NOT NULL
);
INSERT INTO [#Person] ([Height],[Weight],[Age],[HairColor],[EyeColor])
VALUES ('71', '150', '23', 'Brown', 'Blue');
INSERT INTO [#Person] ([Height],[Weight],[Age],[HairColor],[EyeColor])
VALUES ('70', '190', '22', 'Blonde', 'Brown');
INSERT INTO [#Person] ([Height],[Weight],[Age],[HairColor],[EyeColor])
VALUES ('69', '140', '20', 'Black', 'Brown');
INSERT INTO [#Person] ([Height],[Weight],[Age],[HairColor],[EyeColor])
VALUES ('65', '150', '21', 'Brown', 'Green');
SELECT * FROM [#Person]
WHERE
([Height] > '66' AND [Height] < '72')
AND ([Weight] > '150' OR [Weight] < '180')
AND ([Age] > '20' OR [Age] < '25')
AND ([HairColor] IN ('Brown', 'Blonde', 'Black'))
AND ([EyeColor] IN ('Blue', 'Brown', 'Green'));
DROP TABLE [#Person];
This simple example works great. But what if I wanted everybody who met 4 of the 5 conditions? I tried to think of a counting solutions, and then order by the count but I could quite get there. Any help would be greatly appreciated. My live data is actual sales records with 20 some odd record types. I am hoping any solutions I find will scale well.
Hi All, This is my problem. I need the out put of a sql select statement to be "true" or "false" depending on the actual columns value is positive or negative. Does any one how to do this. Thanks in advance, -VJ
I can't figure this out for the life of me. Wanted to know if it's possible to select certain date conditions in a query, then later reference those conditions and to only select the max of them.
I need to do this dynamically as I do not know what the max value is. I've provided an example below:
Select var1 From table1 where ( (Date1 = '11/30/2005') OR (Date1 = '12/31/2005') ) and Date1 = (Max of previously selected values e.g. '12/31/2005')
What I can't figure out is how to dynamically retrieve the max of 11/31/2005 and 12/31/2005. Any ideas are greatly appreciated.
what is order in which conditions are processed for sql query i.e for select * from table1, table2 where cond1 and cond2 and cond3 which condition will be processed first (i.e. for optimination purpose condition cutting down max no. of row shud be placed first or last?)
I have a table where when an employee works LEAVE1 and LEAVE2 paycodes on the same day then in the new column called 'FLAG' we need to put a 1 for LEAVE1 and a 2 for LEAVE2, when these paycodes are not there on the same day then flag will be NULL as for other paycodes.
Tried using the CASE statements but it always puts a 1 or 2 for the respective paycodes regardless of the fact whether they were together on the same day or not.
WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE) AS ( SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1' UNION ALL SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4','REG2' UNION ALL SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '09:00','13:00','4','LEAVE1' UNION ALL SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '14:00','16:00','2', 'LEAVE2'UNION ALL SELECT 1234,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'LEAVE1'UNION ALL SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'REG1'UNION ALL SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '10:00','12:00','2','LEAVE2' )
I have three table For example Employee (Empid , Empname , Esal) Department (Deptid , Deptname , empid ) Staff (staffid , Staffname , Empid)
It is just example how can i update Empname whose staffid =1 accor to staffid) using Join Conditions :- Pls help me out .. or how to update data using JOIN Conditions
Meed to complete this procedure. It fails in the WHEN condition
ALTER PROCEDURE [dbo].[sp_TranpostSupport_Xuat_Nhap_ToTal]
@NO_ AS NVARCHAR(4000), @FromDate DATETIME, @ToDate DATETIME, @TransInvoiceID INT AS SELECT x.NO_,x.NAMES,x.ZIPCODE,x.FromDate,x.ToDate,x.Total450c, n.Total450k,
