Challenge - Swap Two Columns
Apr 30, 2004Ok .. a challenge ...
How do you swap two columns based on some condition without using a cursor ( I mean a set based solution).
ADVERTISEMENT
Swap Rows For Columns In A Table
May 12, 2008have the following table:
RecordKey MonthYear SD1 SD2 SD3
1 Jan 2008 6 AA 0
2 Feb 2008 10 BB 3
3 Mar 2008 12 CC 8
etc....
I need to transform it to this:
1 2 3
Jan 2008 Feb 2008 Mar 2008
6 10 12
AA BB CC
0 3 8
Can this be done automatically in SQL?
Swap The Data Between 2 Columns Of A Table In Sql
Sep 24, 2007I have a table named tbOrders. I want to swap the data between 2 of its columns named OrderNumber and CaseNumber.
Both the columns are varchar datatype. How can i swap the data between the two columns?
How To Swap A Row Of Data With Another
Jun 27, 2006Hi,
I'm pretty new to SQL and certainly new to SQL Server, so this is no doubt a simple question, but how can I take a row of data (say row 117) and swap it with another specified row of data? I know it sounds odd, but the key for each row is automatically generated so I don't want to mess with that, so I just want to swap the other fields.
Here's an example of what I mean:
----------------------------------------------
ID | headline | content | newstype | date |
----------------------------------------------
117 | test | asdfgasd| industry | 06/06|
118 | test2 | adsfg as| company | 06/06|
----------------------------------------------
What i've discovered is that the order of data in the rows isn't correct, so I want to maintain the ID fields seeing as they're in numeric order, but then use that ID to grab the rest of the row (117 for example) and replace the data for 118 with 117's data. Then do the same with 118's original data....
I need to do this because all the related rows in my table were posted on the same date, so other than that they appear on screen based on where they appear in the table, which doesn't match what the client wants...
Sorry for the long post, but as a beginner with SQL I wanted to make sure I made sense!
How Can I Swap Two Rows In A Table
May 19, 2005I need to swap two rows in a table so that i can give the user a choice to move the row data up and down. The top and bottom row values may not be in the serial order. I am using MS Sql 2000. Is there any way to do that or its really difficult big process. I really stuck with this problem can you help me out, please friends.. Your help will greatly appreciated..
Thank you very much
Swap Database To A New Server
Feb 3, 2008hi, we have got a new server at work running SBS 2003. the old server was SBS 2000 and it has a database on it. Both are running the SQL server program. Can anyone give me instructions to transfer these over (i have no idea how). i realise this has probably been asked before but i dont have the time to search so if someone could link a thread with instructions that would be good. cheers.
View 2 Replies View RelatedSwap Column Position
Jul 20, 2005What is a good method/mechanism to swap the position of multiplecolumns?For instance, tblXZY has the followings columns and respectivepositions:tblXZY======xyzUUID 1fn 2ln 3phone 4email 5city 6state 7....Now, I need to make city as 2 and state as 3. BTW, the tblXYZ tablehas data there. Copy/select all the data into a new table withdesired column position would require constraints re-mapping etc.,which seems quite a bit hassle.Thanks.
View 6 Replies View RelatedLog Shipping- How To Resume After Swap
Oct 30, 2006I have setup logshipping in sql 2005. i have a primary and secondary server only.
Suppose for maintenace i bring primary down. . i have to manually bring the secondary server online. usnig restore databse with restore.
1) so far so good. But what now. How can i re-sync the old primary with the new primary?
2) can i return the original secondary back to standby mode so that the primary can resume its role and the log shipping process proceeds.
Transact SQL :: How To Swap Values Of Two Rows In A Table
Sep 10, 2015I am a newbie to Sql server and i am having a task where my current table with always two rows existing and ID column as primary key looks like below:
IDÂ PlateNo Type Image Name
27 455 User img1.jpg
32 542 Alternative img2.jpg
And i want a sql query to modify my table so that the data should be like as shown below:
IDÂ PlateNo Type Image Name
27 542 Alternative img2.jpg
32 455 User img1.jpg
Please Need Rescue- Complex Update Logic Swap
May 14, 2008please need rescue- complex update logic
this is my table
1
2
3
4
5
EMPID
fld1
fld11
fld111
fld2
fld22
fld222
fld3
fld33
fld4
fld44
fld444
fld5
fld55
fld555
1111
A
B
C
7
8
9
G
H
I
J
K
L
M
N
2222
N
M
L
K
J
I
H
G
F
E
D
C
B
A
3333
1
2
3
A
B
C
C
E
Y
I
O
W
Y
P
i need to update for example the eployee 1111 with employee 3333
but with swap ( take the value of employee 1111 in field- fld2,fld22,fld222 and swap value between employee 3333
in field- fld2,fld22,fld222 )
Code Snippet
---update eployee 1111 with employee 3333
-so
if i put the value 2
than ------------------ swap value between 2 employee
set empid1= 1111
set empid2=3333
value_swap=2
if value_swap=2
than
update fld2,fld22,fld222
with fld2,fld22,fld222
------------------- take the value of employee 1111 in field- fld2,fld22,fld222 and swap value between employee 3333
--------------------in field- fld2,fld22,fld222
value_swap
=1
=2
=3
=4
=5
EMPID
fld1
fld11
fld111
fld2
fld22
fld222
fld3
fld33
fld4
fld44
fld444
fld5
fld55
fld555
1111
A
B
C
A
B
C
G
H
I
J
K
L
M
N
2222
N
M
L
K
J
I
H
G
F
E
D
C
B
A
3333
1
2
3
7
8
9
C
E
Y
I
O
W
Y
P
Code Snippet
---update eployee 2222 with employee 1111
-so
if i put the value 5
than ------------------ swap value between 2 employees
set empid1= 1111
set empid2=2222
value_swap=5
if value_swap=5
than
update fld5,fld55,fld555
with fld5,fld55,fld555
------------------- take the value of employee 1111 in field- fld5,fld55,fld555 and swap value between employee 3333
--------------------in field- fld5,fld55,fld555
=1
=2
=3
=4
=5
EMPID
fld1
fld11
fld111
fld2
fld22
fld222
fld3
fld33
fld4
fld44
fld444
fld5
fld55
fld555
1111
A
B
C
7
8
9
G
H
I
J
K
W
Y
P
2222
N
M
L
K
J
I
H
G
F
E
D
C
B
A
3333
1
2
3
A
B
C
C
E
Y
I
O
L
M
N
TNX FOR ALL THE HELP I GET IN THIS Forum
Analysis :: Swap Measures According To Dimension Selection
Oct 9, 2015I've 2 measures that I would like to swap according to which member in a dimension are selected by the user:Ex.
[Measure].[A]
[Measure].[B]
Dimension called TEMP with 2 members (HOT and COLD).So if the user choose HOT it should show [Measure].[A] and if the user choose COLD it should show [Measure].[B]If the dimension is not included in the analysis by the user or if no dimension attribute are choosed then it should default to [Measure].[A].How to do that as Calculated Script in the SSAS cube ?
Transact SQL :: Swap Values In 2 Rows Based On Primary Key
Jun 12, 2015I need swapping values in rows of tables based on primary.
I will explain my scenario with the below example.
ID         Name                 Value  Â
---Â Â Â Â Â Â Â Â Â --------Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â --------Â
1Â Â Â Â Â Â Â Â Â Â Â Â name1Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 202
2Â Â Â Â Â Â Â Â Â Â Â Â name1Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 203
3Â Â Â Â Â Â Â Â Â Â Â Â name2Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 203
4Â Â Â Â Â Â Â Â Â Â Â Â name2Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 202
5Â Â Â Â Â Â Â Â Â Â Â Â Â name3Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 204
In the above example, I want to swap Value 202 and 203 but its not straight forward. I want to swap in a way that, I need to group by Name column and check if 203 comes first in the group then swap else don't.
In the above example, I need to swap the values for name2 but not for name1. Below query is performing a straight forward swap.
UPDATE TblTest
SET Value
=
CASE
WHEN
Value
=202
THEN203
ELSE202
END
WHERE
Valuein(202,203)
I/O Congestion Troubleshooting: Memory And Swap File Usage Question
Jul 20, 2005We are hosting a 140 GB database on SQL Server Version 7 and Windows2000 Advanced Server on an 8-cpu box connected to a 15K rpm RAID 5SAN, with 4 GB of RAM (only 2 GB of which seem to be visible to theOS) and a 4 GB swap file. (The PeopleSoft CIS application will notpermit us to upgrade to SQL 2K.) We recently upgraded the server from4 to 8 cpus and the SAN disks from 10K to 15K drives. But we stillhave heavy SAN disk usage, sometimes at 100%, and read queues oftenaveraging 4 and peaking at 12.The CPUs are loaded at only 20-50%. (The politics are such that it iseasier to throw hardware at the problems.)We are looking into archiving, converting from RAID 5 to RAID 10, andat splitting the mdf file into several file groups in an attempt toget more disk heads into play. (We are also looking at rewriting theapplication to reduce the read volume and frequency.) Does anyone haveany other ideas?Incidentally, does swapfile get used when the physical memory equalsthe OS maximum? If the OS can only see 2 GB and we have 2 GB (actually4 GB) of memory, is the 4GB local swap file on the C drive unused?Thanks in advance for any assistance.
View 1 Replies View RelatedThe Buffer Manager Detected That The System Was Low On Virtual Memory, But Was Unable To Swap Out Any Buffers.
Oct 25, 2007[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 12 buffers were considered and 12 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
View 12 Replies View RelatedSQL Challenge
Oct 4, 2006I just don't have the skills to work this out...maybe there is an SQL guru out there that can solve this:I can't work out how to do a recursive query so I'm using this function:Public Shared Function FindFriendsFriends(ByVal userID As Integer)Dim iUserID As Integer = userIDDim friendAdapter As New TableAdapters.FriendsTableAdapterDim oFriends As New FrontEnd.FriendsFriendsDataTableDim oTemp As New Data.DataTableDim oContainer As New Data.DataTableoFriends = friendAdapter.GetFriendsByUserID(iUserID)For InLoopCounter As Integer = 0 To oFriends.Count - 1oTemp = friendAdapter.GetFriendsByUserID(oFriends.Rows(0)("UserID"))oContainer.Merge(oTemp)NextReturn oContainerEnd FunctionWith this SQL statement in the table adapter is:SELECT tblFriends.FriendHashID, tblFriends.cCreated, tblFriends.UserOwnerID, tblUsers.UserID, tblUsers.Displayname, tblUsers.EmailAddress, tblFriends.RequestStatus, tblFriends.FriendUserID, tblUsers.ProfilePhoto FROM tblFriends INNER JOIN tblUsers ON tblFriends.FriendUserID = tblUsers.UserID WHERE (tblFriends.UserOwnerID = @UserID) AND (tblFriends.RequestStatus = 2) UNION SELECT tblFriends_1.FriendHashID, tblFriends_1.cCreated, tblFriends_1.UserOwnerID, tblUsers_1.UserID, tblUsers_1.Displayname, tblUsers_1.EmailAddress, tblFriends_1.RequestStatus, tblFriends_1.FriendUserID, tblUsers_1.ProfilePhoto FROM tblFriends AS tblFriends_1 INNER JOIN tblUsers AS tblUsers_1 ON tblFriends_1.UserOwnerID = tblUsers_1.UserID WHERE (tblFriends_1.RequestStatus = 2) AND (tblFriends_1.FriendUserID = @UserID)I want to replace the SQL statement with a recursive query that I simply pass the UserID to and get rid of the function which is very inefficient!
View 2 Replies View RelatedHere's A Challenge For You All.
Sep 2, 2004Ok, here's the problem. I have CA's Unicenter ServiceIT Enterprise Edition 5.0 running on a production box that has NT4 (SP6) and SQL server 2000 running on it.
Just before we continue, this is about backing up the database that ServiceIT connects to.
I know that you can schedule EM to make backup's of a specific database over a certain time, but this is part of the problem. What i have since discovered is that ServiceIT will not run on a database backed up and restored by EM.
It will however run on the restored backup created through the dos command pdm_backup (comes with ServiceIT).
What i am trying to figure out is to try a find a way that i can run a dos prompt command to do the following tasks at a specific time of day:
Stop the Paradigm Server Daemons (service)
Run a DBCC CHECKDB on the database AHDTEST50.
If there are no errors, create a verbose backup using the dos command pdm_backup -v -fC:ackup[todaysdate]
If there are errors run a script to correct them, then run a backup
Restart the Paradigm Server Daemons
Challenge...
Aug 2, 2007
How can i programmatically via Tsql change the datatype of all the columns of a table to varchar(1000)???
Like I have a table employee
Employee
(
colA int
colB int
colC varchar
)
If i run the tsql..
it should give me
Employee
(
colA varchar
colB varchar
colC varchar
)
INNER JOIN Challenge
Jan 8, 2008Hello guy!
I wrote a stored procedure that searches for user on my table depending on the search option chosen by the person doing the search. But my challenge is displaying users without a picture. On my members table, i used -1 to indicate a user that doesn't have or set his/her picture, this makes it difficult for me to track that person when the person searching chooses to display members with or without pictures. What happen is that my inner join of the photos table dat reference the user primarypictureid does not exist in the photos table. How do i overcome this challenge. My script
CREATE PROCEDURE [dbo].[spSearchMember]
@username nvarchar(30),
@searchfor char(1),
@firstage int,
@secondage int,
@countryid varchar(50),
@withpicture nvarchar(5)
AS
IF(@withpicture = 'False')
SET @withpicture = '%[0-9]'
IF (@withpicture = 'True')
SET @withpicture = '%[0-9][^-]'
IF(@countryId = '-1')
SET @countryid = '%'
BEGIN
SELECT m.UserName, m.MemberId, m.Gender, m.PrimaryPictureId, DATEDIFF(year,m.BirthDate,GETDATE()) AS 'Age',
c.CountryName, p.PicFileName
FROM Members AS m
INNER JOIN Photos AS p
ON m.PrimaryPictureId = p.PictureId
INNER JOIN Countries AS c
ON m.CountryId = c.CountryId
WHERE (m.Gender = @searchfor) AND (DATEDIFF(year,m.BirthDate,GETDATE()) BETWEEN @firstage AND @secondage)
AND (m.CountryId LIKE @countryid) AND (p.PictureId LIKE @withpicture) AND (m.UserName <> @username)
ORDER BY m.BirthDate ASC
END
Thank!!!!!
Challenge To All DBAs
Aug 26, 2005I have a challenge for all DBAs. I am getting INCONSISTENT TIMINGS for Querry Results from a SQL Server 2000 Standard Edition on a HP Proliant 2 CPU Server with 4 GB RAM and SCSI DrivAFAe.
Our Database is 72 million records and have 8 columns. Most of them are indexed, which are used in the “Where…� clause. In addition to independent indexes, we also have a covering index for 3 most frequently searched fields.
My challenge is that out of the 7000 odd queries that hit the server with various search conditions in a Week, 5% of the queries return result in less than a minute.
The same query at a different Time or with a different Value, returns results inconsistently. For e.g. searching FirstName = “Anna�; LastName = “Williams� returns result in 0.01 sec. Searching for FirstName “Benjamin�; LastName = “Watson� returns in 5 minutes.
Any kind of help is welcome and will be highly appreciated.
- Santy
san.rely@gmail.comNote: Edited to fix white on white font.
Query Challenge
Jun 14, 2001I have a query that I am trying to optimize. It works on some 9000 records and runs too slow. What the query does is takes the multiple assignment of a single contact record to multiple attributes (a.k.a many-to-many). For example:
Membership table.
Contact_ID
1
2
3
Relate table
1 1
1 3
1 4
Relate Item
1 item1
2 item2
3 item3
4 item4
The query will take all ocurrences of the related items and place them in a single field while delimiting by comma "item1" , "item3", "item4"
Here is the query as it exists now:
select
CONTACT_ID,
UNION_NAME
into #tmp
from MEM_UN MU
inner join MEM_UN_REL MUR
on MU.UNION_ID = MUR.UNION_ID
order by CONTACT_ID, UNION_NAME
create TABLE #unionlist (
CONTACT_ID int primary key,
UNIONS varchar(2000) null)
insert into #unionlist (CONTACT_ID, UNIONS)
select distinct CONTACT_ID, UNIONS = '' from MEMBERSHIP
while exists(select CONTACT_ID from #tmp)
BEGIN
update #unionlist
set UNIONS = UNIONS + '"' + (
select min(UNION_NAME) from #tmp
where #unionlist.CONTACT_ID = #tmp.CONTACT_ID
) + '",'
where CONTACT_ID in (select CONTACT_ID from #tmp)
update #unionlist
set UNIONS = UNIONS + '"",'
where CONTACT_ID not in (select CONTACT_ID FROM #tmp)
delete FROM #tmp where UNION_NAME in (
select min(UNION_NAME) from #tmp tmp2
where #tmp.CONTACT_ID = tmp2.CONTACT_ID
)
END
I believe that the slow down is in the process of deleting from #tmp every time it loops through the recordset.
Any suggestions appreciated,
Thx,
Dave
Who Won The Oracle Challenge?
Mar 27, 1999On march 23, Micorosoft was supposed to benchmarch their SQL server 7.0 versus Oracle 8i. I didn't watch the benchmark... I figured that I would be able to read about it in the news. But it seems like there is some moritorium on the subject. I haven't found any result information whatsoever.
Does anybody know who won the challenge? Did microsoft win $1 million from oracle?
Tom Mack, MCSE
Database Administrator
Advancia Corporation
Interesting Challenge
Jul 12, 2005We're having to work with some legacy data. The tables in the so-called database seem to have way more nulls than actual data. One table appears to have around 100 or more columns in it. It has close to 40,000 rows.
This Db has pretty much 0 normalization present.
IOW, your worst nightmare.
Is there a way we could run a query that would return the total number cells inthat contain NULL and another that could return the total number of data-bearing cells so we could come up with a % or a ratio.
Query Challenge
Sep 7, 2004Hello Everybody,
I am attaching a picture of what the table should look like before and after the transformation. Can anybody help? Thank you in advance.
SQL Parsing Challenge!
Nov 9, 2004Hi guys!
Can anyone tell how I can parse the WHERE clause of an SQL statement to check for special characters such as ''' (single quotes) in fields of type varchar?
thanks
nelo
Optimization Challenge
Apr 7, 2006Well i wanted to prove to some guys that cursors are not really that important:shocked: .
:D So this code is suppose to remove duplicate tuples from a table without temporary tables or cursors:D. Except it needs some optimization(and alot of system down time, not sure about that:confused: ).
I would like it, if some one could find an instance of the table when the below code fails or some way to optimize the code or anything;) .
--trashtable for real data
create table abc
(col1 tinyint,
col2 tinyint,
col3 tinyint)
--trash values for trash table
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (3,2,1)
insert into abc values (2,2,3)
insert into abc values (3,2,4)
--check that there are ten rows
select * from abc
--check that there are only five distinct rows
select distinct * from abc
--run code : next 15 line as a batch
declare @lp tinyint
declare @col1 tinyint,@col2 tinyint,@col3 tinyint
set @lp=1
while @lp>0
begin
if not exists (select top 1 * from abc group by col1,col2,col3 having count(col1)>1)
set @lp=0
else
begin
select top 1 @col1 = col1,@col2 = col2,@col3 = col3 from abc group by col1,col2,col3 having count(col1)>1
delete from abc where col1=@col1 and col2=@col2 and col3=@col3
insert into abc values(@col1,@col2,@col3)
end
end
--only distinct values left in trash table
select * from abc
--think code can be optimized
--just wanted to prove: can be done without cursors or temporary tables
SELECT CHALLENGE?
Jun 16, 2008I have a database with three tables: Employees, Assets, and Recovery.
I have the following SELECT statement:
SELECT Employees.EmployeeID, SUM(Assets.Amount) AS [Case Value:], SUM(Recovery.Recovery) AS [Recovery:]
FROM Assets INNER JOIN
Employees ON Assets.EmployeeID = Employees.EmployeeID INNER JOIN
Recovery ON Employees.EmployeeID = Recovery.EmployeeID
GROUP BY Employees.EmployeeID
Here is the challenge:
You will always have data in the Employee table, but not neccesarily in the Assets or Recovery table.
If there are no Assets or Recovery I would still like the query to show 0.00 for the Assets or Recovery for each Employees.EmployeesID
With the above SELECT statement it will only return values that have data created in each of the three tables.
How do you modify the SELECT statement so a row is returned for every Employees.EmployeeID?
Any help will be appreciated!
losstww
Stumped By SQL Challenge
Sep 19, 2005Here is the table:CREATE TABLE [child]([pk_child_id] [int] NOT NULL ,[fk_parent_id] [int] NOT NULL ,[code] [char] (2)NOT NULL ,[dt] [datetime] NOT NULL ,[newcode] [int] NULL)There is a situation where there will be more than one record with thesame [fk_parent_id] value, but different values for the [code]field.If one of those records has a [code]= 5, but the [dt] is AFTER asimilar record where [code]= 6 or [code]= 7 (but same [fk_parent_id]value), I need to set [newcode] = 10. How can I pull this off? Again,the group of records can have different [code] values, different [dt]values, but a common [fk_parent_id].Help!
View 4 Replies View RelatedChallenge: Can You Optimize This?
Jun 6, 2006This code is attempting to find records that have a RegJrnID that doesnot occur more than one time in the table.The reason that I want to find records with non-duplicated RegJrnIDvalues is to create "reversal" records for these such that the reversalrecord has identical values for every column except the TaxableAmountwhich will contain a negative amount. (see: example data below)./* Set up */CREATE TABLE t1(RegJrnID INTEGER, InvoiceDate VARCHAR(8), InvoiceNumberVARCHAR(20), TaxableAmount DECIMAL(32,8))/* Example data */INSERT INTO t1 VALUES (1, '20060101', '2321323', 100.00)INSERT INTO t1 VALUES (9, '20060213', '2130009', 40.01)INSERT INTO t1 VALUES (3, '20060101', '9402293', 512.44)INSERT INTO t1 VALUES (1, '20060104', '2321323', -100.00)INSERT INTO t1 VALUES (4, '20060105', '9302221', 612.12)INSERT INTO t1 VALUES (5, '20060105', '0003235', 18.11)INSERT INTO t1 VALUES (6, '20060111', '5953432', 2101.21)INSERT INTO t1 VALUES (3, '20060111', '9402293', -512.44)INSERT INTO t1 VALUES (7, '20060115', '4234444', 44.52)INSERT INTO t1 VALUES (8, '20060115', '0342222', 95.21)INSERT INTO t1 VALUES (6, '20060119', '5953432', -2101.21)INSERT INTO t1 VALUES (2, '20060101', '5440033', 231.01)/* Show what's in the table - just because */SELECT * FROM t1 ORDER BY RegJrnID, InvoiceDate/* Query for records to reverse */SELECT *FROM t1 a/* Ignore records that have already been reversed */WHERE a.RegJrnID != ALL/* This subselect finds reversed records (i.e. those that have aduplicate RegJrnID) */(SELECT b.RegJrnIDFROM t1 bGROUP BY b.RegJrnIDHAVING COUNT(*) > 1)/* User selection criteria are appended here *//* AND InvoiceNumber >= '5000000' AND InvoiceNumber <= '7500000' *//* Make the results look pretty (optional) */ORDER BY RegJrnID/* Housekeeping */DROP TABLE t1
View 19 Replies View RelatedSQL Challenge Urgent, Please Help
Aug 9, 2006Hello Expert,Here I am asking your help.I have a table with following data:TaskID ParentTaskID TaskName ProjectName1 1 BA Rail2 22 FA Financial3 1 BA.1 Rail4 1 BA.2 Rail5 22 FA.1 Financial6 22 FA.2 FinancialNow I want the following format:ID ParentID Name1 1 Rail2 1 BA3 2 BA.14 2 BA.25 22 Financial6 5 FA.17 5 FA.2I need to create following hierarchy if I could tranform the data theabove way in the Project Dimension:Rail--BA----BA.1----BA.2Financial--FA----FA.1----FA.2Please help and thanks in advance,Soumya
View 2 Replies View RelatedAllow Null Value Challenge
Apr 15, 2008Hi,
I have created a report with the following input parameters
FromDate (mandatory)
ToDate (mandatory)
Installation (mandatory, Multi Value)
Code (mandatory, Multi Value)
SearchCriteria1 (Allow Null Value)
SearchCriteria2 (Allow Null Value)
The Search criteria input parameters allow the user to search for occurences of keywords in the comments field.
The query looks like this:
SELECT installation, day_, code, from_time, to_time, commentFROM LogWHERE code IN (@Code) AND day_ TO BETWEEN @FromDate AND @ToDate AND installation IN (@Installation) AND UPPER(comment) LIKE ('%'+UPPER(@SearchCriteria1)+'%') OR UPPER(comment) LIKE ('%'+UPPER(@SearchCriteria2)+'%')
The problem is it does not work when both the Search criterias are null. I have searched the web for solutions, but not found it. I came across the use of ISNULL(@parameter, field), but in conjunction with = and not LIKE. Tried to alter the last two lines of my query to
UPPER(comment) LIKE ('%'+UPPER(ISNULL(@SearchCriteria1,comment))+'%') OR UPPER(comment) LIKE ('%'+UPPER(ISNULL(@SearchCriteria2,comment))+'%')
and now it seems to run forever until it fails (approx. 20 minutes). If I specify a keyword in one or both of the search criterias it returns the result set within 30 seconds.
Would appreciate som help here.
Interesting Challenge..
Aug 2, 2007I am making a report in Visual Studio..Now my question is,is there anyway that i can add a tab control for my rdl??Hope to here from anyone out there with a know how..Thanks guys!!
View 13 Replies View RelatedTSQL Challenge
Nov 11, 2007Im calling all the TSQL gurus to help me with this problem.
Tables:
Contracts (ContractID,Lastname, Firstname, ContractValue, Date Created
Amendments (AmmendmentID, ContractID, Lastname, Firstname, ContractValue, EffectivityDate, DateCreated)
Amendments table is related to contracts thru ContractID.
Changes to the contract table is made by creating a record to the ammendments table. when this
amendment becomes effective it should take effect on the contract. When correcting the contractvalue
the user must input the amount to be added or subtracted.
For Example:
Current Contract: C1, PETRELLI, PITER, 150.00, created on Jan. 01, 2007
Ammendment: A1, C1, PETRELLI, PETER, 20.00, effective on Jan. 20, 2007, created on Jan. 10, 2007
Ammendment: A2, C1, PETRELLI, PETER, 15.00, effective on Mar. 01, 2007, Created on Feb. 01, 2007
The currect Contract:
As of Feb.01, 2007: C1, PETRELLI, PETER, 170.00
As of Mar.05, 2007: C1, PETRELLI, PETER, 185.00
The Challenge here is this:
How can you make an SQL VIEW that will show the list of Current Contractsand use this view to create a stored procedure (with a parameter @CurrentDate)?
The SP will show the current Contracts based on the supplied parameter.
Example:
ShowCurrentContracts(Jan. 05, 2007)
Current Contract: C1, PETRELLI, PITER, 150.00
ShowCurrentContracts(Feb. 05, 2007)
Current Contract: C1, PETRELLI, PETER, 170.00
ShowCurrentContracts(Jun. 05, 2007)
Current Contract: C1, PETRELLI, PETER, 185.00
REAL CHALLENGE For The Smart One
May 8, 2002Hi,
tell me please how I can trace the modification on the table such as "insert" record into one and syncronize mirror table at the same time once the insert has happend, BUT - no indexes no trace jobs, no any modification or objects on the master table... ha?