Count Values Of 2 Field On Particular Date
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
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
Apr 16, 2014
How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:
Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10
------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)
Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10
Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5
View 2 Replies
View Related
Apr 17, 2007
Here's a portion of the current statement.
UPDATE EngagementAuditAreas
SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,
numInherentRiskID = parent.numInherentRiskID,
numControlRiskID = parent.numControlRiskID,
numCombinedRiskID = parent.numCombinedRiskID,
numApproachTypeId = parent.numApproachTypeId,
bInherentRiskIsAffirmed = 0,
bControlRiskIsAffirmed = 0,
bCombinedRiskIsAffirmed = 0,
bApproachTypeIsAffirmed = 0,
bCommentsIsAffirmed = 0
FROM EngagementAuditAreas WITH(NOLOCK) ...
And what I need is to conditionalize the values of the "IsAffirmed" fields by looking at their corresponding "num" fields. Something like this (which doesn't work).
UPDATE EngagementAuditAreas
SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,
numInherentRiskID = parent.numInherentRiskID,
numControlRiskID = parent.numControlRiskID,
numCombinedRiskID = parent.numCombinedRiskID,
numApproachTypeId = parent.numApproachTypeId,
bInherentRiskIsAffirmed = (numInherentRiskID IS NULL),
bControlRiskIsAffirmed = (numControlRiskID IS NULL),
bCombinedRiskIsAffirmed = (numCombinedRiskID IS NULL),
bApproachTypeIsAffirmed = (numApproachTypeID IS NULL),
bCommentsIsAffirmed = (parent.txtComments IS NULL)
FROM EngagementAuditAreas WITH(NOLOCK)
Thanks.
View 1 Replies
View Related
May 4, 2006
Good morning...
I begin with SQL, I would like to add a field that will be date like 21/01/2000.
Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.
How to do for having date and time in two different field.
Sorry for my english....
Cordially
A newbie
View 3 Replies
View Related
Sep 7, 2015
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
          red   blue  green
  pen       2    0    1
  marker    0    1    0
  pencil      1    3    0
  highlighter  1    1    1Â
We tried Sum but not able to display in single row.
View 2 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
May 17, 2015
I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE"Â .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
View 3 Replies
View Related
Oct 17, 2007
I am trying to drag data from Informix to Sql Server. When I kick off the package
using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.
What options do I have that will work?
View 1 Replies
View Related
Jan 13, 2015
I've got some records like this:
ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0
where each month field has a 0 or 1, depending on if the person was enrolled that month.
I'm being asked to generate a table like this:
ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014
Is there some slam dunk way to do this without a bunch of If/Then statements?
The editor compressed all my space fields, so the column headers are off in some places.
View 8 Replies
View Related
Jul 20, 2005
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
View 2 Replies
View Related
Nov 13, 2007
Hi there.
I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE')
Can anyone out there help?
Please advise. Thanks.
Best.
K7
View 1 Replies
View Related
Jul 6, 2015
A date field in a report returns the date value as:
2015-07-01 13:30:27.000
Is there any way I can script this to appear as:
01 July 2015 (or 01-07-2015 or 01/07/2015)Â - basically to cut out the hours, minutes and seconds?
The best I have managed is: CONVERT (varchar(17),DATE,113) AS Date1 but this still leaves me with:
01 July 2015 13:30
View 9 Replies
View Related
Aug 27, 2015
I have a QA Deployment Date field that is being returned in a custom report I created. I also found a sample date range parameter:
What I want to accomplish:
I want to select a From and To Date and filter the report to only display the rows that have the QA Deployment Date within the selected range.
For example.. I want to select From Date (8/1/2105) and To Date (8/31/2015) and I only want to return only the results that have a QA Deployment date between that selected range.
View 3 Replies
View Related
Jun 18, 2007
Brand new to this, so please bear with me.I'm using the following code fragment to update a datetime field on a SQL Server 2005 database table:cmd.CommandText = "Update Projects Set EntryDate = " & Convert.ToDateTime(txtEntryDate.Text)cmd.ExecuteNonQuery()The result of the update operation is the the database field contains the value "1900-01-01 00:00:00:000". This probably means that I passed nulls to SQL; however, I see a valid date in the txtEntryDate field on my web form (i.e., "06/18/2007"). I also did a "Response.write" to display the txtEntryDate and it looks Okay.Can someone tell me what I'm doing wrong?Thanks!Using Visual Web Developer 2005 Express.
View 1 Replies
View Related
Jun 2, 2014
I'm writing a view to check record counts in a table that has numerous datasets and therefore various "Activity Dates". Is it possible as part of the SQL statement to have a CASE statement for example so that it can identify the field to use as the activity date?
The field to use is being identified using a seperate table so at the moment I have CASE WHEN FieldToUse = '2' THEN MapCol ELSE '[Activity_Date]' END, where FieldToUse = '2' identifies the date field to use and the MapCol data is the field name to be used as the activity date.
Is this even possible?
View 3 Replies
View Related
Apr 23, 2015
My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario
ID Â Â Â Â Â StartDate
1 Â Â Â Â Â Â 2014-01-01
1 Â Â Â Â Â Â 2014-02-01
1 Â Â Â Â Â Â 2014-05-01-------After Gap Restarted
1 Â Â Â Â Â Â 2014-06-01
1 Â Â Â Â Â Â 2014-09-01---------After last gap restarted
1 Â Â Â Â Â Â 2014-10-01
1 Â Â Â Â Â Â 2014-11-01
2 Â Â Â Â Â Â 2014-01-01
2 Â Â Â Â Â 2014-02-01
2 Â Â Â Â Â Â 2014-03-01
2 Â Â Â Â Â Â 2014-04-01
2 Â Â Â Â Â Â 2014-05-01
2 Â Â Â Â Â Â 2014-06-01
2 Â Â Â Â Â Â 2014-07-01
For Id 1 the start date after the latest gap is  2014-10-01 and for id=2 there is no gap so i need the minimum date  2014-01-01
My Expected Output
id       Startdate
1 Â Â Â Â Â Â Â 2014-10-01
2 Â Â Â Â Â Â Â 2014-01-01
View 4 Replies
View Related
Sep 27, 2005
Hi,
I need to convert a var char field to date field (DD/MM/YYYY)
Current convertion format:
CAST(Report_Date as DATETIME)
How can i convert to Date field with date format of DD/MM/YYYY?
Thanks.
Onn Onn
View 2 Replies
View Related
Mar 27, 2008
Hi,
I am very new to using SQL. Our department usually uses Brio to query the various databases under our control. However, I have recently come against a problem that prompted me to create a custom SQL query which works well as far as it goes. My problem is looking for specific conditions in billing information I receive monthly. I would like to compare on of the date fields contained in the database with a field in the form of YYYYMM (200710, for October 2007) I have created a custom column generator that forms a date from the YYYYMM. I would like, however, do the translation on the fly and make the comparison during the query. The problem is that query without the date check returns a mass of data, only about 1 percent of which is what I want.
The beginning of the SQL query looks like this:
FROM From.T_Crs_Tran_Dtl WHERE T_Crs_Tran_Dtl.Crs_Bill_Yr_Mo IN ('200710', '200711', '200712') AND ((T_Crs_Tran_Dtl.Crs_Cde IN ('1G', '1V') AND (T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND (T_Crs_Tran_Dtl.Prev_Stats_Cde IN (' ', 'TK', 'TL') AND T_Crs_Tran_Dtl.Cur_Stats_Cde IN ('TK', 'TL') AND T_Crs_Tran_Dtl.Std_Tran_Typ_Cde='B') OR (T_Crs_Tran_Dtl.Prev_Stats_Cde='UN' AND T_Crs_Tran_Dtl.Cur_Stats_Cde='XX' AND€¦
It is the €œ(T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND€? part of the query that is just plain wrong. The business part of this statement takes the YYYYMM field and turns it into a date which is the last day of YYYYMM.
I hope someone out there can help me with making this comparison.
I appreciate your help.
Bill
View 8 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
Jan 4, 1999
My process return a date in char format like "3/12/1998",
How could I put this data ( in char ) directly to date ISQ Server field ?
Thank's
View 1 Replies
View Related
Mar 16, 2014
I want to display only the date part of a date field which contains both date & time information.
For example I have the value '2013-11-14 00:00:00.000' in my result set, and ideally I would like to show only '2013-11-14'.
I have looked up the datepart() command, however I can't work out how to return all parts of the date, rather than just the year, month, or day.
View 3 Replies
View Related
Jul 14, 2014
I have a date held in a varchar field in a temporary sql table and I want to convert it into a sql date and it doesn't work. I can replicate this as below -
So I run
select
cast ('14/02/2014' as date)
and I get a conversion failed error.
View 2 Replies
View Related
Jul 4, 2014
I have passed createdDate from UI to Stored procedure.createdDate field declared with DateTime.it is having value 2014-07-01.I need to fetch records from the database based upon the created field.but Create_TM in database having value Date with timestamp.so how would i change the createdfield in stored procedure.
ALTER PROCEDURE [dbo].[ByDateRange]
@Feed VARCHAR(50),
@CreatedDate DATETIME
select * from Date_table where Create_TM = @CreatedDate
View 1 Replies
View Related
Dec 29, 2005
I have a table named "shift" and I need to setup my query to return only data where the field "startime" = today. The problem I am running into is the starttime field it laid out like "2005-12-29 14:00:00" with different time values. I need to ruturn everything that has todays date regardless of the time value. I tried using GetDate() but that is returning data for other days as well or just data before or after the current time. Does anyone have any suggestions? This is driving me crazy! Thanks, Garrett
View 7 Replies
View Related
Nov 3, 2006
Can someone tell me sql query for filtering date field for current day,not last 24hours but from 00:00 to current time?
View 2 Replies
View Related
Feb 26, 2008
Hi,
I need to insert into a column (lets say column x) a date based on the date on another column (lets say column y).
What I need is:
Take the day and month from column x (all records are formated yyyy-mm-dd)
Place it in column y
The yyyy in column y should be - currenct year +1 and no the year in column x.
All help welcome.
View 9 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
