Right Use Of Like In An Or Clause Of Where Statement
May 30, 2002
I have the below where clause on a select
where substring(pa.other_id_number,5,1) = @monthid
and pa2.df_patstatus like 'act%'
and ((pa2.df_institutionname not like ('ACT%')
or pa2.df_institutionname not like ('county%')))
It returns records that have either one of the not like statements. If I comment out the line and use below it will work for one of the statements
where substring(pa.other_id_number,5,1) = @monthid
and pa2.df_patstatus like 'act%'
and pa2.df_institutionname not like ('ACT%')
How can I get the two to work together. I thought the parenthesis would work but no luck.
actually i need to do a sql report... there are 3 parameters (date from. date to, issue_user_id) that are required in order to retrieve the data...now i am facing one problem, if i just enter the date_from and date_to, and leave the issue_user_id blank, i wish to retrieve all the data, but i can't do it.
i have try to use:
if @IssueUserID = ' ' Select....(blah blah ) else select (blah blah)...it is long and work in Query analyzer but no working in .NET Reporting services.... :confused: is it possible to enter IF ..ELSE statement in WHERE clause?hope that u all can help.....
================================================== == SELECT REPLACE(REPLACE(REPLACE(REPLACE(tbl_Receipt.Paymen tRefID, '1', 'Deposit for Admission'), '2', 'Consultation Services'), '3', 'Medical Fees'), '4','Others') AS PaymentRefID, tbl_Receipt.ReferenceNo, tbl_Receipt.ReceiptDate, CONVERT(varchar(10), tbl_Receipt.ReceiptDate, 103) AS ReceiptDt, tbl_Receipt.TotalAmountPaid, tbl_Receipt.Payee, tbl_ReceiptDT.PaymentTypeID, tbl_PaymentType.PaymentType, tbl_ReceiptDT.Amount, tbl_User.Name, tbl_Receipt.IssueUserID, tbl_User.Designation FROM tbl_Receipt WITH (nolock) INNER JOIN tbl_ReceiptDT WITH (nolock) ON tbl_Receipt.ReceiptID = tbl_ReceiptDT.ReceiptID INNER JOIN tbl_PaymentType WITH (nolock) ON tbl_ReceiptDT.PaymentTypeID = tbl_PaymentType.PaymentTypeID INNER JOIN tbl_User ON tbl_Receipt.IssueUserID = tbl_User.UserID
WHERE (tbl_Receipt.ReceiptDate BETWEEN CONVERT(datetime, @DateFrom, 103) AND CONVERT(datetime, @DateTo, 103)) AND (tbl_Receipt.IssueUserID = @IssueUserID) ORDER BY tbl_User.UserID, tbl_ReceiptDT.PaymentTypeID
I am trying to write sql to basically say if c.rel_value IN ('XYZ','ABC') then rel_attr_id = 'A0' else rel_attr_id = 'C1'...bbut to get the rel_value we need the rel_attr_id to be 'A0' to begin with....so my statement currently is...
select c.client, c.attribute_id, b.dim_value, a.description, c.rel_attr_id, c.rel_value, 'N' as status from agldescription a, agldimvalue b, aglrelvalue c where b.client = c.client AND b.attribute_id = c.attribute_id AND b.dim_value BETWEEN c.att_val_from AND c.att_val_to AND a.client = b.client AND a.attribute_id = b.attribute_id AND a.dim_value = b.dim_value AND a.language = 'EN' AND a.attribute_id = '70' AND c.rel_attr_id = 'A0'
then I want to say as per above but if the rel_value are in XYZ or ABC then sqap rel_attr_id to 'C1' otherwise keep with 'A0'...
I need a SQL statement that selects a specific year (@yr type int) in the "createddate" column...if this @yr is equal to 0 then I want to select ALL columns regardless of the year...This is what I have so far, but it doesnt work...SELECT * FROM tblUsersWHERE year(CreatedDate)=CASEWHEN @yr<>'0' THEN @yrELSE NOT NULLEND
Hi!I want to make search engine and I have problem with query for this search. User can write username to search or text to search or both. So at first I made query for each event individually: ALTER PROCEDURE [dbo].[Show_Search_Topics] @username varchar(200), @search_text varchar(200), @days int AS DECLARE @date DATETIME SET @date = DATEADD(day,@days,GETDATE()) IF @username IS NOT NULL AND @search_text IS NULL BEGIN SELECT COUNT(dbo.forum_topics.post_user_id) AS UserPosts, forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_id, forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa, COUNT(DISTINCT forum_topics_2.post_id) + 1 AS post_total FROM dbo.forum_topics INNER JOIN dbo.aspnet_Users ON dbo.forum_topics.post_user_id = dbo.aspnet_Users.uID INNER JOIN dbo.forum_topics AS forum_topics_1 ON dbo.aspnet_Users.uID = forum_topics_1.post_user_id INNER JOIN dbo.forum_kategorie ON forum_topics_1.post_cat_id = dbo.forum_kategorie.forum_kat_id LEFT OUTER JOIN dbo.forum_topics AS forum_topics_2 ON forum_topics_1.post_id = forum_topics_2.post_parrent_id GROUP BY forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_parrent_id, forum_topics_1.post_id, forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa HAVING (forum_topics_1.post_parrent_id = 0) AND (dbo.aspnet_Users.UserName = @username) AND (forum_topics_1.post_current_date >= @date) ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC END ELSE IF @username IS NULL AND @search_text IS NOT NULL BEGIN SELECT COUNT(dbo.forum_topics.post_user_id) AS UserPosts, forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_id, forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa, COUNT(DISTINCT forum_topics_2.post_id) + 1 AS post_total FROM dbo.forum_topics INNER JOIN dbo.aspnet_Users ON dbo.forum_topics.post_user_id = dbo.aspnet_Users.uID INNER JOIN dbo.forum_topics AS forum_topics_1 ON dbo.aspnet_Users.uID = forum_topics_1.post_user_id INNER JOIN dbo.forum_kategorie ON forum_topics_1.post_cat_id = dbo.forum_kategorie.forum_kat_id LEFT OUTER JOIN dbo.forum_topics AS forum_topics_2 ON forum_topics_1.post_id = forum_topics_2.post_parrent_id GROUP BY forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_parrent_id, forum_topics_1.post_id, forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa HAVING (forum_topics_1.post_parrent_id = 0) AND (forum_topics_1.post_current_date >= @date) AND (forum_topics_1.post_title LIKE '%' + @search_text + '%') ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC END ELSE IF @username IS NOT NULL AND @search_text IS NOT NULL BEGIN SELECT COUNT(dbo.forum_topics.post_user_id) AS UserPosts, forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_id, forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa, COUNT(DISTINCT forum_topics_2.post_id) + 1 AS post_total FROM dbo.forum_topics INNER JOIN dbo.aspnet_Users ON dbo.forum_topics.post_user_id = dbo.aspnet_Users.uID INNER JOIN dbo.forum_topics AS forum_topics_1 ON dbo.aspnet_Users.uID = forum_topics_1.post_user_id INNER JOIN dbo.forum_kategorie ON forum_topics_1.post_cat_id = dbo.forum_kategorie.forum_kat_id LEFT OUTER JOIN dbo.forum_topics AS forum_topics_2 ON forum_topics_1.post_id = forum_topics_2.post_parrent_id GROUP BY forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_parrent_id, forum_topics_1.post_id, forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa HAVING (forum_topics_1.post_parrent_id = 0) AND (forum_topics_1.post_current_date >= @date) AND (forum_topics_1.post_title LIKE '%' + @search_text + '%') AND (dbo.aspnet_Users.UserName = @username) ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC END RETURN This 3 queries are different only by Having clause. So I want to put If/Case in Having clause, but I have problem. Can anyone help me?Also I want to make paging from SQL level, so if anyone will be so helpful and make working this query with this: ALTER PROCEDURE [dbo].[Show_Search_Topics] @username varchar(200), @search_text varchar(200), @days int, @page int, @page_size int AS WITH Results As ( //QUERY)
) SELECT * FROM Results WHERE RowNumber BETWEEN (@page_size * @page + 1) AND (@page_size * (@page + 1)) ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC RETURN I will be grateful :-)
Hi, I need help to use CASE Statement within my WHERE Clause. I want to change the WHERE Clause based on my condition as following: SELECT ... FROM ... WHERE (condition) AND (condition) AND (condition) AND ( CASE Table.Category WHEN 'Drinks' THEN Table1.Field1 = 1 -- Problem line ELSE Table1.Field1 = 1 AND Table1.Field2 = 1 -- Problem line END ) In the above case, my WHERE Clause is dependent on one of the fields in a table. If it has a certain value then only Table1.Field1 is used otherwise Table1.Field1 & Field2 come into action. I am getting error on the mentioned line (Problem Line). Since CASE is an expression and does not execute a statement, can anyone help me to get my WHERE Clause working... Thanks in advance...
can i use case statement in where clause. The scenario is as follow
declare @param int select * from table1 where column1 = 'asdf' column2= @param
In the above sql, if @param is '' then i don't want to include it in the where clause.I can use "like" statement for that, but i want exact value not partial value.
can i use case in where clause so that if @param is '' then i will not include in the where clause
Can I do the following? Keep getting an error stopping at the first < of the where clause.
declare @mon as int, @yr as int, @myDate as varChar(20) set @yr=2006 set @mon=1 set @mydate='01/31/2006 23:59:59'
select 0 as DTAP, 0 as DT, 0 as TD, 0 as HIB, 0 as IPV, 0 as MMR, 0 as HEPB, 0 as _VAR, count(v.procedureKey) as FLU, 0 as PPV23, 0 as PCV7, v.chartID, max(rs1.dateService) as dateService from dbo.tbl1 v, (select distinct dateService, chartID, procedureKey from fhc.dbo.tbl1 where (datePart(year,dateService)=@yr and datePart(month,dateService)=@mon) and (procedureKey='90657' or procedureKey='90658')) as rs1 where (v.chartID=rs1.chartID) and (v.procedureKey=rs1.procedureKey) and (case when @mon=1 then dateDiff(month,dateService,@myDate)< 216 when @mon=2 then dateDiff(month,dateService,@myDate)<244 when @mon=3 then dateDiff(month,dateService,@myDate)<275 when @mon=4 then dateDiff(month,dateService,@myDate)<305 when @mon=5 then dateDiff(month,dateService,@myDate)<336 when @mon=6 then dateDiff(month,dateService,@myDate)<366 when @mon=7 then dateDiff(month,dateService,@myDate)<32 when @mon=8 then dateDiff(month,dateService,@myDate)<63 when @mon=9 then dateDiff(month,dateService,@myDate)<93 when @mon=10 then dateDiff(month,dateService,@myDate)<124 when @mon=11 then dateDiff(month,dateService,@myDate)<154 when @mon=12 then dateDiff(month,dateService,@myDate)<185 end) group by v.chartID, rs1.procedureKey
i have wrote a query which compares two tables, returning anywhere the qty is not the same in each table:
(simple ex)
Select order_no from table1 inner join table2 on table1.order_no = table2.order_no group by order_no having table1.Qty<> table2.Qty
BUT... I need to add a table3, where there maybe (or may not be enters - thus a left join). If there is an entry in table3 then use qty in table3 and not table1... so having becomes:
CASE WHEN table1.Qty<> table3.Qty THEN table3.Qty<> table2.Qty ELSE table1.Qty<> table2.Qty END
I have someone telling me that I should have put my AND statement in the join instead of the where part, so is there a difference in the where I put it. Is there a difference in the results in any way between the two here in the results in anyway?
Example: Given a query: But let’s take a simple one here:
Query(1) Select ct1.Name, ct1.address, ct1.city, ct1.state, ct1.zipcode From cutTableA ct1 Left join cutTableA ct2 On ct1.ID = ct2.ID Where ct1.zipcode = '14124' AND ct1.Name = 'Bob'
Query(2) Select ct1.Name, ct1.address, ct1.city, ct1.state, ct1.zipcode From cutTableA ct1 Left join cutTableA ct2 On ct1.ID = ct2.ID AND ct1.zipcode = '14124' Where ct1.Name = 'Bob'
I need to have a CASE statement inside a WHERE clause. Is this possible? Here is my WEHRE clause. Any suggestions are appreciated:
WHERE --r.pBOM_ID=d.fEngr_BOM_ID and r.fItem_ID=a.pEngr_Item_ID and r.level<=@v_level+1 and case when @v_showrootlevel=1 then r.level>1 else r.level>1 end order by r.pID
I am trying to create a condition if the value of a parameter is null then pass a certain WHERE condition to my query. I keep on getting this error:
Line 15: Incorrect syntax near '='.
this is my query that lies on a PROC:
SELECT count(a.pEngr_Item_ID) as assembly_count FROM Engr_Item a INNER JOIN Engr_BOM_Control b ON a.pEngr_Item_ID=b.fEngr_Item_ID WHERE b.Bill_Type=@v_Bill_Type and a.Item_No=@v_Item_No and case when @v_Rev_Lett is not null then a.Item_No=@v_Rev_Lett else a.Item_No=@v_Rev_Lett end
I have the following stored procedure, I would like to use IF statement or something of the sort in the where clause i.e. The last line in the SP is: AND (category.categoryID = @categoryID), I only want to check this, if @categoryID is not = 12. So can I do something like this:
IF @categoryID <> 12 AND (category.categoryID = @categoryID)
STORED PROCEDURE:
CREATE PROCEDURE sp_get_total_risk_patients @categoryID int AS
SELECT COUNT(DISTINCT patient.patientID) AS total_patients FROM patient INNER JOIN patient_record ON patient.patientID = patient_record.patientID INNER JOIN sub_category ON sub_category.sub_categoryID = patient.sub_categoryID INNER JOIN category ON category.categoryID = sub_category.categoryID WHERE risk = 6 AND (completed_date = '' OR completed_date IS NULL) AND (category.categoryID = @categoryID)
The problem is that I want to only insert values that have the value "1". listofAnsers is an array that returns values of "1" and "2". If there a way to limit the inserts to values of "1". Another words, I want to just insert the values of "1" into the table. I know that you experts would say I should control this on the code side and thats true, however, I can't (because I don't control the code side) so is there a way to do this via SQL? Am I making any sense? I was thinking that I needed to add a "where" clause at the end of the code, but I don't know where to go from here.
If you could help me with my syntax i would really appreciateit. I'm trying to change the where clause on the fly, but it'sgiving me a syntax error.Hopefully from what I have below you can tell what i'm afterthe first part should evaluate a boolean expression, then if true,search using one field, otherwisesearch using a different fieldWHERECase WHEN @myCompany = 933THEN tblClient.companycode = 933 --problem lineELSEtblCase.clientcode = @myClient --problem lineENDAnd tblCase.status = 'Active'thank you!!
Can anyone tell me if it's possible to use a Case statement in a Whereclause, and if so, the proper syntax?J.R.Largo SQL ToolsThe Finest Collection of SQL Tools Availablehttp://www.largosqltools.com
Code Block INSERT INTO [INTRANETSQLEXPRESS].Trapped.dbo.TBL_Debtsolv (Debtsolv_ID, Payment_Amount, Payment_Status, Client_Status) SELECT Client.ID AS ClientID, InitialInstallment.OffsetAmountExpected AS FirstExpectedPayment , CASE WHEN Payment.TotalPaid <= 0 OR LatestPaymentDate IS NULL THEN 'No Money Paid' WHEN Payment.TotalPaid < InitialInstallment.OffsetAmountExpected AND Payment.TotalPaid >0 AND InitialInstallment.DateExpected + ISNULL(Schedule.DayOffset, 0) >GETDATE() THEN 'Still Awaiting - Part Paid' WHEN Payment.TotalPaid >= InitialInstallment.OffsetAmountExpected THEN '1st Payment Made' WHEN Payment.TotalPaid < InitialInstallment.OffsetAmountExpected AND InitialInstallment.DateExpected + ISNULL(Schedule.DayOffset, 0) <GETDATE() THEN 'Late - First Payment Not Made' ELSE 'Status Unclear' END AS Status , CASE WHEN Client.Status IN(0,1,2,3,4,5,6,7) THEN 'In Preparation' WHEN Client.Status IN(8,9) THEN 'Active' ELSE 'Inactive' END AS ClientStatus FROM SALEEN.Debtsolv.dbo.Client_Contact as Client LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Payment_Schedule AS Schedule ON Client.ID = Schedule.ClientID INNER JOIN SALEEN.Debtsolv.dbo.Client_LeadData CLD ON Client.ID = CLD.Client_ID INNER JOIN SALEEN.Debtsolv.dbo.Type_Client_Status TCS ON Client.Status = TCS.ID LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Type_Lead_Source TLS ON CLD.SourceID = TLS.ID LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Type_Lead_Source_Group AS LeadGroup ON TLS.[Group] = LeadGroup.ID LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Users [User] ON CLD.Counsellor = [User].ID LEFT OUTER JOIN (SELECT ClientID , SUM(CAST((Amount + ISNULL(AmountOffset, 0))AS Money) / 100) AS OffsetAmountExpected , MAX(DateExpected) AS DateExpected FROM SALEEN.Debtsolv.dbo.Payment_Schedule AS PS WHERE (SequenceID <=(SELECT CASE WHEN NumInitialInstalments = 0 THEN 1 ELSE IsNull(NumInitialInstalments, 1) END AS NumInitialInstalments FROM SALEEN.Debtsolv.dbo.Client_LeadData AS LD WHERE (Client_ID = PS.ClientID))) GROUP BY ClientID ) AS InitialInstallment ON Client.ID = InitialInstallment.ClientID LEFT OUTER JOIN (SELECT Receipt.ClientID , SUM(CAST(Receipt.Amount AS Money)) / 100 AS TotalPaid FROM SALEEN.Debtsolv.dbo.Payment_Receipt Receipt INNER JOIN SALEEN.Debtsolv.dbo.Type_Payment_Status Type ON Receipt.Status = Type.ID WHERE (Receipt.Status = 5) GROUP BY ClientID) AS Payment ON Client.ID = Payment.ClientID LEFT OUTER JOIN (SELECT ClientID , MAX(ID) AS MaxPSID , MAX(LastPayment) AS LatestPaymentDate FROM SALEEN.Debtsolv.dbo.Payment_Schedule Schedule WHERE (NOT (LastPayment IS NULL) AND LastPayment > CONVERT(DATETIME, '1980-01-01 00:00:00', 102)) GROUP BY ClientID) AS LatestPaymentDate ON Client.ID = LatestPaymentDate.ClientID INNER JOIN (SELECT ClientID , MAX(SequenceID) AS LFPDSequenceID FROM SALEEN.Debtsolv.dbo.Payment_Schedule PS WHERE (PaymentType = 1) GROUP BY ClientID) AS LastPayment ON Client.ID = LastPayment.ClientID AND Schedule.ClientID = LastPayment.ClientID AND Schedule.SequenceID = LastPayment.LFPDSequenceID WHERE Client.ID = TBL_Debtsolv.Debtsolv_ID
Executing this statement gives the following error:
Code Block
Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "TBL_Debtsolv.Debtsolv_ID" could not be bound.
I think thsi is soemthing to do with referencing another table in the INSERT into command as if I put:
Code Block WHERE Client.ID = '12065'
The statement executes with no problem. I think I have read in some other threads that you cannot reference another table in the WHERE part of an INSERT INTO clause but cannot find out how I can make this work.
Most of the statement comes from a pre-made report from one of our systems and I have slighlty changed it to insert the data into another database that we have.
I have a query which is something like this, but it returns an error:
SELECT * FROM tablex
WHERE
CASE WHEN 'sexec' IS NOT NULL THEN
dm_marque = 'foo' AND year(dm_date) LIKE 'pyear' AND dm_month LIKE 'pmonth' AND dm_weekno LIKE 'pweek' and dm_sexec LIKE 'sexec' ELSE
dm_marque = 'foo' AND year(dm_date) LIKE 'pyear' AND dm_month LIKE 'pmonth' AND dm_weekno LIKE 'pweek' END
GROUP BY dm_marque
In this case sexec is a form parameter, if something is passed then I need to include it in the where statement, if it isn't I need to include something else. I am using CASE because there are three of these parameters and I want it to stop evaluating as soon as it matches as more than one may match but I only want to apply one.
Hi, I'm trying to inner join an update statement. Something like this:
update #point_connection_temp AS a inner join #point_connection_temp_two as b on a.key_fld = b.key_fld set a.next_point = b.next_point where #point_connection_temp.next_point is null order by a.key_fld
I'm getting an error message:Incorrect syntax error near AS Any help will be greatly appreciated.Thank you!!!!!!!!!1
I have a table 'table_list' which contains two columns, table_name and a record_count. This table stores a list of tables and their corresponding record counts.
What I am trying to do is, to be able to write a select statement, that can read each table name in the 'table_name' column, execute a select count(*) for the same, and update its record_count with the result of select count(*).
This is the code in my procedure..
DECLARE @tab_list CURSOR set @tab_list = CURSOR FOR select * from table_list OPEN @tab_list
DECLARE @tab_name varchar(256) DECLARE @rec_cnt int FETCH NEXT FROM @tab_list INTO @tab_name, @rec_cnt
select count(*) from @tab_name
This select is looping around along with FETCH till all the table names are exhausted and their counts are updated from the cursor back into the table.
Problem is that, I am not able to use select count(*) from @tab_name, and its not accepting a variable there.
Please help me to construct the select statement that is similiar to
x=<table name> select * from x where x is a variable and the table name gets substituted.
I need to write a SQL script where a join condition is using date columns (effective_date, ineffective_date). The effective date columns can be slightly different (e.g. differ by a day) for some rows of data. I need the join condition to accommodate these date differences and return these rows of data as well.
I have a table which uses multiple joins to create another table but it turns out that the effective_date which is used in the join to match row together does not work all the time since some of the dates for the effective date column are out of sync meaning records that show data as missing even when the other table contains the data. I tried the SQL script below using the BETWEEN clause but it returning 6 rows instead of 3–
I am supposed to delete all rows from USER and its child tables based on PracticeID = '55' filter condition.
I have dynamically generated queries to delete child table first followed by parent. Table [EncounterSignOff] si child and [User] is parent.
I would like to know, whether the Query 1 is valid for deleting child records?
Query:1
DELETE Top(100000) FROM [dbo].[EncounterSignOff] FROM [dbo].[User] INNER JOIN [dbo].[EncounterSignOff] ON [User].[UserID] = [EncounterSignOff].[UserId] WHERE [User].PracticeID = '55';
Query:2
DELETE Top(100000) FROM [dbo].[User] WHERE [User].PracticeID = '55';
I have a population split between two vendors. One gets last names between A and R, the other the rest. Now, on a given date vendor 1 gets everybody.
I can accomplish this with a case statement on the upper range (R or Z), but it seems I should be able to do this without testing at all after the turnover date.
A small bit of the code:
declare @get_date datetime = convert(char(10),getdate(),101) select top 10 pt.pt_id, pt.last_name fromsmsmir.mir_acct a join smsmir.mir_pt pt on (a.src_sys_id = pt.src_sys_id and a.pt_id = pt.pt_id and a.from_file_ind = pt.from_file_ind
[Code] ....
Seems I should be able to not test the last name after the turnover date, but I can't figure out how.
CREATE PROCEDURE [dbo].[GetProspectBySearch] @StartRowIndex AS INT, @MaximumRows AS INT, @ModuleType AS VARCHAR(50) = NULL, @ProspectCategory AS VARCHAR(50) = NULL, @ProspectsWithoutContact AS BIT = NULL AS BEGIN IF @Status = 'All' BEGIN SET @Status = NULL END
[code]....
1) How do I can write count and paging statements in to single statement ? 2) There is parameter @ProspectsWithoutContact is using for differentiation for count and paging statements. If @ProspectsWithoutContact is "1" then this clause ( AND ContactPerson = NULL AND TelNo = NULL AND FaxNo = NULL) will be added in to both count and paging statements. How do I can write this in to single statement with out IF ELSE ?
After many hours search many forums and many failed experiments, I figure it's time to turn to the experts.
I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls.
SELECT uniqueID, lob, xdate FROM mytable WHERE
CASE WHEN @myparam = 'ALL'
THEN
xdate >= '2007-09-01' ELSE
xdate >= '2007-09-01' or lob = @myparm END
I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank.
I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much.
