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
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'
END
FROM(
select COUNT(studentid) as Stcount FROM dbo.student) Stdtbl
But can I do this with just one select statement?
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
What is the best way to script this
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 =
CASE WHEN
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'
END
Is this possible, I am trying to execute a stored procedure depending on what parameter is selected, something like this????
Case
when field = 'value' then execute sp_procedure else execute sp_procedure_2 end
case
How I am using a CASE statement within a WHERE clause to filter data:
CREATE PROCEDURE dbo.GetSomeStuff
@filter1 varchar(100) = '',
@filter2 varchar(100) = ''
AS
BEGIN
SELECT
[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?
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
<code>
SELECT DISTINCT
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]
</code>
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
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
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 -
Acura
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
Make
Total Count
RetailCount
WSCount
Acura
798
510
288
Audi
256
75
181
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'
Hi,
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
tblMessage
For example
tblMessage.checked = 1 = ("checked")
tblMessage.checked = 0 = ("unChecked")
any help much appreciated..
thanks!
mike123
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 =
case
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.
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.
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...
Thoughts are greatly appreciated!
TT
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'
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:
Code:
CASE
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?
SELECT
SUM(((CASE WHEN
o.date>= a.activity_date, other filter condition, other filter condition
THEN
(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 ))
from
emailtable a
LEFT JOIN opportunity o
left join drawtable d
left join drawtable d2
etc
The tricky part is I'm joining that same table twice.....would this be better in a max/min case when statement?
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,
CONSTRAINT [PK_MailBox.Message] PRIMARY KEY CLUSTERED
[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],
CASE
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.
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 RelatedI 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'
THEN 'ADJ'
WHEN RIGHT([ih].[DistributorInvoiceNumber],1) = 'R'
THEN 'REV'
WHEN RIGHT([ih].[DistributorInvoiceNumber],3) = 'CRR'
THEN 'REV'
WHEN RIGHT([ih].[DistributorInvoiceNumber],2) = 'CR'
THEN 'CREDIT'
ELSE NULL END AS 'Status'
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?
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
I have a stored procedure in which we are deriving some flags. So, we used series of CASE statements.
For examples
CASE
WHEN LEFT(CommissionerCode, 3) IN ('ABC','DEF',...) THEN 1
WHEN PracticeCode IN (.......) THEN 1
WHEN (CommissionerCode IN (.....) OR PracticeCode NOT IN (.....) OR .....) THEN 1
ELSE 0
END
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.
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 >.
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
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.
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
USE ODS_ST
/*
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".
*/
CREATE TABLE #CLASS_TIMES
(
START_TIME DATETIME,
END_TIME DATETIME
)
INSERT INTO #CLASS_TIMES (START_TIME, END_TIME)
SELECT '2015-05-08 10:00:00.000','2015-05-08 10:45:00.000' UNION ALL --The record in question
[Code] ...
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:
INNER JOIN Data d ON
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]
ELSE NULL END
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?
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?
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CASE
WHEN msdb..backupset.type = 'D' THEN 'Database'
[Code] .....
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?
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 RelatedI'm getting an error as "Incorrect syntax near the keyword 'CASE' ". When trying to run this query.
CREATE PROCEDURE dbo.SP_CDB_EA2
@RiskRef varchar(100)
--drop table #Tmp
AS
BEGIN
[code]....