Like Clause In SQL Query

Mar 13, 2007

Hi folks,

How can I get all names that start with "sci" or "eng" without using the OR clause. In other words, how can I modify the following statement so I don't use the OR clause:
select * from course where (name like 'sci%' or name like 'eng%')

Can I use regular expressions to achieve that?

Thanks!
-Parul

View 14 Replies


ADVERTISEMENT

SQL Query With A WHERE ALL Clause.

Jul 13, 2007

I am building a search application that has several fields in it - I want to be able to allow the user to put in specific search criteria or all them to say "All" and then pull back any records with anything in that row.
Example
Select *From mytableWHERE (SDate >= 1-1-2007) AND (EDate <= 1-25-2007) AND (Location = "ALL" or In ALL) this is where I am cloudy.. What/How would I write in the query to pull back everything, I know I could just leave the Location out of the query all together, but then that does not allow them to select just one the next time they run the application.
 Thoughts?
Thanks,Ad.

View 4 Replies View Related

Where Clause In Sql Query

Oct 14, 2005

Hi everyone,   I am trying to run this query and it is not returning a single row. Although I can see one row with this 10/14/2005 date in the table.SELECT  iSourceId, UserId,FROM    tblEmployeeWHERE dtInsertDate >= '10/14/2005' and dtInsertDate <='10/14/2005'I also tried to rewrite the query this waySELECT  iSourceId, UserId,FROM    tblEmployeeWHERE dtInsertDate = '10/14/2005' but still it is not returning a single row.Please let me know what am I doing wrong.Thanks.

View 2 Replies View Related

LIKE Clause In Query

Aug 30, 2004

Question..

In a MSSQL SELECT Statement e.g

SELECT t1.fname, t1.lname, t2.district_name, t2.district_number FROM t1 AS table1, t2 AS table2 WHERE t2.district_name LIKE 'S%'

i have these values in the table

table1

fname lname
mike jackson
roy mires

table2

district_name district_number
South 123
Daggerty 7845
duffel 7224
rubble 7545

Now the query is dynamic (the letter is that the like clause is run against)

When the letter D is searched for i get the 2 colums duffel, daggerty BUT when S is searched against I get nothing..

I am confused as to why, It doesn't seem to be case sensitive, as the 2 colums duffel and Daggerty 1 is d is in lowercase and the other is in uppercase. andive tries both lowercase s and uppercase S and still got nothing.

Is tehre a better way to use the LIKE clause? Ive looked and looked for documentation about the LIKE clause but I cannot find anything

Am i doing something wrong?

any help would be greatly appreciated

View 1 Replies View Related

TOP Clause Query ?

Jun 17, 2004

Hi all,

In Oracle 'ROWNUM' can be used with any variables.

eg : select sno from test1 where rownum < variable1 ( say variable1 is a local variable )

Is there any equivalent for the above in SQL Server ?

Hint :

If 'select sno from test where rownum < 10' in Oracle, then SQL Server equivalent is 'select top 9 sno from test'.

The same way I need the equivalent for the above.

Thanks,
Sam

View 1 Replies View Related

How WHERE Clause Can Be Used In MDX Query?

Feb 1, 2007

Hi,

I am writing MDX query to retrive a set of data based on selected range of date.

I have written a MDX query but it is not filtering .

My code:

SELECT NON EMPTY { [Measures].[Fact Table Count] } ON COLUMNS, NON EMPTY topcount({ ([Date Time1].[Date Time1].[Date Time1].ALLMEMBERS ) } ,1000)DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [Date Time1].[Date Time1].&[2006-01-25T05:53:07] : [Date Time1].[Date Time1].&[2006-02-25T15:53:56] ) ON COLUMNS FROM [Cube Analysis]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS



This code should display only data with selected range of date but it displaying all data.

Can any one give a solution to filter the data based on Date using WHERE clause.



Thank you.

View 2 Replies View Related

Using Having Clause Or Views For SQL Query

Sep 5, 2007

Does anyone know how to count rows of data from 1 table that meet one or two different criteria and then get the probability of occurance for that criteria ... as an output column?
Do I use 'Having' or temp tables or Views?
Here is my output for now.  I am trying to solve for 'Prob' - everything else works on it's own.Craig
 
SELECT Wins_Lng/Trades_Lng as Prob
FROM Transactions
SELECT Count(Ticker) as Trades_Lng
FROM Transactions
WHERE TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND TransKind='B' (SELECT COUNT(Ticker)as Wins_Lng
FROM TransactionsWHERE Transkind='B' AND TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND Profit_Lng>=0)
 
 
 
 
 

View 2 Replies View Related

Need Help With Query Statement 'IN' Clause.

Jun 29, 2005

I have a column in the database that stored moduleId that are seperated by '|' (pipes).  For Example: '527|343|454'

I need to add a where clause to a query that pulls the data based on a
ModuleId. For Example: select * from table where 527 in [column above]

Does anyone know how I can do this in a query?  Normally I could
use an IN statement, ex: select * from table where 527 in (527,343,454)

How can I get the column in that format?

Thanks for the help in advance,

KM

View 2 Replies View Related

Using DTS Parameterized Query &#39;IN&#39; Where Clause

Apr 30, 2002

I want to export an SQL Server table to an Excel Spreadsheet driven by a web interface.
I am using Cold Fusion to call a SQL Server Stored procedure. The SP accepts a variable (IDlist) from the web page and sets this to a Global Variable.

EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("outIDlist").Value', @outIDlist

The SP then executes a DTS package to export to Excel. The DTS package uses the Global variable in the SQL Query thus:

SELECT ...
FROM ...
WHERE tblPropertyRegister.IDProperty IN (?);

This works fine when I pass one single ID (@outIDlist = "20") into the stored procedure.
But it returns no records when I pass multiple IDs (@outIDlist = "19, 20, 21") into the stored procedure. It works fine also if I "hard code" the IDlist into the DTS query (eg WHERE tblPropertyRegister.IDProperty IN (19, 20, 21);).
The problem appears to be in the setting of the global variable in the stored procedure.

Has anyone had any experience with this? Any feed back would be greatly appreciated. TIA

Alan

View 2 Replies View Related

SQL Query Error With The AS Clause...

Dec 13, 2006

I got SQL Query error with this sql statement....


Code:


sSQL = "SELECT VIN, Year, MakeID AS 'Make', ModelID AS 'Model', Name AS 'Dealer', PhoneOne AS 'Phone', StockDate AS 'Stock Date', SoldDate AS 'Sold Date', RepairCost AS 'Repair Cost' FROM "
sSQL = sSQL & sView
sSQL = sSQL & " WHERE VIN = '" & sVIN & "'"



The error I got is invalid column 'MakeID' and invalid column 'ModelID'. I'm not familiar with the term "AS" in SQL so can anyone explain what's the problem here?

View 3 Replies View Related

Using IN Clause To Query Two Databases

Jan 16, 2005

Hello,

I've put together the following query, but it has been unsuccessful running it so far.


Code:

select * from Contractors.dbo.Contacts
where VendorNo in (select No_ from [BMIS Live Database].dbo.[BMIS Live Database$Vendor]
where [Name] like '%of%')



I receive the following error when I run it in Query Analyzer:


Code:

Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.



I'm trying to grab values from the column, "No_" in the Bmis.. database then only select data from the Contractors database if the VendorNo column holds one of the values grabbed from the Bmis.. database. I think my syntax is just completely wrong but hopefully someone might pick up on my mistakes. Any help would be greatly appreciated, thanks!

Alex

View 2 Replies View Related

Something Like An 'is Numeric' In The Where Clause? (was Query Help)

May 22, 2006

I've got a set of records that has a varchar data type with most of the info being numeric, i.e.

002563256
025636982
000025632

99% of the data is similar to this but there are a few oddbals with alpha characters:

a2532222

is there a way to put something like an 'is numeric' in the where clause?

View 1 Replies View Related

Group By Clause Query Help

Jul 23, 2005

This is my queryselect ano,max(date),a_subject from MY_TAB where table_name='xyz' andano=877group by a_subject,ano order by a_subjectANOmax(Date)A_Subject8772005-01-20 00:00:00.000Subject_18771900-01-01 00:00:00.000Subject_28772004-12-20 00:00:00.000Subject_38772005-01-19 00:00:00.000Subject_4--------------------------------------------------------------------------When I put the status column in, it fetches all the rows.select ano,max(date),a_subject,status from MY_TAB wheretable_name='xyz' and ano=877 group by a_subject,ano,status order bya_subjectANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8771900-01-01 00:00:00.000Subject_3Not Started8771900-01-01 00:00:00.000Subject_4Not Started8772005-01-19 00:00:00.000Subject_4Not Started-----------------------------------------------------------------------now what i want isANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8772005-01-19 00:00:00.000Subject_4Not StartedThanks a lot for your help.AJ

View 2 Replies View Related

ODBC Query ... Getting Where Clause

Apr 11, 2007

OK ... I am using UPS Worldship that issues an ODBC query to my MS2Kserver ... Worldship can query either a table or a view and retreiveshipping info for a supplied orderid.I need to create a DB table that will track the orderids requestedfrom Worldship so that I can stop doubleships. That is to set up afunction to allow the info to be sent only once to worldship.I need to execute a stored procedure to write to a table and enforcebiz logic.So .. I've created a view that Worldship can execute an ODBC queryagainst (v_upsPull) ... in which I guess the query issued will belike: SELECT * FROM v_upsPull WHERE orderid = 123456The view is:CREATE VIEW dbo.v_upsPullASSELECT * FROM OPENROWSET ( 'SQLOLEDB', '[db]'; '[user]'; '[password]','exec sp_ups_pull')When the ODBC query calls the view the sp_ups_pull store procedurer isexecuted.However ... I do not have access to the original Where clause in theODBC query in the stored procedurer.Is there a way I can get access to the ODBC Where clause and pass itinto the stored procedurer?If not is there some other way I can create a DB table and run aselect against it ... based on the Worldship query?

View 3 Replies View Related

SQL Query Help-- Order By Clause

Jul 20, 2005

HiI want a simple select query on a column-name (smalldatetime) withvalues dislayed in desc order with null values FIRST.i.e.Select orderdate from ordersorder by ( null values first and then orderdate in desc order)could any one please helpThanks

View 7 Replies View Related

Query Cannot Be Updated Because The FROM Clause

Mar 27, 2007

Hi everybody,



I have a problem. My provider(ISP) is supporting SQL Native Client driver and my forum supplier is only supporting SQLOLEDB. I am trying to access our sql2005 DB located at our ISP.


I have changed this line:
strCon = "Provider=SQLOLEDB;Connection Timeout=90;" & strCon
To:
strCon = "Driver={SQL Native Client};Connection Timeout=90;" & strCon

Now I can access the database, but when I am trying to loging I get this error:
Server Error in Forum Application
An error has occured while writing to the database.
Please contact the forum administrator.

Support Error Code:- err_SQLServer_loginUser()_update_USR_Code
File Name:- functions_login.asp

Error details:-
Microsoft OLE DB Provider for ODBC Drivers
Query cannot be updated because the FROM clause is not a single simple table name.



What can I do?? I am stuck in between and need a solution.....

Regards Gerry!


View 2 Replies View Related

Need An Special ORDER BY Clause Query

Feb 25, 2008

Table:ColumnsUsersList:UserID, UserName, Country
I need a query which select all the rows from the above mentioned table with all fieldsButThe order the rows is First all the users from "Pakistan"Second all the users from rest of the countries except "Pakistan" in ascending order
So the query first return all the users from Pakistan and the the users from rest of the world in ascending order.
Forexample,
1, ABC, USA2, XYZ, Saudi Arabia3, LMN, Pakistan4, TQR, India5, PTR, Afghanistan
then the query returns.
3, LMN, Pakistan5, PTR, Afghanistan4, TQR, India2, XYZ, Saudi Arabia1, ABC, USA

View 5 Replies View Related

Problem With My Where Clause (long Query)

Mar 1, 2008

Hello,
I have to update a query that was given to me so that it displays only items that were created in a certain month and certain year (which I prompt the user for). I hard-coded a month and year to test (2008 year, 2 month). My results are including other months I'm not asking for.
I have marked my problems areas with /* Problem 1 */ and /* Problem 2 */ (same where clause n 2 locations).|What am I doing wrong? Doesn't matter what year or month I enter, I am always getting extra data1st 3 columns of results (I removed the calculated fields)
 2008 1 Incoming2008 1 Both2008 2 Outgoing2008 2 Incoming2008 2 BothI can't for the life of me figure out how to fix this up.
query below...
================================
DECLARE @TheYear  integerDECLARE @TheMonth integer
SET @TheYear  = 2008SET @TheMonth = 2
SELECT * FROM ( SELECT    year(startime)  as yearstart,  month(startime) as monthstart,  directioncodename
, count(CASE
            WHEN  new_issuecategoryname is null
                  THEN activitycountvalue
            END ) as nullcall
, count(CASE
            WHEN  new_issuecategoryname='ACDelco Comment'
                  THEN activitycountvalue
            END ) as acdelcocommentcall
, count(CASE
            WHEN
            new_issuecategoryname='ACDelco General Inquiry'
                  THEN activitycountvalue
            END ) as acdgeneralinquirycall
, count(CASE
            WHEN 
            new_issuecategoryname='ACDelco PPD'
                 
                  THEN activitycountvalue
            END ) as acdppdcall
, count(CASE
            WHEN 
            new_issuecategoryname='ACDelco Promotion'
                 
                  THEN activitycountvalue
            END ) as acdpromotioncall
, count(CASE
            WHEN
            new_issuecategoryname='ACDelco Promotion - Calendar'
                 
                  THEN activitycountvalue
            END ) as acdpromotioncalendarcall
, count(CASE
            WHEN 
            new_issuecategoryname='ACDelco TSS Program Enquiry'
                 
                  THEN activitycountvalue
            END ) as tssprogramenquirycall
, count(CASE
            WHEN 
                        new_issuecategoryname='ACDelco TSS Promotion'
                 
                  THEN activitycountvalue
            END ) as tsspromotioncall
, count(CASE
            WHEN 
            new_issuecategoryname='ACDelco Training'
           
                  THEN activitycountvalue
            END ) as acdtrainingcall
, count(CASE
            WHEN 
                  new_issuecategoryname='ACDelco TSS Benefits'
                 
                  THEN activitycountvalue
            END ) as tssbenefitscall
, count(CASE
            WHEN 
                  new_issuecategoryname='ACDelco Other'
                 
                  THEN activitycountvalue
            END ) as acdothercall
, count(CASE
            WHEN 
            new_issuecategoryname='ACDelco TAC Number'
                 
                  THEN activitycountvalue
            END ) as acdtacnumbercall
, count(CASE
            WHEN
            new_issuecategoryname='ACDelco PPD Promotion'
 
                  THEN activitycountvalue
            END ) as acdppdpromotioncall
, count(CASE
            WHEN 
      new_issuecategoryname='RealRewards - ISC'
                 
                  THEN activitycountvalue
            END ) as realrewardsisccall
, count(CASE
            WHEN
                  new_issuecategoryname='RealRewards - CounterPerson'
                 
                  THEN activitycountvalue
            END ) as realrewardscounterpersonscall
, count(CASE
            WHEN 
                        new_issuecategoryname='ACDelco Event'
                 
                  THEN activitycountvalue
            END ) as acdelcoeventcall
, count(CASE
            WHEN 
            new_issuecategoryname='ACDelco TSS Lead'
                 
                  THEN activitycountvalue
            END ) as acdtssleadcall
, count(CASE
            WHEN
            new_issuecategoryname='ACDelco TSS Other'
                  THEN activitycountvalue
            END ) as acdtssothercall
, count(CASE
            WHEN  new_issuecategoryname is not null
                  and   new_issuecategoryname!='ACDelco Comment'
                  and   new_issuecategoryname!='ACDelco General Inquiry'
                  and   new_issuecategoryname!='ACDelco PPD'
                  and   new_issuecategoryname!='ACDelco Promotion'
                  and   new_issuecategoryname!='ACDelco Promotion - Calendar'
                  and   new_issuecategoryname!='ACDelco TSS Program Enquiry'
                  and   new_issuecategoryname!='ACDelco TSS Promotion'
                  and   new_issuecategoryname!='ACDelco Training'
                  and   new_issuecategoryname!='ACDelco TSS Benefits'
                  and   new_issuecategoryname!='ACDelco Other'
                  and   new_issuecategoryname!='ACDelco TAC Number'
                  and   new_issuecategoryname!='ACDelco PPD Promotion'
                  and   new_issuecategoryname!='RealRewards - ISC'
                  and   new_issuecategoryname!='RealRewards - CounterPerson'
                  and   new_issuecategoryname!='ACDelco Event'
                  and   new_issuecategoryname!='ACDelco TSS Lead'
                  and   new_issuecategoryname!='ACDelco TSS Other'
                  THEN activitycountvalue
            END ) as othercall
,count(activitycountvalue) as totalcall
 
FROM (
select
            startime =
                  CASE
                        WHEN filteredphonecall.new_cmgstartdatetime is not null
                              THEN filteredphonecall.new_cmgstartdatetime
                        WHEN filteredphonecall.actualstart is not null and filteredphonecall.new_cmgstartdatetime is null
                              THEN filteredphonecall.actualstart
                        ELSE
                              filteredphonecall.createdon        
                        END  
            , 1 as activitycountvalue
            , new_issuecategoryname
            , new_issuecategory
            , 'phone call' as activitytypecodename
            , filteredphonecall.new_languagename
            , filteredphonecall.directioncodename
from   /* PROBLEM 1 */                filteredphonecall
  WHERE   (    (      filteredphonecall.new_cmgstartdatetime is not null      AND        (Year(filteredphonecall.new_cmgstartdatetime)  = @TheYear)      AND         (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth)     )
    OR                  (     filteredphonecall.actualstart is not null      AND     (Year(filteredphonecall.new_cmgstartdatetime)  = @TheYear)     AND        (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth)    )      OR    (     filteredphonecall.actualstart is not null     AND      (Year(filteredphonecall.actualstart)  = @TheYear)    AND       (Month(filteredphonecall.actualstart) = @TheMonth)    )
    OR    (     filteredphonecall.createdon is not null     AND       (Year(filteredphonecall.createdon)  = @TheYear)     AND        (Month(filteredphonecall.createdon) = @TheMonth)    )  )
)as phoneactivities
GROUP BY  year(startime), month(startime), directioncodename
 
UNION ALL
 
/* KATHY1 */
SELECT  year(startime) as yearstart
, month(startime) as monthstart
,'Both' as directioncodename
, count(CASE
            WHEN  new_issuecategoryname is null
                  THEN activitycountvalue
            END ) as nullcall
, count(CASE
            WHEN  new_issuecategoryname='ACDelco Comment'
                  THEN activitycountvalue
            END ) as acdelcocommentcall
, count(CASE
            WHEN
            new_issuecategoryname='ACDelco General Inquiry'
                 
                  THEN activitycountvalue
            END ) as acdgeneralinquirycall
, count(CASE
            WHEN 
            new_issuecategoryname='ACDelco PPD'
                 
                  THEN activitycountvalue
            END ) as acdppdcall
, count(CASE
            WHEN 
            new_issuecategoryname='ACDelco Promotion'
                 
                  THEN activitycountvalue
            END ) as acdpromotioncall
, count(CASE
            WHEN
            new_issuecategoryname='ACDelco Promotion - Calendar'
                 
                  THEN activitycountvalue
            END ) as acdpromotioncalendarcall
, count(CASE
            WHEN 
            new_issuecategoryname='ACDelco TSS Program Enquiry'
                 
                  THEN activitycountvalue
            END ) as tssprogramenquirycall
, count(CASE
            WHEN 
                        new_issuecategoryname='ACDelco TSS Promotion'
                 
                  THEN activitycountvalue
            END ) as tsspromotioncall
, count(CASE
            WHEN 
            new_issuecategoryname='ACDelco Training'
           
                  THEN activitycountvalue
            END ) as acdtrainingcall
, count(CASE
            WHEN 
                  new_issuecategoryname='ACDelco TSS Benefits'
                 
                  THEN activitycountvalue
            END ) as tssbenefitscall
, count(CASE
            WHEN 
                  new_issuecategoryname='ACDelco Other'
                 
                  THEN activitycountvalue
            END ) as acdothercall
, count(CASE
            WHEN 
            new_issuecategoryname='ACDelco TAC Number'
                 
                  THEN activitycountvalue
            END ) as acdtacnumbercall
, count(CASE
            WHEN
            new_issuecategoryname='ACDelco PPD Promotion'
 
                  THEN activitycountvalue
            END ) as acdppdpromotioncall
, count(CASE
            WHEN 
      new_issuecategoryname='RealRewards - ISC'
                 
                  THEN activitycountvalue
            END ) as realrewardsisccall
, count(CASE
            WHEN
                  new_issuecategoryname='RealRewards - CounterPerson'
                 
                  THEN activitycountvalue
            END ) as realrewardscounterpersonscall
, count(CASE
            WHEN 
                        new_issuecategoryname='ACDelco Event'
                 
                  THEN activitycountvalue
            END ) as acdelcoeventcall
, count(CASE
            WHEN 
            new_issuecategoryname='ACDelco TSS Lead'
                 
                  THEN activitycountvalue
            END ) as acdtssleadcall
, count(CASE
            WHEN
            new_issuecategoryname='ACDelco TSS Other'
                  THEN activitycountvalue
            END ) as acdtssothercall
, count(CASE
            WHEN  new_issuecategoryname is not null
                  and   new_issuecategoryname!='ACDelco Comment'
                  and   new_issuecategoryname!='ACDelco General Inquiry'
                  and   new_issuecategoryname!='ACDelco PPD'
                  and   new_issuecategoryname!='ACDelco Promotion'
                  and   new_issuecategoryname!='ACDelco Promotion - Calendar'
                  and   new_issuecategoryname!='ACDelco TSS Program Enquiry'
                  and   new_issuecategoryname!='ACDelco TSS Promotion'
                  and   new_issuecategoryname!='ACDelco Training'
                  and   new_issuecategoryname!='ACDelco TSS Benefits'
                  and   new_issuecategoryname!='ACDelco Other'
                  and   new_issuecategoryname!='ACDelco TAC Number'
                  and   new_issuecategoryname!='ACDelco PPD Promotion'
                  and   new_issuecategoryname!='RealRewards - ISC'
                  and   new_issuecategoryname!='RealRewards - CounterPerson'
                  and   new_issuecategoryname!='ACDelco Event'
                  and   new_issuecategoryname!='ACDelco TSS Lead'
                  and   new_issuecategoryname!='ACDelco TSS Other'
                  THEN activitycountvalue
            END ) as othercall
,count(activitycountvalue) as totalcall
 
FROM (
select
            startime =
                  CASE
                        WHEN filteredphonecall.new_cmgstartdatetime is not null
                              THEN filteredphonecall.new_cmgstartdatetime
                        WHEN filteredphonecall.actualstart is not null and filteredphonecall.new_cmgstartdatetime is null
                              THEN filteredphonecall.actualstart
                        ELSE
                              filteredphonecall.createdon        
                        END  
            , 1 as activitycountvalue
            , new_issuecategoryname
            , new_issuecategory
            , 'phone call' as activitytypecodename
            , filteredphonecall.new_languagename
            , filteredphonecall.directioncodename
from                 filteredphonecall
  /* PROBLEM  2 */                filteredphonecall
  WHERE   (    (      filteredphonecall.new_cmgstartdatetime is not null      AND        (Year(filteredphonecall.new_cmgstartdatetime)  = @TheYear)      AND         (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth)     )
    OR                  (     filteredphonecall.actualstart is not null      AND     (Year(filteredphonecall.new_cmgstartdatetime)  = @TheYear)     AND        (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth)    )      OR    (     filteredphonecall.actualstart is not null     AND      (Year(filteredphonecall.actualstart)  = @TheYear)    AND       (Month(filteredphonecall.actualstart) = @TheMonth)    )
    OR    (     filteredphonecall.createdon is not null     AND       (Year(filteredphonecall.createdon)  = @TheYear)     AND        (Month(filteredphonecall.createdon) = @TheMonth)    )  )
)as phoneactivities
GROUP BY  year(startime), month(startime)
) as orderedresults
order by  yearstart, monthstart, directioncodename DESC;

View 1 Replies View Related

SQL Query Problem In Group By Clause

Jan 26, 2004

I am developing Staff Allocation System,
database is sql server 2000.
I have problem in retrieve the staff informations,

employee working which Project and what project have assign to him, what is his assign project or contract no,

One employee working more then one project, retrieve information one employee how many projects are working,

What is his approved position, what is his assign position.
It the main data have to retrieve, as well as retrieve all fields which related to those tables.

I use this query.

select name,apppos approved_position,appcont approved_contract,appdate employee_appr_date,munref Municipality_Ref,dcilref DCIL_REF,projtype Project_Type,strdate Project_str_date,comdate Projcet_comp_date,extdate Proejct_ext_date,dept,emptype Employee_Type from contract,emp,apprecords where contract.rec_id=emp.rec_id and emp.rec_id=apprecords.rec_id and apprecords.name='dewachi'


above query retrieve no data,

how can use group by clause in the above query ?

group by apprecords.appcontract

group by clause give error.

above query have to retrieve data from the three tables, I have four tables, what query I use so that all four tables data retrieve like this.


Name, approved_position, approved_contract,assign_position,assign_contract,startdate,completion_date,........ and so on…
Group by apprecords.appposition
……….


Contract Table (basic data entry contract table)
-------------------------------------------------------
rec_id
Contract No.
ProjectType
StartDate
CompletionDate
ExtendedDate


Employee Table (basic data entry employee table)
---------------------------------------------------------
rec_id
EmpNo
Name
Position
Department
EmployeeType


Approved Records Table (in this table all information about
the employee and his approved
position and contract )
------------------------------------------------------------------------
rec_id
Name
Approved Date
MunicipalityRefNo
DCILRefNo
ApprovedPosition
ApprovedContract


Assign Project Table (in this table all information about the
employee his assign the project)
--------------------------------------------------------------------
rec_id
Name
AssignPosition
AssignContract
EmpProjectStartDate
EmpProjectEndDate
ShiftNo
ProjectStatus



Regards.


MATEEN

View 6 Replies View Related

GROUP By Clause, /sub Query Problems

Aug 15, 2005

I'm trying to list salesreps (if they have any sales for a particular date) with their total sales amounts for a queried date, but when running this sql string in QueryAnalyzer, it says there is an error with syntax on Line 1 near "s" :SELECT o .Rep_ID, o .ID, s.ID, SUM(b.orderamount) AS totalsales, b.order_ID
FROM (SELECT b.Deal_ID
FROM btransactions b
WHERE b.BoardDate = '20050815') SalesReps s INNER JOIN
orders o ON o .Rep_ID = s.ID INNER JOIN
b ON o.ID = b.Deal_ID
GROUP BY d .Rep_ID, d .ID, s.ID, b.order_ID
HAVING (SUM(b.orderamount) > 0)???.NetSports

View 1 Replies View Related

Computed Column In Where Clause - QUERY Help

Apr 29, 2007

Hi
I need suggestion for a query. Consider following 2 tables.

Table-1 "T1"
-----------
|ID|Name
|1 |abc
|2 |def
|3 |erw
|4 |rwg
|5 |her


Table-2 "T2"
----------
|ID|Qty
|1 |12
|1 |2
|2 |22
|3 |10
|2 |14


I want a query which displays ID, Name and MAX(Qty) for each item where Max(Qty)>=10 i.e. result should be


Result
----------
|ID|Name|Qty
|1 |abc |12
|2 |def |22
|3 |erw |10

I tried:

Select t1.*, (Select Max(Qty) From T2 where ID=t1.ID) as MaxQty
FROM T1 t1
WHERE MaxQty>=10

But it fails as computed or inline query columns can not be added in where clause.


However following works:
Select t1.*, (Select Max(Qty) From T2 where ID=t1.ID) as MaxQty
FROM T1 t1
WHERE (Select Max(Qty) From T2 where ID=t1.ID) >=10

BUT IS IT OPTIMIZED?
Please suggest an optimized way to handle such scenarios.

View 1 Replies View Related

Four Different Values In The 'where' Clause (was SQL Query Question)

Sep 28, 2006

Hello everyone. This is my first post here, so be gentle =)

I need to construct a query, that would return roughly 6000 rows of data.

There are some conditions, or joins, that I can't figure out. Maybe you could help me?

This is the first one.

Invoice.ID-DocParty.DocumentID -> DocParty.OtherID-Party.ID -> Party.IDNumber

This can be achieved with inner join, no problem. Pretty simple.

However, there's a catch =)

DocParty.Role can have four different values in the 'where' clause. Is there a
way to fetch all of these four values without returning four duplicates with
only one field differing?

There are multiple fields in the query that are to be fetched in similar ways. Therefore,
using a IN('value1','value2','value3','value4') would increase the number of selected rows
a lot.

In addition, there is another type of condition that needs to be fullfilled.

Invoice.Type1Account-Account.ID -> Account.Number
Invoice.Type2Account-Account.ID -> Account.Number

Basically, there two fields in the 'main' table that are joined to the same field in another table
with different conditions. Can this be fetched with the same row as all the other data without duplicates?

Should I use a view somehow? How can I construct a view with these complex conditions if I can't
construct an SQL query, that would return no duplicates (pseudo-du

View 13 Replies View Related

Joins Vs Where Clause - Performance Query

Dec 5, 2006

Hi There !!

To finetune performance for some of our queries,

I have come across suggestions to use

- JOINS instead of WHERE clause wherever possible
- and avoid using Aliases

Although Avoiding aliases looks reasonable I am yet to be convinced about JOINS replacing the WHERE CLAUSE . What is the experts take on this one ????

Also,

I checked the estimated plan in SQL server by running the following 2 queries into my Query Designer

tables : dba ( empid, empname )
project ( project_empid references dba.empid, project_name )


USING A WHERE CLAUSE and Alias
-------------------------
select a.emp_name from dbo.dba a, dbo.project b
where
a.empid =b.project_emp
and b.project_name is not null

USING A JOIN
-----------------
select emp_name from dbo.dba
as
a inner JOIN dbo.project
ON empid = dbo.project.project_emp
AND dbo.project.project_name is not NULL

******

I find from the Estimated plan that both the queries give the same amount of cost ( I/O, CPU, et all ) :shocked:

Any comments/ suggestions.

Thanks,

Have a great time
-Ranjit.

-------------------------------------
It pays to be honest to your DBA

View 4 Replies View Related

UPDATE Query With ORDER BY Clause

May 7, 2012

I could write a query with a sub-query in order to perform an UPDATE on the most recent 60,000 records of a table based on a date field, but unfortunately I am receiving an error.

Code:
SELECT * FROM DMTM
SET transmit_date = '2012-05-07 00:00:00.000', transmit_status = '1223'
WHERE temp_pk in

[code]...

View 6 Replies View Related

Adding A Count Clause To A Query

Jun 5, 2008

I have the following query where I select records from Active_Activities_temp which do not match on cde_actv in the table ACTIVITY_CORE_LISTING:
SELECT Active_Activities_temp.*
FROM Active_Activities_temp LEFT JOIN
ACTIVITY_CORE_LISTING ON
Active_Activities_temp.cde_actv=ACTIVITY_CORE_LISTING.cde_actv
WHEREACTIVITY_CORE_LISTING.cde_actv is null
ORDER BY prtcpnt_id
So for example, if a participant has a cde_actv=38 (which doesn't exist in ACTIVITY_CORE_LISTING), that record would appear as the query is currently.

The issue is that participants can have multiple records in Active_Activities_temp and if a participant has a record that does exist in ACTIVITY_CORE_LISTING, no records for that participant should appear in this query result. For example, if a participant has two records in Active_Activities_temp, one with a cde_actv 38 (which does not appear in ACTIVITY_CORE_LISTING) and one with a cde_actv 33 (which does appear in ACTIVITY_CORE_LISTING), no records for that participant should appear in the result. Currently the record with cde_actv=38 does appear.

What code can I implement to do what I need to do? Thanks so much.

View 5 Replies View Related

Query Based On Group By Clause

Jun 21, 2008

Hello!
suppose i have two tables, table1 columns(empcode (pk), empDept) and table2 columns(empcode (FK),Date,Attendance) i wanted to write a query to get output like
DEPT ABSENT
-----------------------------
Accounts 10
EDP Section 0 **
Admin 2
Stationary 0**

if no employee is absent in the department it has to display Zero

View 2 Replies View Related

Query Performance With Order By Clause?

Jul 20, 2005

Hi all,Just wondering if anyone can tell me if an order by clause on a selectquery would have any impact on the time it takes to retrieve results?Essentially I'm selecting Top 1 out of a table via various criteriaand currently getting it back without an order by clause. The order bywould only include the column that has the clustered primary index onit.Can anyone tell me if in theory this will slow the query down?Many thanks in advance!Much warmth,Murrau

View 1 Replies View Related

Single Update Query Using In Clause In SP

Jan 4, 2008

Hello,

I'm collecting id based on user's selection and storing to ArrayList/String[in asp.net]. Now want to pass those ids stored to stroed procedure. I want SP to update all records in single statement using in like update table set field in (value).

I'm storing id with comma (,) separator. Below is my asp.net code:


string sId=string.Empty;

for(int a=0;a<Id.Count;a++)

{

Id += Id;

if(a!=Id.Count-1)

sId +=",";

}

In database id column is int type. I'm passing id collected with comma separator and passing it as a parameter to SP. but it is giving error related converting to int or similar.

Plz help me create Stored Procedure in SQL 2005 based on above scenario.

Thanks

View 7 Replies View Related

SQL Query Help - Right Outer Join With Where Clause

Aug 23, 2007

Using SQL Server Reporting services 2005

I am reporting on a system with 32 devices, each of these devices can have certain events that happen to it that are logged and timestamped.
I need a table to show the count of each events that have happened to it within a certain time period.
This code snippet below works fine BUT if there are no events that happen to a certain device in the time period, then that device is 'missing' from the table.
What I need is basically a row for every device, regardless of if it has had any events happen to it (I will just show '0' for the event count)
Any thoughts? I'm a complete newbie at this by the way.

Thanks




Code Snippet




SELECT DeviceStatusWords.DeviceName, COUNT(DeviceEventDurationLog.StatusBit) AS BitCount, DeviceEventDurationLog.StatusBit AS Bit
FROM DeviceEventDurationLog RIGHT OUTER JOIN
DeviceStatusWords ON DeviceEventDurationLog.DeviceID = DeviceStatusWords.DeviceID
WHERE (DeviceEventDurationLog.TimeIn > @StartDate) AND (DeviceEventDurationLog.TimeIn < @EndDate)
GROUP BY DeviceStatusWords.DeviceName, DeviceEventDurationLog.StatusBit
ORDER BY DeviceStatusWords.DeviceName

View 7 Replies View Related

Lookup Object With Where Clause In Query

Feb 25, 2008



Hi,

I have a Dimension Dim_Customer with these fields:

CustomerKey (mandatory)
CustomerAccount
CompanyKey (mandatory)

I used the lookup object to compare if the data from the working table (Dim_WCustomer) is existing in the Dim_Customer.

The reference column is Customer Account. However, the records with CustomerKey 1 to 5, which have special function when linked to fact table, have no customer account (NULL) that's why i want to filter that out from the lookup.

This is the select statement in my lookup: Select CustomerKey, CustomerAccount where CustomerKey > 5. When I previewed this, i don't see the 1-5 customerkey. However, when I ran the data flow, it gives me this error:

OnError,,,Add new records to Dim_Customer,,,25/02/2008 6:43:52 PM,25/02/2008 6:43:52 PM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E2F.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Customer'. Cannot insert duplicate key in object 'dbo.Dim_Customer'.".


When I removed the 1-5 customers in the database, it worked well. It seems "where" clause in lookup doesn't function well....

anyone who can help?

che

View 4 Replies View Related

Can't Use Stored Procedure In Query Where Clause???

Jan 17, 2008



Hi,

By reading answers on the web I have found out that I can't use a stored procedure in a where clause of my query, but I can use a User defined function. This almost fits my needs but not quite. The function would work great if it could insert the results of its query into our cache table but you can't insert stuff into external tables to the function.

The problem is that our stored procedure/function does looping to find parent objects way back up the tree to find out permissions for certain records. Since the stored procedure and function do so much querying to find the root most object that has permissions set there is a lot of reads in our call. We would like to cache this process so that next time they look for permissions it only does one read first. But in order for our caching to work the function needs to insert the results it found in our cache table which it can't do and the stored procedure can't be used in a where clause so that doesn't work. Any suggestions?

Query looks like this, the query is built on the fly through code.

select Title, Descriptions FROM defects df WHERE dbo.fnHasProjectRights(df.ProjectID);

and that function first checks the cache table to see if it has ran before for that projectID and if not then starts doing all its logic to get permissions.

Any suggestions how to approach this? I just wish functions could insert and or stored procedures could be used in the where clause since they can insert.

Thanks,

View 7 Replies View Related

Using Group By Clause In Union Query

Sep 19, 2007

Hi all,

I have two table having datas like

Table1
--------------------------------------------------------------------
A C1 C2 C3 C4
--------------------------------------------------------------------
x 0 0 3 2
x 0 1 0 2
x 0 0 2 1
y 1 5 2 0


Table2
--------------------------------------------------------------------
A C1 C2 C3 C4
--------------------------------------------------------------------
x 0 0 1 4
y 1 0 3 1
y 1 2 0 0
y 0 0 5 1

select * from(
select A,C1,C2,C3,C4 from Table1 group by A
union
select A,C1,C2,C3,C4 from Table2 group by A
)as t

Result:
--------------------------------------------------------------------
A C1 C2 C3 C4
--------------------------------------------------------------------
x 0 1 5 5
y 1 5 2 0
x 0 0 1 4
y 2 2 8 2

But i need the result like i.e grouped by column 'A'
--------------------------------------------------------------------
A C1 C2 C3 C4
--------------------------------------------------------------------
x 0 1 6 9
y 3 7 10 2

select * from(
select A,C1,C2,C3,C4 from Table1 group by A
union
select A,C1,C2,C3,C4 from Table2 group by A
)as t group by A

The above query gives the following error
[Error Code: 8120, SQL State: S1000] Column 't.C1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Please help me out.

-Anand

View 3 Replies View Related

Analysis :: MDX Query Parameters In WHERE Clause?

Sep 25, 2015

I need to filter my select statement with 2 parameters, each of which defaults to the "All" member.  Some of the members may have spaces in the name.

So I need to handle something like these:

FROM MySalesCube
WHERE ( ... expression here ...)

The expression above  needs to resolve to a set using both parameters

 STRTOSET("[Dept].[Dept][" + @pDept + "] , [Salesperson].[SalesPerson].[" + @pSalesPerson + "]")

Running the query in SSMS, Im using something like this to test

WHERE ( [SalesPerson].[SalesPerson].&[17]  , [Department].[Department].[All] )

I just need to use the above in an expression with parameters, where BOTH SalesPerson and Department could use a specific member OR use the All member.

[update]

Here is what I have now:

WHERE ( STRTOSET(CSTR("([SalesPerson].[SalesPerson].[" + @pSalesPerson +"]  , [Department].[Department].[" + @pDept +  "])")  ) )

If I hard-code @pDept to something in a string, it works ok, otherwise, I simply get an error

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved