Count Of String Field
Jan 17, 2008
Hi All ,
I have one field in SQL Server Report Called Status which is text field.
that status can be "Abesent" , Late In", "Early Out" , "Early Out and Late in"
, its in table Like this.
Emp no | Name | Date | Time | Status
1 Kaisar 1-1-07 7:15 Late In
1 Kaisar 1-2-07 17:15 Early Out
1 Kaisar 1-4-07 - Absent
1 Kaisar 1-5-07 - Absent
1 Kaisar 1-6-07 - Absent
And So On......
----------------------------------------------------------------
i want to get count some thing like this
Total Of Absent : 3
Total Of Late in : 4
Total Of Early Out :5
any idea how can i do this ?
View 5 Replies
ADVERTISEMENT
Jul 3, 2013
I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....
Select Distinct
sum(isnull(cast([Total Count] as float),0))
from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'
View 9 Replies
View Related
Dec 30, 2003
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable
mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))
option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End
Please help
View 6 Replies
View Related
Mar 20, 2014
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
View 9 Replies
View Related
Feb 13, 2006
We have the following two tables :
Link ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
View 1 Replies
View Related
Mar 5, 2007
I have a table like this. Each ConNo can have many boxes. The number of the box is an identfier, not a quantity.ie Box 3 = 1 box.A box may have a letter as a suffix or not.
I want to group by ConNo and total the boxes so I know how many Boxes are in each ConNo eg ConNo 200 5 boxes
TransId 1234
ConNo 200
BoxNo 1
BoxSuffix
TransId 1235
ConNo 200
BoxNo 1
BoxSuffix
TransId 1236
ConNo 200
BoxNo 2
BoxSuffix
TransId 1237
ConNo 200
BoxNo 2
BoxSuffix A
ConNo 200
BoxNo 1
TransId 1238
ConNo 201
BoxNo 1
TransId 1239
ConNo 201
BoxNo 1
TransId 1240
ConNo 201
BoxNo 3
TIA
View 8 Replies
View Related
Apr 10, 2008
I've got a stored proc which returns a location and corrosponding defects for that location. My matrix has the locations as rows and a count of defects as the data.
so...
LOCATION COUNT
--------------- ----------
AREA1 10
AREA2 5
AREA3 3
AREA4 2
AREA5 1
How can I manipulate the matrix (or table?) so I'll only show the Top 3 highest counts? I've tried screwing around with the Top N Filter, but as soon as I do =COUNT(Field!Defect.Value) on it the report wont run anymore. If the data came over in the above format that would make my life so much easier...but my matrix is all about aggregate data
Also I cant modify the stored proc as that dataset is used on multiple areas in the report
Any help would be great
Thanks,
Steve
View 4 Replies
View Related
Jul 31, 2013
I have a string like aaannbbghtgggbbaa, I want a query to get count of characters in string like :
a 5
b 4
others 20
View 3 Replies
View Related
Jan 3, 2005
Hi,
I am trying to update a field in a temptable with the count of items in another table. To illustrate, CustomerID=23 and I want the number of occurences in the temp table. Here's the code which DOESN'T work:
INSERT INTO TempTable
(
CustomerID,
FirstName,
LastName,
DateAdded,
AlbumPicture,
LayoutCount
)
SELECT
Albums.CustomerID,
Customers.FirstName,
Customers.LastName,
DateAdded,
AlbumPicture,
COUNT(*) FROM Layouts WHERE Layout.customerID = Albums.CustomerID
FROM
Albums JOIN
Customers on (Albums.CustomerID=Customers.CustomerID)
Please take a look at the COUNT line. Here I want to count the occurences of a specific customerid in another table and put in into th LayoutCount field.
SQL server reports "Incorrect syntax near the keyword 'FROM'". Any ideas how to achieve this?
Many thanks!!
Eric
View 1 Replies
View Related
Nov 18, 2014
I want to get a count of how many PosAnswer are associated with the QuesToAsk 'List your top 5 favorite places'
This is the syntax I am using to achieve this, and it works, but wasn't sure if there was a better way (changing the data structure is unfortunately not an option in this situation). Here is my table structure
Code:
Create Table Test
(
QuesToAsk varchar(1000),
PosAnswer varchar(1000)
[Code] ....
And this is the syntax I use to get the count I am after
Code:
DECLARE @VariableName varchar(25)
DECLARE @FormattedVariableName varchar(25)
DECLARE @FieldCount int
SET @VariableName =
[Code] ....
View 5 Replies
View Related
Sep 13, 2013
I am attempting to combine to queries that I created and am not having success. I need to get a count on a field for a specific value and have to do this by gender and by an age range. So here is the query I came up with. I don't get any errors produced but I also don't get any results.
$query = "SELECT p1.sex, age_group, COUNT(CASE WHEN p4.q1 = 'Yes' THEN p4.q1 END) AS heart_attack FROM (select p1.sex,".
" CASE WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 11 AND 20 THEN '11-20'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate()))
[code]....
View 8 Replies
View Related
Sep 17, 2007
Hopefully this makes sense. I am new to SQL Reporting. My code is below. I am trying to get my query to display 0 when a category (prob_ctg.sym, does not have a Count value.
SELECT COUNT(*) AS Expr2, ISNULL(COUNT(*), '0') AS Total, CONVERT(varchar(20), Requestor.c_last_name) + ', ' + CONVERT(varchar(20),
Requestor.c_first_name, 112) AS Customer, Calls.ref_num, Calls.category, prob_ctg.sym, Calls.summary, DATEADD(s, Calls.open_date - 21600,
'1/1/70 12:00:00 am') AS OpenDT, DATEADD(s, Calls.close_date - 21600, '1/1/70 12:00:00 am') AS CloseDT, [Group].c_last_name AS GroupName
FROM ctct Requestor INNER JOIN
ctct [Group] INNER JOIN
call_req Calls ON [Group].id = Calls.group_id ON Requestor.id = Calls.customer INNER JOIN
int_org INNER JOIN
prob_ctg ON int_org.id = prob_ctg.organization ON Calls.category = prob_ctg.persid
WHERE (int_org.iorg_c_id LIKE '%400111%') AND (prob_ctg.del = 0) AND (DATEADD(s, Calls.open_date - 21600, CONVERT(DATETIME, '1970-01-01 00:00:00',
102)) BETWEEN @stdt AND @eddt)
GROUP BY Calls.category, prob_ctg.sym, Calls.summary, Calls.ref_num, Calls.open_date, Calls.close_date, [Group].c_last_name, int_org.iorg_name,
int_org.iorg_c_id, Requestor.c_last_name, Requestor.c_first_name
View 4 Replies
View Related
Oct 13, 2015
I want to avoid iterating through a table row by row and programmatically incrementing a counter,/I have define a view called Q2 with the following fields of interest:
Trial varchar(25)
BI int
Track int
CAT int
What I would like to do is partition by Trial, BI, and Track, and count the number of times CAT changes value. In Access, I would just iterate through a recordset inside a function, but I would like to see if there is a set based solution.Adding Sample Data and Output.
Trial BI Track CAT
A 5 3 1
B 5 3 2
[code]...
View 5 Replies
View Related
Dec 30, 2003
How do I count the number of specific characters in a string ?
Example:
declare @var as varchar(50)
set @var="1abc1efg1"
If I wanted to count the "1"...I'll get "3" for answer.
This could maybe done by using a while loop, but is there any T-SQL command for this?????
View 1 Replies
View Related
Apr 19, 2005
How can i create a stored procedure that count or sum value of field
e.g.
f1 f2
f3 f4 f5
record 1
1 2
3 1
and get answer like this 1=4 - 2=1 - 3=1
View 1 Replies
View Related
Jul 24, 2013
I have a table in Access 2007 that has about 30 field names and I want to have a count of how many unique rows there are in each field. I want to have these results put into another table that will just have the field name and then the count of how many unique rows there are.
I have code in VBA that will loop through my SQL and change out the field name, but I can't seem to get the SQL right before I can start looping it. For just one field name this would be what I have to count the unique names...
So far I have this:
INSERT INTO newtable
COUNT(*) FROM (SELECT Raw_Table.FieldName, COUNT(Raw_Table.FieldName) AS CountOfFieldName
FROM Raw_Table
GROUP BY Raw_Table.FieldName);
And its not going too well.
View 1 Replies
View Related
May 7, 2015
I want to count the values of 2 field on particular date
I have user table with 3 field
1.from_userid
2.to_userid
3.Date
Now i want to count the no. of files from_userid have send, no. of files to user_id have received on particular day. The data is like this
From_userid to_userid date
3953 6274 10/22/2014
3953 6152 10/22/2014
1112 2710 10/22/2014
3953 1851 10/23/2014
3953 4302 10/23/2014
4302 2710 10/23/2014
View 2 Replies
View Related
May 8, 2007
I need to figure out the number of managers without listing them. Label the column Number of Managers.
any help is appricated.
Number of Managers
-------------------------
6
View 3 Replies
View Related
Jan 24, 2008
I have a string of characters in my data flow and I need to add a derived column showing the # of times a certain character appears within that string. For example, my string in the data flow is:
NNNNNRJKSURNNNEJNNNN
Now I need to count the number of "N"s in that column. From the example above, I should get the integer 12, and that would be the value of my derived column. Any ideas?
View 7 Replies
View Related
Jul 22, 2015
Below are the columns I have in table Customers.
Date CustomerID
7/14/2015 AAA
7/14/2015 BBB
7/15/2015 AAA
7/15/2015 BBB
7/15/2015 CCC
7/16/2015 AAA
7/17/2015 AAA
7/18/2015 AAA
7/19/2015 AAA
What I need is to find running sum of count of customerIDs. here CustomerID is string. So, the expected result is
Date Run_Sum
7/14/2015 2
7/15/2015 5
7/16/2015 6
7/17/2015 7
7/18/2015 8
7/19/2015 9
View 6 Replies
View Related
Oct 18, 2004
I am trying to write a stored procedure that updates a value in a table based on the sort order. For example, my table has a field "OfferAmount". When this field is updated, I need to resort the records and update the "CurrRank" field with values 1 through whatever. As per my question marks below, I am not sure how to do this.
Update CurrRank = ??? from tblAppKitOffers
where appkitid = 3 AND (OfferStatusCode = 'O' OR OfferStatusCODE = 'D')
ORDER BY tblAppKitOffers.OfferAmount Desc
All help is greatly appreciated.
View 2 Replies
View Related
Sep 14, 2000
I am trying to run the plan analyser on a SQL query that I cut and pasted from a Siebel trace. I get the following error
[Microsoft][ODBC SQL Server Driver]COUNT field incorrect
There is no count in the SQL query, and there doesn;t seen to be a message number to look up, anyone got any ideas?
I have done the obvious stuff like changing the DB name in the query, but to no avail....
Cheers
Mike
View 1 Replies
View Related
Sep 21, 2004
I'm stuck. This is in C#.
I am making the following query:
string query = INSERT INTO region_info(prefix, region, last_update) VALUES ('" + RegionInfo.Prefix + "', '" + region + "', ???TIMESTAMP???);
and then executing
query = query.Replace("???TIMESTAMP???", "'" + DateTime.Now.ToString("yyyyMMdd") + "'");
Thus, an example query is:
INSERT INTO region_info(prefix, region, last_update) VALUES ('907209', 'Alaska-Juneau', '20040921');
When I execute this query through my program(uses ADO.net), I get a "COUNT field incorrect or syntax error" exception, but if I run this same query through the query analyzer, it works fine.
Any ideas on what'z going wrong?
Thanks
View 5 Replies
View Related
May 12, 2006
Is it possible to count the same field with different criteria. It would be something like this.
car_table
car_id
car_name
car_brand
So you would execute a statement which would count(car_brand) with two different criteria.
I am not sure if this is possible or if there is another way to approach it.
View 1 Replies
View Related
Jan 22, 2015
I have a table with 4 column in below
Total amount = 1000
salemancode1 = space
salemancode2 = Staff-99
salemancode3 = space
salemancode4 = staff-88
How I can write a one query statement to do this, we expect to count how many salemancode is not space and count the number of salesman to over the total amount.
total amount / (no_of_saleman) as commission
the result is 1000/ 2 the commission is $500.
View 1 Replies
View Related
Jun 17, 2014
See sample data below. I'm trying to count the number of occurrences of strings stored in table @word without a while loop.
DECLARE @t TABLE (Id INT IDENTITY(1,1), String VARCHAR(MAX))
INSERT INTO @t
SELECT 'There are a lot of Multidimensional Expressions (MDX) resources available' AS String UNION ALL
SELECT 'but most teaching aids out there are geared towards professionals with cube development experience' UNION ALL
[Code] .....
View 9 Replies
View Related
Oct 30, 2014
I have created table in which there are four columns (id, date, parcelname, parcelnumber) and 10 rows. I want to count record of the column parcelnumber but condition that, in between two different dates the record should be counted.
View 9 Replies
View Related
Jun 20, 2007
Hi everyone,
I have created a report which has 3 groups. The report output as shown below. I am having trouble getting the SUM of Total Credtis for each Org.
Can't seem to get the total 42 and 16 (highlighted), but can get total unists 11 and 13. I get expression contains aggregate function. This is because Units assessed is the Count of IDs (details hidden from the report).
Report has three groups Org , Assessor and Unit.
Can someone please help me with this?
Appreciate help.
Thank you,
Ski
Org 1(Group1)
Unit Credits Units Assessed(# of Trainees) TotalCredits
Assessor 1 Unit 1 2 4 (Count of Ids) 8 (2*4)
Assessor 2 Unit 2 1 2 2 (1*2)
Assessor 3 Unit 3 5 2 10 (5*2)
Unit 4 2 1 2
Assessor 4 Unit 5 10 2 20
--------------------------------------------------------------------------------------------------------
11 42 -----------------------------------------------------------------------------------------------------------
Org 2
Assessor 3 Unit 1 2 3 6
Assessor 4 Unit 6 1 10 10
--------------------------------------------------------------------------------------------------------
13 16
--------------------------------------------------------------------------------------------------------
View 4 Replies
View Related
May 28, 2015
I want to update a field and in this field insert a increment count, for example:
When I make, "Select * from Users order by User" displays:
User1 | NULL
User1 | NULL
User1 | NULL
User2 | NULL
User2 | NULL
and I want to do this:
User1 | 1
User1 | 2
User1 | 3
User2 | 1
User2 | 2
how to do this?
View 7 Replies
View Related
Aug 28, 2007
I want to create a trigger that, when a field is updated or a record is inserted, counts the number of words in "field1", inserting this numeric value into another field in the same table. What would be the most efficient, fastest method for doing this?
View 15 Replies
View Related
Oct 21, 2015
I have to count the number of instances of each word in the Comments column throughout the table using SQL Server. Eg:
Row# Comments
1 I like working on SQL
2 I enjoy sleeping and like watching TV
So the output should be :
Word Count
I 2
like 2
working 1
on 1
SQL 1
.......
Any way to get this count using MS SQL?
View 5 Replies
View Related
Dec 8, 2014
Because of a limitation on a piece of software I'm using I need to take a large varchar field and force a carriage return/linebreak in the returned sql. Allowing for a line size of approximately 50 characters, I thought the approach would be to first find the 'spaces' in the data, so as to not split the line on a real word. achieve.
--===== Simulate a passed parameter
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = (select a_notes
from dbo.notestuff as notes
where a_id = '1')
[Code] .....
View 5 Replies
View Related
Sep 26, 2014
I am trying to write a stored procedure that loops through the list of user tables, gets the record count for each one and write a record to an audit table with DATE, TABLENAME, RECORDCOUNT.I keep getting an error "Conversion failed when converting date and/or time from character string".Here is the script...
DECLARE @table nvarchar(500)
DECLARE @sql nvarchar(520)
DECLARE CursorSelect CURSOR FOR
select table_name from INFORMATION_SCHEMA.tables where table_name not like 'sys%' order by table_name
[code]....
View 2 Replies
View Related
