I need to perform an update where there are multiple scenarios which determine the value that is entered. Below is a sort've psuedo code of how it needs to be.
Update MyTable SET MyColumn = CASE WHEN MyCol1 = 'Value1' Then NewValue Else
WHEN MyCol1 <> 'Value1' And MyCol2 = 'Active' Then 'Value1'
In the scenario where MyCol1 <> Value1 and MyCol2 <> 'Active' then no update would occur and the original value would remain intact.
I have 20 rows in dbo.test so i will get 20 as my output, now i need to write a case statement here such that when count(*) = 0 then it should display text filed 'NO Data' else it should display the count.
I have scenario where i have to pick one particular value from where condition. Here is the example:A store can have different types i-e A or B , A and B or either A or B.
Store Type Sales 11 A 1000 23 A 1980 23 B 50 5 B 560
I want to filter the store in "where clause" where
1)- if the store has type A and B, then assign only A 2)- if the store has type A associated with it then assign A 3)- if the store has type B associated with it, then assign B.
Select Store, sum(sales), Type from table1 where (TYPE]= (case when [TYPE] in ('A','B') then 'A' when [TYPE]='A' then 'A' else 'B'end)) GROUP BY [store], [TYPE]
The above statement is not working for when store has only Type B associated with it.
I have a query which works absolutely fine when connecting to an actual server:
WITH CLAIMDATA AS( SELECT DISTINCT DB_NAME() AS DBName, 'UA' AS Client, POLICY AS KMPONO,
[code]...
If i change the connector to REPLPROD (which is a linked server): From REPLPROD.XUNMDTAUAI.dbo.UPPOREP UP INNER JOIN REPLPROD.XUNMDTAUAI.dbo.UKKMREP UK
I get the error:
Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 125, Level 15, State 4, Line 1
All- I'd be interested if any of you could advise as to how the nested CASE statement below could refactored to be more concise. (Works fine as is, but just interested to know!) Thanks! 1 SELECT headcount.person_id, person.last as Lname, person.first as Fname, 2 3 CASE headcount.coming WHEN 'False' THEN '0' ELSE 4 5 CASE person.is_adult WHEN 'True' THEN 6 7 CASE person.is_y_parent_or_kid WHEN 'True' THEN activity_session.usd_per_y_parent 8 ELSE activity_session.usd_per_guest_adult9 END10 ELSE11 12 CASE person.is_kid WHEN 'True' THEN13 14 CASE person.is_y_parent_or_kid WHEN 'True' THEN activity_session.usd_per_y_kid15 ELSE activity_session.usd_per_guest_kid16 END17 END18 END19 20 END AS 'Cost', 21 22 23 24 25 no_answer, not_coming, coming, wanted, comment, activity_session.act_session_id, for_sale, headcount_id FROM headcount26 27 INNER JOIN person28 ON headcount.person_id = person.person_id29 30 INNER JOIN activity_session31 ON headcount.act_session_id = activity_session.act_session_id
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
I've been beating my head against this for a bit and haven't been able to figure this out I want to pull 1 set of values between a date time range if @Report = '1' but if @Report = 2 or 3 I want it to drop 3 of the parameters so it will pull all items. by dropping the where parameters for @BeginRangeDate and @LookOutDate and L.COLineStatus. My report works great if I go in and run Report 1 with the date parameters in the where statement and also works great for Reports 2, 3 if I REM out those 3 items in the Where statement. Here is what I currently have
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.
I've always been mistified why you can't use a column alias in the group byclause (i.e. you have to re-iterate the entire expression in the group byclause after having already done it once in the select statement). I'mmostly a SQL hobbiest, so it's possible that I am not doing this in the mostefficient manner. Anyone care to comment on this with relation to thefollowing example (is there a way to acheive this without re-stating theentire CASE statement again in the Group By clause?):Select 'Age' =CaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))end,max(SubmittedOn), COUNT(SCRID) AS NbrSCRsFrom SCRViewWHERE(StatusSort < 90) ANDCustomerID = 8 andUserID = 133group byCaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))endOrder by max(submittedon) descThanks,Chad
In the below query, I can get the individual/single group by columns as well as multiple but I cannot control the order in which I would like to group by the data.
So lets say I want to group the data by OS->browser->browser_version(just one example) then I cannot achieve that as the order of OS column comes later in the query.
I know one option would be to write a dynamic SQL but i dont want to do that because of performance reasons. Any other way this can be achieved?
select case when @include_browser = 1 then browser_name end as browser_name, case when @include_browser_version = 1 then browser_version end as browser_version,
Is it possible to combine a CASE statement with two different columns to retrieve data into one result column? In one column it has multiple JobCode, but this needs to be divided. The only way I can see to do this is take the data from another column to get the results.Ex. JobCode - has one code for several job descriptions (there are about 30), but all within this code cannot have the same level of access. So I need to divide them out, and put them in one column for AccessLevel.
JobTitle - has one code for one job, (but there are over 100).I want to pull from both columns to get the results I need to assign appropriate access level in one column.
Case JobCode (they all have same job code, but everyone in this job code should not have same access) When '45' Then '1' (Principal, Asst. Prin, or any Administrator, Counselors) When '25' Then '2' (this could be teacher, etc. ) Case JobTitle (this is how access should be) When '12345' Then '1' (this is Administration only)
I am trying to run the below but I get an error of 'Incorrect syntax ')'' --- I have tried every angle I can think of around the parens to fix this but nothing I do is working.
UPDATE abcdefg SET [Date] = GETDate(), [readytogo] = ( CASE WHEN [customername] NOT IN (Select [customername] from [server].[database].[dbo].[view]) THEN 'Yes' ELSE 'Needs Verification'
I have used the below update query. However, its updating only the first value. Like its updating AB with volume when c.Type = ABC, similarly for CD. Its not updating based on the 2nd or the next case condition.
Update XYZ Set AB = a.Amt * (CASE WHEN c.Type = 'ABC' THEN (c.volume) WHEN c.TYPE = 'DEF' THEN (c.volume) WHEN c.Type = 'GHI' THEN (c.volume) Else 0 END), CD = CASE WHEN c.Type = 'MARGIN' THEN '4105.31' WHEN c.Type = 'ABC' THEN '123.1' WHEN c.Type = 'DEF' THEN '234.2' WHEN c.Type = 'GHI' THEN '567.1' END from table1 a join table2 b on a.Cust = b.Customer join table3 c on b.account = c.account and b.channel =c.channel
Why its not working properly? But if i use Select statement instead of update query its working properly.
I am trying to add the letters 'MS' in front of value while using a case statement. If Dispo = 2 I want it to pull back 'Inactive', else I want it to pull back the Value with MS in front (eg. "MS14"). The data in the value column are numbers. Would I use a CONCAT? If so where does that need to go?
Case when dispo = 2 then 'Inactive' else cast(Value as varchar(11)) end ,
table A: | ID | FRUIT | VEGETABLE | GOOD | -------------------------------------------- | 1 | orange | cabbage | no | | 1 | apple | lettuce | yes | | 1 | kiwi | broccoli | no | | 1 | pear | kale | yes |
table B: | ID | FRUIT | VEGETABLE | ------------------------------- | 1 | apple | lettuce | | 2 | pear | kale |
If the fruit and vegetable in table A is found in table B, then set the GOOD column = yes, else no.
This is what I have so far.
update tableA set GOOD = (case when tableA.id = C.id then 'yes' else 'no' end ) from (select tableA.id as id from tableA A left join tableB B on B.fruit = A.fruit and B.vegetable = A.vegetable) C
Hi, i've got this stored procedure: @LidFederatieNummer varchar(20), @ClubID int, @LidClubNummer varchar(50), @PersoonNaam varchar(100), @ClubStamnummer varchar(10), @ClubNaam varchar(50), @SeizoenID int, @ShowAllJN bit, @LidFederatieJN int, @CategorieID int AS BEGIN
IF @CategorieID IS NOT NULL BEGIN DECLARE @LicentieVerplicht int SET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID) END
IF (@ShowAllJN = 0) BEGIN SET RowCount 100 END
CASE @LicentieVerplicht WHEN '1' THEN BEGIN SELECTDISTINCT ClubStamnummer, ClubID, ClubNaam, LidID, PersoonID, Persoon, LidFederatieNummer, LidClubNummer, vwClubLidPersoonAlgemeen.LidClubID, vwClubLidPersoonAlgemeen.LicentieJN, UserName, PersoonActiefJN, LI.SeizoenID, LI.LidFederatieJN FROM vwClubLidPersoonAlgemeen INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC) WHERE LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND Persoon LIKE '%'+@PersoonNaam+'%'AND ClubStamnummer LIKE @ClubStamnummer AND ClubNaam LIKE '%' + @ClubNaam + '%' AND (@ClubID = 0 OR ClubID = @ClubID) AND (@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND (@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND vwClubLidPersoonAlgemeen.LicentieJN = 1 Order By Persoon END
WHEN '2' THEN BEGIN SELECTDISTINCT ClubStamnummer, ClubID, ClubNaam, LidID, PersoonID, Persoon, LidFederatieNummer, LidClubNummer, vwClubLidPersoonAlgemeen.LidClubID, vwClubLidPersoonAlgemeen.LicentieJN, UserName, PersoonActiefJN, LI.SeizoenID, LI.LidFederatieJN FROM vwClubLidPersoonAlgemeen INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC) WHERE LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND Persoon LIKE '%'+@PersoonNaam+'%'AND ClubStamnummer LIKE @ClubStamnummer AND ClubNaam LIKE '%' + @ClubNaam + '%' AND (@ClubID = 0 OR ClubID = @ClubID) AND (@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND (@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) ORDER BY Persoon END
WHEN '3' THEN BEGIN SELECTDISTINCT ClubStamnummer, ClubID, ClubNaam, LidID, PersoonID, Persoon, LidFederatieNummer, LidClubNummer, vwClubLidPersoonAlgemeen.LidClubID, vwClubLidPersoonAlgemeen.LicentieJN, UserName, PersoonActiefJN, LI.SeizoenID, LI.LidFederatieJN FROM vwClubLidPersoonAlgemeen INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC) WHERE LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND Persoon LIKE '%'+@PersoonNaam+'%'AND ClubStamnummer LIKE @ClubStamnummer AND ClubNaam LIKE '%' + @ClubNaam + '%' AND (@ClubID = 0 OR ClubID = @ClubID) AND (@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND (@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND vwClubLidPersoonAlgemeen.LicentieJN = 0 Order By Persoon END According the value of my @LicentieVerplicht variable: DECLARE @LicentieVerplicht intSET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID) My where clausule should change... how can i achieve this cause my case is not working because it is not inside the select statement
Hi everyone, I have the following problem withj this query:
SELECT DocNumber, Title,FirstName,LastName,Paper, ANNO,Location,Keywords, URL= case WHEN URL IS NULL THEN '|<a href=''https://illiad.library.nova.edu/illiad/FNN/illiad.dll?ILLiadRedirect=OpenURL&genre=thesis&isbn='+CONVERT(varchar,DocNumber)+'&title='+CONVERT(varchar,Title)+'&rft.pub='+CONVERT(varchar,Program)+'&rft.edition='+ CONVERT(varchar,Paper) +'&date='+ CONVERT(varchar,ANNO)+ '&aulast=' + CONVERT(varchar,Lastname) + '&aufirst=' + CONVERT(varchar,Firstname)+ '&url_ver=Z39.88-2004&rfr_id=info:sid/MARPS:Practicum''target=''blank'')> Order through ILLiad</a>' ELSE URL END FROM DocTable1 WHERE FREETEXT....
When URL is null, I don't get the string showing. It works properly only in the query analyzer.
Hey guys, i'm trying to use a CASE within a select but i'm not sure how to get started.
Here is my select so far, i'm inserting into a temp table that is used within a loop to gather data from my trigger:
Code:
insert into @temp_par select CONVERT(varchar(20),d.PAT_ID), ep.EXT_PAT_ID, d.DRUG_DESC_ID, d.ALLERGY_CD, CONVERT(varchar(20),d.PAR_DT,120), ISNULL(d.PAR_TXT, 'NULL'), ISNULL(REPLACE(m.DRUG_NM,'"','^"'),'NULL'), ISNULL(m.DOSAGE_FORM_CD,'NULL'), ISNULL(m.STRGTH,'NULL'), ISNULL(m.STRGTH_UM,'NULL') from deleted d --PATIENT_PRIOR_ADVERSE_REACTIONS join EXTERNAL_PATIENT ep on ep.PAT_ID = d.PAT_ID join MEDNAME m on m.DRUG_DESC_ID = d.DRUG_DESC_ID
I would like to use a CASE for the d.ALLERGY_CD column. The data in this column is usually a 1,2,3,4,5 but i'd like to convert those to their real data, for example:
Limitations: Needs to be single sql statement for exec, no udf/stored proc Objective: select aggregate count from tblPerson grouped by s_fk, division,Desc, areaDesc with rollup for totals if mail_goes_to=1, get the s_fk value via tblPerson/tblAffiliation/tblOrg/tblCity/tblCounty if mail_goes_to=2, get the s_fk value via tblPerson/tblCity/tblCounty, skipping tblAffiliation/tblOrg
What's the best method? A case statement? Would a cross-join work? Thanks so much for any and all advice!
INSERT INTO #rpt(Hic, DtImported, Source, PlanID, LastName, FirstName, MemID, CaseStatus) SELECT s.Hic, s.DtImported, s.Source, dbo.LastErolledPlan_C_D(s.Hic,s.IsPart_C) as PlanID, m.LastName, m.FirstName, dbo.GetMemID_PartC_D(s.Hic,s.IsPart_C) as MemID, s.CaseStatus = CASE WHEN CaseStatus ='1' or CaseStatus='0' THEN 'New' CASE WHEN CaseStatus <>'1' or <> CaseStatus <>'0' Old
ElSE 'No Valid' End
from tbEsccSuspects s inner join tbPDMMembers m on s.Hic = m.HicNumber Where s.EUFStatus = 1 -- AND DtEUFSent is NULL
what I am doing wrong in the case statment that I am getting an error. ?????
ClaimNumTransactionDateUsername ClaimNum TransactionAmountUserName 2000074 20150209jerry.witt 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000074 20150626DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL
[Code] .....
So,if we look at the result set, we notice 2 conditions where the IG_FinancialTransactionSummary.Username is like 'Data' and if we see the transaction date then sometimes that is the max transaction date or sometimes there are transactions that happened after but that doesn't have like '%data%' in username . So, i need to add a new column to my sql query which should basically verify if the username is like '%data%' and if that is the max(transaction date) or even if there are any transactions after that doesn't have like '%data%' then YES else No.
Hi,I am wondering how do I write a query that will take case sensitive into consideration. For example, I have "ABCD", "abcD", ABcd", "AbCd" in a table. I want to retrieve "AbCd" only from the table. This is the sql statement that I have:sqlSelect = "SELECT * FROM [al_table] WHERE [alphabet]= @alpha"Dim selectCmd As New SqlCommand(sqlSelect, myConnection) selectCmd.Parameters.AddWithValue("@alpha", "AbCd") From what I have it will return all those value. But that is not what I want. Thanks
I need to pull one field from one table and one field from another table that is i need to pull 'eGroupName' field from 'Exception' Table and 'eGroup Description' field from 'eGroup' Table but there is no connection between these two tables means there is no forign key relationship between these two tables but i need to pull both fields . If i use INNER JOIN i need to mention relationship between both tables right? so how to write query for this , and one more thing is i need to add an extra column as "Location"which is not there in either of tables for that i need to use CASE Statement as if DataSource = 1 then "ABC" else "BCD" . pls help me out in writing SQL Statement??? is this correct ?? its showing me errors Select Exception.eGroupName, eGroup.eGroupDescription from Exception Inner Join eGroup ON ??? (case when 'DataSource =1' then 'ABC' then 'BCD' endcase) Where ..... Pls correct me Thanks
I need a hand in this script. SELECT a.id ,value = CASE when (x1-x0) = 0 then ((Y2-Y0)-(Y1-Y0))/(0.000000001*(X2-X0)) ELSE ((-111.48277-Y0)-(Y1-Y0))/((X1-X0)*(32.72383-X0)) END ,value2 = CASE when (x1-x0) = 0 then ((Y2-Y0)-(Y1-Y0))/(0.000000001*(X2-X0)) ELSE ((-111.48277-Y0)-(Y1-Y0))/((X1-X0)*(32.72383-X0)) END FROM table_name a where value > 0 ORDER BY VALUE
The problem I'm having is in the where clause. Because the value is not a column name sql server is giving me an error. I need to evaluate the value returned. How can I get around this problem? Any sort of advise will help! Thank you in advance!
I am using a CASE statement within a SELECT query to sum up values for different customers.
SELECT CR_CUST.Customer_Code, 'General_01' = CASE WHEN CR_PROD.Part_Class_Code = '01' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END, 'General_07' = CASE WHEN CR_PROD.Part_Class_Code = '07' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END, 'General_08' = CASE WHEN CR_PROD.Part_Class_Code = '08' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END FROM CR_CUST INNER JOIN CR_INVOICE ON CR_CUST.Customer_Code = CR_INVOICE.Customer_Code INNER JOIN CR_PROD ON CR_INVOICE.Product_Code = CR_PROD.Product_Code WHERE (CR_PROD.Part_Class_Code = 1 OR CR_PROD.Part_Class_Code = 7 OR CR_PROD.Part_Class_Code = 8) GROUP BY CR_CUST.Customer_Code, CR_PROD.Part_Class_Code
My question is this - is it possible to expand my SQL Query into a Sub Query so that each customers data appears on the same line of the results?, like so...
I can achieve this by writing my results into a temporary table and extracting the data with the following SQL Query, but I just thought it would be really cool if I could do it in one SQL Statement without using a temporary table.
SELECT Customer_Code, SUM(General_01), SUM(General_07), SUM(General_08) FROM #MyTempTable GROUP BY Customer_Code
