Transact SQL :: Count Only Latest Entry
May 25, 2015
I have a table with below structure, with a sample entry for 1 country:
ID Date Event CountryCode TxnID
1 22/05/2015 ABC 123 111
2 23/05/2015 XYZ 123 111
3 20/05/2015 XYZ 123 222
4 21/05/2015 ABC 123 222
So, I need to get a count like:
FOR COUNTRYCODE=123
ABC count= 1
XYZ count= 1
Which means, I want to count for each transaction, the latest entry based on date. And there can be many countries like this. And I need to count both entries for ABC & XYZ.Normal count will give me duplicate entries.
View 10 Replies
ADVERTISEMENT
Oct 1, 2015
Got a view with a number of monthly tables joined with union all. The fields' structure is as follows:
Report_Date
Staff_ID
Staff_Name
...
Current_Department
In such a case any query to reflect all the columns with an additional column reflecting the said Staff's latest Department?
In other words, ones still working in the organization reflecting in the latest Department field, the Current Department and those already resigned/fired reflecting the last department they worked at with respect to the Report_Date.
View 6 Replies
View Related
Apr 8, 2008
Hi,
I have the following situation:
I have a table that is log of transactions of useractions. Each time a userstatus changes, a new entry is made into the table.
The table has (currently) about 4 million records, and keeps growing each day with thousands of records. It logs the status of about 150 users (currently active).
What I now want is to get the log-entry from all the distinct users that have a log-entry on the latest day (so current day, 'where TimeStamp > 2008-04-08').
(extremely simplified) Example:
(date is y/m/d)
USERID TIMESTAMP STATUS
----------------------------------------------------
... (thousands and millions of records above)
Ed 2008-04-07 18:00:00 Logged_Out
Jim 2008-04-07 18:30:00 Blabla_Status_5
Jack 2008-04-07 19:00:00 Logged_Out
Jim 2008-04-07 19:30:00 Logged_Out
Jim 2008-04-08 06:00:00 Logged_In (<< notice new day)
Jim 2008-04-08 06:01:00 Blabla-Status_1
Bob 2008-04-08 06:03:00 Logged_In
Fred 2008-04-08 06:05:00 Logged_In
Jim 2008-04-08 06:08:00 Blabla-Status_2
Jack 2008-04-08 06:12:00 Logged_In
Fred 2008-04-08 06:20:00 Blabla_Status_5
Jack 2008-04-08 06:12:00 Logged_Out
Fred 2008-04-08 06:12:00 Blabla_Status_4
Jack 2008-04-08 06:12:00 Logged_In
Jack 2008-04-08 06:12:00 Blabla_Status_7
----------------------------------------------------
The result should be:
USERID TIMESTAMP STATUS
----------------------------------------------------
Jack 2008-04-08 06:12:00 Blabla_Status_7
Fred 2008-04-08 06:12:00 Blabla_Status_4
Jim 2008-04-08 06:08:00 Blabla-Status_2
Bob 2008-04-08 06:03:00 Logged_In
----------------------------------------------------
(How) can this be done in one stored procedure?
View 7 Replies
View Related
Jul 24, 2015
We have 2 Tables, tblMain, which contains unique products and tblHistory, which contains a history of the product Records are added to tblHistory daily and then the prices in tblMain are updated with those prices. The problem that we have is that since there are multiple instances of the same product in tblHistory, we're not always updating using the most recent price. Below is the current SQL that we are using.
Update TM SET TM.Price = TH.PriceHist,
TM.DateUpdated = TH.DateUpdatedHist
FROM tblHistory TH LEFT JOIN tblMain TM ON TH.ProdCode = TM.ProdCode
Below is a sample of data in tblHistory and the records that I would need to be used for the update.
ProdIDPriceHistDateUpdateHist
115.257/20/2015
117.157/15/2015
120.357/23/2015
520.107/25/2015
With the above. I would need corresponding records in tblMain to be updated with the entry on 7/23/2015 for ProdID 1 and with ProdID 5
View 15 Replies
View Related
May 10, 2015
Got a Table_A in the form of:
Cust_ID
Cust_Name
and another Table_B as:
Recording_Date
Invoice_ID
Cust_ID
Cust_Address
In such a case let's say I need a View to represent the columns:
Cust_ID (From Table_A)
Cust_Name (From Table_A)
Cust_Address (From Table_B)
However, with the Customers changing their addresses and having multiple records of different dates in Table_B how to reflect the Latest address of the same in the requisite View?In other words Cust_Id (AB), Cust_Name (CD), who as per Table_B have 100 records with three different addresses (EF, GH & IJ) spread over two years. How to fetch the only latest address in this case? i.e. a record in View_C as:
Cust_ID
Cust_Name
Cast_Address
AB
CD
IJ
View 7 Replies
View Related
Sep 29, 2015
Empid 1 has 2 entries for the date 09/01/2015 and my left join returns both of those entries. What do I need to alter to make it so that only the most recent entry is returned not both entries?
Create Table #one
(
empid varchar(100)
,empbadgeid varchar(100)
,emplunchtime decimal(18,4)
,empbreaktime decimal(18,4)
,empworktime decimal(18,4)
[code]....
View 5 Replies
View Related
Nov 19, 2015
I am wanting to create a query so that I can combine each of the found duplicates into one entry.
An example of this is:
Name |ID |Tag |Address |carNum
-------------------------------------------------------
Bob Barker |2054| 52377 |235 Some road |9874
Bill Gates |5630| 69471 |014 Washington Rd. |3700
Bob Barker |2054| 97011 |235 Some road |9874
Bob Barker |2054| 40019 |235 Some road |9874
Steve Jobs |8501| 73051 |100 Infinity St. |4901
John Doe |7149| 86740 |7105 Bull Rd. |9282
Bill Gates |5630| 55970 |014 Washington Rd. |3700
Tim Boons |6370| 60701 |852 Mnt. Creek Rd. |7059
In the example above, Bob Barker and Bill gates are both in the database more than once so I would like the output to be the following:
Bob Barker |2054|52377/97011/40019|235 Some road |9874
Bill Gates |5630|69471/55970 |014 Washington Rd.|3700
Steve Jobs |8501|73051 |100 Infinity St. |4901
John Doe |7149|86740 |7105 Bull Rd. |9282
Tim Boons |6370|60701 |852 Mnt. Creek Rd. |7059
Notice how Bob Barker & Bill Gates appends the tag row (the duplicated data) into one row instead of having multiple rows. This is because I do not want to have to check the previous ID and see if it matches the current id and append to the data.
View 2 Replies
View Related
Jun 24, 2015
Below is the table information. I want the Code information with latest EDate only.
CREATE TABLE Test
(
EDate Datetime,
Code varchar(255),
Cdate int,
Price int
);
[Code] ....
Expected output :
2015-06-23 00:00:00.000 CL 20150701 73
2011-04-08 00:00:00.000 XP 20110501 37
2015-06-23 00:00:00.000 HO 20150701 22
View 13 Replies
View Related
Jul 17, 2015
I'm trying to create a report which would give the latest transaction on a database, which all sit on different servers. I wanted to know if there is an a simple way tracking the latest transactions instead of getting the information from the database tables.
View 3 Replies
View Related
Sep 30, 2015
I work for an organization that repairs serialized devices. Each time a device is repaired it's serial number is recorded in a database table along with the date it was repaired along with other information about the device. There are multiple cases where a unit has been repaired more than once.
I am trying to write a query that will return the serial only once and that record will be the record of the latest repair date. To sum it up,
Return a list of serials where if a serial exists more than once in the table, return only the instance of the serial record(s) with the max(created_dt). The end result will be a list of distinct serial numbers.
Here is my Query. The problem I believe is in my sub-query but I am not sure how to structure it.
SELECT
S.Id
, RMA
, PinSerial
, L4Serial
, L4Model
[Code] ....
View 3 Replies
View Related
Aug 27, 2015
I need to find the missing months in a table for the earliest and latest start dates per ID_No. As an example:
create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth)
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'
For the above table, the answer should be:
ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01
1) don't include an ID column,
2) don't use the start date/end dates in the data or
3) use cursors, which are forbidden in my environment.
View 9 Replies
View Related
Nov 26, 2015
CREATE TABLE #Dateissue
(ID int,
Code nvarchar (20),
Datein datetime,
Declined datetime )
[Code] .....
I have a table here. I want find a way of getting the latest date, when the code is the same. If the Declined date is null. Then I still want the latest date. E.g. ID 3.
If the declined date is filled in. Then I want to get the row, when the Datein column value is greater then the declined date only.
I tried grouping it by max date, but i got an error message when trying this out. Against the code
WHERE MAX(Datein) > Declined
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference. What do I need to do to get both my outputs working?
View 9 Replies
View Related
Apr 19, 2015
My requirement is to get the earliest start date after a gap in a date column.My date field will be like this.
Table Name-XXX
StartDate(Column Name)
2014/10/01
2014/11/01
2014/12/01
[code]...
In this scenario i need the latest start date after the gap ie. 2015/09/01 .If there is no gap in the date column i need 2014/10/01
View 10 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
Jun 20, 2013
How do you know when you should use COUNT and when to use SUM functions?
View 12 Replies
View Related
Jun 29, 2015
I have the following query and want the results within my query although I want to be able to get the SUM of my COUNT results. So basically I want the SUM of [Document Count] within my query/
DECLARE @Date date;
SET @date = '02/05/2015'
SELECT CONVERT(VARCHAR,CONVERT(Date, @Date, 103),101) AS [Date Scanned], (SELECT COUNT(*) FROM QUEUE WHERE date_scanned = @Date) AS [Total Scanned],
COUNT(dbc_state) AS [Document Count],
CASE
WHEN dbc_state = 1 AND ON_HOLD = '1' THEN 'On Hold'
[code]....
View 3 Replies
View Related
Jul 2, 2015
I have table like below
col1 volume
A 22
B 28
C 1
E 3
D 4
Output should be like below
col1 vol
A 22
B 28
Others 8
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
Sep 2, 2015
I'm working on a data analysis involving a table with a large number of records (close to 2 million). I'm using only three of the columns in the table and basically am grouping results based on different criteria. The three columns are PWSID, Installation and AccountType. I have to Provide the PWSID column with a count of the total number of installations per PWSID, also a count of AccountTypes per PWSID. I have the following query, but the numbers aren't adding up and I'm not sure why. I'm falling short in the total count by around 60k records.
CREATE TABLE [dbo].[CATASTRO_PSWID_SHPMUNINEW](
[Installation] [numeric](38, 8) NULL,
[AccountType] [nvarchar](50) NULL,
[PWSID] [smallint] NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
[code]....
View 8 Replies
View Related
May 28, 2015
The below is my query in SQL SERVER 2012
DECLARE
@PN_INC INT,
@POSITION_DESC VARCHAR(15)
SELECT @PN_INC= MAX(PositionNumberInc) FROM dbo.tblPosition WHERE LTRIM(RTRIM(UPPER(PositionDescription)))=LTRIM(RTRIM(UPPER(@POSITION_DESC)));
SELECT @PN_INC is returning null when there is no record in the table;
1. how can I make it return 0 when @PN_INC is null else it should pick the MAX(PositionNumberInc) value
2. I have written the below query to return 0 when @PN_INC is null but I need query to select the MAX(PositionNumberInc) value when @PN_INC is not null
SELECT @PN_INC= coalesce(MAX(PositionNumberInc), 0) FROM dbo.tblPosition WHERE LTRIM(RTRIM(UPPER(PositionDescription)))=LTRIM(RTRIM(UPPER(@POSITION_DESC)));
View 6 Replies
View Related
Aug 25, 2015
How can I display 0 when using COUNT and GROUP BY?I'm using SELECT CASE in my query. I was trying to use COALESCE but no result, COUNT result = 1. (there should be 0).
COALESCE((COUNT(DISTINCT (CAST((CASE
WHEN CurrStat = @Stat AND LogDate = @LogDate THEN Enumber ELSE 0 END) AS int)))), 0) AS InTrack,
View 5 Replies
View Related
Jun 1, 2015
I have a table with a couple hundred billion records (sql server 2005). When I do a select count(*) from tblx -- it takes this side of forever. Is it possible to count partitions and then add them up to make it faster?
How I could improve the performance for count(*) of this huge table. Note: if the partition idea sounds viable -- what would that look like?
View 11 Replies
View Related
Aug 22, 2015
My table is test and I have an ID and DateTest columns
I would like to count the weeks with more then one record.
So far I got this and return the weeks with 1 record per week. How can I count the weeks with more then one record
select sum(c)
from (
select c = count( id) over (partition by id, datepart(week, DateTest))
from test where id = '1' and DateTest >= '7-7-2015'
) a where c = 1
View 11 Replies
View Related
May 20, 2015
I have a table which table has :Identity Column (Identity), Schema name and Table_Name.
So I would like to write a cursor For each Table Count(*) from Table--@Cnt Int , Schemaname and Tablename need to store another table.
Implement a USP, using a cursor that scan the table, generate select count statement from configuration table and fire the select count statement and record the result of the query in the log table :
how can I write a cursor and Import Those results into to Another table.
View 3 Replies
View Related
Apr 25, 2015
I have a really basic question. The following SQL query works for me:
Select EnterUserID, Enterdate
from tblCsEventReminders
where EnterDate >= Convert(datetime, '2015-04-01')
I am essentially trying to write a query to count the number of user logins after a certain date, so I need to count 'EnterUserID' but I am having problems getting the count() function to work.
View 3 Replies
View Related
Aug 20, 2015
I am trying to find a count on group of our memberid`s who were active within a year since 2010 till today for particular memberships in my table I have memberid int effectivedate datetime termdate datetime Membershiptype varchar(10) ='GOLD','Silver' and 'Platinum'. I haven't used sql in a long time..
View 6 Replies
View Related
Jun 18, 2015
I have conducted a thorough search in the forums and cannot quite find my answer. I have a date field called open_date. If the open_date is more than 30 days old, I need to count it. I have started with the following code:
SELECT 'Older_Than_30Days' =
CASE
WHEN open.date >= 30 THEN '1'
ELSE '0"
END
My problem is the WHEN.
View 6 Replies
View Related
Sep 15, 2015
I have a question regarding the total count of the table rows
Select count (name) from test. Lets say I have got 200 count. And Select count (lastname) from test1.I have got 200.And this counts I should store it in "There are nn name items awaiting your attention and nn pending lastname's awaiting your approval".
So now I have to store the count in 'nn'.
View 5 Replies
View Related
Oct 7, 2015
I am trying to get count of records by month wise when they select year .It was showing the out put correctly but its showing months arer in numbers,but I want to display Jan,Feb ...
SELECT DISTINCT Standard, COUNT(Standard) AS Total,month(ReportDate) Month
FROM CPTable where
year(ReportDate) = '2015'
GROUP BY Standard, Standard ,
month(ReportDate)
Output
Standard Total Month
NULL 0 1 //Jan
NULL 0 2 //Feb
NULL 0 3
NULL 0 4
NULL 0 5
OSHA 18001, 1 5
NULL 0 6
NULL 07
NULL 08
OSHA 18001,158
TL 9000,18
NULL 09
OSHA 18001,139
View 4 Replies
View Related
Jul 3, 2015
I am using SQL 2012. I have a GROUP BY and I want to select two other fields from my table at the same time: One column that is a string (account_code) and one that I need to perform a count on (customer_number). I know the code COUNT(DISTINCT customer_number) works for getting that. I need to select both of those fields on top of what I have. I have the following:
DECLARE @Providers TABLE (ID INT IDENTITY(1,1),
Provider_Name VARCHAR(20),
Uniq_Id VARCHAR(10),
Total_Spent MONEY,
Total_Earned MONEY)
INSERT INTO @Providers (Provider_Name, Uniq_Id,Total_Spent,
Total_Earned)
[Code] .....
View 21 Replies
View Related
Jul 3, 2015
I am using SQL Server 2008.Each stock item will have default 4 document type (1, 5, 6, 7) and each will have 3 zone's (1, 2, 3) to qualify. Each zone will be updated to 1 for that document type if the item successfully pass through it. If all zone are NULL means no transaction. How to retrieve only the failed rows which means not all zone are 1 or NULL.In the image GJ-00064 has one row failed. So how to get the count of failed rows for each item
Expected result:
Uniid <-> Stockcode <-> FailedRows
1670 <-> GJ-00064 <-> 1
View 8 Replies
View Related
Jul 31, 2015
who has workflows created and ordered by CreateTimestamp . i need to count the number of workflows from top to point where there is either a success or failure workflow that occurs at the latest .
1.ban 137108351 has success workflow and prior to that workflow it has 2 workflows (exclude success and failure)
2.ban 104917284 has success workflow as latest (it still has failure but not considered because it is occurred earlier to success ) and prior to that workflow it has 2 workflows (exclude success and failure)
3.ban 107500674 has failure workflow and prior to that workflow it has 0 workflows (exclude success and failure)
below provided code for sample data as well
GO
/****** Object: Table [Temp].[deleteit] Script Date: 7/31/2015 3:04:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Temp].[deleteit](
[ban] [nvarchar](256) NULL,
[code]....
View 12 Replies
View Related
Dec 3, 2015
I have a table called Employee which have 6 columns. This table are having 1000 records. Now I want to know the distinct value count of all these 6 columns as well as normal count. like this:
ColumnName DistinctCount NormalCount
Id 1000 1000
Name 1000 1000
Phone 600 600
PINCode 200 1000
City 400 1000
Gender 2 1000
View 5 Replies
View Related