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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Jan 15, 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
.NET Developer
View 1 Replies
View Related
Mar 14, 2007
I'm pretty new to SQL and querying, and I'm not sure if this is thecorrect group to post this to, but hopefully someone can point me inthe right direction.I have the following query:SELECT Distinct dbo.DLFolders.FolderCode,dbo.PropertyCharValues.ParentId,dbo.PropertyCharValues.DocumentTypePropertyId,dbo.PropertyCharValues.PropertyCharValue,dbo.PropertyDateValues.DocumentTypePropertyId AS Expr1,dbo.PropertyDateValues.PropertyDateValueFROM dbo.PropertyDateValues INNER JOINdbo.PropertyCharValues ONdbo.PropertyDateValues.ParentId = dbo.PropertyCharValues.ParentIdINNER JOINdbo.Documents ON dbo.PropertyCharValues.ParentId= dbo.Documents.DocumentId INNER JOINdbo.DLFolders ON dbo.Documents.DLFolderID =dbo.DLFolders.FolderIDWHERE (dbo.PropertyDateValues.ParentId = '195203')Which yields these results (except the far right column, which I'veadded manually to describe what the PropertyCharValue is. I didn'tbother with the PropertyDateValues, but they are basically InvoiceDate, Post Date, and Check Date):FolderCodeParentIdDocumentTypePropertyIdPropertyCharValueExpr1PropertyDateValue42819520311942006-06-03 00:00:00.000Vendor Number42819520311962006-07-05 00:00:00.000Vendor Number428195203119932006-06-30 00:00:00.000Vendor Number4281952032Bhellington Corporation42006-06-03 00:00:00.000VendorName4281952032Bhellington Corporation62006-07-05 00:00:00.000VendorName4281952032Bhellington Corporation932006-06-30 00:00:00.000Vendor Name428195203368001-042138142006-06-03 00:00:00.000Invoice Number428195203368001-042138162006-07-05 00:00:00.000Invoice Number428195203368001-0421381932006-06-30 00:00:00.000Invoice Number4281952035000000005242006-06-03 00:00:00.000Voucher Number4281952035000000005262006-07-05 00:00:00.000Voucher Number42819520350000000052932006-06-30 00:00:00.000Voucher Number4281952037000001002742006-06-03 00:00:00.000Check Number4281952037000001002762006-07-05 00:00:00.000Check Number42819520370000010027932006-06-30 00:00:00.000Check Number4281952032242842006-06-03 00:00:00.000Company Code4281952032242862006-07-05 00:00:00.000Company Code42819520322428932006-06-30 00:00:00.000Company Code428195203233908.7842006-06-03 00:00:00.000Amount428195203233908.7862006-07-05 00:00:00.000Amount428195203233908.78932006-06-30 00:00:00.000Amount42819520324APVO-000001542006-06-03 00:00:00.000Batch ID42819520324APVO-000001562006-07-05 00:00:00.000Batch ID42819520324APVO-0000015932006-06-30 00:00:00.000Batch ID4281952032504057500042006-06-03 00:00:00.000GL Accounts4281952032504057500062006-07-05 00:00:00.000GL Accounts42819520325040575000932006-06-30 00:00:00.000GL Accounts4281952032504057600042006-06-03 00:00:00.000GL Accounts4281952032504057600062006-07-05 00:00:00.000GL Accounts42819520325040576000932006-06-30 00:00:00.000GL Accounts42819520312342842006-06-03 00:00:00.000Target Company42819520312342862006-07-05 00:00:00.000Target Company428195203123428932006-06-30 00:00:00.000Target CompanyAs you can see, the results triplicate the PropertyCharValue items andmultiply by 11 the PropertyDateValues because they are on differenttables and because I don't know what I'm doing.I would like to see a result with columns like this:FolderCode ParentID VendorNum VendorName InvNum VouchNumCompCode Amount BatchID GLAccounts GLAccounts(2) TargetCompany InvDate PostDate CheckDateTo that end, I tried the following query as an attempt in thatdirection:Declare@IDNum intSet @IDNum = '195203'SELECT distinct dbo.DLFolders.FolderCode,dbo.PropertyCharValues.ParentId,(Select PropertyCharValue from PropertyCharValues WhereParentId = '195203' and DocumentTypePropertyId= '5') as VouchNum,(Select PropertyCharValue from PropertyCharValues WhereParentID = '195203' and DocumentTypePropertyID= '25') as GLNumber,--dbo.PropertyCharValues.DocumentTypePropertyId,dbo.PropertyCharValues.PropertyCharValue,(SELECT PropertyDateValueFROM dbo.PropertyDateValuesWHERE ParentId = '195203' ANDDocumentTypePropertyID = '4') AS InvoiceDate,(SELECT PropertyDateValueFROM dbo.PropertyDateValuesWHERE ParentId = '195203' ANDDocumentTypePropertyID = '93') AS PostDate,(SELECT PropertyDateValueFROM dbo.PropertyDateValuesWHERE ParentId = '195203' ANDDocumentTypePropertyID = '6') AS CheckDateFROM dbo.PropertyDateValues INNER JOINdbo.PropertyCharValues ONdbo.PropertyDateValues.ParentId = dbo.PropertyCharValues.ParentIdINNER JOINdbo.Documents ON dbo.PropertyCharValues.ParentId= dbo.Documents.DocumentId INNER JOINdbo.DLFolders ON dbo.Documents.DLFolderID =dbo.DLFolders.FolderIDWHERE (dbo.PropertyDateValues.ParentId = '195203')And I discovered that a subquery can't have more than one result.I'm not sure what avenues to try next. Any suggestions?
View 6 Replies
View Related
Jul 20, 2005
Hai1. Is it possiable to delete a record from the parent table.It iseven ok to me , if it leads to the deletion of all the child tables .2.I 've come across a situiation where the name of the table is to besupplied by the variable in my sp ,like@t = 'table1'select * from @t -- it gives the error.--actually i want "select * from table1" & in my sp i gave asSo i am forced to give the table name from the sub query.how can i acheive this thru query where the table name has to besupplied to the from clause from the sub queryWith ThanksRaghuraman.C
View 5 Replies
View Related
Jul 20, 2005
This seems simple, but it keeps tricking me. Could not get the properresult. Thanks in advance!TABLE1App_ID Business_Unit_IDPROFILE_ID1001AA13001AA1TABLE2 - SUPERSETApp_IDBusiness_unit_ID1 0011 0021 0031 0072 0022 0033 0013 0023 0074 0094 008USING Business_Unit_ID and App_ID in TABLE1,check to see if App_ID & Business_Unit_ID is in table2if app_id and Business_Unit_Id from table1 is found in table2 (or is asub-set in table2) thenretun trueelse falseIn this example, the result should be True
View 3 Replies
View Related
Jun 25, 2007
Hi
MailMethodId Is an integer, and it has 2 possible values 1 which means 'Mail to Participant' or 2 which means 'Mail To client'
select
cp.PlanId,
cp.ClientPlanId,
psi.MailMethodId,
psi.StatementTypeId,
cp.PlanName,
cp.ClientId ,
c.ClientName
from ClientPlan cp
Join PlanStatementInfo psi on cp.PlanId = psi.PlanId
Join Client c on cp.ClientId = c.ClientId
Where cp.ClientId = @ClientId
Union
Select
cp.PlanId,
cp.ClientPlanId,
2,
1,
cp.PlanName,
cp.ClientId,
c.ClientName
From
ClientPlan cp
inner join Client c on cp.ClientId = c.ClientId
where
cp.ClientId = @ClientId
and
cp.PlanId Not IN
(Select psi.PlanId from PlanStatementinfo psi)
So how can i get the Mail methodId to display 'Mail to Participant' if the data is 1 and 'Mail to Client' if the Data is 2
i tried doing this
case When psi.MailMethodId = 1 then 'Mail to Participant' Else 'Mail to Client' End,
instead of psi.MailmethodId, but i am getting a error message that says
Syntax error converting the varchar value 'Mail to Participant' to a column of data type int.
Any Help will be appreciated
Regards
KAren
View 3 Replies
View Related
May 24, 2008
hi all,
I have one table called Supplier,In that i have one field with name Supplier_Name.
My requirement is,I need one Stored procedure or Query to fill Drop down list.
In That drop down first time,I need to fill first Three names of the Suppliers(1,2,3 Positions),Next time when user opens that form(in form Load) drop down should contain next 3 suppliers(4,5,6 Positions),Next time again next 3 suppliers(7,8,9 Positions).Like this it has to select Three suppliers in a loop.
Any body give me stored Procedure or query for this.
I am using Sql Server 2005 Database.
Pls help me.
View 7 Replies
View Related
Feb 16, 2008
My table is like this.
ID FLD1 FLD2 FLD3 FLD4
----------------------------------------------------------------------------------
2 NULL NULL NULL *3455
4 NULL VINCENT NULL ANDREW
9 NULL MURRAY NULL JACOB
10 NULL NULL NULL *9755
13 NULL CHARLES NULL ANITA
16 NULL MARCH NULL PRINCE
20 NULL BRAD NULL QUEEN
I need to update FLD1 and FLD3 for rows 4 & 9 with *3455, and,
FLD1 and FLD3 for rows 13, 16 and 20 with *9755.
I need an update query to do this.
Thanks.
View 9 Replies
View Related
Feb 5, 2007
I need help in optimizing this query. The major time takes in calling a remote database. Thanks in advance.ALTER PROCEDURE dbo.myAccountGetCallLogsTest@directorynumber as varchar(10),@CallType as tinyint ASdeclare @dt as intSELECT TOP 1 @dt=datediff(day,C.EstablishDate,getdate())FROM ALBHM01CGSERVER.Core.dbo.Customer C INNER JOIN ALBHM01CGSERVER.Core.dbo.UsgSvc U ON C.CustID = U.CustIDWHERE (U.ServiceNumber = @directoryNumber)ORDER BY C.EstablishDate DESCIF @dt>90select DN as Number, Remote_DN as [Remote Number], City, StartTime as [Start Time], EndTime as [End Time] from vw_Call_Logs where DN = '1' + @directoryNumber and call_type = @CallType and datediff(day,starttime,getdate())<90order by starttime descELSE select DN as Number, Remote_DN as [Remote Number], City, StartTime as [Start Time], EndTime as [End Time] from vw_Call_Logs where DN = '1' + @directoryNumber and call_type = @CallType and datediff(day,starttime,getdate())< @dtorder by starttime desc
View 13 Replies
View Related
Mar 26, 2008
Hi,
I have 2 tables. Plan and User. In my User table I have the following columns
UserId ClientId Login Password PlatformId
1 1 abc XXX 1
4 1 jcn XXX 2
20 1 djfd XXX 1
and the Plan table
PlanId PlanName
1 abc plan
2 Plan etc..
and ihave a sproc that will return the all the plans based on the clientId but i want to also get all the plans that have PlatformID 1 for the same client. This is my Sproc..
SELECT
NULL PlanId,
'< All Plans >' PlanName
UNION
SELECT Distinct
PlanId,
PlanName
FROM
Plan cp
Join User u on cp.ClientId = u.ClientId
WHERE
u.PlatformId = 1
AND
cp.ClientId = @ClientId
OR
(
cp.ClientId = cp.ClientId
AND
@ClientId IS NULL
)
ORDER BY
PlanName ASC
But they dont filter it by PlatformId
any help will be appreciated.
Regards,
Karen
View 3 Replies
View Related