Help Needed With Query Syntax

Nov 30, 2005

Hi

Can someone please tell me whats wrong with the last line of the query
below. The first three lines work fine but when i add the fourth line i get
an error message (see text at ERROR MESSAGE)

sql_HTTermijnRecords = "select * from Orders where FaktuurGeprint =
'J'" & _
"AND dathergestuurd Is Not Null " & _
"AND PerBankKas Is Null " & _
"AND " & HTdatumMinAantalDagen & " > " & dathergestuurd

The last line is a comparison between two dates.

ERROR MESSAGE
============================================
Run-time error 3075

Syntax error (missing operator) in query expression 'FaktuurGeprint =
'J' AND dathergestuurd Is Not Null AND PerBankKas Is Null AND
22-11-2005 >'

========= end error message==============================

The first date (22-11-2005) is visible in the error message but the second
date is missing.
As you can see there is nothing after the > but there should be date
information from a DB-cell named dathergestuurd.
I tried a lot of things but i keep getting the same message.

What is wrong with the syntax of the last line ???

T.i.a.

Regards

Tino Wintershoven
The Netherlands

View 4 Replies


ADVERTISEMENT

SQL Syntax Help Needed

Sep 26, 2006

Hi, I have a simple problem that I need a quick solution for. I hope someone can help.I have a list of email addresses that contain many different email providers (hotmail, yahoo, gmail, etc)What I'm trying to do, it select all emails that are not from a select list of providers...Here is the code I have so far... (this isnt giving me what I want)SELECT EmailAddress as Email FROM TBL_EmailAddresses WHERE EmailAddress <> '%aim.com%' OR EmailAddress <> '%hotmail.com%'OR EmailAddress <> '%msn.com%'OR EmailAddress <> '%yahoo.com%'OR EmailAddress <> '%gmail.com%'OR EmailAddress <> '%aol.com%'I want to select all emails that are not of the above types. Thanks in advance.  

View 5 Replies View Related

Syntax Needed

Jul 4, 2007

I am querying a SQL Server 2000 server remotely and have couple of small syntax problems using the WHERE Clause in a Select statement

1. I wish to extract all records where the first 2 characters of a Varchar field (postcode) do not start with 2 specific characters (BT)

2. I wish to extract all records where a Char field is not blank (ie has at least 1 character in it)

Many thanks

Mike

View 3 Replies View Related

Syntax Help Needed

Dec 26, 2007

I cannot work out what I need to do to fix the query attached. I am getting the error Line 105: Incorrect syntax near ')'.

Thank you for your helpCREATE PROCEDURE spMHCPall
(

@type int
)
AS

Select
fname, lname,
dtmenroll,
ysnenroll,
lngtype,
lngprimaryprovider ,
Type,
strreason,
provider,
lngenrollmentid,
encounterdate,
encounterdate2,
countencounter,
countlocus,
counttplan,
countmrpt,
counttplanCBT,
counttplanCBT16,
countgroupattenance,
dtmbaseline,
dtmenrollment,

satsurvey,

encountercount,
countcontact,
contactdate,
psymdconsults,
countQOLSurvey,
lngpatientid,


Case When
( Case When
(
Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01')
then IsNull(Date1,'1900-01-01')
Else IsNull(Date2,'1900-01-01')
End )>IsNull(Date3,'1900-01-01')
Then
(
Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01')
then IsNull(Date1,'1900-01-01')
Else IsNull(Date2,'1900-01-01')
End )
Else
IsNull(Date3,'1900-01-01')
End)>IsNull(Date4,'1900-01-01')
Then
( Case When
(
Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01')
then IsNull(Date1,'1900-01-01')
Else IsNull(Date2,'1900-01-01')
End )>IsNull(Date3,'1900-01-01')
Then
(
Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01')
then IsNull(Date1,'1900-01-01')
Else IsNull(Date2,'1900-01-01')
End )
Else
IsNull(Date3,'1900-01-01')
End)
Else
IsNull(Date4,'1900-01-01')
End MaxDate,
Case When COALESCE(Date1,Date2,Date3,Date4) > DATEADD(M,-1,CONVERT(CHAR(8),GETDATE(),112)) then 'false' When COALESCE(Date1,Date2,Date3,Date4) is null then 'false' else 'true' end ysncontact
From
( Select A.lngpatientid, ysnenroll,lngtype, lngprimaryprovider, strreason,dtmenroll, lngenrollmentid,
'Date2'=(Select max(dtmcontact) FROM tblMHCPcontact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type ),
'Date4'=(Select max(B.dtmenroll) FROM tblMHCPEnrollment B where A.lngpatientid=B.lngpatientid and B.lngtype = @type),
'Date3'=(Select max(visitdate) FROM tblMHCPEncounter B where A.lngpatientid=B.lngpatientid and A.lngtype = @type ),
'psymdconsults'=(Select count(visitdate) FROM tblMHCPEncounter B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and B.providerid = '1'),
'countlocus'=(Select case when count(dtmdate)= 0 then 'N' else 'Y' end FROM tblMHCPLOCUS B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmdate between dateadd(month, -6, getdate()) and getdate() ),
'counttplan'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPtreatmentplan B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmstart between dateadd(month, -6, getdate()) and getdate()),
'countmrpt'=(Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and dtmdate between dateadd(month, -1, getdate()) and getdate()),
'counttplanCBT'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and dtmintake between dateadd(month, -1, getdate()) and getdate()),
'counttplanCBT16'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and dtmintake between dateadd(month, +3, getdate()) and getdate()),
'countgroupattenance'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPGroupAttenance B where A.lngpatientid=B.lngpatientid and dtmgroup between dateadd(day, +7, getdate()) and getdate()),
'contactdate'=(Select max(dtmcontact) FROM tblMHCPContact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmcontact between dateadd(day, -7, getdate()) and getdate()),
'dtmbaseline'=(Select max(dtmintake) FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and ysnbaseline = 1),
'dtmenrollment'=(Select max(dtmenroll) FROM tblMHCPenrollment B where A.lngpatientid=B.lngpatientid and A.lngtype=b.lngtype and ysndisenroll is null),

'contactcount'=(Select case when count(dtmcontact)= 0 then 0 else 1 end FROM tblMHCPContact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmcontact between dateadd(day, -7, getdate()) and getdate()),
'countcontact'=(Select count(dtmcontact) FROM tblMHCPContact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmcontact between dateadd(day, -7, getdate()) and getdate()),
'satsurvey'=(Select case when count(dtmSurvey)= 0 then 'N' else 'Y' end FROM tblMHCPClientSat B where A.lngpatientid=B.lngpatientid and dtmSurvey between dateadd(day, -70, getdate()) and getdate()),
'encountercount'=(Select case when count(visitdate)= 0 then 0 else 1 end FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate()),
'encounterdate'=(Select Max(visitdate) FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate()),
'countencounter'=(Select count(visitdate)FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate()),
'countSatSurvey'=(Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPSurveyQOL B where A.lngpatientid=B.lngpatientid and dtmdate between dateadd(month, -6, getdate()) and getdate()),
'countQOLSurvey'=(Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPSurveyQOL B where A.lngpatientid=B.lngpatientid and dtmdate between dateadd(month, -6, getdate()) and getdate()),
'Date1'=(Select max(dtmgroup) FROM tblMHCPGroupAttenance B where A.lngpatientid=B.lngpatientid and A.lngtype = @type),
'fname' = (Select strfname FROM tblPatient as P where A.lngpatientid=P.lngpatientid ) ,
'lname' = (Select strlname FROM tblPatient as P where A.lngpatientid=P.lngpatientid ) ,
'Type' = (Select strtype FROM tbllkpMHCPEnrollment as P where A.lngtype=P.lngtype ),
'Provider' = (Select fname + ' ' + lname as pName FROM tbllkpMHCPprovider as P where A.lngprimaryprovider=P.staffid)
FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate()))

From tblMHCPEnrollment A where A.lngtype = @type and ysnDisEnroll IS NULL) AS lngpatientidDate

View 4 Replies View Related

SQL Syntax Assistance Needed For A Wildcard

Dec 20, 2007

I have the following SQL Statement and I am trying to pass in a wildcard for the "Branch Parameter".  The Branch Parameter is a numeric field.
I need (Branch = %) wildcard to select all the records.  Can someone tell me the proper syntax for this?
SELECT     EmployeeID, CountryCode, FName, LName, Branch, Title, Status, Required, Total, PercentageFROM         dbo.vw_Compliance_Percentage_EmployeeWHERE     (Branch = @branch)ORDER BY Branch
 I receive the message failed to convert parameter value from string to an int32

View 7 Replies View Related

Syntax Error In FROM Clause - Help Needed Please!!

Jun 8, 2008

 Hi all,I am a newbie to asp and have been using VWD to make a database for an assignment but I am having big problems trying to extract some data to a datalist view. I intend to use this page to display all information of hotel rooms. I know its probably really obvious to some of you but its driving me mad!!! Any help would be greatly appreciated.     Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)        Dim ds As New DataSet()        Dim sGetRooms As String = "SELECT RoomID, RoomType, " _          & "RoomName FROM Rooms2 " _          & "WHERE RoomType LIKE @RoomType " _          & "ORDER BY RoomType"        Dim sGetroomsizeandprice As String = "SELECT ID, RoomSize, RoomPrice, @RoomType " _          & "FROM roomprices JOIN Rooms2 ON Rooms2.ID = roomprices.ID " _          & "WHERE RoomType LIKE @RoomType " _          & "ORDER BY RoomPrice"        Dim sConnect As String = ConfigurationManager.ConnectionStrings("White Sand's Hotel - Dan MahilConnectionString").ConnectionString        Using con As New OleDbConnection(sConnect)            Dim da As New OleDbDataAdapter(sGetRooms, con)            Dim param As New OleDbParameter("RoomType", OleDbType.VarChar, 10)            param.Value = Request.QueryString("RoomType") & "%"            da.SelectCommand.Parameters.Add(param)            Try                da.Fill(ds, "Rooms2")                da.SelectCommand.CommandText = sGetroomsizeandprice                da.Fill(ds, "roomprices")            Catch ex As Exception                Label4.Text = "ERROR: " & ex.Message                Exit Sub            End Try        End Using        Dim pkcol As DataColumn = ds.Tables("Room2").Columns("RoomID")        Dim fkcol As DataColumn = ds.Tables("roomprices").Columns("ID")        Dim dr As New DataRelation("MenuLink", pkcol, fkcol)        ds.Relations.Add(dr)        DataList1.DataSource = ds        DataList1.DataMember = "Rooms2"        DataList1.DataBind()    End Sub  

View 2 Replies View Related

OPENQUERY UPDATE Syntax Help Needed

Apr 25, 2007

Hi AllI am updating a local table based on inner join between local tableand remote table.Update LocalTableSET Field1 = B.Field1FROM LinkedServer.dbname.dbo.RemoteTable BINNER JOIN LocalTable AON B.Field2 = A.Field2AND B.Field3 = A.Field3This query takes 18 minutes to run.I am hoping to speed up the process by writing in OPENQUERY syntax.ThanksRS

View 1 Replies View Related

Assistance Needed With Syntax Error

Nov 13, 2007

I am getting the following syntax error to my query below. This query is similar to many I currently use and I have not been able to track down the error. Can anyone spot the error?


Msg 102, Level 15, State 1, Line 3

Incorrect syntax near '='.

Msg 102, Level 15, State 1, Line 43

Incorrect syntax near 'data'.


Select

e.account_number,

epi.Provider_Role = [1],

epi.Provider_Role = [2],

epi.Provider_Role = [3],

epi.Provider_Role = [4],

epi.Provider_Role = [5],

epi.Provider_Role = ,

epi.Provider_Role = [7],

epi.Provider_Role = ,

epi.Provider_Role = [9],

epi.Provider_Role = [10],

epi.Provider_Role = [11]

from (SELECT e.account_number,

row_number() over (partition by epi.PROVIDER_ROLE order by e.account_number asc) as rownum,

e.medrec_no,

e.account_number,

Isnull(ltrim(rtrim(pt.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(pt.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(pt.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(pt.patient_sname)), '')

AS SRM_PatientName,

CONVERT(int,pm.PatientAge),

left(e.admission_date,11) as Admit_Date,

left(e.episode_date,11) as Disch_Date,

(CASE WHEN DATEDIFF(DAY, e.admission_date,e.episode_date) = 0 Then 1

ELSE DATEDIFF(DAY, e.admission_date,e.episode_date) END) AS LOS,

pm.PrinOpPhys,

epi.PROVIDER_CODE,

epi.PROVIDER_ROLE,

pe.PERSON_NAME as physician_name

From srm.episodes e inner join

dbo.PtMstr pm on pm.accountnumber=e.account_number inner join

srm.ITEM_HEADER ih ON ih.ITEM_KEY = e.EPISODE_KEY INNER JOIN

srm.PATIENTS pt ON pt.PATIENT_KEY = ih.LOGICAL_PARENT_KEY inner join

srm.CDMAB_PROV_EPI epi on epi.episode_key=e.episode_key inner join

srm.providers p on p.provider_key = epi.provider_key inner join

srm.person_element pe on pe.item_key = p.provider_key

Where e.episode_date is not null and pm.AnyProc like '%4495%'

) data

PIVOT

(max(epi.Provider_Role) for rownumber

in ( [1], [2], [3], [4], [5], , [7], , [9], [10], [11] )) pvt

order by e.account_number

Thanks!

View 10 Replies View Related

Syntax Check Needed On Case Statement

Feb 26, 2008

Hello experts!

I have a case statement that provides filtering of hours during certain days of the week. An example is the data I want to show on Sunday is different from the rest of the week. I am using....





Code Snippet

WHERE ((CASE WHEN Datepart(dw, TestDateTime) = 1 AND datepart(hh, TestDateTime) BETWEEN 8 AND 22 THEN 1 WHEN Datepart(dw, TestDateTime)
>= 2 AND datepart(hh, TestDateTime) BETWEEN 6 AND 23 OR
datepart(hh, TestDateTime) BETWEEN 0 AND 2 THEN 1 ELSE 0 END) >= @ShowCore)


Esentially it gives a parameter (@showcore) to where it shows the filtered hours when 1 is selected, and all hours if 0 is selected.


Basically, Sunday I want to show transaction from between 8am and 10pm, All other days would be 12am - 2am and 6am to 11:59:59 when selecting 1 as the parameter.


Any help is appreciated.

View 3 Replies View Related

Help Needed With This Query

Jul 23, 2007

i have marked in bold the query in question ..whch gives me the the runtime error mentioned at the below , i double checked everything all the table names and the field names are correct so whats the problem  , please anyone??im really stumped!   if (Radio_Btn_Book.Checked == true)        {            string book_query = "update Issue_Book_Reserve I set I.Issue_Book_Reserve_state  = 1 where I.Book_Id = Books.Book_Id and Books.Book_Name = '" + Session["Book_name"].ToString()+"'";            SqlCommand Cmd_book = new SqlCommand(book_query, con);                        con.Open();            Cmd_book.ExecuteNonQuery();            con.Close();                   } ERROR: The column prefix 'Books' does not match with a table name or alias name used in the query.The column prefix 'Books' does not match with a table name or alias name used in the query.  

View 3 Replies View Related

SQL Query Help Needed.

Jan 14, 2008

Hi,
TABLES 
(Association CONTAINS COLUMNS AssociationID),
(Group CONTAINS COLUMNS GroupID, GroupName, AssociationID),
(GroupMembers CONTAINS COLUMNS GroupID, GroupMemberID, UserprofileID, DateCreated, DateRemoved),
(UserProfiles CONTAINS COLUMNS UserProfileID, UserID), 
(AllUsers CONTAINS COLUMNS UserID, FirstName, LastName)
 I am trying to write a query to collect information about  from the tables. I need to collect all the Users who are not members of Group A in Association I.
Note that Users can belong to more than one group and have more than one profile.
I would appreciate it if you could help me figure out how to deal with this logic. Thanks in Advance

View 2 Replies View Related

Help Needed With A Query

Mar 20, 2008

Hi
   I have a function which returns the periodId from the period table and the function is as follows:
 ALTER FUNCTION [dbo].[udf_Quarter] (@Date datetime)
RETURNS int
AS

BEGIN

DECLARE @month int
SET @month = Month(@Date)
RETURN
CASE
WHEN @month BETWEEN 1 AND 3 THEN 5
WHEN @month BETWEEN 4 AND 6 THEN 6
WHEN @month BETWEEN 7 AND 9 THEN 7
WHEN @month BETWEEN 10 AND 12 THEN 8
END
END
 Which works fine but i want to do the following with it. We produce quarterly statements.. So the user comes in jan or the begining of Feb to upload and process and order Statements from 10/31 to 12/31 the the 4 quarter data.. So Is there a way i can check if the user orders the statement in between jan and feb it has to reture PeriodId 5.
 
Any help will be appreciated.
Regards,
Karen 

View 5 Replies View Related

Help Needed For This Query..

Apr 10, 2008

I have three parameters called @rsLengthofservice, @rsLengthofServicematch and @rsLenghtofservicePS.
How do i check if all the three parameters have the same value..
Any help will be appreciated..
Regards,
Karen

View 5 Replies View Related

Help Needed With This Query

Apr 25, 2008

Hi,
  I have this query
 SELECT
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
--cp.ClientId
FROM
[Order] o
Inner Join Plan cp on o.PlanId = cp.PlanId and o.CreatedByUserId = cp.UserId
Inner Join User c on o.CreatedByUserId = c.UserId
WHERE
--cp.ClientId = @ClientId
--AND
o.OrderDate BETWEEN @StartDate AND @EndDate
ORDER BY
o.OrderId DESC Which gives the list of order from the start date to the end date..but it doesnt List the orders which are some mins apart. For eg. 16524/24/2008 10:48:05 AM4252840
16534/25/2008 10:15:42 AM378550
16544/25/2008 10:51:24 AM3147110 16614/25/2008 1:35:54 PM425045
16624/25/2008 2:30:43 PM4174387130
16634/25/2008 2:47:14 PM154825
16644/25/2008 2:49:10 PM265085
  it listed order no. 1652 and 1654 but not 1653 and in the next 4 it didnt list 1663.. why is this happening.. I am using a reports in the report server. any help will be appreciated. Regards,Karen
  
 So it kist

View 3 Replies View Related

SQL Query - Help Needed

Nov 17, 2003

Hi everyone,

I consider myself good at SQL when it comes to SELECTS, UPDATES, INSERTS etc - but now I'm actually wanting to delete a record.

How can I do this? Can I just use:

DELETE * FROM Table WHERE RowID = '23'

??

Can anyone help me out?

Cheers
Andrew

View 4 Replies View Related

T-SQL Query Help Needed

Jun 6, 2005

hi,
I have three tables with the following fields:

User:
UserID
Name

Test:
TestID
Name

Grade:
GradeID
TestID
UserID
Score


And I want to display the result something like this:

UserID   Name    Test1   Test2    Test3  
1             
Azam    
23       
34        45
2             
Ali         
34       
45         56 

How can this be accomplised thanks,

View 10 Replies View Related

Help Needed !! Sql Query

Aug 7, 2005

my app contains one form (aspx) and it has different controls to be filled by user (textbox,radiobutton ..etc)it has one button which i want to use to pass values entered in these controls to other page and do some queries to sql server there (2nd page)Now the thing is ...my controls can have NULL values ...like user could enter just one parameter and hit button or user can fill 2 parameter and hit enter so on the other hand (2nd page) how should i query the database accordingly ....

View 2 Replies View Related

Help Needed With A Query

Dec 2, 2005

I have some data that looks like this:
field1(pk)  field2               field3
563136    199535840A    D2119562408    199615461C    D2119562806    199748610A    D2119547463    199748610A    D2368562278    200255598A    D2368562286    200255598A    D2468
Field2 can have the same value.
What I need is to return all records where field3 is not 'D2468' ever for field2.  So with the above data, I'd want the first 4 records returned, but not 5 and 6 because for those field2 is the same value and in 6, field3 is 'D2468'.
So, I can't simply say:
SELECT a.field2FROM table1 a inner join table2 b on a.field2 = b.field2    and a.field5 = b.field5where a.field3 not like 'D2468'
because it will still return record 5. 
Can anyone help me with this?  Thank you!
 

View 3 Replies View Related

Help Needed On SQL Query

Jul 5, 2000

Hi

I need help here. I have two queries one on each server and one does not work the way it should be. Can some help me here in figuring out if both these queries are same or no. If there is any difference, what is causing that difference. Thanks

Query1:
SELECT Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.CountyName,Shipment.Zip, Shipment.WarehouseCd, Phone
FROM Orders INNER JOIN ((NewWarehouse
INNER JOIN Shipment ON NewWarehouse.WarehouseCd = Shipment.WarehouseCd)
INNER JOIN WarehouseCurrency ON NewWarehouse.WarehouseCd = WarehouseCurrency.WarehouseCd)
INNER JOIN Warehouse ON (Shipment.CountryCd = Warehouse.CountryCd) AND (NewWarehouse.WarehouseCd = Warehouse.WarehouseCd)) ON Orders.OrderNum = Shipment.OrderNum
WHERE BE = 5 AND AddressTy = 1 AND WarehouseCurrency.CurrencyCd ='USD' AND Active = -1
GROUP BY Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.CountyName, Shipment.Zip, Shipment.WarehouseCd, Phone
ORDER BY Min(Shipment.OrderNum) DESC)

Query 2: (This is working)
SELECT Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.Zip, Shipment.WarehouseCd, Shipment.Phone, Shipment.CountyName
FROM ((Orders INNER JOIN (NewWarehouse

INNER JOIN Shipment ON NewWarehouse.WarehouseCd = Shipment.WarehouseCd) ON Orders.OrderNum = Shipment.OrderNum)
INNER JOIN WarehouseCurrency ON NewWarehouse.WarehouseCd = WarehouseCurrency.WarehouseCd)
INNER JOIN Warehouse ON (NewWarehouse.WarehouseCd = Warehouse.WarehouseCd) AND (Shipment.CountryCd = Warehouse.CountryCd)
WHERE (((Orders.BE)=5) AND ((Shipment.AddressTy)=1) AND ((WarehouseCurrency.CurrencyCd)='USD') AND ((NewWarehouse.Active)=-1))
GROUP BY Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.Zip, Shipment.WarehouseCd, Shipment.Phone, Shipment.CountyName
ORDER BY Min(Shipment.OrderNum) DESC

I am sorry I am not that good in sql as some of you may be. Thanks.

View 3 Replies View Related

Query Help Needed

May 31, 2006

I have 2 tables:
Table1 has a field [Description](varchar) 100
Table2 has a field [IDDescription] int and field [Description] varchar(100)

In table 2 I have IDDescription store but some time also description wich is not store in table2
I need to pull all description (if it is from table2 or table1). How do I do this?

View 2 Replies View Related

Help Needed In A Query

Dec 16, 2005

I have a query that need to do something like this.


About the data:
---------------
I have a rules table which has got all the rules, and a log table that has the logs which says whether the rules are satisfied or not over a point of time.


select rules, sum(decode(status,'pass','1')) as passed, sum(decode(status,'fail',1)) as failed from rulestable,logstable where rulestable.logid=logstable.id

My expected Output
---------------------------------
Rule1 passed= 10 failed=12
Rule2 passed=11 failed=15


But i donot have decode() function in mssql nor can I use IF ELSE here. Can anyone please tell me how this can be implemented here?

Thanks

View 2 Replies View Related

Help On Query Needed

Jan 14, 2007

Ok guys.

Lets say i have 2 tables. One called 'Leads' and the other called 'Sales_Reps'. When a new lead comes in would like to assign it to the sales rep who has the least number of active leads. If there is a tie, I'll just assign it alphabetically between the sales reps who are tied with the least.

Can someone point me in the right direction here? I would assume I'd need to do some sort of select count, but I'm not sure how to begin.

Thanks!

View 8 Replies View Related

Query Help Needed .

Nov 20, 2007

Hi pals,

I need small help.
I have a table with data as follows.


select * from test_data

c1 c2 c3
----------------------
111 4 101
122 3 101
133 2 102
144 2 103
155 1 103

Now i need to generate a new column c4 as sequence number in the output


I need to get the output something as follows

c4 c1 c2 c3
-----------------------------
1 111 4 101
2 122 3 101
1 133 2 102
1 144 2 103
2 155 1 103

The newly generated column contains sequence numbers starting from 1 and the sequence should be resetted again to 1 whenever a new c3 value is encountered(for example when the c3 value changes from 101 to 102 the sequence should be resetted to 1).

Can anyone help me out on this regard.

Thanks in advance,
franky

View 3 Replies View Related

Query Needed

Jan 16, 2008

consider i have these tables:
tbl_requests
id
description
owner

tbl_owners
id
name

tbl_RequestStatus
id
description

am looking for output look like this:
OwnerName New Completed
x 2 5
y 4 3

table requests holds number of requests where each request has an owner, table owners holds the owners names, table status holds values for a request status for example : new, in progress and completed, the query am looking for should show each owner with the number of request he/she is handling grouped by the status, so we can say that owner X has 2 new requests and 5 completed requests.

View 9 Replies View Related

Query-help Needed

Apr 26, 2008

hi
i have a table with below structure
id no.of visit no of sales no of delivery.

I want all records to be selected with the following data also
average( no.of visit),average( no of sales ), average( no of delivery) in a single query ?

View 6 Replies View Related

Query Help Needed

May 13, 2008

hi all,

i have to copy all records from a table to a new table by copying only 100 records at a time.how can i do this ....

thanks in advance.......

View 9 Replies View Related

Query Help Needed

May 14, 2008

hi all
i have table which has a userid,email and other fields.
And i have a text file called email.txt,this text file has userid and email values corresponding to those in the table.And this file has only this userid and email fields separated by commas..

for example

1,jwalton@uswest.net
2,jeff@hmhr.com
3,james@hotmail.com

And i need to update the emailid field in table with the emailid in this text file.

ok tanx in advance...

View 6 Replies View Related

Query Help Needed

May 20, 2008

hi all
I know a constraint's name,say cst_name .And i know which database it is existing,say db.then can i know which table belongs to this constraint in tat db with help of a query?

tanx in advance

View 2 Replies View Related

Help Needed In SQL Query

Jun 5, 2008

Hi all,

I have a query which returns below result.

PID PName Quarter Value
1NewLookQ11000
1NewLookQ24200
1NewLookQ35400
1NewLookQ49000
2PepeQ13000
2PepeQ21200
2PepeQ34400

What I want - Another query on the above result which will return me the data as below without using temporary tables

PID PnameQ1Q2 Q3Q4
1NewLook1000420054009000
1Pepe300012004400 0


Please advise.

View 2 Replies View Related

Query Help Needed

Jun 27, 2006

Hi i have table like below

SNONAMEMARKSRANK
----------------------------
1A56
2B35
3C77
4D86
5E35
6F77
7G63
8H42
9I51

Now i have to give ranks marks wise like this

SNONAMEMARKSRANK
----------------------------
1A565
2B358
3C772
4D861
5E358
6F772
7G634
8H427
9I516

in above table there is no 3rd and 9th Rank,because C,F shred the 2nd rank
and B and E shred the 8th rank

to generate this type of result some body help me

View 3 Replies View Related

Help Needed With This SQL Query

Feb 5, 2007

I have a table with the following columns
Id, title, year,dateedited with id being the primary key...

typically rows look like this

1 test 2000 1/1/2006
2 test2 2002 3/1/2006
3 test3 2004 4/1/2006
4 test4 2000 5/1/2006

how do i retrive the latest entry for each year as in something like this

4 test4 2000 5/1/2006
2 test2 2002 3/1/2006
3 test3 2004 4/1/2006

Please help me with the query

View 2 Replies View Related

Query Help Needed...PLEASE

Aug 8, 2007

In the table below for Query3, I need to be able to extract the last time "each' RN_TEST_ID was tested
for each month. For example, for RN_TEST_IDs '1453' and '1420', the rows in the results set would look like this because they are the most recent records for that specific ID for each Month. Thank you, Thank you, Thank you!

8/1/2007 09:55:48 1453 Passed
8/2/2007 10:32:17 1420 Passed
7/3/2007 09:28:42 1453 Passed







Query3
RN_EXECUTION_DATE RN_EXECUTION_TIME RN_TEST_ID RN_STATUS
8/2/2007 11:14:51 1421 Passed
8/2/2007 10:32:17 1420 Passed
8/2/2007 09:30:28 1420 Failed
8/2/2007 09:29:20 1418 Passed
8/2/2007 09:18:45 1418 Failed
8/1/2007 16:06:22 1416 Failed
8/1/2007 14:19:56 1413 Passed
8/1/2007 14:19:44 1413 Failed
8/1/2007 14:19:32 1414 Passed
8/1/2007 09:55:48 1453 Passed
8/1/2007 09:36:52 1453 Failed
7/3/2007 09:28:42 1453 Passed

View 6 Replies View Related

Query Help Needed! :)

Dec 4, 2007

Hi all!

I have been working on the query that is supposed to pull out data to do the following thing:

there are orders that are both complete and not complete yet in the same table.

there is startDate and endDate for complete ones and forecastStartDate and forecastEndDate for not complete one.

I need to pull out data from the same table but using two different conditions in order to create monthly income.

Is there any easy way of doing it? Or do i need to separate my main table. However the last would result in me changing the whole application because of one stupid statement!


Thanks for help.
Lucas

View 2 Replies View Related







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