COndition Spli - Error Date Condition

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 !

---

How To For Date Is Not Null Or Nothing Condition
How can i write a condition for like isdate(Fields!DueDate.value)

I tried isdate but i am getting an error message. what is the equivalent for isdate in sql server reporting services formula fields or calculated fields expression.

Thank you very much for the information.

 

 

*********************************************

=IIF(isdate(Fields!DueDate.Value), ................

********************************************

View Replies !

---

Date Condition In Where Clause?
Hi ALL,

Well, i have one view:

create view vwdate
as
select custno, custname, city, date
from salestab
where date = ????

well, this question mark...i have to fill..

like i want if month is june then i want for whole may month results of custno, custname and city..

if month is july then i want results for whole june month..

means for previous month results i want whatever the date is for that particular month..

Example:

if date='06/15/08'
then results will be
custno custname city date
---------------------------------
1 AAAA NJ 05/01/08
2 SDS CT 05/04/08
........................ 05/18/08
.......................... 05/30/08

if date='06/30/08' OR '06/1/08' then also results will be same..
so i want for previous month results..

so can anyone help me to get the correct condition in where clause?

Thanks ALL..

View Replies !

---

Convert Varchar To Date In Where Condition
Hi guys.
i have a field in a table which datatype is varchar and i m storing datetime and number in it.. but when i try to use select statement like ( between startdate and enddate) or number (= ) operator it's not working..
 
select * from table
where cast(Field as datetime) between @startdate and @enddate

 
 
second query that i m trying to exectue is
 
select * from table
where cast(Field as bigint) = @Fieldid

 
but both are not returning any value...can anyone tells me where i m doing mistake

View Replies !

---

Using A Year Parameter In A Date Condition
I am collecting a year parameter from my user and want to use that year as part of a complete date condition.

So I collect the year say '2003' from my user.

A condition must be applied so that the date must be after 01-APR-...and here is where i want to utilize the parameter @Year. So I can apply the same date condition depending on what year we are looking at.

I am currently using the following logic:

 (DATEPART(yy, CANCELLATION_DATE) = @Year)
AND
(DATEPART(mm, CANCELLATION_DATE) >= 04)
AND
 (DATEPART(dd, CANCELLATION_DATE) >= 02)

This causes problems as the date can be only Jan, Feb, Mar, Apr 01 or 02.  What I need is to gather all dates prior to Apr 02, with the year paramter to follow. 

We originally developed this querty using Oracle so idealy there would exist a pl-sql to t-sql conversion tool out there!  Anybody know of one? 

Here is how the pl-sql is structured:
CANCELLATION_DATE >= TO_DATE('02-APR-'||:"Year",'DD-MON-YYYY') )  ) )

Thanks in advance,
mpaskevi

View Replies !

---

Error Condition For Updates
Hi,

I have a series of Updates that I perform on 7 tables. I would like to execute these updates on a nighly basis via a job. I have the update statements lined up in a stored proc.

So I have -

Create Proc Update
AS
SET NOCOUNT ON

DECLARE @RETURNVALUE int
DECLARE @ERRORMESSAGETXT varchar(510)
DECLARE @ERRORNUM int

set @RETURNVALUE = 0

BEGIN ---Start here

Update table1
Update table2
.
.
.
Update table7

SELECT @ERRORNUM = @@ERROR
IF @ERRORNUM = 0
SELECT @RETURNVALUE = 0
ELSE
BEGIN
SELECT @ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
WHERE error = @@ERROR
RAISERROR (@ERRORMESSAGETXT, 16, 1)
SELECT @RETURNVALUE = 1
END
RETURN @RETURNVALUE
END --- END HERE

---

Is the error checking at the appropriate place? Should I have error conditions after every update? If yes then what condition do I check for?

Thanks

View Replies !

---

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 !

---

ADO + MS Access Cannot Use &&"date&&" As Query Condition
 

First I have to apologize about posting the problem here , since it not actually relate to SQL Server
 
I am really new to database programming.
I use ADO (the ActiveX one , Not ADO.NET) In an MFC Application to access MS ACCESS Database
 
 
I can insert date in to table using statement like this
 
insert into tableXXX (date_field) values ('1/2/2007')
 
 
but when i tried to query it using this statement
 
select * from tableXXX where date_field = #1/2/2007#

 
 
Nothing return
 
I also tried to use #1-2-2007# , #01-02-2007# but it all the same
 
Can someone tell me how to successfuly use date as a query condition ?
 
Thank in Advance
 
 
 

View Replies !

---

Condition (if / Else) In SQL?
Hi all,I'm building a DataSet on Visual Studio and don't know how to do a condition (if/else) with SQL... I have a search form, with a DropDownList and have 2 options in it: Search by Title or Search by Author. If the "Title" is selected, then the value is "title and if "Author" is selected, then the value is "author".Here is what I have right now for the DataSet, as seperated queries but I think I can combine them to be one single query 1.This will returns the songs that matches the title:SELECT     LYRICS_PK, LYRICS_TITLE, LYRICS_TITLE2, LYRICS_WRITER, LYRICS_WRITER2, LYRICS_COWRITER, LYRICS_DATE_ADDED, UserId_FK,                       LYRICS_APPROVED, LYRICS_TYPE, LYRICS_VIEWS, LYRICS_ADDED_BYFROM         t_lyricsWHERE ((@LYRICS_TITLE IS NULL) OR (LYRICS_TITLE LIKE '%' + @LYRICS_TITLE + '%') OR (LYRICS_TITLE2 LIKE '%' + @LYRICS_TITLE + '%')) AND (@LYRICS_TYPE = 'title') 2. This returns the songs that matches the author: SELECT     LYRICS_PK, LYRICS_TITLE, LYRICS_TITLE2, LYRICS_WRITER,
LYRICS_WRITER2, LYRICS_COWRITER, LYRICS_DATE_ADDED, UserId_FK,
                      LYRICS_APPROVED, LYRICS_TYPE, LYRICS_VIEWS, LYRICS_ADDED_BY
FROM         t_lyrics

WHERE ((@LYRICS_AUTHOR IS NULL) OR (LYRICS_AUTHOR LIKE '%' +
@LYRICS_AUTHOR + '%') OR (LYRICS_AUTHOR2 LIKE '%' + @LYRICS_AUTHOR + '%'))
AND (@LYRICS_TYPE = 'author') This is very inefficient because I have 2 queries, and I need to build 2 ObjectDataSources as well as 2 different GridViews to display the results. I think we can do something likeSELECT .... ... FROM t_lyricsif (@LYRICS_TYPE = 'title')     DO THE WHERE CLAUSE THAT RETURNS MATCHES WITH TITLEelse if (@LYRICS_TYPE = 'author')    DO THE WHERE CLAUSE THAT RETURNS MATCHES WITH AUTHOR But I don't know how to write that in T-SQL.Any help would be greatly appreciated,Thank you very much,Kenny. 

View Replies !

---

Where Condition
 da = New Data.SqlClient.SqlDataAdapter("SELECT [Products].[Names], Count([ProductList].[Products]) AS [Total]  FROM [Products]     LEFT JOIN [ProductList]     ON [ProductList].[Names] = [Products].[Names]       GROUP BY [Products].[Names] ", strConnection)
can we use a where condition in the statement.If so how can we use it.

View Replies !

---

Where Condition
hi

i want to give .text filed of a TextBox to where condition but the result is not correct.

The SQL is like following:

selStr = "SELECT COUNT(*) FROM Users WHERE (Name = usernameTextBox.Text) and (Password = passwdTextBox.Text)"

View Replies !

---

Only NOT Condition In CONTAINS
 

Hi All,
 
I have stored Candidates Resume in binary(Image) format in database.
I have a search feature to search resume.
For Example:
 
with all these words: ASP.Net, SQL Server, 2 Years
without these words: Java
 
Here I get Candidate which CONTAINS("ASP.Net" AND "SQL Server" AND "2 Years" AND NOT "Java")
 
Now I want to search candidates only
without these words: Java
 
it means the condition is CONTAINS(NOT Java)
 
is this possible?
if yes, how?
 
Thanks in advance.
 
 
 
 

View Replies !

---

WHERE Condition
Hi,
 
In following query, I use three conditions in WHERE caluse. When I use only CreditUnion.Id=@CreditUnionID
Then I get the right value set. But when I join other two conditions , i get all the values instead of relevant data for that parameter.
 
Can anyone say why it happenes?
 



Code Block
SELECT
     Member.LastName + ' ' + Member.FirstName AS MemberName, CASE WHEN CuStatus = 'Existing'    THEN            'Existing' ELSE 'New' END AS MemberType,  EnumCUMembershipStatus.UIText AS Status, CDOrder.DecidedOnCU, SysUserLogon.LastName + ' ' + SysUserLogon.FirstName AS CUDecisionOfficer,  'CD' AS ProductType, CreditUnion.Name

FROM            
                      Member INNER JOIN
                      CDOrder ON Member.LastCDOrderFK = CDOrder.Id AND Member.Id = CDOrder.MemberFK INNER JOIN
                      CreditUnion ON Member.CreditUnionFK = CreditUnion.Id INNER JOIN
                      EnumCUMembershipStatus ON Member.CuStatus = EnumCUMembershipStatus.Name INNER JOIN
                      SysUserLogon ON CreditUnion.Id = SysUserLogon.CreditUnionFK
 
WHERE     (CreditUnion.Id = @CreditUnionID) AND (Member.CuStatus = 'Approved') OR (Member.CuStatus = 'Declined')

UNION

SELECT    
                      Member_1.LastName + ' ' + Member_1.FirstName AS MemberName,
                      CASE WHEN CuStatus = 'Existing' THEN 'Existing' ELSE 'New' END AS MemberType, EnumCUMembershipStatus_1.UIText AS Status,  LoanApplication.DecidedOnCU, SysUserLogon_1.LastName + ' ' + SysUserLogon_1.FirstName AS CUDecisionOfficer, 'Loan' AS ProductType, 
CreditUnion_1.Name

FROM        
                          Member AS Member_1 INNER JOIN
                      LoanApplication ON Member_1.LastLoanApplicationFK = LoanApplication.Id AND Member_1.Id =            LoanApplication.MemberFK LEFT OUTER JOIN CreditUnion AS CreditUnion_1 ON Member_1.CreditUnionFK = CreditUnion_1.Id LEFT OUTER JOIN  EnumCUMembershipStatus AS EnumCUMembershipStatus_1 ON Member_1.CuStatus =  EnumCUMembershipStatus_1.Name LEFT OUTER JOIN SysUserLogon AS SysUserLogon_1 ON LoanApplication.SysUserLogonFK = SysUserLogon_1.Id AND LoanApplication.SysUserLogonCUFK = SysUserLogon_1.Id AND CreditUnion_1.Id = SysUserLogon_1.CreditUnionFK
 
WHERE     (CreditUnion_1.Id = @CreditUnionID) AND
                      Member_1.CuStatus = 'Approved' OR Member_1.CuStatus = 'Declined'

 
 

View Replies !

---

No/any Condition
I have a query something like

select a, b, c from mytable where a=@prmA

If prmA is something like generic all, any, I want this query return value without condition. Is there a way to do this with sql or I should write stored procedure that checks @prmA and all other condition parameters and generate new SQL statement?


Regards,
Hakan

View Replies !

---

And / Or Condition
hi,

i'm working on a query and have discovered something fairly simple regarding "and" / "or" condition.

if I use e.g.

id_product in ('1111','2222')

as a result i should get all products that match id = 1111 and id = 2222.

But if I do it like

id_product in ('1111')
and id_product in ('2222')

as a result i get 0 rows returned, where as i want to find invoices that have both products, and not those which have either product 1111 or 2222 or even both :)

thank you for any suggestions!

View Replies !

---

CONDITION
Hi,I want to write a SQL stmt.(condition) that checks the following @ActionPLanID > 0 and ActionPlanID exists in table A simultaneously?

I was thinking of using IF NOT exists but don't know how to write it. Am I going in the right direction?

Thanks,
VBJP

View Replies !

---

Search Condition
Hi This is madhavi
am working with a project with ASP.NET Using VB.NET..
i have requirement that i have to provide the result based on search condition....
First : For Serach i have to search based on given CITY and CATEGORY....
 
      For this i have written a StoredProcedure like:
******************************************************************************************************************
         Create PROCEDURE YellowPages_Search(@city nvarchar(50),@SearchWord nvarchar(200),@Name varchar(50) OUTPUT,@CompanyName varchar(50) OUTPUT,@Address varchar(1000) OUTPUT,@PhoneNo varchar(50) OUTPUT,@MobileNo varchar(50) OUTPUT,@Fax varchar(50) OUTPUT,@Email varchar(50) OUTPUT,@WebSite varchar(50) OUTPUT)AS
declare @sql nvarchar(1000)set @sql='select * from YellowPages_Userdetails where city='''+@city + '''and (category like ''%' + @SearchWord + '%'' or subcategory  like ''%' + @SearchWord + '%'') '
exec(@sql)
GO
*************************************************************************************************************************************************************************
Now  i want to extend this search condition for LOCATION and SUBCATEGORY
 means my search condition should include CITY , LOCATION , CATEGORY and SUBCATEGORY
(here the location and subcategory may be given or may not be given)
so please help me out
Thanks in Advance,
Madhavi
 

View Replies !

---

IN CONDITION QUESTION
I am an ASP.NET Developer I am using two SQL Server databases, 2005 and sql express.I am using a select statement on an IN CONDITION
One table, Table name = SOP10100 resides in a SQL Server 2005 DatabaseThe other table,  Table name = ORDER resides on a SQL server express Database
I am writing the following sql statement
SELECT ORDERNO, CARRIER FROM SOP10100WHERE ORDERNO IN ('ORD000234','ORD000384',....)
My question is how many values can I fit on this IN conditionI mean the maximum number of values (upperlimit)
Is there a better way to do this Using ASP.NET ?

View Replies !

---

Copy On Condition
i have two tables A and B with the same fields,
If the id field of table B equals id field in Table A i need to update th edata for that id row.If the id field doesn;t match then i need to insert a new record in tale A for that id
that is i need to perform insertion or updation into table A depending on table B dataCan anyone give me some idea how to start?

View Replies !

---

Exclude Condition
i have two tables: "Person" and "Year". "Person" can have many "Year"
(one to many relation). i want a query which returns all the records
from "Person" where "Year" is 2005 but exclude if there is any "Year"
with 2004. how can i write that query? any help will be appreciated.
i did try
<code>
SELECT * FROM Person JOIN Year ON Person.Id = Year.PersonID WHERE Year.Year = 2005 AND Year.Year <> 2004
</code>
but it doesn't seem to work. i want this query to return records from
Person where there is no any year with 2004 but only 2005. If a person
has both 2004 and 2005 exclude that person.

View Replies !

---

If Exists Condition..
which is more proper:if DB_ID(@db) IS NOT NULLorIF EXISTS (SELECT name FROM sys.databases WHERE name = @db)?regards

View Replies !

---

MS SQL IF ELSE Condition Checking
Hello,I have Table1 with column Email, which has mail addresses in theformat 'useremailid@'. Few of these Email values are NULL where nomail address was specified.In my View1 I'm using SUBSTRING like... Left(Email,CHARINDEX('@',Email)-1) AS EMAIL_NAME (to cut out the @ sign) FROM Table1 and getjust the usermailidAnyway, what I would like to do is use the above to get all theusermailids and have a condition that checks if Email field IS NULLand if TRUE replaces it with blank value ''So something like IF (Email IS NULL) THEN Email = ''I've never really used IF ELSE in a query and would be gratefull ifsomeone could help me out. I don't want to leave out the NULL valuesusing WHERE Email is NOT NULL, I just want to convert them to an emptystring ''Many thanks in advance :-)Yas

View Replies !

---

Condition With Group By
Data:PROJ PLAN TOTTIME UNITA P1 10 DAYA P2 10 HOURA P3 1 MONTHWHEN I'M DOING GROUP BY ON PROJAND CALCULATING TOTTIME IT CONSIDER ONE OF THE UNIT I.E. DAY, HOUR, MONTHI WANT TO SUMUP ALL WITH HAVING UNIT CALCULATION ALSO.10 DAY=10 DAYS10 HOUR=1.25 DAYS1 MONTH=20 DAYSTHE RESULT SHOULD BE LIKE THIS:PROJ PLAN TOTTIME (IN DAYS)-------------------------------A ALL 31.25-------------------------------THANKS IN ADV.T.S.NEGIJoin Bytes!

View Replies !

---

IF Condition In Join??
Dear GroupI'd be grateful if you can send me on the right track in achieving this.I have three tables A,B,C outlined as follows:Table: AField: RowIDField: EntityIDField: TypeIdentifierTable: BField: RowIDField: NameTable: CField: RowIDField: NameLet's assume I've the following records:Table A:1,1,02,1,1Table B:1,SmithTable C:1,XYZCorporationThe table joins are as follows:A.EntityID = B.RowIDA.EntityID = C.RowIDI would like to select all records from Table A and display the Names fromeither Table B or Table C, depending on the Field TypeIdentifier.E.g.: SELECT Name FROM A JOIN B ON (A.EntityID = B.RowID) JOIN C ON(A.EntityID = C.RowID) IF TypeIdentifier = 0 SELECT Name FROM B IFTypeIdentifier = 1 SELECT Name FROM CResultset:SmithXYZCorporationIs this somehow possible?Thanks very much for your time & efforts!Martin

View Replies !

---

?Queries In While Condition
When using a query in the condition of a while loop, is that query being performed each time?

EX:
While (@X <= (Select count(*) from SOME_TABLE))

View Replies !

---

SQL Query Condition
Hi,

I am creating a stored procedure which takes a few paramaters. One of which is a string of comma separated codes. At the end of my select query the codes will be used in the where clause.

What I'm doing is parsing the comma separated string and populating a temp table called #codes. So my query will look something like this:

select * from tableA
where tableA.col1 = 'something'
and tableA.code in (select * from #codes)
and....

However, the code paramater can be null and if this is the case I want the query to be called for all codes - i.e. I effectively want to remove the and tableA.code in (select * from #codes) part of the where clause.

Is there any clever way of doin this other than having a if... else... and writing 2 queries - one with the and and one without it?

Hope this is clear.

Thanks,
Wallace

View Replies !

---

MDX - Exclude Condition
I have to built a query to get the % for all the Region (Americas, Asia and Europe) from a cube.

But in these regions some countries are excluded and treated seperate.

Like Asia does not include India and Japan.

How do I get the ASIA query using an EXCLUDE condition.

Please help.

View Replies !

---

Using Alias In Where Condition
Can we do

Select BookNo as Catalog from Books
where Catalog = 12356

I need to find the way to use alias in "where" for very complex query
Is anyone has way around it ?
Thank you

View Replies !

---

Using Where Condition With Dates
I'm trying to make sql that check date in database

Select ... From ....
Where ((id=1) and (port=2) and (logdate=#12/31/2001#));

I'm getting 0 records even if its exist.
I know the problem is in the "logdate=#12/31/2001#"
What is the problem and why the sql ignores it ?

View Replies !

---

Where Condition Using Variable
Hi,

I've come across a funny when using variables within a WHERE clause in a multiple JOIN statement.

I have the following tables:

Articles table
==============
Article_ID int PK IDENTITY(1,1)
Title varchar(128)
Article_Date datetime
Author_ID int FK REFERENCES Authors(Author_ID)
Archive char(1)
WaitingForConfirm char(1)

Authors table
=============
Author_ID int PK IDENTITY(1,1)
Author_Name varchar(64)

TextArticles table
==================
ContentRow_ID int PK IDENTITY(1,1)
Article_ID int FK references Articles(Article_ID)
Content varchar(2048)

Below is an extract from a fairly complex stored procedure used to search based on a variable number of values including Author, Title and Content.

SELECT Articles.Article_ID
, Articles.Title
, Articles.Article_Date
, Authors.Author_Name
FROM Articles
LEFT OUTER JOIN Authors
ON Articles.Author_ID = Authors.Author_ID
LEFT OUTER JOIN TextArticles
ON Articles.Article_ID = TextArticles.Article_ID
LEFT OUTER JOIN FileArticles
ON Articles.Article_ID = FileArticles.Article_ID
WHERE Articles.Article_Date BETWEEN @startdate AND @enddate
AND (Articles.Archive > 'Y' OR Articles.Archive < 'Y')
AND (Articles.WaitingForConfirm > 'Y' OR Articles.WaitingForConfirm < 'Y')
AND Articles.Title LIKE @title
AND authors.author_name LIKE @author
AND TextArticles.Content LIKE @content
ORDER BY Articles.Article_Date DESC

If any of these variables are passed in as NULL I convert to '%' and any variable that is not null is altered to '%value%'. The problem arises with the @content variable - which is varchar(128). The following variables are passed in:

@startdate = 'JAN 01 2000'
@enddate = GETDATE()
@author = 'smith' - which is changed within the procedure to '%smith%'
@title = NULL - which is changed within the procedure to '%'
@content = NULL - which is changed within the procedure to '%'

The procedure returns 0 rows based on these criteria - even though if I hard- code these values the expected number of rows are returned.
I have used various print statements throuout the procedure and the @content variable is always correctly established.

I have come across this before with another table/column/join combination and am at a loss to explain it. Any help would be greatly appreciated.

Many Thanks

. . Greg

View Replies !

---

Between And In Search Condition
hi,

if i use the following query, isn't it supposed to output same amount of records?

select... from... where invoice.billcycle in (104,204,304)


and


select ... from ... where invoice.billcycle between 104 and 304


for some reason, i get 38 from the first and 7 from the second query.
thanks for the help in advance

View Replies !

---

Stuck With OR Condition
Hi,

I have a sql problem I'm trying to solve. I'm selecting from a table based on a foreign key, and the select returns 2 rows. The table has a column called type, and each row for the foreign key has a different type value. Hopefully the example below can help to explain:

Case 1:

PK | FK | Type | Text
--------------------------
1 | 226 | 0 | some text goes here
2 | 226 | 1 | NULL

Case 2:

PK | FK | Type | Text
--------------------------
3 | 334 | 0 | some text goes here
4 | 334 | 1 | actual text I want to select is in this cell

I'm trying to create a select statement to grab the text for the foreign key I'm looking up. In case 2, I want the text where type=1 but in case 1 I want the text where type=0.

I had started writing it as

select text from table where fk=334 and ( (type=4 and text is not null) or type=0 )

but this returns both rows. What I what is something that I think is more akin to

case a || case b

expression in programming - if case a evaluates as true, use that, otherwise evaluate case b and use if true, otherwise return false.

I hope you can understand what I'm trying to get and any suggestions would be much appreciated.

Thanks in advance,
Peter

View Replies !

---

Somewhat Dynamic Where Condition
I'm using sql server 2000.
 
I'd like to create a stored procedure that has a somewhat dynamic where condition.
 
I'd like to do sth like this:
 
ALTER         PROCEDURE [dbo].[sp_Situation](@type_data as integer )As
...
 
declare @where_cond varchar(20)
 
if @type_data = 1

set @where_cond = 'A, B, C'
else

set @where_cond = 'A'
 
select col1, col2, col3...
from table_1
where col1 in (@where_cond)
 
A, B and C are single values (col1 contains or A or B or C, not a string 'A, B, C').
 
Is it possible to do sth like that?

View Replies !

---

Like Condition In Sql Statement
This is my sql query

Select DOJ AS 'JoiningDate' from emp where DOJ like '%2008/09/04%'

there are 8 records on this time.But it displays no result .
The column is datetime datatype
year month date or we need to mention any thing other than this.

 
 

View Replies !

---

If Null Condition
 

I have a this code
 

SELECT @maxid = MAX(id) FROM Quote

 
now i need to check if @maxid is null.. if so i want to set it as 1. how do i do this

View Replies !

---

How To Write This Condition?
Hi,

 

I am trying to set up a conditional split. As usual, my books do not explain what I am trying to do :-)

 

flat file source --> conditional split (if Column0 contains a certain string send to error file)

 

In the Conditional Split Transformation Editor I have the following for "condition":

 

FINDSTRING( "my search string", [Column0] ,1 )

 

TITLE: Microsoft Visual Studio
------------------------------

Error at myPkg [Conditional Split [8259]]: The expression "FINDSTRING( "my search string[Column0] ,1 )" is not Boolean. The result type of the expression must be Boolean.

Error at myPkg[Conditional Split [8259]]: The expression "FINDSTRING( "my search string[Column0] ,1 )" on "output "Case 1" (8351)" is not valid.

Error at myPkg [Conditional Split [8259]]: Failed to set property "Expression" on "output "Case 1" (8351)".

 

As you can see I want it to search Column0. Can someone please point out my error.

 

Thanks

View Replies !

---

How To Check A Condition
hi

as i am a beginner to ssis. i have tried a lot to solce a logicbut it did not work.the situation is as i have a view which is updated dynamically whenever thebase table gets updated. then from view i hvae to select the new inserted row with the order id as identity column. there is a column in a table as salespersonid i have to check if the value is null or not.if its null then i have to assign a sales personid from the table havind the sales person detail. if its already in the row then i have to create a xml file of that data and send file so,e location. i have created a stored procedure to work on the creation of a xml file. but i hve tried a lot about how to check the value of the salepersonid which may be null or anu number and deceide to do one of the above operation. i have to do it in ssis.

i am stuck on it.


can any one have any idea.on proceeding it.


thanks in advance.

View Replies !

---

Insert With Condition
Hi,
what i am trying to do is, reading from an array which has the column name and value and then insert that value to the column which is fetched from that array.And on the other hand i should use select to get the data's which have the same ID.
more detail: what i have >> (VARID,columnName,Value)
what i want>> insert to table (columnName) values (Value) while ID =VARID

any idea how i should do that?
tnx

View Replies !

---

Condition In Subtotal?
Hi
I have the following matrix
                            CA           AZ

No surplus           11              5

Surplus                  12            10

Zotal                      100            50
Totlal                     123           65
I want that only (No surplus and Surplus) sum include  in total Can I apply This condition on Subtotal.

thanks in advance

View Replies !

---

Run Subreport According To Condition.
Hi all,

in my report, I am using a subreport which takes two parameters and in my main report there is a filed named "ProcessCode".

My problem is that, this subreport should be only run if ProcessCode is 11 oder 29. Otherweise there is no need to run subreport. the column of subreport will be shown as emtpy.

I know, I can return empty string from my stored procedure if the processcode is not 11 or 29, but I would like to learm, is it possible to not render subreport according to condition?

Thanks in advance.

 

 

View Replies !

---

CountDistinct With Condition?
Hi all.
Trying to use CountDistinct but I need some help.
 
Table as..
 
EventID    UserID    County
100               1          USA

100               2          USA
100               9          CANADA
101               1          USA
101               10         CANADA
 
etc
 
1. I can get Unique number of UserID using CountDistinct for whole Events.
 
If I want to get Unique number of UserID with specific County?
Such as how many unique USA users ?
 
Is that possible to add some condition to CountDistinct ?
 
Please advice.
 
 
Thank you!
 
 
Raymond
 

View Replies !

---

MULTIPLE IIF CONDITION
I am trying to put a rag colour to text using the expression builder The RAG colours are the standard RED - AMBER -GREEN except the condtions to each are 2
 
For example A figure is between 70% and 75% and the code for this is P
I can manage to get this to work with the condition being between the percentage but not if it also meets the code P criteria.
 
Any assistance in mulitple conditions would be great  
 

View Replies !

---

How To Put Where Condition In Xml Query
 hi,
I am to SSRS.I using following xml file .
<Sales>
<Region value = €œWest€?>
<Name>Vinod Krishnan</Name>
<Detail>
<Country name = €œUSA€? amount = €œ12345678.90€? />
<Country name = €œCanada€? amount = €œ898545.03€? />
</Detail>
</Region>
<Region value = €œEast€?>
<Name>Kylan Mylappillii</Name>
<Detail>
<Country name = €œIndia€? amount = €œ333348.00€? />
<Country name = €œChina€? amount = €œ678912.34€? />
<Country name = €œIndonesia€? amount = €œ100000.00€? />
</Detail>
</Region>
</Sales>

I wrote query in quer tab
as
<Query>
<ElementPath>
Sales /Region /Detail/Country
</ElementPath>

it returns all data.
how to put "where " condition in above query.
</Query>

View Replies !

---

Condition On Sum Value (t-sql Query)
 

Hello all,
I've got some problem with the following query:
select  
subscriber_id, sum(convert(int,n_inboundcalls)) as totalinbound
from calldiversityratedaily
where totalInbound < 10
group by subscriber_id

 
The error message provided is:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'totalInbound'.
 
I'm using MSSQL server 2000, version 8.00.194 and it appears that I can't use the name I give to the sum in the where clause.
Could anyone help me please?

View Replies !

---

Where Condition Does Not Work
Hi All,
 

In the following query, although I try to restric data using FROM clause , still I get all the data without filtering. When I put it in where clause other where conditions also does not work.
Can anyone help me to fix this pls?
 
 



Code Block
SELECT         
 
MoneyFlowTransaction.ScheduledSettlementDate, Member.LastName + ' ' + Member.FirstName AS MemberName, MoneyFlowTransaction.Amount,   MoneyFlowTransaction.Direction, MoneyFlowTransaction.Status, EnumTransactionPurpose.UIText AS TransactionPurpose, CreditUnion.Name
 
FROM           
 
MoneyFlowTransaction LEFT OUTER JOIN   Member ON MoneyFlowTransaction.InfoMemberId = Member.Id LEFT OUTER JOIN  EnumTransactionPurpose ON MoneyFlowTransaction.Purpose = EnumTransactionPurpose.Name LEFT OUTER JOIN    CreditUnion ON MoneyFlowTransaction.InfoCreditUnionId = CreditUnion.Id AND (MoneyFlowTransaction.Purpose IN ( 'PartnerDepOriginationProceeds', 'PartnerDepRedemptionRequest' , 'PartnerHelpProceeds'  ,'PartnerHelpRequest', 'PartnerLoanCollHelpIntProceeds', 'PartnerLoanCollHelpPriProceeds', 'PartnerLoanCollUserIntProceeds', 'PartnerLoanCollUserPriProceeds', 'PartnerLoanDisbursementRequest',               'PartnerLoanFeesProceeds'))

WHERE        (CreditUnion.Id IN (@CreditUnionID))  AND (MoneyFlowTransaction.Status = 'Ready') OR
                      (MoneyFlowTransaction.Status = 'InProgress')

 
 




Thanks

View Replies !

---

Subtracting With A Condition
One of the following selected fields is a sum.  I need to subtract ISNULL(R.CMS_AMOUNT,0) where R.Waived = '1
from that amount.
Pretty much just subtract those amounts where the waived field is 1.  I am having trouble with the syntax.  Can anyone help?  Thanks.'
 
 

SELECT  P.Efs_Network_ID
 , F.DBA_Name
 , F.ACCOUNT_STATUS
 , SUM(ISNULL(R.CMS_Amount,0))  - isnull(RA.Amount,0) AS Amount  --   ??  isNULL(R.CMS_AMOUNT,0) WHERE
R.Waived = '1'  ??????
 
, isnull(SUM(RA.Pending),0) AS Pending
 , F.Salesman_no
FROM  tblPimsFDSLookup P
INNER JOIN ACH_MerDemo F
 ON P.Efs_Network_ID = F.Efs_Network_ID
LEFT OUTER JOIN tblrecovery R
 ON P.IntMid = R.IntMid
LEFT OUTER JOIN(
 SELECT sum(isnull(Amount,0)) as amount
 , intmid
 , SUM(CASE WHEN RA.Status = 'P' THEN 1 ELSE 0 END) AS Pending
 FROM tblACHRecoveryAmounts RA
 --WHERE IntMid = 641046
 WHERE RA.Status IN ('A','P','M','S')
 Group by intmid
) AS RA
 ON P.IntMid = RA.IntMid
WHERE  P.INTMID = @INTMID
AND R.Waived = 0
AND Upgraded IS NULL
GROUP BY  
 P.Efs_Network_ID
 , F.DBA_Name
 , F.ACCOUNT_STATUS
 , RA.Amount
 ,F.Salesman_no

View Replies !

---

Count If Condition
 

hi guys

I need to do a count on a column in my table but i have to check for a condition first.

Here is my sample data






















Providerdate
Region
DHB
DHBName
PHO
PHOName
Practice
Practicename
PracticeAddress
Practice_Startdate
Practice_Enddate
Practitioner
PractitionerName
Practitioner_Startdate
Practitioner_Enddate
Locum

1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000045
Horatepai
15 Epiha Street, Paraparaumu
1/07/2003

29201
Geraldine Victoria MacKenzie Jordan
8/06/2005
31/10/2005
no

1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000045
Horatepai
15 Epiha Street, Paraparaumu
1/07/2003

22079
Glenn Morton Colquhoun
1/03/2006

NO

1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000045
Horatepai
15 Epiha Street, Paraparaumu
1/07/2003

14121
Meaburn Charles Staniland
1/07/2003

NO

1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000045
Horatepai
15 Epiha Street, Paraparaumu
1/07/2003

9877
Philip White
1/07/2003

NO

1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000045
Horatepai
15 Epiha Street, Paraparaumu
1/07/2003

11678
Susan Prudence Wilson
1/07/2003

NO

1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000045
Horatepai
15 Epiha Street, Paraparaumu
1/07/2003

3881
Wilfred Travis Wilson
5/01/2002

yes

1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000052
Paraparaumu Medical Centre
92-94 Kapiti Road, Paraparaumu
1/07/2003

22742
Adrian Howard Beaumont Gilliland
13/01/2005
28/10/2005
NO

1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000052
Paraparaumu Medical Centre
92-94 Kapiti Road, Paraparaumu
1/07/2003

18188
Amanda Mary Clarke
1/07/2003

NO

1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000052
Paraparaumu Medical Centre
92-94 Kapiti Road, Paraparaumu
1/07/2003

32909
Andre Honda Garib
29/12/2005

no

1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000052
Paraparaumu Medical Centre
92-94 Kapiti Road, Paraparaumu
1/07/2003

13042
Christopher Alan Fawcett
1/07/2003

NO

1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000052
Paraparaumu Medical Centre
92-94 Kapiti Road, Paraparaumu
1/07/2003

16556
Jennifer Maree O'Donnell
1/07/2003
22/12/2005
YES

1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000052
Paraparaumu Medical Centre
92-94 Kapiti Road, Paraparaumu
1/07/2003

13650
Timothy Stapleton Smith
1/07/2003

NO

1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000046
Paul Norton Surgery
51 Kapiti Road, Paraparaumu
1/07/2003

14621
Paul Stephen Norton
1/07/2003

NO

1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000053
Raumati Road Surgery
23 Raumati Road, Raumati Beach, Paraparaumu
1/07/2003

11202
Katherine Dana Stone
3/10/2005
7/10/2005
yes

1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000053
Raumati Road Surgery
23 Raumati Road, Raumati Beach, Paraparaumu
1/07/2003

13048
Robyn Lynette Crow
1/07/2003

NO

 

 

now i have to add two new columns as LocumYes and LocumNo
In Locumyes column I have to count the number of 'yes' in the locum column grouped by practitioner and in locumno i have to count the number  of 'no' for that practitioner.

The situation is a practitioner can work on more than one practice and he can work as a 'yes' locum in one,'no' locum' in another or vice versa.It can be of any sort of combination.

so I have to do a query which should group by practitioner and count the number of yes and no for each practitioner..

 

 

Thanks

Mita

View Replies !

---

Average With Condition
Problem

fldyear fldascending flddecending
2007 20 23
2006 21 14
2007 41 12
2007 12 5


how do i find the average of fld ascending and descending where year=2007

Your help will be appreciated

View Replies !

---

Hi, How Could Put A Condition In Statement.
Hi, i wanted to put a condition based on my codes below.. but where i had to put the condition? like where QTY=3
please help..

INSERT INTO magpatoc.dbo.RSOTransfer(RSONO, Customer, ItemCode, ItemDescription, Source, MOQ, QuantityRequired, Remarks, ZeroStock, NewProduct, ProjectForecast, WithMotherPO, Other, RequestedBy, RequestedDatetime, NotedBy, RecievedBy, RecievedDatetime, PreparedBy, PreparedDatetime, ApprovedBy, ApprovedDate, ReservationNoDate, PurchaseOrderNo)
SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','c:CopyOfRSODB.mdb';'admin';'',FinalCustItemRSO)


ed9teenMagnaza

View Replies !

---

Group By Condition
Hi,

I Have the following "select statement" with "group by condition", I want the outgoing result for the query to be vno with (scamt <> sdamt) the example is as following:-


select vno,vtype,tdate,sum(camt) as scamt,sum(damt) as sdamt
from transact
where year = 2007
group by vno,vtype,tdate
HAVING (scamt <> sdamt)
order by vno

but i have the following error

Invalid column name 'sdamt'.

View Replies !

---

INSERT With Condition
I need to understand how to INSERT a record from one table to another only when a condition is meet between the two tables. Example...NewTable has new employees on it that are not on the current EmployeeTable. So, when NewTable.EmployeeNo does not match a EmployeeTable.EmployeeNo then I want to INSERT.

Can someone please help me understand what code I need to make this happen? Thanks.

View Replies !

---