I want to add an INNER JOIN based on a condition. If @Sports is not '' then I want to add the inner join statement...
The following doesnt work:
declare @Sports nvarchar(50)
set @sports='1'
SELECT DISTINCT tblUserData.UserName,tblUserData.UserCode,Description,IsDonator,IsVIP,BirthDate,IsMale,ShowAgeOnly,
tblCountries.CountryPicture,
case @LanguageColumnName
when 'nl' then tblSexuality.nl
when 'en' then tblSexuality.en
when 'de' then tblSexuality.de
end
as sexualityname,tblCountries.CountryName,ThumbNailPicture,UserRanking,LastActivityDate,NearestBigCity,DistanceToNearestBigCity,
ROW_NUMBER() OVER (ORDER BY IsVip DESC,UserRanking DESC,LastActivityDate DESC) as RowNum FROM aspnet_Users
INNER JOIN tblUserData ON aspnet_Users.UserId = tblUserData.UserID
INNER JOIN tblCountries ON tblUserData.Country=tblCountries.CountryID
INNER JOIN tblSexuality ON tblUserData.Sexuality=tblSexuality.SexualityID ,
case @Sports
when <>'' INNER JOIN tblUsersAndSports ON tblUserData.UserCode=tblUsersAndSports.UserCode
end
I was asked to add an additional column to an existing query. I'm using Microsoft Query with a MS SQL 2000 server, and don't have much knowledge of SQL in general. Here's the existing query:
SELECT A.COMPANYCODE, A.INVOICENUMBER, A.LINENUMBER, A.SONUMBER, A.CUSTOMERCODE, A.SHIPPERNUMBER, A.INVOICEDATE, A.ITEMCODE, A.QUANTITYINVOICED, A.UNITPRICE AS 'InvPrice', A.QUANTITYINVOICED * A.UNITPRICE AS 'ExtInvPrice', INVENTORY.UNITPRICE AS 'StdPrice', INVENTORY.STANDARDCOST, A.QUANTITYINVOICED * INVENTORY.STANDARDCOST AS 'ExtCost', (A.QUANTITYINVOICED * A.UNITPRICE) - (A.QUANTITYINVOICED * INVENTORY.STANDARDCOST) AS 'GM$', (INVENTORY.UNITPRICE - A.UNITPRICE) * -1 AS 'PriceListDiff' FROM ABW.DBO.SALESANALYSISHISTORY A, ABW.DBO.INVENTORY INVENTORY WHERE INVENTORY.COMPANYCODE = A.COMPANYCODE AND INVENTORY.ITEMCODE = A.ITEMCODE AND ((A.COMPANYCODE = 'csp') AND (A.QUANTITYINVOICED <>$ 0) AND (A.INVOICEDATE BETWEEN '03/1/08' AND '03/31/08')) ORDER BY A.INVOICEDATE, A.ITEMCODE
They want a column added to the current query where if A.Unitprice is greater than or equal to Inventory.UnitPrice then populate the column with A.QuantityInvoiced*A.UnitPrice. I posted on another forum, and the advice I got was to add this:
SELECT NewColumn = CASE WHEN A.Unitprice >= Inventory.UnitPrice THEN A.QuantityInvoiced * A.Unitprice ELSE 'null' END, FROM ABW.DBO.SALESANALYSISHISTORY A, ABW.DBO.INVENTORY INVENTORY
I'm not sure how to integrate it to the current query, so I've tried running it by itself, and I get the error: Didn't expect 'A' after the SELECT column list.
Any help would be greatly appreciated to modify the current query to display the new column.
SELECT COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [New Visitors], COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [Returning Visitors] FROM content_hits_tbl WHERE (hit_date BETWEEN DATEADD(mm, - 1, GETDATE()) AND GETDATE())
=======================
How do I add up both COUNT/CASE columns? Would it be: SUM([New Visitors] + [Returning Visitors]) AS Total
I tried this and it doesn't work. I get invalid column names error for both.
I have even tried: SUM([COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)] + [COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)]) AS Total
You would think that there would be some gui functionality in VS08 that would do this...
I have a a Group By query which is working fine aggregating records by city. Now I have a requirement to focus on one city and then group the other cities to 'Other'. Here is the query which works:
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' From [Output-MarketAnalysis] Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active' Group by [City]
Here is the result:
St. Louis 1000 Kansas City 800 Columbia 700 Jefferson City 650 Joplin 300
When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' From [Output-MarketAnalysis] Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active' Group by [City]
Here is the result:
St. Louis 1000 Other Missouri City 800 Other Missouri City 700 Other Missouri City 650 Other Missouri City 300
Remove Fields From results from Innerjoin In Sql --------------------------------------------------------------------------------
Hi, I' ve been struggling for a few days with following problem. I have a query with 2 tables (below). They are joined by one key field checknr. I would only like to see field comm from table OTCUser.checklistcomm, but only the last result which are conform to the condition. So the fields in bold shouldn't appair.Could someone help me ?
SELECT MAX(p.Modifdatum) AS modifdatumv, OTCUser.checklistcomm.comm, p.Status, p.station, p.checkitem FROM OTCUser.checkliststat p INNER JOIN OTCUser.checklistcomm ON p.Checknr = OTCUser.checklistcomm.checknr GROUP BY p.station, p.checkitem, OTCUser.checklistcomm.comm, p.Status HAVING (p.Status = '2') AND (p.station = '00BE104740') ORDER BY p.station, p.checkitem
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?
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename] SET [No] = CASE WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa' ELSE 'Null' END
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.
Tried a few ways and I can ge this to work at the end in then WHEN part. Just struggling to put this together to be accepted as a CASE WHEN statement, probably missing the obvious.
Case when Postcode like '%[abcdefghijklmnopqrstuvwxyz%]' then 'Lowercase Postcode' else 'Postcode OK' end as [DQPostcode]
collate Latin1_General_CS_AS
Simple terms looking for all instances of Lowercase characters in the Postcode field
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.
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
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!
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.
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.
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!
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
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
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
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
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 )
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
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
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
In my temptable I'm still trying to replace NULL values with 0 (zero).I tried ISNULL and that didn't work.I tried ISNULL and it didn't work. I was told by a senior developer I work with that I should do something like a case when. whatever that is.
INSERT INTO #TEMPLEASEDOLLARSSUBMITTED (SalesName, UserID, LeaseDollarsSubmitted) ( SELECT (FirstName + '' + LastName)AS [Sales Team Member], Usr.UserID, SUM (ISNULL(MerAppEqL.MonthlyPayment,0))AS [Leases $ Submitted] FROM MerchantAppEquipmentLease MerAppEqL WITH(NOLOCK) INNER JOIN MerchantAppEquipment MerAppEqt ON MerAppEqL.EquipmentLeaseID = MerAppEqt.LeaseNumber
[code]...
Or should I have put the ISNULL when I updated #TEMPDASHBOARD because that is where the NULL values show up. They don't show up in #TEMPLEASEDOLLARSSUBMITTED table?
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
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
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
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,