SQL Server 2012 :: Count Value In Case Statement

Aug 5, 2014

My table structure like below.

id date value1 value2
1 5-june-2104 Yes No
1 6-june-2014 No Yes
2 5-june-2104 Yes Yes

Want to calculate yes count on any day for same id

View 5 Replies


SQL 2012 :: Using Count Function And Case In One Select Statement

Jul 9, 2014

I am selecting the count of the students in a class by suing select COUNT(studentid) as StCount FROM dbo.student But I need to use a case statement on this like if count is less than 10 I need to return 'Small class' if the count is between 10 to 50 then I need to return 'Medium class' and if the count is more than 50 then 'Big class'.

Right now I am achieving this by the following case statement

SELECT 'ClassSize' = CASE WHEN Stcount<10 THEN 'Small Class'
WHEN Stcount>=10 and StCount<=50THEN 'Medium Class'
WHEN Stcount>50 THEN 'Big Class'
select COUNT(studentid) as Stcount FROM dbo.student) Stdtbl

But can I do this with just one select statement?

View 2 Replies View Related

SQL Server 2012 :: Update Statement With CASE Statement?

Aug 13, 2014

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] =
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'

What is the best way to script this

View 1 Replies View Related

SQL Server 2012 :: Case When Statement - N Number Of Years Without Modifications

Feb 4, 2014

Below is the scenario which I have currently in my query. I need to write this query without any hardcode values , so that it will work til n number of years without modifications.

Startdate =
Trandate between '06-04-2013' and '05-04-2014' then '06-04-2013'
Trandate between '06-04-2012' and '05-04-2013' then '06-04-2012'
Trandate between '06-04-2011' and '05-04-2012' then '06-04-2011'
Trandate between '06-04-2010' and '05-04-2011' then '06-04-2010'
Trandate between '06-04-2009' and '05-04-2010' then '06-04-2009'
Trandate between '06-04-2008' and '05-04-2019' then '06-04-2008'

View 5 Replies View Related

SQL Server 2012 :: Case Statement Executing A Stored Procedure

Aug 20, 2014

Is this possible, I am trying to execute a stored procedure depending on what parameter is selected, something like this????

when field = 'value' then execute sp_procedure else execute sp_procedure_2 end

View 1 Replies View Related

SQL Server 2012 :: Filtering Query Using CASE Statement Within WHERE Clause

Aug 21, 2014

How I am using a CASE statement within a WHERE clause to filter data:

@filter1 varchar(100) = '',
@filter2 varchar(100) = ''

[Code] .

What I want, is to be able to pass in a single value to filter the table, or if I pass in (at the moment a blank) for no filter to be applied to the table.

Is this a good way to accomplish that, or is there a better way? Also, down the line I'm probably going to want to have multiple filter items for a single filter, what would be the best way to implement that?

View 5 Replies View Related

SQL Server 2012 :: Case Statement - How To Get Values Into Alias Extension

Oct 6, 2014

I have a bit of trouble getting values into one alias field, my code is below. I am trying to get values into the alias extension, Agent_ID is sometimes null, and so is agent_id2, however sometimes they both have values in them, and then only one of the values is every returned. When in the example below only Agent_ID (11111) is ever returned by I want both of them returned.

Agent_ID Agent_ID2
11111 22222
CASE WHEN [AGENT_ID] is not null then AGENT_ID
when agent_id2 is not null then agent_id2 end as extension
FROM [AA_Helper].[dbo].[tblEmpData]

View 9 Replies View Related

SQL Server 2012 :: Significant Performance Dropping When Use CASE Statement

Oct 14, 2014

1. I have a simple JOIN statement between A and B, e.g. Cities A JOIN Countries B:

SELECT A.City_Name, B.Country_Code, B.Country_Area
FROM Cities A
JOIN Countries B
ON B.Country_Id = A.Country_Id
WHERE B.Country_Type='ABC';

That statement works absolutely fine, very fast (less than a second) and returns me 2 records

2. I need to replace Country Area column with 1 for Europe and 0 for all the rest. I implement so in the following way:

SELECT A.City_Name, B.Country_Code, CASE B.Country_Area WHEN 'EUR' THEN 1 ELSE 0 AS Country_Area
FROM Cities A
JOIN Countries B
ON B.Country_Id = A.Country_Id
WHERE B.Country_Type='ABC';

Now to get the same two records it takes 03:55 minutes (!)

I have looked into Estimated Execution Plan, but couldn't spot any difference - all straight forward.

It is SQL 2012 SP1 with compatibility level set to 110

View 9 Replies View Related

Count In Case Statement

Apr 24, 2014

I have to count the number of Ideas and Markets here.

CASE WHEN Team IN ('Development/Deployment Project', 'Deployment Fixed Team', 'Development Fixed Team', 'Non Fixed Team') THEN 'Ideas' ELSE 'Markets' END

View 4 Replies View Related

Trying To Count A Case Statement?

Dec 3, 2007

I need to get a total count of leads and then separate the counts by either Retail or Wholesale -

Here's my table schema -

CREATE TABLE [dbo].[Sent] (
[IdentID] [int] IDENTITY (1, 1) NOT NULL ,
[LeadID] [bigint] NOT NULL ,
[AffiliateID] [bigint] NULL ,
[PartnerID] [int] NULL ,
[FranchiseID] [bigint] NULL ,
[FirstName] [t_Name] NULL ,
[LastName] [t_LastName] NULL ,
[Address] [t_Address] NULL ,
[Zip] [t_ZipCode] NULL ,
[Make] [t_Make] NULL ,
[Model] [t_Model] NULL ,
[DateIn] [datetime] NULL ,

Here's my query - Since I'm grouping by the partnerid

select distinct make, count(leadid) as TotalCount, case
when PartnerID = 1 then 'retail'
else 'wholesale' end
as disposition
from leads_sent (nolock)where datein between '2007-09-01' and '2007-09-30'
group by make, partnerid
order by make

Here's a sample my current output -

1 wholesale Acura
2 wholesale Acura
4 wholesale Acura
5 wholesale Acura
21 wholesale Acura
34 wholesale Acura
37 wholesale Acura
56 wholesale Acura
57 wholesale Acura
72 wholesale Acura
510 retail Audi
1 wholesale Audi
3 wholesale Audi
7 wholesale Audi
12 wholesale Audi
16 wholesale Audi
18 wholesale Audi
23 wholesale

Here's the output I need

Total Count



View 4 Replies View Related

SQL Server 2012 :: Case Statement - Populate Field Names In Table

Apr 9, 2014

Is this a correct syntax to populate a field name PHONES in my CUSTOMERS TABLE

case when(d.phone = (SELECT phone from CALLS where exists(select home_phone, mobile, toll_free from CALLS2) Then 1 END 'PHONES'

View 1 Replies View Related

Help With Query (count With Case Statement)

Mar 29, 2008


I have the following query, that returns the proper count value I am looking for. I would like to modify it a little bit, but can't remember exactly how to do it.

select count(messageFromID) FROM tblMessage WHERE messageFromID = 1000) as OutBoundMessages

Basically now, it returns the "OutBoundMessages" column

I would like it to return "OutboundMessages_unChecked" and "OutboundMessages_checked" as well as "OutboundMessages_total" (I guess I could determine this value by adding the two values in the front end too. I definatley dont want to do a lookup to determine the total )

I determine if the column is "checked" or "unChecked" by a column in

For example

tblMessage.checked = 1 = ("checked")
tblMessage.checked = 0 = ("unChecked")

any help much appreciated..


View 7 Replies View Related

SQL Server 2012 :: Replacing CASE Statement In Update With Table-driven Logic

Oct 20, 2014

I have a stored proc that contains an update which utilizes a case statement to populate values in a particular column in a table, based on values found in other columns within the same table. The existing update looks like this (object names and values have been changed to protect the innocent):

UPDATE dbo.target_table
set target_column =
when source_column_1= 'ABC'then 'XYZ'
when source_column_2= '123'then 'PDQ'

[Code] ....

The powers that be would like to replace this case statement with some sort of table-driven structure, so that the mapping rules defined above can be maintained in the database by the business owner, rather than having it embedded in code and thus requiring developer intervention to perform changes/additions to the rules.

The rules defined in the case statement are in a pre-defined sequence which reflects the order of precedence in which the rules are to be applied (in other words, if a matching value in source_column_1 is found, this trumps a conflicting matching value in source_column_2, etc). A case statement handles this nicely, of course, because the case statement will stop when it finds the first "hit" amongst the WHEN clauses, testing each in the order in which they are coded in the proc logic.

What I'm struggling with is how to replicate this using a lookup table of some sort and joins from the target table to the lookup to replace the above case statement. I'm thinking that I would need a lookup table that has column name/value pairings, with a sequence number on each row that designates the row's placement in the precedence hierarchy. I'd then join to the lookup table somehow based on column names and values and return the match with the lowest sequence number, or something to that effect.

View 9 Replies View Related

SQL Server 2012 :: Case Statement On Nvarchar With Literal String Comparison To Varchar?

Apr 14, 2015

how SQL 2012 would treat a literal string for a comparison similar to below. I want to ensure that the server isn't implicitly converting the value as it runs the SQL, so I'd rather change the data type in one of my tables, as unicode isn't required.

Declare @T Table (S varchar(2))
Declare @S nvarchar(255)
Insert into @T
Values ('AR'), ('AT'), ('AW')
Set @S = 'Auto Repairs'
Select *
from @T T
where case @S when 'Auto Repairs' then 'AR'
when 'Auto Target' then 'AT'
when 'Auto Wash' then 'AW' end = T.STo summarise

in the above would AR, AT and AW in the case statement be treated as a nvarchar, as that's the field the case is wrapped around, or would it be treated as a varchar, as that's what I'm comparing it to.

View 3 Replies View Related

SQL Statement, Adding Two COUNT/CASE Statements

Dec 12, 2007

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


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...

Thoughts are greatly appreciated!


View 8 Replies View Related

SQL Server 2012 :: Select Case Statement To Remove Part Of String After One Or Two Specific Characters

Jun 3, 2015

I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'

Example Record 1. Elland **REQUIRES BOOKING IN***
Example Record 2. Theale, Nr Reading, Berkshire
Example Record 3. Stockport

How do I achieve this in a CASE Statement?

The following two case statements return the correct results, but I some how need to combine them into a single Statement?

,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0
THEN LEN(Address )
ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'

,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0
THEN LEN(Address)
ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'

View 8 Replies View Related

Case Statement - Count Orders That Fall Into Specific Dollar Buckets

Aug 2, 2013

I have a table of orders. I was asked to count the orders that fall into specific dollar buckets. Normally I would use a CASE statement for this, but in this case, there are over 100 different buckets!

For example, I need to count the orders in $5 increments up to $400. The CASE statement would look like this:

WHEN sum(revenue) BETWEEN 0.01 AND 5.00 THEN [0.01 to 5.00]
WHEN sum(revenue) BETWEEN 5.01 AND 10.00 THEN [5.01 to 10.00]
WHEN sum(revenue) BETWEEN 395.01 AND 400.00 THEN [395.01 to 400.00]

Is there an easier way to do this, maybe with a loop?

View 11 Replies View Related

SQL 2012 :: Case When Statement With Dates

Jun 29, 2015

o.date>= a.activity_date, other filter condition, other filter condition
(select coalesce(d.balance,d2.balance) from drawtable d where coalesce(d.date, d2.date) < a.activity_date order by d.date desc limit 1) - ( select coalesce(d.balance, d2.balance) from drawtable d where coalesce(d.date, d2.date) = interval 'current date'
else end ))

emailtable a
LEFT JOIN opportunity o
left join drawtable d
left join drawtable d2

The tricky part is I'm joining that same table twice.....would this be better in a max/min case when statement?

View 9 Replies View Related

SQL 2012 :: Case Statement Causes Duplicates

Jul 24, 2015

I have successfully built a messaging system into my application, I now in the process of displaying the messages in the UI.

The following are how my tables are constructed.

CREATE TABLE [MailBox].[Message](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[SenderId] [bigint] NOT NULL,
[Message] [nvarchar](max) NOT NULL,
[SentDate] [datetime] NOT NULL,

[Code] .....

Now I haven't set the foreign key on the MessageReceipient table yet. When someone sends me an email I insert a record into [MailBox].[Message] and output the insert id into MessageReceipient along with the ReceipientId this is working as expected, when I then click on my inbox I call the following stored procedure:

Select p.Username, count(mr.RecipientId) [TotalMessages],
WHEN mr.ReadDate is null then 1 -- New message
WHEN mr.ReadDate is not null then 0 -- Message has been read
END AS NewMessage
FROM [User].[User_Profile] p
JOIN [MailBox].[Message] m on p.Id = m.SenderId
JOIN [MailBox].[MessageRecipient] mr on m.Id = mr.MessageId
GROUP BY p.Username, mr.RecipientId, mr.ReadDate

This will give me the person who has emailed me, the total amount of messages and if the message is new or its been read, I do this by checking the ReadDate column as shown in the case statement (but this gives me duplicates, which is not what I want). Lets say user1 emails me 5 times so when I call this proc I will have the same user displayed to me 5 times, what I'm trying to achieve with the proc is it will show User1 as the following:

User1 5 Messages 1 or 0 New Messages

I can get it to display as follow when I remove the case statement

User1 5 Messages

but as soon as I add the case statement back in then I get 5 rows.

How can I change this proc in such a way that it will display the data as follows;

User1 5 Messages 1 or 0 New Messages

New Messages is dependent on ReadDate if its null then we have a new message, otherwise its been read.

View 3 Replies View Related

SQL 2012 :: Case Statement That Contains Multiplication

Aug 24, 2015

I'm trying to create a case statement that if a field = a certain code, I'd like to take another field * 0.9. But, I'm getting a Null value for the answer..here is the statement:,case when parts.ndc = '50242-0138-01' then labels.BAGSDISP*0.9 end "Units Dispensed"..For this example labels.BAGSDISP is a value of 2. So, in theory it should be 2 * 0.9 and the result should be 1.8 but I'm getting a NULL

View 9 Replies View Related

SQL 2012 :: How To Use RTRIM In Case Statement

Oct 1, 2015

I have a DistributorInvoiceNumber that can end with in 'R', 'A', 'CRR' or 'CR'.I am trying to write a case statement like so:

CASE WHEN RIGHT([ih].[DistributorInvoiceNumber],1) = 'A'
WHEN RIGHT([ih].[DistributorInvoiceNumber],1) = 'R'
WHEN RIGHT([ih].[DistributorInvoiceNumber],3) = 'CRR'
WHEN RIGHT([ih].[DistributorInvoiceNumber],2) = 'CR'

For the most part the code is working, with the exception of the fields that just end in 'R'. An example of this is 471268R, 2525125901CRR, 11100325230CR
Basically if the number ends with an A, then its an Adjustment, if it ends with JUST an R, then its a Reversal; if it ends with just a CR then it is a Credit and if it ends with CRR then it is a Reversal (Credit Reversal). How can I differentiate between the different R's since three of them end with R? Would I use a RTRIM command somehow?

View 1 Replies View Related

SQL 2012 :: Order Of Preference In Case Statement

Sep 9, 2015

I'm trying to set up a statement that gives me a field called 'BINNO' if the payor = Commercial. But, I have a few customers that don't have Commercial. They have a Payor of Grant or Part D. How would I set up a statement that looks for Commerical 1st...then Grant or Part D. I started with this

case when inscomp.payor = 'COMMERCIAL' then INSCOMP.BINNO

View 9 Replies View Related

SQL 2012 :: Converting Case Statement To Config Table?

Nov 3, 2015

I have a stored procedure in which we are deriving some flags. So, we used series of CASE statements.

For examples

WHEN LEFT(CommissionerCode, 3) IN ('ABC','DEF',...) THEN 1
WHEN PracticeCode IN (.......) THEN 1
WHEN (CommissionerCode IN (.....) OR PracticeCode NOT IN (.....) OR .....) THEN 1

I need to put these conditions in config table and generate dynamic sql.

What is the best way to do this? especially, 3rd condition with OR logic with multiple columns involved.

View 2 Replies View Related

SQL 2012 :: Estimate Row Count - WHERE Clause Of Statement

Oct 16, 2014

How SQL calculates the estimated row count when < or > are used in the WHERE clause of a statement. For example:-

WHERE Created_Datetime_utc > CONVERT(DATETIME,'2014-10-14 10:00:00',102)

I know how the estimated number of rows are calculated when an = is used but I've been googling and cannot find anything about < and >.

View 2 Replies View Related

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("", MyConnection)        Dim i As Integer        Dim fBeginTransCalled As Boolean = False
        'messagetype 1 =internal messages        Try            '            ' Start transaction            '            MyConnection.Open()            cmd.CommandText = "BEGIN TRANSACTION"            cmd.ExecuteNonQuery()            fBeginTransCalled = True            Dim obj As Object            For i = 0 To MessageIDs.Count - 1                bSuccess = False                'delete userid-message reference                cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID"                cmd.Parameters.Add(New SqlParameter("@UserID", UserID))                cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString))                cmd.ExecuteNonQuery()                'then delete the message itself if no other user has a reference                cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1"                cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString))                obj = cmd.ExecuteScalar                If ((Not (obj) Is Nothing) _                AndAlso ((TypeOf (obj) Is Integer) _                AndAlso (CType(obj, Integer) > 0))) Then                    'more references exist so do not delete message                Else                    'this is the only reference to the message so delete it permanently                    cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2"                    cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString))                    cmd.ExecuteNonQuery()                End If            Next i
            '            ' End transaction            '            cmd.CommandText = "COMMIT TRANSACTION"            cmd.ExecuteNonQuery()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception            'LOG ERROR            GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message)        Finally            If fBeginTransCalled Then                Try                    cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection)                    cmd.ExecuteNonQuery()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 5 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Problem Using Result From CASE In Another CASE Statement

Nov 5, 2007

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 =
[logic for condition 1]
WHEN [logic for condition 2]
WHEN [logic for condition 3]
WHEN [logic for condition 4]
shipment_status_text =
[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'
...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 =
[logic for condition 1]
WHEN [logic for condition 2]
WHEN [logic for condition 3]
WHEN [logic for condition 4]

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'

...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?



View 1 Replies View Related

SQL Server 2012 :: Calculation In CASE And Out

May 8, 2015


I am having trouble with the CASE statement when ">50". It comes out as zero (0) when according to the data in the example should be ".9".
SELECT '2015-05-08 10:00:00.000','2015-05-08 10:45:00.000' UNION ALL --The record in question

[Code] ...

View 0 Replies View Related

SQL Server 2008 :: Optimizing Join With CASE Statement

Jul 20, 2015

I have data that I want at multiple granularities, 5,15,30 and 60 minutes. To reduce repetition, I have put them all in the same table, so that there is a column for 5,15,30 and 60 minutes, with a filtered index on each of the columns that removes the nulls. This means that each day will have 288 slots, but only 24 of the slots are filled in for 60 min data, and all of them are filled for 5 minute data.

I have another column that specifies the interval granularity, and my first thought was to access my data through a join, where I can use a CASE statement, and depending on the data granularity necessary, it will look at a different column:

AND d.settlement_key =
CASE st.interval_granularity
WHEN 5 THEN [5_min_settlement_key]
WHEN 15 THEN [15_min_settlement_key]
WHEN 60 THEN [60_min_settlement_key]

Despite the presence of the indexes on the columns, then the process seems to be quite slow, I think probably due to the fact that any query plan isn't going to know beforehand which of the columns it is going to use for any given dataset, until it actually starts to run, so it may not be optimised.

How I could optimise this based on the given structure? Maybe there are hints to be added to the join, or maybe I can clear the query plan each time the SQL is run? My other option for dealing with the data of different granularity was to use one column and repeat the data multiple times, each at the different granularity, but this makes my data, row and table sizes much higher, as we are adding just a column for each additional granularity. Would this work any better in future versions of SQL server, maybe with column store indexes?

View 5 Replies View Related

SQL Server 2014 :: Finding NULL In CASE Statement

Sep 15, 2015

The below code returns a NULL for all the Differential backups. I am trying to return the word Differential when there is a NULL because I can tell from when the NULLs occur that those are my differential backups. How can I get the result I want?

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

WHEN msdb..backupset.type = 'D' THEN 'Database'

[Code] .....

View 9 Replies View Related

SQL Server 2012 :: Case Sensitive Pattern Matching?

Dec 20, 2013

I have a set of data where a column contains titles which have been formatted as follows:

"FirstWordSecondWordThirdWord...." etc.

That is, all the words have been concatenated but can be visually separated by their capital first letters.

For reporting purposes, I need to break this column into the separate words so that it looks like:

"First Word Second Word Third Word...." etc.

how this can be achieved?

View 9 Replies View Related

SQL Server 2012 :: How To Check Column Name In Case Sensitive

Apr 21, 2014

I have a table called SrcReg which is having a column name called IsSortSeqNo smallint. I am mapping this column in SSIS and the problem comes when I try to execute against different database which has this table but the column name as ISSortSeqNo. I mean both databases having same name but one with upper case. So SSIS fails executing due to meta validation issue.Is there any way to check whether the column name is in small case or upper case through query?

View 8 Replies View Related

SQL Server 2012 :: Incorrect Syntax Near Keyword CASE

Aug 4, 2015

I'm getting an error as "Incorrect syntax near the keyword 'CASE' ". When trying to run this query.

@RiskRef varchar(100)
--drop table #Tmp


View 9 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved