Transact SQL :: Case Statement For A New Column Based On Date And Username Columns
Sep 2, 2015
Below is the SQL Query i am currently having
SELECT IG_FinancialTransactionSummary.ClaimNum,IG_FinancialTransactionSummary.TransactionCode,
IG_FinancialTransactionSummary.TransactionDate,IG_FinancialTransactionSummary.Username,
FinancialTransactionSummaryTest.ClaimNum,FinancialTransactionSummaryTest.TransactionAmount,
FinancialTransactionSummaryTest.UserName--,FinancialTransactionSummaryTest.TransactionDate
[Code] ....
And here is the result dataset
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.
View 13 Replies
ADVERTISEMENT
Oct 20, 2015
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)
[code]....
View 12 Replies
View Related
Dec 10, 2013
I'm new to SQL Server and would like to add a calculated column to this query from the report writer in our ERP system based on the NextFreq case statement result.
Basically, I want to create a column called service with result as follows:
If IV.meter > NextFreq then the result should be 'OVERDUE'
If (NextFreq - IV.meter) <50 then the result should be 'DUE SOON'
Otherwise the result should be 'NOT DUE'
This is the code from the current report writer query:
Select IV.item, IV.meter, isnull(wt.name,0)as name, case when whh.meterstop is null then 0 end meterstop, whh.rejected, Case when cast(meterstop as int) > 0 then cast(meterstop as int) when meterstop is null then isnull(IV.meter,0) else isnull(IV.meter,0) end EndMeter, ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) as LastFreq,
case when whh.rejected = 1 then ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) when ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) = 0 then 100 when ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) = 100
[Code] ....
View 4 Replies
View Related
Jan 5, 2015
I have to produce a report to calculate no of days based on user input start date and end date. I have tried to explain below.
say for eg: in the tables I have emp name
user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff
within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days
PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days
PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days
Total days 53 Days
For this :
I calculated datediff + 1 and got sub jobs days BUT
say financial director wants to see Title of 'Sub Jobs' with 'Days' from 1st Dec to 31st Dec
so on paper I calulated as :
1-31 Dec 2014
PhaniMarketing NULL (Do not fall in Req Dt)
PhaniAdmin 20 (Deduct 2 days of Nov & calculated 20 days of Dec)
PhaniCRM 11 (Deduct 20 days of Nov and deduct 11 days of Jan so for Dec , we got 11 days)
Total days 31
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
View 4 Replies
View Related
Nov 25, 2015
I have 3 different companies that share the same ticket_types(CRMS System). I need to display the Ticket Types and the 3 company's Ticket Count:
Ticket Type | Company A Count | Company B Count | Company C Count
I can get the information individually for each company, but if a company doesn't have a ticket in one of the ticket_types, then it isn't displayed in a row. So, I tried to write the following, which isn't pulling back any data.
DECLARE @startdate date = '20150306'
DECLARE @enddate date = '20151031'
DECLARE @AcctGrp varchar(20) = '111'
;WITH TType
AS
(
SELECT ctp.description as TicketType
[Code] .....
If I run each SELECT individually from above (excluding the last SELECT), it works and I get the following:
TicketType
AR Request Credit
Availability/Rush
Cancel Order
Credit Card Payment
Expedite Order
Freight Quote
[Code] ...
How to get the query results? Am I even close to getting it right?
View 3 Replies
View Related
Jul 28, 2015
I have a requirement where i want to delete the records based on the Date column. I have table which contain the columns like machinename ,lasthardwarescandate
I want to delete the records based on the max(Lasthardwarescandate) i.e. latest one, column where the machine name is duplicate menace it repeats. So how would i remove the duplicate machine names based on the Lasthardwarescandate column(There are multiple entries for the Lasthardwarescandate so i want to fetch the latest date column).
Note: Duplication should be removed based on “Last Hardware Scan” date.
Only latest date should be considered from multiple records for the same system. "
View 4 Replies
View Related
Oct 19, 2015
I am trying to achieve the below problem statement, however I am getting stuck at the looping part.
EX: We have three columns
First Name | Last Name | Mothers Name
I want to generate the username field using above columns, so lets consider, I have following data
First Name | Last Name | Mothers Name
a b cdef
a b cdfg
a b cdfj
Expected Usernames:
1: a.b
2: a.b.c
3. a.b.cd
Basically, it has to be FirstName.LastName.(incremental letters from MothersName until the name becomes unique)
View 4 Replies
View Related
Sep 13, 2007
I am writing a fairly simple sql, and I would like to write something like
Code Snippet
select
firstname as firstname,
case
when firstname = 'Peter' then 'yes'
else
'no'
end as whatever
from
MyTable
where
whatever = 'yes'
And this should then select out the rows where column number 2 is 'yes'.
It doesn't work, and I have to copy the firstname = 'Peter' into the where clause.
But why?
View 4 Replies
View Related
May 5, 2015
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
View 7 Replies
View Related
Sep 23, 2013
I have to produce a report, but not sure of the best way to get the required results
Aim - to count how many [FDMSAccountNo] there are per given [Month_end_date], and then do a case when on the[Retail_amount]
E.g.
10 Fdmsaccountno in Jan
Those 10 Fdmsaccountno vary in [Retail_amount]
I have 5 fdmsaccountno which are between %0 & £5
2fdmaccounno beterrn £6& £10
3 fdmsaccountno £10>
My query is
SELECT [FDMSAccountNo]
,[Month_end_date]
,[Retail_amount]
FROM [FDMS].[dbo].[Fact_Fee_History]
where [Fee_Sequence_Number] = '32r'
and Month_end_date between '2013-01-01' and '2013-12-01'
View 5 Replies
View Related
Oct 5, 2007
I have the following table of data. I need to take a date from a large table and do the following case:CASEWhen date < date(0) Then '0'When date between date(0) and date(1) Then '1'When date between date(1) and date(2) Then '2'When date >= date(3) Then '3'What I need is to be able to read all the dates the the Date table, sort then chronologically, and build the dynamic CASE statement so that the first When statement is < Date(0) and the last When statement is >= Date(Last)I hope I am making sense. Dates will be added to the table about once a year or so and I don't want to keep going back into the sql function and rewrite it with the latest date. Any ideas how to manipulate these dates into a case statement? Don't worry about the second table below. I just wanted you to see why I need to return an int from the Case function.thanksMilton
Dates Table
Date
4/1/2003
1/1/2006
4/2/2007
Fee Table
Date
Period
Class
Fee
1
Daily
True
329
1
Half Day
True
178
1
OT
True
49
1
Hourly
True
41
1
Daily
False
156
1
Half Day
False
86
1
OT
False
27
1
Hourly
False
19
2
Daily
True
355
2
Half Day
True
192
2
OT
True
50
2
Hourly
True
44
2
Daily
False
171
2
Half Day
False
92
2
OT
False
28
2
Hourly
False
21
3
Daily
True
364
3
Half Day
True
197
3
OT
True
51
3
Hourly
True
45
3
Daily
False
175
3
Half Day
False
94
3
OT
False
29
3
Hourly
False
21
View 3 Replies
View Related
Mar 10, 2008
I need to, ultimately, create a flatfile for exporting insurance information to a third-party vendor. Each individual is to have no more than 1 line per file, which contains their coverage information, if any, on 4 different type of insurance. What i need is for 2 fields in a table to determine the output for multiple fields in the flatfile.
What I have so far works, to an extent. If I have insurance types 1, 2, 4 (of types 1-4) required output is (__ = 2 blank spaces):
1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E
== == == == == == == == == == == == == == == == == == == ==
Y N __ MD XX Y N __ MD XX N __ __ __ __ Y N __ DN XX
If they have coverage, A always = Y, B always = N, C always = blank(null), D is their ins. type, E is their cov. type(CASE statement). if they DON'T have that type of coverage, A always = N and the remaining field are NULL.
After a lot of work, and scouring a forum or 2, I attempted a whole lot of CASE functions. Below is an sample of code representing the 1x statements. This same code is repeated 4 times with the 1x being altered with 2x, 3x, 4x.
CASE HB.PLAN_TYPE
WHEN '10' THEN 'Y'
ELSE 'N' END AS 1A,
CASE HB.PLAN_TYPE
WHEN '10' THEN 'N'
ELSE ' ' END AS 1B,
' ' AS 1C,
CASE HB.PLAN_TYPE
WHEN '10' THEN HB.BENEFIT_PLAN
ELSE ' ' END AS 1D,
CASE HB.PLAN_TYPE
WHEN '10' THEN (CASE WHEN HB.COVRG_CD ='1' THEN 'XX'
WHEN HB.COVRG_CD ='2' THEN 'YY'
WHEN HB.COVRG_CD ='3' THEN 'ZZ'
ELSE 'WW' END)
ELSE ' ' END AS 1E,
It works to an extent. While the desires/required output it above, the output this produces (same scenario) is:
1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E
== == == == == == == == == == == == == == == == == == == ==
Y N __ MD XX N __ __ __ __ N __ __ __ __ N __ __ __ __
1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E
== == == == == == == == == == == == == == == == == == == ==
N __ __ __ __ Y N __ MD XX N __ __ __ __ N __ __ __ __
1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E
== == == == == == == == == == == == == == == == == == == ==
N __ __ __ __ N __ __ __ __ N __ __ __ __ Y N __ DN XX
While there is supposed to be 1 line, regardless of number of insurance types, it only produces 1 line per type. I first tried this in Access, and was able to get it to work, but it required multiple queries resulting in a crosstab, export to Excel and manually manipulate the data, export to text file to manipulate, import back into Excel to manipulate, import back into Access and finally export into a flatfile. Far too much work to produce a file which is why I'm trying to convert it to raw SQL.
Any assistance in this matter would be greatly appreciated.
View 5 Replies
View Related
Sep 25, 2015
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
CASE@Report
WHEN'1'THEN
WHEREL.RequestedShipDate>=@BeginRangeDate
ANDL.RequestedShipDate<=@LookoutDate
ANDDO.ForecastDate>=@ForecastDate
[Code] .....
View 7 Replies
View Related
Jul 20, 2005
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
View 4 Replies
View Related
Aug 9, 2015
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,
[Code] ....
View 4 Replies
View Related
Sep 24, 2015
I use Order By Used with Case Statement and may be this is the reason for which i am getting error
Windowed functions can only appear in the SELECT or ORDER BY clauses.
Here is my complete sql where i use Row_Number function for pagination with sorting.
DECLARE @StartIndex INT
DECLARE @EndIndex INT
DECLARE @SortColumn VARCHAR(MAX)
DECLARE @SortDirection VARCHAR(MAX)
SET @StartIndex = 1
[Code] ....
View 7 Replies
View Related
Nov 11, 2015
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'
[code]....
View 5 Replies
View Related
Nov 19, 2015
That may not be the most exact TITLE I have used for a question as CASE may not be the best expression to use but I have a query:
select AR.ClientID,
AR.ReferenceID,
AAC.SequenceID,
AA.AppearanceDate,
CASE
WHEN AAC.Outcome IS NULL Then 'Cancelled' else
[Code]....
This returns the following data set:
ClientID ReferenceID
SequenceID AppeareanceDate
Outcome Rownum
1000000 2
2000358 2005-11-07
Yes 1
[Code] ...
What I want the eventually solution to be is that for each unique/distinct client, reference and sequence there is one record (row). Based on the row_number, I want the first 'AppearanceDate' renamed to 'AppearanceDate1' with the first AppearanceDate as the data and the first Outcome to be renamed 'Outcome1' with the first Outcome as the data. Any subsequent rows would follow as columns:
ClientID ReferenceID
SequenceID AppeareanceDate1
Outcome1 AppearanceDate2
Outcome2
1000000 2
2000358 2005-11-07
Yes NULL
[Code] ...
Only four AppearanceDates and outcomes are required based on the unique/distinct Client, Reference and SequenceIDs. How I can loop through the Appearances based on the row_number and show each record in one row?
View 3 Replies
View Related
Jun 4, 2015
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.
View 18 Replies
View Related
Jul 28, 2015
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 ,
View 27 Replies
View Related
Nov 6, 2015
below is my original query
select Value = count(*) from dbo.test
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.
View 5 Replies
View Related
Jul 27, 2015
below is the sql query i am trying to write and i receive an error saying Incorrect syntax near the keyword 'Between'.
Update dbo.Claims_Primary_Adjuster_Test_Sujith
SET
Claims_Primary_Adjuster_Test_Sujith.Supervisor = Claims_Internal_Transfer.Supervisor,
Claims_Primary_Adjuster_Test_Sujith.Office = Claims_Internal_Transfer.Office,
Claims_Primary_Adjuster_Test_Sujith.Specialty = Claims_Internal_Transfer.Specialty
from dbo.Claims_Primary_Adjuster_Test_Sujith
Left Outer Join Stg.HS_DW_RV_ClaimsON Claims_Primary_Adjuster_Test_Sujith.Claim_Number = HS_DW_RV_Claims.ClaimNumber
[code]....
how to modify my code such that it works for my condition?
View 10 Replies
View Related
May 18, 2015
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.
View 2 Replies
View Related
Jun 22, 2015
I have a table A and lookup table B.
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
View 6 Replies
View Related
Jan 18, 2008
A happy belated New Year to all!
Can someone please help me with the following. How do I, in SS 2000, use a value to label a column?
example
Col A Col B Col C Col D
CA '12/1/07' '11/1/07' '10/1/07'
I want to use the month and year of the date to rename the column.
Col A 12-2007 11-2007 10-2007
CA '12/1/07' '11/1/07' '10/1/07'
Thank you in advance for any help you can offer.
TMendez
View 5 Replies
View Related
May 17, 2015
Table Structure
EID COLA COLB
1 name A
1 age 23
1 city hyd
1 email abc@live.in
1 mobile 45126
2 name B
2 age 43
[code]....
how to display the result where any of the mandatory fields (name,age,city,email,mobile)are missing then it should display as that field as Null
View 9 Replies
View Related
May 20, 2015
Display customized data based on customized where statement from UDT.
The UDT is a parameter inside of a stored procedure.
Problem:
A parameter from a stored procedure is
@communication communications readonly
This parameter is a User-Defined Table Types (UDT) It contains criteria based on end-user's selection from a filtration functionality from a webpage.
Four example of filtration critera based on four end-users' selection that is located inside of a table below.
Each UDT table contains different criteria:
Number Criteria
------ --------
1 Phone
3 Email
Number Criteria
------ --------
1 Phone
2 Cellphone
3 email
Number Criteria
------ --------
4 None
Number Criteria
------ --------
2 Cellphone
1 Phone
Is it somehow possible to use the criteria's value as a column name in the where statement? I want to filtrate the data of the table datatable based on id, name and the UDT's criteria.
I was enable to apply the criteria inside of a variable by looping the UDT's table but the next thing is to paste it in the where statement after "id=1 and name" below
select *
from datatable
where id = 1 and name = 'Cost'
How should I do it?
[URL] .....
create table datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);
[Code] .....
View 4 Replies
View Related
Nov 15, 2013
I want to update multiple column in one table using with case statement. i need query pls..
stdidnamesubject result marks
1 arun chemistry pass 55
2 alias maths pass 70
3 babau history pass 55
4 basha hindi NULL NULL
5 hussain hindi NULL nULL
6 chandru chemistry NULLNULL
7 mani hindi NULLNULL
8 rajesh history NULLNULL
9 rama chemistry NULLNULL
10 laxman maths NULLNULL
View 2 Replies
View Related
Oct 31, 2015
I have 5 different tables with same structure. Each table has an account column and another column count. I am trying to generate a report based of the columns in each table. Account is same in all tables with same account numbers but count will be different.
Eg:
Table 1 has Account Count
Table 2 has Account Count
Table 3 has Account Count
Table 4 has Account Count
Table 5 has Account Count
I want output as:
Account Count Account Count Account Count Account Count Account Count
View 4 Replies
View Related
Nov 4, 2015
#EMAIL_ADDRESSES which hold records similar to the following (CREATE code below):
View 6 Replies
View Related
Nov 16, 2015
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.
View 7 Replies
View Related
Aug 6, 2015
I am required to find or check that a specific word exists in string or not.
Suppose I have to find the word 'st' than I need the result true if and only if the following occurrences are there.
1. 'St is valid;' -> true
2. 'DOB is valid;ST is invalid;' -> true
3. 'DOB is valid; ST is invalid;' -> true
4. 'DOB is valid;invalid ST;' -> true
5. 'DOB is valid; invalid ST;' -> true
6. 'DOB is valid; invalid STate;' -> false
Means the exact ST should be search. It is not free text search.
T-SQL is needed to be used in select statement with case using PATINDEX, RegEx or any suitable t-sql command.
View 10 Replies
View Related
Sep 22, 2015
In a table I have some rows with flag A & B for a scode, some scode with only A and some are only B flags.
I would like to fetch all rows with flag A when both flags are present, no rows with B should be fetched. Fetch all rows when only single flags are present for a scode.How to achieve this using TSQL code.
View 2 Replies
View Related
