Transact SQL :: Finding Unique Counts Based On Consecutive Days?
Jul 9, 2015
I have a scenario here where the data looks like -
ID Date
100 07/01
100 07/02
100 07/03
100 08/01
100 08/02
100 08/15
Now I need to find out unique occurrences of ID - 100 ( where count = unique only if the occurrences are in consecutive days, gap of even 1 day causes it to be a different instance ) - SO with the above data I should have unique occurrences as 3.efficient way to calculate this ?
I have the following table (Table does not have unique key id )
Last Name First Name DATE Total-Chrg
Jaime KRiSH 5/1/2015 -4150.66 Jaime KRiSH 5/1/2015 1043.66 Jaime KRiSH 5/1/2015 1043.66 Jaime KRiSH 5/1/2015 4150.66 Jaime KRiSH 5/3/2015 4150.66 Peter Jason 5/1/2015 321.02 Peter Jason 5/1/2015 321.02 Peter Jason 5/23/2015 123.02
I want the results to be in following way
Uniq ID Last Name First Name DATE Total-Chrg
1 Jaime KRiSH 5/1/2015 -4150.66 2 Jaime KRiSH 5/1/2015 1043.66 2 Jaime KRiSH 5/1/2015 1043.66 3 Jaime KRiSH 5/1/2015 4150.66 4 Jaime KRiSH 5/3/2015 4150.66 5 Peter Jason 5/1/2015 321.02 6 Peter Jason 5/1/2015 321.02 7 Peter Jason 5/23/2015 123.02
May be we may do by dense_rank or Row_Number, but I couldn't get the exact query to produce based on the above table values. There are some duplicates in the table(which are not duplicates as per the Business). For those duplicated Unique ID should be same(Marked in Orange Color which are duplicates).
I have a table with 3 columns: Item# | Date | ItemAmount. Everyday there is a number of transactions entered. An Item# can only be entered once par day (if it has occurred that day).
What I want to do is to : retrieve the number of total days where an Item has been entered for more than 2 consecutive days (for the month).
Example: if item I022 has been entered Monday and wed, then ignore, but if it's been entered Mon, Tues then return 2, if Mon, Tues, Wed then return 3 because the days are consecutive.
Hi! I'm trying to create a query to calculate the number of days between two dates, but I only want to include working days. Is there a way to do this?
We have customer accounts that we measure usage. We want to run a report for all customers whose current usage is 0 and a count of how many months it has been zero consecutively. Here is an example.
I am trying to determine the amount of days in a month to prorate a month end estimate. We measure service calls and need to approximate how many we will have at month end. I would like to automate a query to post on our web and need to know how many days are in the current month.
A possible solution would be to piece together a datetime variable using getdate and dateadd then use a datepart. However , I don't know how to create a datetime variable this way.
Hi and Thank you in advance I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates. Example if Date1 = 11/26/1999 Date2 = 11/30/1999
the DateDiff(dd,Date1,Date2) the result should be 2 I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates. Thank you in advance Tomas
Hi and Thank you in advance I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates. Example if Date1 = 11/26/1999 Date2 = 11/30/1999
the DateDiff(dd,Date1,Date2) the result should be 2 I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates. Thank you in advance Tomas
In my query I have a date field and using that date field I want to pass the date to a function, or whatever is most appropriate, then compare that date with a range of the last 4 weeks, which will be numbered 1-4, 1 being earliest and 4 being latest, and then return the week number.
I've determined I need some sort of look up but will need to be dynamic - thinking maybe a temp table, first using the date the report is run on and counting 28 days back from the most recent last saturday then setting number to 1 for first 7 days then 2 for next 7 days etc.
Here is the desired counted output, I would like to pull distinct Date, MachineNumber, TestName and then count how many times they occur in the raw data form.I do need to perform a case on the date because right now its in a datetime format and I only need the date.
I am pulling three columns with the same names from 8 different tables. What I need to display the date, machine & test name and count how many times a test was run on a machine for that date. I have a feeling this can be handled by SSAS but haven't built an analysis cube yet because I am unfamiliar with how they work. I was wondering if this is possible in a simple query. I tried to set something up in a #Temp table. Problem is the query takes forever to run because I am dealing with 1.7 Million rows. Doing an insert into #temp select columnA, columnB, columnC from 8 different tables takes a bit.
hai I have to delete a single row from a table , where there are multiple rows with the same data and i want to delete only one row and i cannot alter my table structure. Thanks in advance satya
I have simple query that works fine if I write it every time. I want to get this one time group by each year and week ending. here us the query:
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/09'
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/16'
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/23'
How can I get the count for each week year and each week ending? Weekending 05/16 will include ID's from weekending 05/09 as well and week ending 05/23 will include all the ID's from 05/16 and 05/09 and so on...
I am using Access 2007 and I have 10 columns of data.
I am trying to select all the data from all 10 columns where the first column's data returns only the Unique values and the other columns return all the data from the row that is returned from the first column.
But that returns all the data in the table and all values in the Sorter column with duplicates because each row has distinct data.
I am trying to return unique values from [SFR Rates All].Sorter (the first column) and all the data from the other 9 columns that are contain the row with the unique value in Sorter.
I know that I am missing something basic but well, I can't figure it out.
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState 1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE 1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
I am in a situation where I need to find out unique field names with different values in a table having 200+ columns. Let's say I have two rows with 200+ columns ( I exported these rows from Lotus Notes to SQL Server) I am not sure what columns makes unique of these rows. It's going to be tedious by checking each column values. Is there anyway I can write a squl query on these two rows which outputs column names which are having unique values. I would appreciate If anybody gives me hint about achieving desired result
I am having a bit of difficulty trying to work how to write a query. I am normally only used to doing SELECT, UPDATE and DELETE queries ... the simple ones.
However I have come across a query whereby things get a lot more complex.
My company has an employee table with a way to store data that I have not been used to.
EMPLID, NAME, DATELASTMODIFIED, EmployeeStatus. (There are lots more but N/A)
The problem that I have is that there can be more than one row for an employee id. Therefore I cannot uniquely identify an employee id. Instead what I have discovered I have to use is to find an employee with the last modified date higher than the other rows with the same employee id.
The query I am trying to achieve is:
Code:
Count all employees that have an employee status of 'Employed'.
(Therefore counting only one employee per id rather than counting the same employee more than once)
The trouble is, translating this into a SQL query. I have tried to have a go but don't know how to query:
Code:
SELECT COUNT (DISTINCT EmplId)[ FROM Employeetable AS e WHERE e.LASTDATEMODIFIED <And Stuck> AND EmployeeStatus = 'Employed'
So I thought hmm lets pseudo code this, but then I get stuck into compelixty:
Code:
SELECT * FROM EmployeeTable WHERE EmployeeStatus = 'Employed'
int counter = 0; ForLoop (EmplId) { LastDate Date if(LASTMODIFIEDDATE > LastDate) { LastDate = LASTMODIFIEDDATE } //How do I return the value? Do I count the value? }
Has anyone come across this issue before? Can anyone help it quite fundemental to what I have to query so help on this is highly appreciative.
I have a table which maps two related IDs. That table has 3 columns: ID, BHID & EPID. I need to find all of the BHIDs where the EPID is unique. It seems easy enough, but I keep going in circles..
USE [CGB] GO /****** Object: Table [dbo].[ePID_BHID] Script Date: 04/15/2015 15:48:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
I'm trying to query an SQL table column with date values to show 8 Days ago results.
I've started with this query:
SELECT ficheiro, erro, descritivo_erro, contrato, DO, movimento, data, descritivo, tipo_movimento, desconto, montante, comissao, IVA FROM status_day WHERE (YEAR(data) = YEAR(GETDATE())) AND (MONTH(data) = MONTH(GETDATE())) AND (DAY(data) = DAY(GETDATE()) -8) ORDER BY descritivo_erro, contrato
The problem is that the text in red will have some problems when the month changes - If I want the 8 days ago results from January and the system date is 1st of February the query will not return any values.
I read something about DATESERIAL but is wasn't conclusive on how to use it with system date.
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.
I am a beginner at SQL so thanks ahead of time.....How do I get unique rows based on studentID? Distinct and group by don't seem to workDESIRED RESULTSStudentID First Name Last Name Other Columns...............................................634565491 MARINA BALDERAZ 640484566 TERE BALDERAZCURRENT SQL AND RESULTS.....SELECT ClassRosterRecID, StudentDataRecID, StudentDataKey, StudentID, FirstName, LastName, CurrentGrade, Gender, Ethnicity, EconDisadvantaged, TitleI, Migrant, LEP, Bilingual, ESL, SpecialEducation, GiftedTalented, AtRisk, CareerTech, Dyslexia, LastName + ', ' + FirstName AS LastNameFirstName, EconDisadvantagedSort, TitleISort, MigrantSort, LEPSort, BilingualSort, ESLSort, SpecialEducationSort, GiftedTalentedSort, AtRiskSort, CareerTechSort, DyslexiaSort, DistrictID, CampusID FROM vClassDemographicsDetail WHERE (DistrictID = '057910') AND (CampusID = '057910101') AND (LastName LIKE '%BALDERAZ%')StudentID First Name Last Name Other Columns...............................................634565491 MARINA BALDERAZ 634565491 MARINA BALDERAZ 634565491 MARINA BALDERAZ 640484566 TERE BALDERAZ640484566 TERE BALDERAZ640484566 TERE BALDERAZ
I don't immediately find if this is possible but hope someone can give me an answer: is it possible to make a unique constraint over 2 columns but only when 1 column has a specific value ?
Example: table (tableid, instancetype, instancename, ..) instancetype can be A or B if it is A then instancename must be unique but for B instancename is not unique as these are copies from A
only solution I can think of is to make a trigger on an insert to check what the instancetype is and do a select to see if the name already exists in the table or not..
are there other solutions to make a constraint like this ?
The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on
I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType and insert into #Request table along with the other data. How do I do this?
SAMPLE DATA
Code Block Insert into #CallDetail(12123,1) Insert into #CallDetail(53423,1) Insert into #CallDetail(6532,1) Insert into #CallDetail(62323,1) Insert into #CallDetail(124235,1) Insert into #CallDetail(65423,2) Insert into #CallDetail(56234,2) Insert into #CallDetail(2364,2) Insert into #CallDetail(34364,2) Insert into #CallDetail(85434,2)
Insert Into #Call(111,1,12123) Insert Into #Call(112,1,53423) Insert Into #Call(114,1,6532) Insert Into #Call(123,2,62323) Insert Into #Call(134,1,124235) Insert Into #Call(143,2,65423) Insert Into #Call(145,1,56234) Insert Into #Call(154,2,2364) Insert Into #Call(185,1,34364) Insert Into #Call(195,1,85434)
Insert Into #request Values('324234','Jack','SA023',12,111,0); Insert Into #request Values('223452','Tom','SA023',12,112,0); Insert Into #request Values('456456','Bobby','SA024',12,114,0); Insert Into #request Values('22322362','Guck','SA024',44,123,0); Insert Into #request Values('22654392','Luck','SA023',12,134,0); Insert Into #request Values('225652','Jim','SA055',67,143,0); Insert Into #request Values('126756','Jasm','SA055',67,145,0); Insert Into #request Values('786234','Chuck','SA055',67,154,0); Insert Into #request Values('66234','Mutuk','SA059',72,185,0); Insert Into #request Values('2232362','Buck','SA055',67,195,0);
EXPECTED OUTPUT will be (See the last column for unique nos). :
Code Block Insert Into #request Values('324234','Jack','SA023',12,111,1); Insert Into #request Values('223452','Tom','SA023',12,112,2); Insert Into #request Values('456456','Bobby','SA024',12,143,1); // Calltype = 1 empid= 1, but state is different, hence unique id is 1 Insert Into #request Values('22322362','Guck','SA024',44,114,1); Insert Into #request Values('22654392','Luck','SA023',12,123,3); Insert Into #request Values('225652','Jim','SA055',67,143,1); Insert Into #request Values('126756','Jasm','SA023',69,134,1); Insert Into #request Values('786234','Chuck','SA023',72,145,2); Insert Into #request Values('66234','Mutuk','SA059',72,185,1); Insert Into #request Values('2232362','Buck','SA055',67,195,2);
Please note that this will not be run as a batch query, but the no. has to be generated and inserted into #record table in realtime. I have given bulk of records for understanding of the problem
I have created table called Login in sql server where i have column usercode, email and login_date (login_date is datetime type)So, i created web application using .net. whenever user logged in, i am allowing based userLoged table and i am inserting into login table.
I have 3 month of record in my table. if i pass 2 and 10, i need to select the record of between 2 and 10 days of record of every month. if i pass 10 and 20, it should select the record between 10 and 20 of every month. How to query for that?
I'm trying to calculate the time difference between a date field and today's date in days. The date field is not mandatory and can therefore be blank. I'm trying to execute the following query:
SELECT employee_code, Civil_ID, DATEDIFF(Day, Civil_ID, GETDATE()) FROM ODEV_VIEW_Credentials_Expiry_Dates WHERE Civil_ID IS NOT NULL AND Civil_ID != '' ORDER BY employee_code
I keep getting the following message:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Warning: Null value is eliminated by an aggregate or other SET operation.
No matter what filter I use to process non-blank dates, it never works.
