Select Condition From Log Table

An Easy Way To Reference The Nth Row From A Table Meeting A Condition X, And The Mth Row From The Same Table Meeting Condition Y
Hi

I am developing a scientific application (demographic forecasting) and have a situation where I need to update a variety of rows, say the ith, jth and kth row that meets a particular condition, say, x.  

I also need to adjust rows, say mth and nth that meet condition , say y.

My current solution is laborious and has to be coded for each condition and has been set up below (If you select this entire piece of code it will create 2 databases, each with a table initialised to change the 2nd,4th,8th and 16th rows, with the first database ignoring the condition and with the second applying the change only to rows with 'type1=1' as the condition.)

This is an adequate solution, but if I want to change the second row meeting a second condition, say 'type1=2', I would need to have another WITH...SELECT...INNER JOIN...UPDATE and I'm sure this would be inefficient.

Would there possibly be a way to introduce a rank by type into the table, something like this added column which increments for each type:







ID
Int1
Type1
Ideal Rank by Type

1
1
1
1

2
1
1
2

3
2
1
3

4
3
1
4

5
5
1
5

6
8
2
1

7
13
1
6

8
21
1
7

9
34
1
8

10
55
2
2

11
89
1
9

12
144
1
10

13
233
1
11

14
377
1
12

15
610
1
13

16
987
2
3

17
1597
1
14

18
2584
1
15

19
4181
1
16

20
6765
1
17

The solution would then be a simple update based on an innerjoin reflecting the condition and rank by type...

I hope this posting is clear, albeit long.

Thanks in advance

Greg

PS The code:

USE

master

GO

CREATE DATABASE CertainRowsToChange

GO

USE CertainRowsToChange

GO

CREATE TABLE InitialisedValues

(

InitialisedValuesID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL

)

GO

CREATE PROCEDURE Initialise

AS

BEGIN

INSERT INTO InitialisedValues (Int1 )

SELECT 2

INSERT INTO InitialisedValues (Int1 )

SELECT 4

INSERT INTO InitialisedValues (Int1 )

SELECT 8

INSERT INTO InitialisedValues (Int1 )

SELECT 16

END

GO

EXEC Initialise

/*=======================================================*/

CREATE TABLE AllRows

(

AllRowsID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL

)

GO

CREATE TABLE RowsToChange

(

RowsToChangeID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL

)

GO

CREATE PROCEDURE InitialiseRowsToChange

AS

BEGIN

INSERT INTO RowsToChange (Int1 )

SELECT 2

INSERT INTO RowsToChange (Int1 )

SELECT 4

INSERT INTO RowsToChange (Int1 )

SELECT 8

INSERT INTO RowsToChange (Int1 )

SELECT 16

END

GO

EXEC InitialiseRowsToChange

GO

CREATE PROCEDURE PopulateAllRows

AS

BEGIN

INSERT INTO AllRows (Int1 )

SELECT 1

INSERT INTO AllRows (Int1 )

SELECT 1

INSERT INTO AllRows (Int1 )

SELECT 2

INSERT INTO AllRows (Int1 )

SELECT 3

INSERT INTO AllRows (Int1 )

SELECT 5

INSERT INTO AllRows (Int1 )

SELECT 8

INSERT INTO AllRows (Int1 )

SELECT 13

INSERT INTO AllRows (Int1 )

SELECT 21

INSERT INTO AllRows (Int1 )

SELECT 34

INSERT INTO AllRows (Int1 )

SELECT 55

INSERT INTO AllRows (Int1 )

SELECT 89

INSERT INTO AllRows (Int1 )

SELECT 144

INSERT INTO AllRows (Int1 )

SELECT 233

INSERT INTO AllRows (Int1 )

SELECT 377

INSERT INTO AllRows (Int1 )

SELECT 610

INSERT INTO AllRows (Int1 )

SELECT 987

INSERT INTO AllRows (Int1 )

SELECT 1597

INSERT INTO AllRows (Int1 )

SELECT 2584

INSERT INTO AllRows (Int1 )

SELECT 4181

INSERT INTO AllRows (Int1 )

SELECT 6765

END

GO

EXEC PopulateAllRows

GO

SELECT * FROM AllRows

GO

WITH Temp(OrigID)

AS

(

SELECT OrigID FROM

(SELECT Row_Number() OVER (ORDER BY AllRowsID Asc ) AS RowScore, AllRowsID AS OrigID, Int1 AS OrigValue FROM Allrows) AS FromTable

INNER JOIN

RowsToChange AS ToTable

ON FromTable.RowScore = ToTable.Int1

)

UPDATE AllRows

SET Int1=1000

FROM

Temp as InTable

JOIN Allrows as OutTable

ON Intable.OrigID = OutTable.AllRowsID

GO

SELECT * FROM AllRows

GO

USE

master

GO

CREATE DATABASE ComplexCertainRowsToChange

GO

USE ComplexCertainRowsToChange

GO

CREATE TABLE InitialisedValues

(

InitialisedValuesID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL

)

GO

CREATE PROCEDURE Initialise

AS

BEGIN

INSERT INTO InitialisedValues (Int1 )

SELECT 2

INSERT INTO InitialisedValues (Int1 )

SELECT 4

INSERT INTO InitialisedValues (Int1 )

SELECT 8

INSERT INTO InitialisedValues (Int1 )

SELECT 16

END

GO

EXEC Initialise

/*=======================================================*/

CREATE TABLE AllRows

(

AllRowsID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL,

Type1 int NOT NULL

)

GO

CREATE TABLE RowsToChange

(

RowsToChangeID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL,

Type1 int NOT NULL

)

GO

CREATE PROCEDURE InitialiseRowsToChange

AS

BEGIN

INSERT INTO RowsToChange (Int1,Type1 )

SELECT 2, 1

INSERT INTO RowsToChange (Int1,Type1 )

SELECT 4, 1

INSERT INTO RowsToChange (Int1,Type1 )

SELECT 8, 1

INSERT INTO RowsToChange (Int1,Type1 )

SELECT 16, 1

END

GO

EXEC InitialiseRowsToChange

GO

CREATE PROCEDURE PopulateAllRows

AS

BEGIN

INSERT INTO AllRows (Int1, Type1 )

SELECT 1, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 1, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 2, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 3, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 5, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 8, 2

INSERT INTO AllRows (Int1, Type1 )

SELECT 13, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 21, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 34, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 55, 2

INSERT INTO AllRows (Int1, Type1 )

SELECT 89, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 144, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 233, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 377, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 610, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 987, 2

INSERT INTO AllRows (Int1, Type1 )

SELECT 1597, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 2584, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 4181, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 6765, 1

END

GO

EXEC PopulateAllRows

GO

SELECT * FROM AllRows

GO

WITH Temp(OrigID)

AS

(

SELECT OrigID FROM

(SELECT Row_Number() OVER (ORDER BY AllRowsID Asc ) AS RowScore, AllRowsID AS OrigID, Int1 AS OrigValue FROM Allrows WHERE Type1=1) AS FromTable

INNER JOIN

RowsToChange AS ToTable

ON FromTable.RowScore = ToTable.Int1

)

UPDATE AllRows

SET Int1=1000

FROM

Temp as InTable

JOIN Allrows as OutTable

ON Intable.OrigID = OutTable.AllRowsID

GO

SELECT * FROM AllRows

GO

View Replies !

---

SELECT UNION SELECT Condition
let say i got such condition

INSERT INTO TABLE
SELECT
WHERE XX NOT EXISTS (SELECT 1 FROM TABLE)
UNION
SELECT
WHERE XX NOT EXISTS (SELECT 1 FROM TABLE)

do you think that mssql will produce error or problem?
from what i heard it will.

View Replies !

---

If Condition In Select Statement...
Hi,
I need to write an if condition in SELECT statement. Below is the code for the same. But its throwing error. Can some refine the code below.




SELECT tblCustomer.Customer_LegalName,
(IF (tblCustomer.IsNRACustomer = TRUE) SELECT tblCustomer.Customer_PassportNo ELSE
ISNULL(tblCustomer.Customer_TaxId, tblCustomer.Customer_PassportNo)) AS TAXID,

tblCustomer.Customer_DoingBusinessAs, tblSeed_EDDCategory.CategoryName, '2' AS DCS, tblUser_OfficerCode.User_OfficerCode,
tblCustomer.Customer_AreaId, tblCustomer.Customer_BranchId, CONVERT(VARCHAR(11), tblCustomer_EDDCategory.CreateDate)
AS CreateDate, tblSeed_EDDCategory.EDDCategoryId, tblCustomer_EDDCategory.Category_CreateEmpId,
tblCustomer_EDDCategory.CustCatId, tblCustomer.Customer_Id, tblSeed_Area.AreaName, tblSeed_Employee.Name,
tblUser_OfficerCode.User_OfficerName, tblCustomer.Customer_TaxId, tblCustomer.IsNRACustomer,
tblCustomer.Customer_PassportNo
FROMtblCustomer INNER JOIN
blCustomer_EDDCategory ON tblCustomer.Customer_Id = tblCustomer_EDDCategory.CustomerId INNER JOIN
tblSeed_EDDCategory ON tblCustomer_EDDCategory.EDDCategoryId = tblSeed_EDDCategory.EDDCategoryId INNER JOIN
tblSeed_Employee ON tblCustomer.Customer_CreateEmpId = tblSeed_Employee.EmployeeId INNER JOIN
tblUser_OfficerCode ON tblCustomer.Customer_CreateEmpId = tblUser_OfficerCode.EmployeeId INNER JOIN
tblSeed_Area ON tblCustomer.Customer_AreaId = tblSeed_Area.AreaId


Thanks,
Rahul Jha

View Replies !

---

'IF' Condition On Select Statement?
Can you implement the same type of feature in SQL as MS Access uses with it's "immediate if"
or iif() function? In other words can you look at a specific row and change the contents
of one field based on the contents of another field in the same row?

I am trying to create a view like the the following:

CREATE VIEW AS Test
SELECT
name,
city ,
IF city = 'NY' state ELSE country
FROM address_table

The view's 3rd field will be either [state] or [country] depending on the contents of [city]

Thanks in advance!

..Marisa

View Replies !

---

Need Help On Select Statement Wit Like Condition
I tried following code and it return no data after query run successfully. I m testing it on msaccess . 
 
SELECT Book.ID, Book.Title, Book.Arthor, Book.Publisher, Book.Year, Book.Price, Inventory.B_ID, Inventory.Quantity
FROM Book INNER JOIN Inventory ON Book.ID=Inventory.B_ID
WHERE Book.ID=Inventory.B_ID And ("Book.Arthor" Like '%es%');


-------

I have test and test2 in Arthor column ... Thanks all !!
 

View Replies !

---

Condition In Select Statement
col1          col2 col3   col4

36930.60   145    N   . 00
17618.43   190   N    . 00
6259.20    115    N    .00
8175.45     19    N     .00
18022.54   212   N    .00
111.07      212   B     100
13393.05   67   N     .00


In above 4 col

if col3 value is B then cursor has to fectch appropriate value from col4.
if col3 value is N then cursor has to fectch appropriate value from col1.
and also wants the sum of result above conditions when column 3 has two values(i.e N,B) for single col2 value
 
here col2 values are unique
 
take an example for col2=212
if col3=B
  then result is 100
if col3=N
then result is 18022.54
for  a single col 2 value ,if col3= N and B then we want sum of above 2 conditions i.e (100+18022.54)
but not the sum of (100+18022.54+111.07+0.0) .

 
Can any one reply for this..............
 

View Replies !

---

WHERE Condition In SELECT Statement
 

Hi All,
I want get the result in a single SELECT statement
 
 SELECT
 SUM (PAID_LOSS),
SUM (PAID_EXP)
GROUP BY COMPANY
FROM VIEW_POLICY
WHERE  Accounting_Period  GE  200701  and LE 200712
-************************************
SELECT
 SUM (MEDICAL_RESERVE)
GROUP BY COMPANY
FROM VIEW_POLICY
WHERE  Accounting_Period  LE 200712
 
 

View Replies !

---

Condition In Select Query
create function stzipcustinfo

( @campaign varchar(50), @startdate datetime, @enddate datetime,@audience_type char(10) )


RETURNS TABLE

AS

RETURN


(Select distinct t.campaign,t.Sopnumbe,t.Custnmbr,t.Custname,

t.Address1,t.Address2,t.City,t.State,t.Country,t.Zipcode, t.Phone,sum(t.totalquantity) as Totalquantity,

t.Audience_type

from

(

select distinct

v.sopnumbe, v.campaign, v.custnmbr, v.custname, v.address1, v.address2,v.city,v.state,v.country,v.zipcode,

v.Phone, v.totalquantity as totalquantity,


case @audience_type

when v.custclas then v.custclas

end as audience_type
from vwstzipcustaudienceinfo v

Where (v.itemnmbr = '' or v.itemnmbr=v.itemnmbr) and v.Campaign = @Campaign and (v.docdate between @startdate and @enddate)


) as t

where t.audience_type is not null

group by t.campaign,t.Sopnumbe,t.Custnmbr,t.Custname,

t.Address1,t.Address2,t.City,t.State,t.Country,t.Zipcode, t.Phone, t.Audience_type

)

--select * from mystatezipcustaudienceinfo('cpd','1/1/2008','5/15/2008','INDPATIENT') --getting 500rows

--select * from mystatezipcustaudienceinfo('Cpd'','1/1/2008','5/15/2008','INST-HOSPITAL') -- note getting single row

problem have a “-“ in them..i m not getting result..so what condition should be in red color one..


thanks

View Replies !

---

T-SQL: Different SELECT Executed Depending On Condition.
I'm trying to execute a different SELECT statement depdning on a certain condition (my codes below). However, Query Analyzer complains that 'Table #Endresult already exists in the database', even though only one of those statements would be executed depending on the condition. Any ideas as to a work around? I need the result in an end temporary table.


IF @ShiftPeriod = 'Day'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkDays = 1
ELSE IF @ShiftPeriod = 'Night'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkNights = 1
ELSE IF @ShiftPeriod = 'Evenings'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkEvenings = 1
ELSE
SELECT * INTO #EndResult FROM #NursesAvailable

View Replies !

---

How To Select Train Data With Condition
Hi, in SQL Management Studio, we just click 'Process' to train the data, how could I conditionally select a subset of Data Source (View) to train?

Thanks in advance.
Ricky.

View Replies !

---

If Condition Within Select Query Sql Server 2000
Hi all,
 
I have to write a select query which need some logic to be implemented.
Query is like
select name,number,active,
if active ="true" then
select @days=days from tbl_shdsheet
else
@days=''
end
from tbl_emp
 In the above query there will be days row for that employee if active is true else there won't be any data for that emp in the tbl_shdsheet
So how can i write queery for this.

View Replies !

---

How To Put Condition In Select Statement To Write A Cursor
col1          col2 col3   col4
36930.60   145    N   . 00
17618.43   190   N    . 00
6259.20    115    N    .00
8175.45     19    N     .00
18022.54   212   N    .00
111.07      212   B     .00
13393.05   67   N     .00
In above 4 col
if col3 value is B then cursor has to fectch appropriate value from col4.
if col3 value is N then cursor has to fectch appropriate value from col1.
here col2 values are unique.

Can any one reply for this..............

View Replies !

---

HELP With SQL Query: Select Multiple Values From One Column Based On &&<= Condition.
Hello all.  I hope someone can offer me some help.  I'm trying to construct a SQL statement that will be run on a Dataset that I have.  The trick is that there are many conditions that can apply.  I'll describe my situation:
 
I have about 1700 records in a datatable titled "AISC_Shapes_Table" with 49 columns.  What I would like to do is allow the user of my VB application to 'create' a custom query (i.e. advanced search).  For now, I'll just discuss two columns;  The Section Label titled "AISC_MANUAL_LABEL" and the Weight column "W".  The data appears in the following manner:
 
(AISC_Shapes_Table)
 
AISC_MANUAL_LABEL          W
W44x300                               300
W42x200                               200
(and so on)
WT22x150                             150
WT21x100                             100

(and so on)
MT12.5x12.4                          12.4
MT12x10                               10
(etc.)
 
I have a listbox which users can select MULTIPLE "Manual Labels" or shapes.  They then select a property (W for weight, in this case) and a limitation (greater than a value, less than a value, or between two values).  From all this, I create a custom Query string or filter to apply to my BindingSource.Filter method.  However I have to use the % wildcard to deal with exceptions.  If the user only wants W shapes, I use "...LIKE 'W%'" and "...NOT LIKE 'WT%" to be sure to select ONLY W shapes and no WT's.  The problems arises, however, when the user wants multiple shapes in general.  If I want to select all the "AISC_MANUAL_LABEL" values with W  <= 40, I can't do it.  An example of a statement I tried to use to select WT% Labels and MT% labels with weight (W)<=100 is:
 



Code SnippetSELECT     AISC_MANUAL_LABEL, W
FROM         AISC_Shape_Table
WHERE     (W <= 100) AND ((AISC_MANUAL_LABEL LIKE 'MT%') AND (AISC_MANUAL_LABEL LIKE 'WT%'))
 
 

 It returns a NULL value to me, which i know is NOT because no such values exist.  So, I further investigated and tried to use a subquery seeing if IN, ANY, or ALL would work, but to no avail.  Can anyone offer up any suggestions?  I know that if I can get an example of ONE of them to work, then I'll easily be able to apply it to all of my cases.  Otherwise, am I just going about this the hard way or is it even possible?  Please, ANY suggestions will help.  Thank you in advance.
 
Regards,
 
Steve G.
 
 
 
 

View Replies !

---

One Table, Two Condition, Display Result As One Table
I got one table with 3 columns = Column1, Column2, Column3

Sample Table

Column1 | Column2 | Column3
------------------------------------
A | 12 | 0
A | 13 | 2
B | 12 | 5
C | 5 | 0

Select Column1, Column2, Column3 as New1
Where Column1 = A AND Column2 = 12 AND Column3 = 0

Select Column1, Column2, Column3 as New2
Where Column1 = A AND Column2 = 12 AND Column3 >0

The only difference is one condition Column3 = 0 and another one Column3 > 0. This two condition is not an "AND" condition... but just two separate information need to be display in one table.
So how do i display the result in one table where the new Output will be in this manner

Column1 | Column2 | New1 | New2|

Thanks

View Replies !

---

Create Table On Condition
I import CSV file into MSSQL database table twice a day using DTS. There is a filed name clients, which contains clients name.

I import all data from CSV file to temp table, from temp table it goes to every single clients table. Client A, B and C has separate tables in database as tableA, tableB and tableC.

Problem is that when there is a new client in the CSV file I need to create a table for new client in database manually. I am in need of a script which check for new client entry in CSV file and compare it in clients table. If incoming client is not present in client's table locally then then script should create a new table for new incoming client, automatically.

Thanx. Waiting for someones response !

View Replies !

---

Returns TABLE Based On A Condition
HI all,In SQL Server, i have a function which will return a table. likecreate function fn_test (@t int) returns table asreturn (select * from table)now i want the function to retun the table based on some condition like belowcreate function fn_test(@t int) returns table asif @t = 1 return (select * from table1)else return (select * from table2)It is not working for me. Please give me your suggesstions. It's very urgent.Thank you in advance....

View Replies !

---

How Do I Use OR Condition In Table Filter Expression
 

Hai guys,
 
In my report I want to use OR condition instead of AND in Table Filter Expression. By default this option is disabled in VS2005 Pro. How do I enable this? or is there any other way to do this? Thanks in advance.

View Replies !

---

Update Table With Multipe Condition
 

hi, I want to ask how I want update my table using a multiple condition.My table format is like this:
 





IC_NO
F05
F07
F08
F09
Ind

 
123
 
 
452
C

 
654
852
 
 
P

 
 
 
125
 
A

 
 
 
 
526
C

 
 
What I want to do is:
 
IF Ind = C, F05 <> NULL  then IC_NO = F05
IF IND = C, F05 = Null and F09 <> Null then IC_NO = F09
IF IND = P, F05 <> Null and F07 <> Null then IC_NO = F07
IF Ind = A, F05 = Null and F08 <> Null then IC_NO = F08
 
your helpful highly appreciated..thanks

View Replies !

---

Update All The Records Of A Table On A Condition
Hi
I have a two tables as follows
 
Table Category
{

ID PK,
LastUpdate DateTime
}
 
Table Master
{

ID  PK
Catrgory DateTime
}
 
I wanted to update Catrgory coulmn of all records in the Master table with the Value of LastUpdate of the CategoryTable the where the ID of the both the table are same
 
Can any one please let me know the query
 
~Mohan
 

View Replies !

---

Can I Print The Results Of A Condition Based On The Condition?
For example..

select * from mytable where MyNum = 7

If this brings back more than 1 row, I want to display a message that says,

Print 'There is more than one row returned'

Else (If only 1 row returned), I don't want to print anything.

Can I do this? Thx!

View Replies !

---

COndition Spli - Error Date Condition
Dear friends,

I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...

 

I need to filter rows in a dataflow...

I created a condition spli to that... maybe there is a better solution...

And the condition is: Datex != NULL(DT_DATE)

(Some DATE != NULL)

 

 

[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row).  The expression results must be Boolean for a Conditional Split.  A NULL expression result is an error.

 

What is wrong??

Regards,

Pedro

View Replies !

---

UPDATE From A Table To Another Table On A Condition
I have four tables:  Contact, Contact_Detail, Contact_Information and Contact_Main.  I am trying to update a table called Contact_Detail with information from the other three tables. 
 
tbl=Contact
pk=ContactId
Contact_Name
 
tbl=Contact_Information
pk=Information_ID
Information
 
tbl=Contact_Detail
detail_ID
Contact_ID
Information_ID
detail
 
 
tbl=Contact_Main (no pk, table is temporary)
Contact_Name
Airline
Focal
Title
MailAddress1
MailAddress2
ShippingAddress1
ShippingAddress2
Country
Email
Phone
Fax
Notes
 
Here is my pseudocode for transact SQL.  I have completed QUERY1 and QUERY2 but not sure how to implement UPDATE 1.  Any assistance is appreciated.
 
Select Contact_Main.Contact_Person = Contact.Contact_Name (QUERY 1)
Select Contact_Main.Focal = True (QUERY 2 from QUERY 1)
Begin Create a Contact_Detail record (UPDATE 1 from QUERY 2)
 Contact.ContactID -> Contact_Detail.ConctactID
 For X = 1 to 12 Update Contact_Detail record with
         Increment PK -> Detail_ID
         X -> Information_ID
    Begin Case  
          Airline=1 
          Focal=2
          Title=3
          MailAddress1=4
          MailAddress2=5
          ShippingAddress1=6
          ShippingAddress2=7
          Country=8
          Email=9
          Phone=10
          Fax=11
          Notes=12
    End Case
 Next X
End Begin

View Replies !

---

Insert Into Temp Table Based On If Condition
hello all,this might be simple:I populate a temp table based on a condition from another table:select @condition = condition from table1 where id=1 [this will giveme either 0 or 1]in my stored procedure I want to do this:if @condition = 0beginselect * into #tmp_tablefrom products pinner joinsales s on p.p_data = s.p_dataendelsebeginselect * into #tmp_tablefrom products pleft joinsales s on p.p_data = s.p_dataendTha above query would not work since SQL thinks I am trying to use thesame temp table twice.As you can see the major thing that gets effected with the condictionbeing 0/1 is the join (inner or outer). The actual SQL is much biggerwith other joins but the only thing changing in the 2 sql's is the joinbetween products and sales tables.any ideas gurus on how to use different sql's into temp table based onthe condition?thanksadi

View Replies !

---

I Need To Find The Rows That Exist In One Table But Not In The Other With Condition
I need to find the rows that exist in one table but not in the otherwith this condition:(prod_name exist in table1 and not in table2.prod_name ) AND(prod_name exist in table1 and not in table2.'S'+prod_name )explanation:i want to know if the product not exit and if the combination of thecharachter "S" with the product Name also not exist at the othertableB.Ryuvi

View Replies !

---

Replace Characters On Condition In Table And Cell
need help
condition 1
replace characters on condition in table and cells
but only if
how to do it - if i put * (asterisk)  like in employee 111 in day1 - the the upper characters the (A  S  B)
i replace characters  with '-'
and it must  work dynamically
condition 2
replace characters on condition in table and cells
but only if
if i put number or 1 , 2 , 3 , 4 above any cell for example( employee id=222 name =bbbb day1)
i replace characters  with '0' and '#'
and it must  work dynamically
table before the replace




id
fname

val

day1
day11
day111
day2
day22
day222
day3
day33
day333
day4
day44
day444
day5
day55
day555

111
aaaa

2
 A
S
B
 e
 t
 y
 R
 Y
M
 
 
 
 j
 o
 p

111
aaaa

1
 *
 
 
*
 
 
 *
 
 
 
 
 
 *
 
 

222
bbbb

2
 1
 
 
 
 1
 
 
 
 
 
 
 
 
 
 

222
bbbb

1
 A
 -
 -
-
 B
 -
 
 
 
 
 
 
 
 
 

333
cccc

2
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

333
cccc

1
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

444
dddd

2
 
 
 
 
 
 
 
 
3
 
 
 
 
 
 4

444
dddd

1
 
 
 
 
 
 
 -
 -
 C
 
 
 
 
 
 C

555
EEE

2
A
 G
 C
 
 
 
 
 
 
 
 
 
 
 
 

555
EEE

1
 *
 
 
 
 
 
 
 
 
 
 
 
 
 
 

 
 


 














 
table after the replace




id
fname

val

day1
day11
day111
day2
day22
day222
day3
day33
day333
day4
day44
day444
day5
day55
day555

111
aaaa

2
 -
-
-
-
-
 -
 -
 -
-
 
 
 
-
 -
 -

111
aaaa

1
 null
 
 
null
 
 
 null
 
 
 
 
 
 null
 
 

222
bbbb

2
0
 
 
 
 0
 
 
 
 
 
 
 
 
 
 

222
bbbb

1
 #
 -
 -
-
 #
 -
 
 
 
 
 
 
 
 
 

333
cccc

2
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

333
cccc

1
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

444
dddd

2
 
 
 
 
 
 
 
 
0
 
 
 
 
 
0

444
dddd

1
 
 
 
 
 
 
 -
 -
 #
 
 
 
 
 
 #

555
EEE

2
-
 -
 -
 
 
 
 
 
 
 
 
 
 
 
 

555
EEE

1
null
 
 
 
 
 
 
 
 
 
 
 
 
 
 

 
 


 











tnx for the help

View Replies !

---

How To Make Table Row Invisible Based On Certain Condition
 

HI
 
I have the following scenario in my report.

 
-The data is displayed in a table
-The table groups by one field
-Each table row calls a subreport
-There is about 6 paramaters in the report
-The last paramater of the list of paramters is a multivalue paramater and based on what is selected in the  list the corresponding subreport must be shown.
-So i use a custom vbscript funtion to determine if a specific  value was selected or not.
 This functionality is working fine.
 
My problem is if the user does not select all the values in the multi select then i want to make the row invisble
and remove the whitespace so that there is not a gap between the other subreports which is shown.
 
I can make the subreport invisible inside the row but there is still the white space which does not display very nicly.
 
How can i make the row invisible if the vbscript function that is called returns a false value?
 
Here is the funtion I call -> Code.InArray("ValueToSearchFor", Parameters!MultiValueDropDown.Value)
 
The Function returns a true or false.
 
Thanks. 


 
 
 

View Replies !

---

Replace Characters On Condition In Table And Cells
need help

replace characters on condition in table and cells

but only if

if i put number or 1 , 2 , 3 , 4 above the cell of the eployee for example( employee id=222 name =bbbb day1)

i replace characters with '0' and '#'

and it must work dynamically AND replace ONLY THIS characters


table before the replace
id fname val day1 day11 day111 day2 day22 day222
------------------------------------------------------
111 aaaa 2 1 3
111 aaaa 1 A C
222 bbbb 2
222 bbbb 1
333 cccc 2
333 cccc 1
444 dddd 2
444 dddd 1
555 eeee 2 2
555 eeee 1 B

table after the replace
id fname val day1 day11 day111 day2 day22 day222
------------------------------------------------------
111 aaaa 2 0 0
111 aaaa 1 # #
222 bbbb 2
222 bbbb 1
333 cccc 2
333 cccc 1
444 dddd 2
444 dddd 1
555 eeee 2 0
555 eeee 1 #

tnx FOR THE HELP

View Replies !

---

Adding A Column Name To A Table In Each Of The Databases Based On A Condition
i have the folowing databases DB1,DB2,DB3,D4,DB5........

i have to loop through each of the databases and find out if the database has a table with the name 'Documents'( like 'tbdocuments' or 'tbemplyeedocuments' and so on......)

If the tablename having the word 'Documents' is found in that database i have to add a column named 'IsValid varchar(100)' against that table in  that database and there can be more than 1 'Documents' table in a database.
 

can someone show me the script to do it?
 

Thanks.
 

View Replies !

---

Declaring A Table Variable Within A Select Table Joined To Other Select Tables In Query
Hello,
 
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
 
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
 
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty  meaningless that just mean I've really thrown something off.
 
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
 
Thanks in advance,
Andy

 

Select * from

(

declare @years table (years int);

insert into @years

select

CASE

WHEN month(getdate()) in (1) THEN year(getdate())-1

WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())

END

select

u.fullname

, sum(tx.Dm_Time) LastMonthBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-1

and

year(dm_date) = (select years from @years)

and tx.dm_billable = 1

group by u.fullname

) as A

left outer join

(select

u.FullName

, sum(tx.Dm_Time) Billhours

, ((sum(tx.Dm_Time))

/

((day(getdate()) * ((5.0)/(7.0))) * 8)) perc

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

tx.Dm_Billable = '1'

and

month(tx.Dm_Date) = month(GetDate())

and

year(tx.Dm_Date) = year(GetDate())

group by u.fullname) as B

on

A.Fullname = B.Fullname

Left Outer Join

(

select

u.fullname

, sum(tx.Dm_Time) TwomosagoBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-2

group by u.fullname

) as C

on

A.Fullname = C.Fullname

View Replies !

---

Trying To Create A Proc That Will Insert Values Based On A Condition That Is Another Table
Can someone give me a clue on this. I'm trying to insert values based off of values in another table.

I'm comparing wether two id's (non keys in the db) are the same in two fields (that is the where statement. Based on that I'm inserting into the Results table in the PledgeLastYr collumn a 'Y' (thats what I want to do -- to indicate that they have pledged over the last year).

Two questions

1. As this is set up right now I'm getting NULL values inserted into the PledgeLastYr collumn. I'm sure this is a stupid syntax problem that i'm overlooking but if someone can give me a hint that would be great.

2. How would I go about writing an If / Else statement in T-SQL so that I can have the Insert statement for both the Yes they have pledged and No they have not pledged all in one stored proc. I'm not to familar with the syntax of writing conditional statements within T-SQL as of yet, and if someone can give me some hints on how to do that it would be greatly appriciated.


Thanks in advance, bellow is the code that I have so far:

RB



Select Results.custID, Results.PledgeLastYr
From Results, PledgeInLastYear
Where Results.custID = PledgeInLastYear.constIDPledgeInLastYear
Insert Into Results(PledgeLastYr)
Values ('Y')

View Replies !

---

SELECT Query - Different Columns/Number Of Columns In Condition
I am working on a Statistical Reporting system where:


Data Repository: SQL Server 2005
Business Logic Tier: Views, User Defined Functions, Stored Procedures
Data Access Tier: Stored Procedures
Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by


different organizational hierarchies
different number of layers within a hierarchy
select a organization or select All of the organizations with the organizational hierarchy
combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies:
Hierarchy 1


Country -> Work Group -> Project Team (Project Team within Work Group within Country) 
Hierarchy 2


Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:


Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams
Country = "USA", Work Group = all work groups

Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance"
Client = "Client A", Contract = "2007-2008 Maint", Project = all
Client = "Client A", Contract = allI am totally stuck on:


How to implement the data interface (Stored Procs) to the Reports
Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all")
(WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)

Any Ideas?  Should I be doing this in SQL Statements or should I be looking to use Analysis Services.

Thanks for all your help!

View Replies !

---

Update Statement Performing Table Lock Even Though Where Condition On Clustered Primary Index?
Hi All,I have a database that is serving a web site with reasonably hightraffiic.We're getting errors at certain points where processes are beinglocked. In particular, one of our people has suggested that an updatestatement contained within a stored procedure that uses a wherecondition that only touches on a column that has a clustered primaryindex on it will still cause a table lock.So, for example:UPDATE ORDERS SETprod = @product,val = @valWHERE ordid = @ordidIn this case ordid has a clustered primary index on it.Can anyone tell me if this would be the case, and if there's a way ofensuring that we are only doing a row lock on the record specified inthe where condition?Many, many thanks in advance!Much warmth,Murray

View Replies !

---

Parent/Child Rows In Report, Nested Table, Textbox Value In Filter Condition
Hi All,
 
I am working on SQL server 2005 Reports.
I have one report, one dataset is assigned to it, and one table which displays it.
Now I come accros requirement that, the column value in the filter condition for the table is present in one textbox.
 
I can not use textbox i.e. reportItems in filter condition. Can someone suggest me how to use textbox value in filters?
 

I want to display parent/child records on report. I am not getting the proper solution.

The data is like this:

Sequence  ItemCode IsParent

1               XYZ         0            'do not have child record

2               PQR        1             'have child records with sequence no 3

3              ASD          0

3              AFDGE     0

3              VDC          0

4              ASR          1           'have child records with sequence no 5
5              ASR          0
 
If IsParent = 1, that record has child records with sequence = parent sequenece + 1
 
 

I think u can understand the data I need to bind, and it is like:

           XYZ

+         PQR

           ASD

           AFDGE

            VDC

            ASR

On + click we can do show/hide of child records.

I m not getting how to achive this in SQL server report. Can u give some hint?
 
Thanks in advance
Pravin

View Replies !

---

Default Table Owner Using CREATE TABLE, INSERT, SELECT && DROP TABLE
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.

View Replies !

---

How To: Create A SELECT To Select Records From A Table Based On The First Letter.......
Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
 
Thanks Ross

View Replies !

---

Is There A Method To Convert &"Select * From Table&" To &"Select Field1,field2,...fieldn From Table&" ?
Is there a method to convert "Select * From Table" to "Select field1,field2,...fieldn From Table" ?
 
Thanks

View Replies !

---

How To Filter A Table With An &&"OR&&" Condition
I'd like to set the Filters in the Filters tab of the Table Properties dialog to say:

 

=Fields!WT_TO.Value > 0  OR

=Fields!WT_TO_PREV.Value > 0

 

but teh And/Or column is permanently disabled, and its sticking in a default value of AND

 

what's up with that?

View Replies !

---

Need Help W/ Postback To 'Customers' Table On Form Using Select Query From 'Parameters' Table
I have set up a 'Parameters' table that solely stores all pre-assigned selection values for a webform. I customized a stored query to Select the values from the Parameters table. I set up the webform. The result is that the form1.apsx automatically populates each DropDownList task with the pre-assigned values from the 'Parameters' table (for example, the stored values in the 'Parameters' table 'Home', 'Business', and 'Other'  populate the drop down list for 'Type').
The programming to move the selected data from form1.aspx to a new table in the SQL database perplexes me. If possible, I would like to use the form1.aspx to Postback (or Insert) the "selected" data to a *new* column in a *new* table (such as writing the data to the 'CustomerType' column in the 'Customers' table; I clearly do not want to write back to the 'Parameters' table). Any help to get over this hurdle would be deeply appreciated.

View Replies !

---

HOW TO SELECT ROWS IN THE MASTER TABLE WITH NO RELATIONED DATA IN DETAILS TABLE
I have the following data

MASTER
id
name


DETAIL
id
master_id
name

I want a perform a query where i can get all the rows of the master table which have no relationed rows in detail table.

How can I do that???

View Replies !

---

Select * From Table Does Not Select All Records
I have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command.

Any suggestions?

Thanks!

GordonG

View Replies !

---

&&"Select * From Table&&" Is Processed Much Faster Than &&"Select MyField From Table&&" ¿?¿?
 

I have a query that has the following structure
 
Select *
From Table
Where Condition And ... (some 'Exists' conditions)
 
When I run the query using field names the query gets much slower, and I cannot understand Why!

 
Select MyField
From Table
Where Condition And ... (some 'Exists' conditions)

 
I'm talking about three times slower using the Select MyField sintax.
 
Any ideas???

View Replies !

---

Using Result Of A Select Statement From One Table To Add Data To A Different Table.
I have two table with some identical fields and I am trying to populate one of the tables with a row that has been selected from the other table.Is there some standard code that I can use to take the selected row and input the data into the appropriate fields in the other table? 

View Replies !

---

Select ROWS In Table A That Aren't In Table B
I've been scratching my head over this one.
It should be pretty easy to do, but I keep going over the same problem.

I have 3 tables (Suppliers, Categories & Suppliers_To_Categories).

Suppliers_To_Categories contains which suppliers are related to which categories.

The set-up is so (simplified):

Suppliers
id
1 Supplier 1
2 Supplier 2
3 Supplier 3
4 Supplier 4

Categories
id
1 Category A
2 Category B
3 Category C
4 Category D

Suppliers_To_Categories
id  supplierId    categoryId
1    1                  1
1    1                  2
1    1                  3
2    2                  3
3    3                  1
3    3                  3


What I am after is an SQL statement which tells me for a particular category, which suppliers ARE NOT related to it, so they can be assigned.
E.g.

Category D has Suppliers 1-4 unassigned
Category C has Supplier 4 unassigned
Category B has 2, 3, 4 unassigned
Category A has Supplier 2, 4 unassigned etc...

I've got the other side of the SQL statement which tells me which suppliers are assigned to a category (see below):
    SELECT     Suppliers.*
    FROM         CatToSupplier INNER JOIN
                  Suppliers ON CatToSupplier.supplierId = Suppliers.supplierId
    WHERE     (CatToSupplier.CatId = @CatId)

This is probably really easy, but I've become unstuck!

View Replies !

---

SQL Statement Select From One Table Where Not In Another Table
I have two tables with a 1-many relationship. I want to write aselect statement that looks in the table w/many records and comparesit to the records in the primary table to see if there are any recordsthat do not match based on a certain field.Here is how my tables are setup:Task Code TableTCode,TCodeFName,ActiveTracking Table(multiple records)ID,User,ProjectCode,TCode,Hours,DateI want to look at the tracking table and see if there are any TCodelisted here that are not listed in the Task Code table.How do I write a SQL statement to do this?

View Replies !

---

SELECT Id From Table A Where Matching Id Is Not In Table B
Hi

Trying to figure out a fairly simple SQL query.

Basically i have two tables which have both a matching ID column. How ever, i need to find out which ID's are NOT present in BOTH the tables.

for example

table a -
id
12
13
14
15

table b -
id
12
14
15

Table 'a' has an id of 13 which is not in present in table 'b' and this is the value i need to select!

thanks in advance

View Replies !

---

How To Select Value From Table B To Replace Value In Table A
I have an automatic data collection system. The large majority of the time, all data is electronically gathered. On occasion, the need arises to substitute a manually entered value into the data when a valuable piece of information cannot be electronically secured. When a piece of data is open to substitution, it arrives with a value of -9999 (a value not naturally occuring) in Table A. I want to be able to use a trigger on table A to get its substitution value from a corresponding column (Same name) in Table B. Tables A & B will have an identical schema. A is raw data, B is the manually updated table. Any value in the raw table with the content of -9999 gets its replacement from Table B.


I'm open to any ideas!!

RC =:((

View Replies !

---

How Can I Create A New Table With Its Column Named From Another Table's One Column Value By Using A Select Sentence?
For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".

can you tell me how can I complete this with the SQL?

View Replies !

---