Formatting Case Statement Results
I am trying to format the Sys field (which is Int) in case the first byte is zero. But I'm getting an error msg;
Msg 156, Level 15, State 1, Procedure sp_Pull_Cancels, Line 43
Incorrect syntax near the keyword 'Right'.
CASE
WHEN substring(PCRACT, 5, 1) = '4' THEN 3641
WHEN substring(PCRACT, 5, 1) = '5' THEN 2012
WHEN substring(PCRACT, 5, 1) = '6' THEN 9694
END as Right('0000' + Cast(Sys as varchar (4)),4)
Any ideas how I construct this CASE statement to get the desired results.
Thanx,
View Complete Forum Thread with Replies
Related Forum Messages:
Trying To Use The Results Of A Case Statement In My Select List In My WHERE Clause?
I am fairly new with SQL and still learning. I have used a case statemtent for a column in my select list and want to use the results of that statement's field in my WHERE clause but it is not working for me. Here is the code I have so far: SELECT l.loanid, p.investorid, l.duedate, case when pc.duedate >= l.duedate then pc.duedate end as RateDueDate, pc.interestrate FROM loan l inner join participation p on p.loanid = l.loanid inner join paymentchange pc on pc.loanid = l.loanid where p.investorid = '12345' and RateDueDate is not null order by l.loanid, pc.duedate I want to put the results of this case statment in my where clause like highlighted above but it is not working because RateDueDate is not an actual column in the table. Any help would be greatly appreciated. Thanks!
View Replies !
Formatting Results Of The Query
my current query returns data as follows: stopID reason count h1 e 5 h1 v 2 h1 a 1 h2 v 2 h2 m 8 h3 t 6 h3 v 1 Is it even possible to sort/format this query in SQL to return the same data but in this type of format? Using the values of the 'reason' column as column names? stopID e v a m t h1 5 2 1 0 0 h2 0 2 0 8 0 h3 0 1 0 0 6 thanks -r
View Replies !
Formatting Results Of This Query
Hello I have this query to find duplcates is the db. sql Code: Original - sql Code SELECT top 4 old_acct_num,new_acct_num FROM sub1 WHERE old_acct_num IN ( SELECT old_acct_num FROM sub1 GROUP BY old_acct_num HAVING (COUNT(old_acct_num ) > 1) ) order by old_acct_num SELECT top 4 old_acct_num,new_acct_num FROM sub1WHERE old_acct_num IN ( SELECT old_acct_numFROM sub1GROUP BY old_acct_numHAVING (COUNT(old_acct_num ) > 1) ) ORDER BY old_acct_num This pulls back the results like this: old_acct_num new_acct_num 0000014040 10000196 0000014040 30163711 0000017793 70000314 0000017793 30090134 How can I rewrite the query to display reults like this: old_acct_num new_acct_num new_acct_num2 0000014040 10000196 30163711 0000017793 70000314 30090134 Would this be a crosstab query? Thanks!!
View Replies !
Formatting @query Results Using Xp_sendmail
I know formatting should be handled in the client app and not in SQL Serverbut this is the situation I must develop in so any help would beappreciated.I'm running the following in Query AnalyzerUSE PubsDECLARE @MessageSubject VARCHAR(50)SELECT @MessageSubject = 'Report'EXEC master.dbo.xp_sendmail 'me@mine.com',@query = 'SELECT au_fname, au_lname from pubs.dbo.authors',@subject = @MessageSubjectThe results in my email look like:au_fname au_lname------------------- ----------------------------------------Abraham BennetReginald Blotchet-HallsCheryl CarsonI would like the results to be like:First Name: AbrahamLast Name: BennetFirst Name: ReginaldLast Name: Blotchet-HallsFirst Name: CherylLast Name: CarsonThanks
View Replies !
Simple SQL Question About Formatting Results
Embarrassed to ask but nevertheless here i go: I have a SQL like this: select sum(qty) from tblXYZ The results is displayed as (for example) 123456789.56 I want the results to be displayed as 123,456,789.56 (i.e. use the currency or the comma format; whatever it's called). Please help
View Replies !
Problem Using Result From CASE In Another CASE Statement
I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code. Previously, I had been duplicating the CASE logic for both columns, like so: Code Block...beginning of SQL view... shipment_status = CASE [logic for condition 1] THEN 1 WHEN [logic for condition 2] THEN 2 WHEN [logic for condition 3] THEN 3 WHEN [logic for condition 4] THEN 4 ELSE 0 END, shipment_status_text = CASE [logic for condition 1] THEN 'Condition 1 text' WHEN [logic for condition 2] THEN 'Condition 2 text' WHEN [logic for condition 3] THEN 'Condition 3 text' WHEN [logic for condition 4] THEN 'Condition 4 text' ELSE 'Error' END, ...remainder of SQL view... This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise. This is what I'd like to do: Code Block ...beginning of SQL view... shipment_status = CASE [logic for condition 1] THEN 1 WHEN [logic for condition 2] THEN 2 WHEN [logic for condition 3] THEN 3 WHEN [logic for condition 4] THEN 4 ELSE 0 END, shipment_status_text = CASE shipment_status WHEN 1 THEN 'Condition 1 text' WHEN 2 THEN 'Condition 2 text' WHEN 3 THEN 'Condition 3 text' WHEN 4 THEN 'Condition 4 text' ELSE 'Error' END, ...remainder of SQL view... This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text. Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result? Thanks, Jason
View Replies !
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
Formatting Numbers In An SQL Statement
Hi,I have a table that has an ID field which is automatically incremented as each new record is added, so if I do a SELECT * FROM Table1 I get:ID, Name1, Billy2, Bob3, TonyYou get the idea. What I want to do is format the number differently when it's returned from an SQL statement so I get:ID, Name0001, Billy0002, Bob0003, TonySo I need something like SELECT FORMATNUMBER(ID, 4), Name FROM Table1 - Does anything like this exist?Little 'un.
View Replies !
Case Statement Error In An Insert Statement
Hi All, I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance. My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it. Here is my code: Insert into myTblA (TblA_ID, mycasefield = case when mycasefield = 1 then 99861 when mycasefield = 2 then 99862 when mycasefield = 3 then 99863 when mycasefield = 4 then 99864 when mycasefield = 5 then 99865 when mycasefield = 6 then 99866 when mycasefield = 7 then 99867 when mycasefield = 8 then 99868 when mycasefield = 9 then 99855 when mycasefield = 10 then 99839 end, alt_min, alt_max, longitude, latitude ( Select MTB.LocationID MTB.model_ID MTB.elevation, --alt min null, --alt max MTB.longitude, --longitude MTB.latitude --latitude from MyTblB MTB ); The error I'm getting is: Incorrect syntax near '='. I have tried various versions of the case statement based on examples I have found but nothing works. I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
View Replies !
How To Show Records Using Sql Case Statement Or If Else Statement
i want to display records as per if else condition in ms sql query,for this i have used tables ,queries as follows as per data in MS Sql my tables are as follows 1)material fields are -- material_id,project_type,project_id,qty, -- 2)AB_Corporate_project fields are-- ab_crp_id,custname,contract_no,field_no 3)Other_project fields are -- other_proj_id,other_custname,po for ex : vales in table's are AB_Corporate_project ===================== ab_crp_id custname contract_no field_no 1 abc 234 66 2 xyz 33 20 Other_project ============ other_proj_id other_custname po 1 xxcx 111 2 dsd 222 material ========= material_id project_type project_id qty 1 AB Corporate 1 3 2 Other Project 2 7 i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project sample query i write :-- select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id, ab.custname ,op.other_proj_id,op.other_custname,op. po case if m.project_type = 'AB Corporate' then select * from AB_Corporate_project where ab.ab_crp_id = m.project_id else if m.project_type = 'Other Project' then select * from Other_project where op.other_proj_id=m.project_id end from material m,AB_Corporate_project ab,Other_project op but this query not work,also it gives errors i want sql query to show data as follows material_id project_type project_id custname other_custname qty 1 AB Corporate 1 abc -- 3 2 Other Project 2 -- dsd 7 so plz help me how can i write sql query for to show the output plz send a sql query
View Replies !
Number Formatting In A SQL Select Statement
Hi I'm trying to convert and format integer values in a SQL Server select statement to a string representation of the number formated with ,'s (1000000 becomes 1,000,000 for example). I've been looking at CAST and CONVERT and think the answers there somewhere. I just don'tseem to be able to work it out. Anyone out there able to help me please? Thanks,Keith.
View Replies !
Case Statement
Hi I have some question regarding the sql case statment.Can i use the case statement with the where clause.Example: SELECT FirstName, IDFROM myTablewhere case when ID= '123' then id = '123' and id='124' endorder by idBut the above code does not work.
View Replies !
Case Statement In Sql Help
I am trying to use a case statement in one of my stored proc but I am stuck a little bit.Here is a example, something like:declare @id int set @id =1case @id When 1 then select * from contactsend case but this keeps on giving me error: Incorrect syntax near the keyword 'case'. Any help is appreciated!
View Replies !
What Is The Best SQL Statement For This Case ?
Hi !!!i hope one of the sql specialists answer me about the best and most effeceint way to acheive what i am looking for Scenario:-------------i have a 3 tables related to each other Addresses, Groups and GroupAddressthe relation is for both addresses and groups is one to many in the GroupAddress.the behaviour in the application : user can add addresses to his address list and from the address list a user can add an address to many groups like if you have Group name "Freinds" and you add me in it and you have Football team group and you add me to it like that !!!not i have another function called "copy group"in the GroupAddress i have this data as example GroupID AddressID1 41 61 21 441 72 82 62 93 133 73 10and the group ID called "Freinds"i want to copy the group so i can have another group that has the same addresses by one click rather than collectiong them again one by one ...by the way the new copy will have a new group name ( as this is thebusiness logic so user can not have dupicate group name )so what is the best SQL statement that i need to copy the group ???i hope that clear enough!
View Replies !
Sql Case Statement Help
Hi all, I was wondering if there is any way in an sql statement to check whether the data your trying to get out of the DB is of a particular type, ie. Int, char etc. I was thinking about a case statement such as <code> CASE WHEN (MyNum <> INT) then 0 end AS MyNum </code> This has to be included in the sql statement cause I need this field to get other data. Any thoughts on how to achieve this would be greatly appreciated. If I’m in the wrong thread section please advise of best one to get help in.
View Replies !
Case Statement
i am working on a sql statement that is kind of weird i have a table but it only links to another table if that type of activity exists, for instance i have an activity information in the CloseCall table if the call is closed, but if it is a followup activity it is in the followup table i would like to display the close call notes from the closecall table if there is a close call activity in the activity table T_Followup T_activity< T_CloseCall here is the sql statement that i am trying to make work SELECT t_Activity.ActivityDate, tlkup_ActivityType.ActivityTypeName, t_Activity.LocationID, tlkup_Rep.RepFName + ' ' + tlkup_Rep.RepLName AS RepName, tlkup_InterestLevel.InterestLevel, CASE activitytypeid WHEN 6 THEN SELECT Closecallnotes FROM t_closecall WHERE t_Closecall.activityid = t_activity.activityid ELSE t_Activity.ActivityNotes END AS ActivityNotes FROM t_Activity INNER JOIN tlkup_ActivityType ON t_Activity.ActivityTypeID = tlkup_ActivityType.ActivityTypeID INNER JOIN tlkup_Rep ON t_Activity.RepID = tlkup_Rep.RepID INNER JOIN tlkup_InterestLevel ON t_Activity.InterestLevelID = tlkup_InterestLevel.InterestLevelID WHERE (t_Activity.LocationID = 93) ORDER BY t_Activity.ActivityDate DESC is this possible this way if not how do i make it work, right now it is just telling me there is a syntax problem, which is why i thought i would post it here because i am not too sure on the syntax of a case command and how it can be used
View Replies !
Using LIKE In A CASE Statement
I have a unique situation. My data looks something like:Test 1Test 2Test 3Test 2Test 1Test 300Test 200Test 1Test 300Test 200I want to display all of the above like:Test 1Test 2Test 3Test 2MetalsTest 1Test 1Notice that I have everything displayed except for 'Test 200' and'Test 300'. This have been replaced with the word 'Metals' and I'monly displaying it one time, no matter how often Test 200 shows up, orTest 300.My code looks like the following. It works good, except if I have toadd 'Test 400' etc...I would have to hard code them and it will buildup real quick. I looked at GROUPs to see if that would help, but Idon't think it would because it has to be displayed with a differentname such as 'Metals' for all the tests...Test 100, 200, etc... Isthere a better way without having to add each number in it? I thinkthe best way is to use a LIKE statment where the WHEN is being usedbut I keep getting errors if I use the LIKE sytnax where the WHEN isbeing used.SELECT sampleFROM mysamplesWHERE (sample <'TEST 200') AND (sample <'TEST 300')UNION ALLSELECT DISTINCTSAMPLE = CASE Sample WHEN 'test 200' THEN 'Metals' WHEN 'Test 300'THEN 'Metals'ENDFROM MYSAMPLESWHERE (sample = 'TEST 200') OR(sample = 'TEST 300')Any help is appreciated...
View Replies !
SQL CASE Statement
Hi Ive got a simple query where I want to calculate an average of one number divided by the other ie: avg(x / y) Im trying to use a case statement to return 0 in the event that y is 0, to avoid a division by zero error. My query is still returning a division by zero error anyway can anybody help? SELECT CCode, CASE WHEN BS_TOTAL_ASSETS = 0 THEN 0 ELSE AVG(BSCLTradeCreditors / BS_TOTAL_ASSETS) END AS myaverage FROM [Company/Year] GROUP BY CCode, BS_TOTAL_ASSETS Thanks
View Replies !
CASE Statement Help
Hello everyone again, now I want to take my SP a step further. As in using a CASE towards my 'taxable' field. It's value is 1 if no tax, and null if taxed. I have two seperate formulas I want to run depending on the 'taxable' value. Below is what i'm starting to chew on... CASE when tbl_products.ptaxable = '1' then ((1+(.02))*((tbl_products.pprice)*(tbl_material_us ed.mqty))) else 0 end How would I integrate my other formula for when the 'taxable' value is null? After the "else 0" in my CASE? CASE when 'taxable' = 1 THEN run_formula1 ELSE run_formula2 ??? These CASE statments will go in my WHERE clause correct? Thanks!!!
View Replies !
AVG(CASE) Statement - Help Please
I am trying to get avg score by site, by call type. Columns are Site(varchar), Calltype(varchar), totalscore(float). Calltypes are A, B, C, D. Sites are 1, 2, 3, 4. I can do a straight average statement and only get one calltype. I want to do a CASE statement to get all average scores for all calltypes. Select Site, avg(totalscore) as [Avg Score] FROM DB WHERE calltype = 'A' GROUP BY Site Results Site Avg Score (for A) 1 85 2 75.5 3 85.33 SELECT Site, AVG(CASE WHEN TotalScore > 0 AND CallType = 'A' THEN Totalscore ELSE 0 END) AS [Avg Score For A] FROM DB GROUP BY Site Results Site Avg Score For A 1 i get 8.5 2 i get 37.75 3 i get 36.57 Why am I getting a difference? Any help is greatly appreciated - thank you
View Replies !
Case Statement
Have a procedure called Collections_Cats. It runs fine but l want to modify it but l'm finding it difficult to get the desired output. The case statement gives an example of what l'm trying to achieve. l would like to build it into my procedure. l have a table called paysheet customer_no loan_no payday payfrequency etc 0000000000 000000 22 3 111111111122222212 4 3333333333 333333 21 2 5555555555 555555 6 1 payfrequency 1= daily 2= weekly 3= fortnightly 4= monthly l would like to get all the records that satisfy my select criteria. Which is select all records and fields as specified in my procedure that have a payday thats today only? How can l build my case statement into the procedure?Is ther a better way of doing it? l also want to do a count of all the records that have been processed by the procedure ? Declare @Today int Set @Today = (SELECT Day(GETDATE())) SELECT CASE WHEN Pay_frequency = 4 And payday =@Today THEN @Today WHEN Pay_frequency = 3 And payday =@Today THEN @Today WHEN Pay_frequency = 2 And payday =@Today THEN @Today WHEN Pay_frequency = 1 And payday =@Today THEN @Today ELSE 0 END AS "Pay_day",Customer_No,Pay_Frequency FROM Pay_Sheet Order by Pay_frequency Desc ************************************************** ************* ************************************************** ************* CREATE Procedure Collections_Cats AS BEGIN Declare @today int Set @today = (SELECT Day(GETDATE())) DECLARE Collections_Cats_Cursor CURSOR FOR SELECT n.loan_No AS Loan_No, n.customer_No AS Customer_No, c.first_name AS First_name, c.second_name AS Second_name, c.surname AS Surname, c.initials AS Initials, b.Bank_name AS Bank_name, br.branch_code AS Branch_code, d.bank_acc_type AS Bank_acc_type, pay_sheet.pay_frequency AS Pay_Frequency, n.monthly_Payment AS monthly_Payment, pay_sheet.payday AS payday FROM Transaction_Record tr INNER JOIN Loan n ON tr.loan_No = n.loan_No INNER JOIN Customer c ON n.customer_No = c.customer_no INNER JOIN Bank_detail d ON c.customer_no = d.customer_no INNER JOIN Branch br ON d.Branch = br.Branch INNER JOIN Bank b ON br.Bank = b.Bank INNER JOIN pay_sheet ON c.customer_no = pay_sheet.customer_no WHERE Pay_sheet.Payday = @today OPEN Collections_Cats_Cursor -- Perform the first fetch. FETCH NEXT FROM Collections_Cats_Cursor -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM Collections_Cats_Cursor END CLOSE Collections_Cats_Cursor DEALLOCATE Collections_Cats_Cursor END; GO
View Replies !
Case Statement
How do l use the case statement to cater for these updates. BEGIN TRANSACTION UPDATE TBL_DEV_OL_NEW1 SET Entity = ('AB') WHERE AB_CLIENT = 1 COMMIT GO BEGIN TRANSACTION UPDATE TBL_DEV_OL_NEW1 SET Entity = Entity + '' + ' | SB' WHERE SB_CLIENT = 1 COMMIT GO BEGIN TRANSACTION UPDATE TBL_DEV_OL_NEW1 SET Entity = 'SB' WHERE SB_CLIENT = 1 And entity is null COMMIT go BEGIN TRANSACTION UPDATE TBL_DEV_OL_NEW1 SET Entity = Entity + '' + (' | CI') WHERE CI_CLIENT = 1 COMMIT GO BEGIN TRANSACTION UPDATE TBL_DEV_OL_NEW1 SET Entity = 'CI' WHERE CI_CLIENT = 1 And entity is null COMMIT GO BEGIN TRANSACTION UPDATE TBL_DEV_OL_NEW1 SET Entity = Entity + '' + (' | GEMS') WHERE GEMS_CLIENT = 1 COMMIT GO BEGIN TRANSACTION UPDATE TBL_DEV_OL_NEW1 SET Entity = 'GEMS' WHERE GEMS_CLIENT = 1 And entity is null COMMIT GO
View Replies !
IF Else Within A Case Statement
In my query below i have the results ,The thing to observe in the result set it for the name "Acevedo" , "Abeyta" its not doing a group by and populating the results in the following column.Rather its addind a new row and adding it as 1 in the next row. I have to populate the counts in one row for common names.Shall i use a if condition within a case block.If yes how?any other work arounds would be appriciated. Please help Thanks select isnull(replace(Ltrim(Rtrim(P.Lastname)),',',''),'' ) Lastname , case ProductID WHEN 22 then count(S.Product) Else 0 END AS Builders , case ProductID WHEN 23 then count(S.Product) Else 0 END AS Associates , case ProductID WHEN 24 then count(S.Product) Else 0 END AS Affiliates FROM vwpersons p with (nolock) join vwSubscriptions S with (nolock) on S.RecipientID = P.ID where P.Lastname in (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H) group by P.Lastname, S.ProductID having count(P.LastName)>=1 order by 1 Result set : Firstname Builders AssociatesAffiliates Abarca 010 Abascal200 Abelar 100 Abeyta100 Abeyta010 Abreu 100 Abreu 010 Acevedo100 Acevedo050
View Replies !
CASE Statement
I am trying determine if I can do something like the code below. I have done a left join on a table. In the select statement there are three possible values. Yes, No, or NULL. I could like to use a Case statement to determine if there is Null. If so, then output N/A in place of the Null. So then my possible valus are Yes, No, and N/A. Any clues? Thanks, John SELECT TOP 100 OfferDressRoomYN.yesno as OfferDressRoom = CASE WHEN offerDressRoomYN.yesno IS NULL THEN 'N/A' END, FROM dataquestionnaire dq LEFT OUTER JOIN yesno OfferDressRoomYN ON dq.c3_1 = OfferDressRoomYN.yesnoid
View Replies !
CASE Statement
Hello All, I have a condition for which I am trying to write a case statement as follows.. SELECT @Segment_Field = 'Acct_Status_' + CASE @Public_record_Type WHEN NULL THEN ' ' WHEN 'BP' THEN 'BP' + ((CASE @Bankruptcy_Type WHEN NULL THEN ' ' ELSE '1' END) OR (CASE @Acct_Status WHEN NULL THEN '' ELSE '2' END)) END But this is not working.The condition is actually tht I have to set the value for the variable @Segment_Field acc to the value of variables @Bankruptcy_Type and @Acct_Status. It should first check for @Bankruptcy_Type and if it is not null then the value should be Acct_Status_BP1.Then it should check for @Acct_Status and if it is not sull then the vlaue should be set to Acct_Status_BP2. Can somebody please help.. Thanks
View Replies !
CASE Statement With IN/OR
Hello, I'm trying to write a query with case statement. the condition is when project_ref =393 then select qtn_ref in (7070000,7060000)) and when project_ref =391 and select q.qtn_ref=8700000 I need this condition in 'WHERE' statement. I can use 2 SELECT queries using 'IF ELSE', but I woud like to find out if there's any way to use CASE so I can write 1 query. Tring to do something like this but it doesn't work. SELECT * FROM table WHERE qtn_ref = case when project_ref =393 then 7070000 or 7060000 when project_ref =391 then 8700000 Anyone can help, please?
View Replies !
Case Statement
Hi! I need a case that returns the result of a select if it is not null, and -1 if it is null. I did it this way: select case when(select column from table where conditions) is null then -1 else(select column from table where conditions) But it doesn't seem very clever to repeat the select statement. Is there any way I can do it without repeating the "select column from table where conditions"??? Thank you!
View Replies !
SQL CASE Statement
Hi, The below CASE statement should check if the @Period variable is set to a monday date. If it is then I want to get data where r.ReceivedDate is set to a Friday date. What is wrong with the below syntax? Get this error... Incorrect syntax near '='. Code Snippet WHERE (p.Name = 'test') AND CASE WHEN LEFT(DATENAME(dw, @Period), 3) = "MON" THEN (CONVERT(VARCHAR(10), r.ReceivedDate, 103) = DATEADD(day, -3, @Period) ELSE (CONVERT(VARCHAR(10), r.ReceivedDate, 103) = @Period)
View Replies !
T-SQL - CASE Statement
I need to execute a simple Case Statement. Strangely, if I include a comparison operator I receive an error message showing a syntax error (Message 102, Level 15). But the same statement without the operator works jsut fine. Works: Select LastName, Group = Case Age When 15 Then '1' When 18 Then '2' Else '20' From tblAgeGroup End Does Not Work: Select LastName, Group = Case Age When <15 Then '1' When <18 Then '2' Else '20' From tblAgeGroup End Also, I noticed that this does not work in any other combination of the "When.." clause. Any help is appreciated. Thanks
View Replies !
Is Case Statement The Only Way
Hi I need to generate a SQL report like below,its basically calculating the count of students For District Level then for Region Level then for Each School under a Region Like the Display Below District summary Total Male Femal Indian White Asian -- -- -- Type1 22 33 22 11 11 11 23 11 13 Type2 2 €¦6 €¦7 ;;;; 13 14 Region 1 Region 2...................Region 8 Each School School1....School 15 Do I have to have case for each Type for each Race and then for Each Levels of District Region And 500 Schools?? Please Help Thanks
View Replies !
Need Help Using Case Statement
I've these following table SEL Date_Taken | Main_ID | Time | Daily_Rainfall --------------------------------------------------- ... ... ... 4/3/2005 | 202 | 1015 | 2 4/3/2005 | 202 | 1045 | 2 4/3/2005 | 202 | 1215 | 7 4/3/2005 | 203 | 715 | 2 4/3/2005 | 203 | 1345 | 2 4/3/2005 | 203 | 1530 | 7 ... ... ... 5/29/2005 | 203 | 1100 | 56 5/29/2005 | 203 | 1130 | 156 5/29/2005 | 203 | 1145 | 256 ... ... ... Station_Info State | Main_ID -------------------- SEL | 202 SEL | 203 SEL | 204 SEL | 205 SEL | 209 Main_ID | Month_Year | 3MthCumRf | 6MthCumRf | 9MthCumRf | 12MthCumRf ------------------------------------------------------------------------------ 202| 4/30/2005 | 525.8 | 683.11 | 356.33 | 754.33 203| 4/30/2005 | 435.5 | 400.9 | 290.34 | 234.34 204| 4/30/2005 | 265.53 | 453.21 | 543.66 | 753.24 205| 4/30/2005 | 251.38 | 754.33 | 478.34 | 785.22 209| 4/30/2005 | 259.5 | 356.34 | 894.33 | 354.78 202| 5/30/2005 | 565.8 | 383.11 | 756.33 | 254.33 203| 5/30/2005 | 485.5 | 444.9 | 744.34 | 755.34 This query only display Max(Daily_Rainfall) per day from SEL SELECT t1.MAIN_ID,t1.DATE_TAKEN,t1.TIME, t1.DAILY_RAINFALL, CASE WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=3 THEN t3.[3MthCumRf] WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=6 THEN t3.[6MthCumRf] WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=9 THEN t3.[9MthCumRf] WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=12 THEN t3.[12MthCumRf] END AS CumRf FROM (SELECT ROW_NUMBER() OVER(PARTITION BY DATE_TAKEN,MAIN_ID ORDER BY DAILY_RAINFALL DESC) AS RowNo,* FROM dbo.SEL)t1 INNER JOIN dbo.STATION_INFO t2 ON t2.MAIN_ID=t1.MAIN_ID AND t1.RowNo=1 INNER JOIN dbo.LT t3 ON t3.STATION_ID =t2.STATION_ID AND t1.DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-3,GETDATE()),101) ANDt1.DATE_TAKEN<CONVERT(VARCHAR(10), GETDATE(), 101) WHERE t2.STATE='SEL' ORDER BY MAIN_ID,DATE_TAKEN,TIME In my query, i still can query if last3month using CASE WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=3 THEN t3.[3MthCumRf] How to adjust my CASE statement if Main_ID=203, Date_Taken=5/5/2005, i should pickup following rows Main_ID | Month_Year | 3MthCumRf | 6MthCumRf | 9MthCumRf | 12MthCumRf ------------------------------------------------------------------- 203| 5/30/2005 | 485.5 | 444.9 | 744.34 | 755.34 if Main_ID=203, Date_Taken=4/5/2005, i should pickup following rows Main_ID | Month_Year | 3MthCumRf | 6MthCumRf | 9MthCumRf | 12MthCumRf 203| 4/30/2005 | 435.5 | 400.9 | 290.34 | 234.34 Need someone help... plz
View Replies !
Case Statement
Im running the following sql statement but I dont see the expected output. There are few differences between acc & cl1, mcc & cl2 , ncr & cl3 but I dont see either 'ONE' or 'TWO' or 'THREE'. There is even a case where cl3 is null but the sql is not filling in either one or two or three. Query simply returns id & rest as null values. SELECT P1.id, CASE WHEN p1.acc!= p1.cl1 then 'ONE' WHEN p1.mcc!= p1.cl2 then 'TWO' when p1.ncr!= p1.cl3 then 'THREE' Else NULL END As NOnMatchingColumn from (select id, acc, cl1,mcc,cl2,ncr,cl3 from dbo.ml)P1
View Replies !
Case Statement
I have a simple query with a case statement: Select Case when HR.PREM_CUR_QUOTED_AM <> 0 and HR.PREM_LOST_AUTO_AM <> 0 and HR.PREMIUM_BOUND_AM <> 0 and HR.PREMIUM_BOUND_AM <> 0 THen 0 else 1 end Cur_check from dbo.THIT_RATIO_DETL HR In the where clause i want to to put where cur_check = 1 but i'm getting an error that says : Invalid column name 'Cur_check' Can you use a case statement column in the where clause?
View Replies !
Case Statement
I am very new at SQL and I need some help if you can. A lot of my case statements have not been working for me. When I run my query the new names do post into the PRO_XTYP field. But when I want to add a criteria into my where clause it does not recognize the change. I have been trying to figure this out. My where clause would be…… Where Pro_XTYP = ‘Ancilary’ (nothing shows), but when I put Pro_XTYP = ‘ANCI’ then data pops up. Why isn’t my labels working? Do I need to write a IF Clause? Below is my case statement. Pro_XTYP = Case When Pro_XTYP = 'PRIM' then 'Physician' When Pro_XTYP = 'PROV' then 'Physician' When Pro_XTYP = 'SPEC' then 'Physician' When Pro_XTYP = 'ANCI' then 'Ancillary' When Pro_XTYP = 'HOSP' then 'Hospital' else Pro_XTYP End,
View Replies !
Case Statement Help
select * into #t1 from pCodes update #t1 set postcode = left(postcode,(charindex(' ', postcode)+1) + ' ' + right(postcode,3)) from #t1 where len(postcode) = 6 update #t1 set postcode = left(postcode,(4)-1) + ' ' + right(postcode,3) from #t1 where len(postcode) = 7 whats wrong with my case statment below cant seem to get it working! SELECT CASE POSTCODE WHEN LEN(POSTCODE) = 6 THEN left(postcode,(charindex(' ', postcode) -1 )) + ' ' + right(postcode,3), CASE POSTCODE WHEN LEN(POSTCODE) = 7 THEN left(postcode,(charindex(' ', postcode) -1 )) + ' ' + right(postcode,3), END FROM #T1
View Replies !
WHEN / CASE Statement
Folks! Getting error msg from the attached stored procedures! Error msg-'Invalid column name 'TRUE' can you help/tell what I'm doing wrong Thk Josephine CREATE PROCEDURE dbo.SP_EXPERIAN_QUEUE ( @FRAUDANALYSTNVARCHAR(50), @QUEUENVARCHAR(24), @STATUSNVARCHAR(24) ) AS SELECT E.EXPERIANID, E.ALERT_RECEIVED_DATE, E.ACCT, E.SRC_INDV_ID, E.INQUIRY_GOVT_NUMBER, E.CUST_NM, E.INQUIRY_STATE, E.INQUIRY_DATE, E.ALERT_IDENTIFIER, E.[OVERRIDE] , E.BEST_SSN, E.PRECISEIDSCORE FROM TBLEXPERIAN E WHERE TRUE = CASE WHEN @FRAUDANALYST = 'ALL' THEN TRUE WHEN EXPERIANANALYSTASSIGNED = @FRAUDANALYST THEN TRUE ELSE FALSE END AND TRUE = CASE WHEN @QUEUE = 'ALL' THEN TRUE WHEN @QUEUE = 'LOWSCORE' AND ( E.PRECISEIDSCORE < '500' OR ( E.PRECISEIDSCORE < '600' AND ( E.FraudShieldIndicator4 = 'Y' OR E.FraudShieldIndicator5 = 'Y' OR E.FraudShieldIndicator6 = 'Y' OR E.FraudShieldIndicator13 = 'Y' OR E.FraudShieldIndicator14 = 'Y' OR E.FraudShieldIndicator25 = 'Y' OR E.FraudShieldIndicator26 = 'Y' OR E.FraudShieldIndicator27 = 'Y' OR E.FraudShieldIndicator16 = 'Y' OR E.FraudShieldIndicator10 = 'Y' ) ) ) THEN TRUE WHEN @QUEUE = 'HIGH' THEN TRUE ELSE FALSE END AND TRUE = CASE WHEN @STATUS = 'ALL' THEN TRUE WHEN @STATUS = 'PENDING' and ( E.EXPERIAN_DECISION_DT IS NOT NULL AND E.EXPERIAN_DECISION IN ('YELLOW', 'E-MAIL SENT', 'ESCALATED FOR DECISION', 'MANAGEMENT REVIEW', 'QUALITY REVIEW', '7 DAY LETTER') ) THEN TRUE WHEN @STATUS = 'UNWORKED' AND E.EXPERIAN_DECISION_DT is null THEN TRUE ELSE FALSE END Josephine
View Replies !
Case Statement ?
I have a case statement: (AVG_Excess*1.0) / CASE Bound WHEN 0 THEN 1 ELSE Bound END AS Avg_SIR_Excess I want to add null as a when statement with 0 is this correct syntax to use: (AVG_Excess*1.0) / CASE Bound WHEN 0 or NULL THEN 1 ELSE Bound END AS Avg_SIR_Excess Thanks!
View Replies !
Case Statement
(CASE WHEN (Sheet1$.NOTES = 'unscheduled visit') THEN 1 END) AS [unscheduled visit] I am not sure how to use the Case statement with more than one Note ie unscheduled visit early termination I tried (CASE WHEN (Sheet1$.NOTES = 'unscheduled visit') OR (Sheet1$.NOTES = 'early termination') THEN 1 END) AS [unscheduled visit] (CASE WHEN (Sheet1$.NOTES = 'unscheduled visit'OR'early termination') THEN 1 END) AS [unscheduled visit] I am confused on the proper syntax. All the example I can find use a numeric value. Thanks!
View Replies !
Case Statement
Hello, I have a sql statement that uses case statement to calculate a variable, I would like to use that variable in the same sql statement to calculate a different variable. In my code Wed_Var is calculated, but since I dont know a way to use the Wed_Var in Wed_Adj, I copy the whole formula again. Any help is appreciated. Thanks SELECT TOP 100 PERCENT dbo.VIEW_ManPlan_Dock_Prev_4WK.Terminal, Wed_Var = CASE WHEN dbo.VIEW_ManPlan_Dock_Prev_Current.Tue = 0 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Tue_Var WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date < .75 THEN .75 WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date > 1.25 THEN 1.25 ELSE (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date END, Wed_Adj = CASE WHEN dbo.VIEW_ManPlan_Dock_Prev_Current.Tue = 0 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Tue_Var * (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date < .75 THEN .75 * (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date > 1.25 THEN 1.25 * (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) ELSE (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) * ((dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date) END, Week_To_Date = CASE WHEN dbo.VIEW_ManPlan_Dock_Prev_Current.Tue = 0 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date + (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) * dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Tue_Var WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date < .75 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date + (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) * .75 WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date > 1.25 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date + (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) * 1.25 ELSE dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date + (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) * ((dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date) END FROM dbo.VIEW_ManPlan_Dock_Prev_4WK INNER JOIN dbo.VIEW_ManPlan_Dock_Prev_Current ON dbo.VIEW_ManPlan_Dock_Prev_4WK.Terminal = dbo.VIEW_ManPlan_Dock_Prev_Current.Terminal INNER JOIN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday ON dbo.VIEW_ManPlan_Dock_Prev_4WK.Terminal = dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Terminal ORDER BY dbo.VIEW_ManPlan_Dock_Prev_4WK.Terminal
View Replies !
Case Statement
i ahve one fucniton: create function fntotalcountcustclas ( @campaign varchar(50), @startdate datetime, @enddate datetime) RETURNS TABLE AS RETURN ( Select t.itemnmbr,t.custclas, t.custclasdescription, t.totalcustclas as totalcount from ( select vi.itemnmbr, replace(vc.custclas,'','Unspecified') as custclas, vc.custclasdescription, count(vc.custclas) as totalcustclas from vwcustnmbr vc join vwitemnbmr vi on vi.sopnumbe=vc.sopnumbe Where vi.Campaign = @Campaign and (vc.docdate between @startdate and @enddate) group by vi.itemnmbr,vc.custclas, vc.custclasdescription ) as t ) when i m executing it: select * from fntotalcountcustclas('copd','1/1/2008','4/11/2008') order by totalcount desc i m getting results like: itemnmbr,custclas,custclasdescription,totalcount ------------------------------------------------ 06-5841 STANDARD Standard(web) 31 06-5840 STANDARD Standard(web) 30 kr-014 STANDARD Standard(web) 72 06-5841 INDPATIENT Patient 12 06-5840 INDPATIENT Patient 9 06-5845 INDPATIENT Patient 6 06-5841 PROGRAM Program 6 06-5841 INST-HOSPITAL Hospital 11 ................... Basically, i ahve to use one condition to get corrrect output related to inputs: like - i have to input @category varchar(50), @category_value varchar(50) and if category = 'campaign' then category_value = '' then output should be itemnmbr sum(totalcount) [whatever should be custclas or custclasdesscription] itemnmbr sumcount ----------------- 06-5840 52 06-541 101 06-452 26 kr-045 252 and if categroy = 'item' then category_value = any itemnmbrs(06-5840,06-5845,06-5841 etc..) then output should be itemnmbr custclas custclasdescription totalcount ----------------------------------------------------- 06-5840 STANDARD Standard(web) 31 06-5840 INDPATIENT Patient 9 06-5840 PROGRAM Program 6 06-5840 INS-HOSPITAL Hospital 17 like that.. can anyone help me to write case statement. thanks a lot!! create function fntotalcountcustclas ( @campaign varchar(50), @startdate datetime, @enddate datetime, @category varchar(50), @category_value varchar(50)) RETURNS TABLE AS RETURN ( Select t.itemnmbr,t.custclas, t.custclasdescription, t.totalcustclas as totalcount, case when category from ( select vi.itemnmbr, replace(vc.custclas,'','Unspecified') as custclas, vc.custclasdescription, count(vc.custclas) as totalcustclas from vwcustnmbr vc join vwitemnbmr vi on vi.sopnumbe=vc.sopnumbe Where vi.Campaign = @Campaign and (vc.docdate between @startdate and @enddate) group by vi.itemnmbr,vc.custclas, vc.custclasdescription ) as t )
View Replies !
Help With Case Statement
I'm not sure if I am doing this the right way. In my table I have project ids. Each project id has several activities associated with it. Each project has a starting balance. Each activity posts an expense to the total balance of the project. If the project has enough money to handle the charges made by the activities, all the activity expenses can be "posted". If there isn't enough money, I want to loop through the activities, check to see if there is enough of a balance to "post" the first one, If there is, then I want to re-adjust the balance and check the second activity. I want to scroll through each project/activity to see what can be "posted". Here is what I have so far, but I cannot work out how to change the total balance amount. Hopefully what I am trying to do makes sense! declare @testId nchar(6) declare @RowNum int declare @newBalance int select top 1 @testId=projID from #ProjIds set @RowNum = 0 WHILE @RowNum <= (Select Max(temp_id) from #ProjIds) BEGIN set @RowNum = @RowNum + 1 set @newBalance = (select top 1 Bal_2300 from #RevRecData where @testId=projId order by projID, activity) select projId, activity, postCr, Bal_2300, 'New_Status' = Case when (postCr <= Bal_2300) then 'Can Clear' else 'Still Check' END, 'New_Balance' = Case when (postCr <= @newBalance) then (@newBalance - postCr) else @newBalance End from #RevRecData where @testId=projId order by projID, activity select top 1 @testId=projId from #ProjIds where projId > @testID END
View Replies !
|
TRACKER
