SQL Server 2012 :: MIN And MAX Dates For Groups Of Rows?

Jan 21, 2014

I have a dataset that contains an EmployeeID, StartDate, EndDate, and Location. Each record tells me at which location and employee was during a payroll period (fortnightly). So the data looks like:

EMP_KEYSTART_DTEND_DTLOCATION
120130117201301318103
120130117201301318103
120130131201302143354
120130131201302148103
220130117201301311234
220130131201302144567
120130214201302283354
220130214201302281234

Employees can be at multiple locations during the two weeks. They can work at one location, stop working there, start working somewhere else, and then maybe go back to their old location. There are duplicate records here as each employee can be at the same location multiple times during the two week period. What I need to capture is the actual start and end date of an employee at each location for each 'assignment'. An assignment is defined as a continual period of employment at a location with gaps of no less than 4 days - if there is a gap of more than four days then that is classed as a new assignment.

View 7 Replies


ADVERTISEMENT

SQL Server 2012 :: Compare Dates Between 2 Different Rows And Columns?

Feb 18, 2015

What I need to be able to find is any records where the Discontinue_Date is greater than the Effective_Date on the next row for a given Customer ID and Part_ID. This is a customer pricing table so the Discontinue_Date of row 53 for example should never be greater than the Effective_Date of row 54130, these are the records I'm looking to find. So I'm looking for a SELECT query that would look for any records where this is true. Obviously the last Discontinue_Date row for a Customer_ID will not have a next row so I wouldn't want to return that.

View 9 Replies View Related

SQL 2012 :: Creating A Unique ID For Groups Of Rows?

Mar 20, 2015

We have a sorted data that looks like the first 3 columns below, and fourth colum is what I want to create)

Shift_start meal_break shift_endShift ID
1 0 0 1
0 0 0 1
0 0 1 1

1 0 0 2
0 0 0 2
0 0 1 2

I need to find a method to assign unique Shift IDS to rows that correspond to a single shift. For instance, the first shift would begin on the first row when shift_start flag is turned on, and end on the third row when shift_end flag is turned on.

Can I do this in SQL ?...some kind of grouping ?

View 2 Replies View Related

SQL Server 2012 :: Rank On Repeating Groups Of Bit Field?

Jan 16, 2015

I have some data where a bit value changes over time and I want to rank it by the repeating groups, how do I write the SQL so that I get the result below?

I want to sort by create date, and every time the bit changes value we start the rank over.

If I partition by the bit field, it will just group the entire data set by the bit field.

Bit CreateDate Rank
1 3/15/2014 1
1 3/14/2014 2
0 3/9/2014 1
0 3/8/2014 2
0 3/6/2014 3
1 3/4/2014 1
0 2/28/2014 1
0 2/20/2014 2
0 2/15/2014 3
0 2/10/2014 4

View 3 Replies View Related

SQL Server 2012 :: How To Choose Max Of Each Groups Formed By Row Number

Sep 15, 2015

I have a below table as:

IF OBJECT_ID('tempdb..#Test') IS NOt NULL
DROP TABLe #Test
--===== Create the test table with
create table #Test([Year] float,
Age Int,
)
INSERT INTO #Test
([Year], Age)

[Code]...

I queried below to get additional column

Select *,row_number() over(partition by [Year] order by Age) as RN from #Test as

YearAgeRN
2014301
2014312
2014323
2015251
2015262
2015273
2015284
2015295

i want one more addtional column (Desired Output) with max of RN in each group as below"

YearAgeRNDesired output
20152515
20152625
20152735
20152845
20152955
20143013
20143123
20143233

View 7 Replies View Related

SQL Server 2012 :: Calculate Number Of Groups And Group Size With Multiple Criteria

Jun 15, 2015

I need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table

GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end
and in the second Temp Table, I have
select
,GrpSize=sum(grpsize)
,NoofGrp=count(distinct grpsize)
From Temp1

The issue is for the date of 5/26, the begtime and endtime are not consistent. in Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I wan that client to be in the second group (grp2) My partition over creates 4 groups rather than two.

View 9 Replies View Related

SQL Server 2008 :: Compare Dates In Rows Of A Table?

Apr 8, 2015

I have the following information in a table. What I would like to do is pull out all the visits for each customer that are less than 30 days apart.

Customer# VisitDate
9082012-07-28 00:00:00.000
9082013-09-20 00:00:00.000
9082013-12-23 00:00:00.000
9082014-01-10 00:00:00.000
9082014-01-27 00:00:00.000
9082014-02-16 00:00:00.000
9082014-05-21 00:00:00.000
9082014-05-30 00:00:00.000
9082014-10-01 00:00:00.000
9082015-02-28 00:00:00.000
9082015-03-22 00:00:00.000
9272012-02-16 00:00:00.000
9272014-12-14 00:00:00.000
9272014-12-23 00:00:00.000

View 2 Replies View Related

SQL Server 2012 :: Dates In Different Languages

Mar 31, 2014

Is there a way to make a single insert (in a loop) and take system dates and insert them in different languages without making a new loop for each language.

Example

Create table dateLanguage(
monthName nvarchar(20),
monthNameEs nvarchar(20),
monthNameFr nvarchar(20),
MonthNamePt nvarchar(20)
)

So for example
January,
Enero,
Janvier,
janeiro

View 3 Replies View Related

SQL Server 2008 :: Creating Rows Between Dates In Single Statement

Apr 21, 2015

I am trying to find an easy way to create multiple of just two date in a single sql statement.

E.G.

A statement using the parameters

@StartDate = '2015-01-01'
@EndDate = '2015-01-05'

Ends up with rows:

'2015-01-01'
'2015-01-02'
'2015-01-03'
'2015-01-04'
'2015-01-05'

What would be the best way to do this ?

View 3 Replies View Related

SQL Server 2012 :: Getting End Dates For History Segments?

Aug 14, 2014

I have a customer history table with the follow structure and data:

CustomerID Tier StartDate RecordStatus

123 A 01/01/2013

View 3 Replies View Related

SQL Server 2012 :: Getting End Dates For History Segments

Aug 14, 2014

I have a customer history table with the follow structure and data:

CustomerID Tier StartDate RecordStatus

123 A 01/01/2013 1
123 A 03/01/2013 0
123 B 03/01/2013 1
123 B 06/01/2013 0
123 A 08/01/2014 1
456 C 02/01/2014 1

CREATE TABLE TEMPHISTORY(
CUSTOMERID VARCHAR(11),
TIER VARCHAR(10),
STARTDATE DATE,
RECORDSTATUS TINYINT)

[Code] .....

The RECORDSTATUS value of 1 means the record is active. A corresponding record of the same CustomerID, Tier. in startdate chronology, with a value of 0 indicates that the previous record with the status of 1 has now terminated and the startdate of the record with recordstatus of 0 is the start date of the termination of the previous record, or better stated, the end date of the previous record.

What I need to do is re-record the above data the startdate of each terminated record become an enddate for the previous record, minus 1 day, as follows:

CUSTOMERID TIER STARTDATE ENDDATE
123 A 01/01/2013 02/28/2013
123 B 03/01/2013 05/31/2013
123 A 08/01/2014 NULL
456 C 02/01/2014 NULL

View 1 Replies View Related

SQL Server 2012 :: List Dates In Columns

Nov 11, 2014

I have a table (Event_Table) like:

EmployeeID, CustomerID, Date
1, 11, 2014-11-11
2, 13, 2014-12-10
1, 11, 2014-12-21
2, 13, 2015-01-11
1, 11, 2015-03-02

And now I would like to have a summary with a unique Employee/Customer combination and 3 Date columns like:

EmployeeID, CustomerID, Date1, Date2, Date3
1, 11, 2014-11-11, 2014-12-21, 2015-03-02
2, 13, 2014-12-10, 2015-01-11

Dates should be arranged with the first date in Date1, the next in Date2 and the third in Date3 (if there are forth and more dates I donĀ“t care)

View 2 Replies View Related

SQL Server 2012 :: Selecting Max Between Two Dates For A Given Year

Jul 23, 2015

I have three tables: EMP (ID, NAME), EMPDATE (ID, STARTDATE, ENDDATE), YEAR(YEAR)

I would like to get the most recent date within a given year per each EMP? For example, EMPID 1 can be enrolled in many programs, each program has start end dates. I need to list the most recent date an employee was enrolled (max date between START AND END DATE which ever is most recent enrollment) for a given year. For example, for 2014 his/her most recent enrollment should be 10/23/2014 for year 2014 and 2013-10-24 for year 2013.

SELECT ID, EMP.NAME, DTE.StartDate, DTE.ENDDATE, year
FROM
EMP_DATE DTE join
EMP_INFO EMP on EMP.ID = DTE.ID join
YEAR YR on YR.YEAR = YEAR(DTE.STARTDATE)

DATA SAMPLE:

EMP
ID NAME
1 JOHN

EMP_INFO
ID STARTDATE ENDDATE
12013-10-24 2014-03-11
12014-06-13 2014-03-11
12014-06-15 2014-03-11
12014-09-08 2014-03-11
12014-09-12 2014-03-11
12014-09-14 2014-03-11
12014-01-13 2014-05-17
12014-05-14 2014-06-09
12014-06-10 2014-06-16
12014-08-31 2014-09-04
12014-09-05 2014-09-06
12014-09-07 2014-10-23

YEAR

Year
2012
2013
2014
2015

View 9 Replies View Related

SQL Server 2012 :: Difference Between Two Dates (Excluding Weekends)

May 19, 2014

I have a table with a list of jobs along with their start and end datetime values.

I am looking for a function which will return the time taken to process a job using a start date and an end date. If the date range covers a Saturday or Sunday I want the time to ignore the weekends.

Example

Start Date=2014-05-15 12:00:00.000
End Date=2014-05-19 13:00:00.000

Total Time should be: 2 Days, 1 Hour and 0 Minutes

View 5 Replies View Related

SQL Server 2012 :: Calculating Working Hours Between 2 Dates

May 22, 2014

This function will return working hours between given 2 dates. This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM. This function also assumes that the working hours are between 7:30 AM and 4 PM. There is a section for public holidays there. We have a table for that you might not so that piece needs to be fixed.

CREATE function [dbo].[fnc_myHinkley_ASSY_CalcWorkingMinutes] (@StartDate datetime, @EndDate datetime)
RETURNS decimal(14,2)
/*
Programmer: Goran Borojevic
Date: 5/14/2014

This function will return working hours between given 2 dates. This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM. This function also assumes that the working hours are between 7:30 AM and 4 PM.
*/
AS
BEGIN

--check if one of the dates is null
if @StartDate is null or @EndDate is null
RETURN 0

[code]...

View 9 Replies View Related

SQL Server 2012 :: Aggregation Table On Sliding Dates

Sep 18, 2014

currently need to re-create an aggregate table in a proc every night to aggregate purchases broken down by person/store in groups of 3, 6 12 etc months.finding the performance of it is very slow as it covers 500,000 million rows.The query looks like

SELECTCusID(int)
, StoreID(int)
, SUM(L3M) as Last3Months
, SUM(L6M) as Last6Months
, SUM(L9M) as Last9Months

[code]...

I need to make changes to this because it is using a BETWEEN on a datetime column. I was wondering though, if anyone else has made agg tables like this before an found a better way of doing them?

View 0 Replies View Related

SQL Server 2012 :: How To Calculate Dates Difference In A Same Table

Sep 4, 2015

I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment.

Each customer is uniquely identified by a customer#

I need to find out if the customer came back after 200 days or more when the first appointment date was between jan12014 and Aug 31 2014. I am only interested in first follow up appointment after 30 days or more.

How can i do that in a query?

View 5 Replies View Related

SQL Server 2012 :: Find Most Recent Record Of Consecutive Dates

Feb 23, 2015

I have a table full of service invoice records. Some of the invoices are continuous, meaning that there may be an invoice from 01-16-2015 through the end of that month, but then another invoice that starts on feb 1 and goes for 6 months.

I want to only pull the most recent. Keep in mind that there may be other invoices in the same table for a different period. An example might be:

FromDate ToDate Customer Number Contract Number
02/01/2015 07/31/2015 2555 456
01/15/2015 01/31/2015 2555 456
04/01/2013 09/30/2015 2555 123
03/13/2013 03/31/2013 2555 123

From this table, I would like a query that would give me this result:

01/15/2015 07/31/2015 2555 456
03/13/2013 09/30/2015 2555 123

There will likely be more than just 2 consecutive records per contract number.

View 4 Replies View Related

SQL Server 2012 :: Number Of Months Between Two Dates In YYYYMM Format

Jun 15, 2015

I am looking to calculate no of months between two dates which are in YYYYMM format.

Like no of months between 201505 and 201305

View 7 Replies View Related

Selecting Groups Of Rows In A Table?

Feb 6, 2014

Table 1:
AB C
114.7
214.7
314.7

Table 2:
ADE
11a1.2
11b1.8
22a2.1
22b2.3
33a0.6

I would like to filter the second table taking one row per ID of Field A (first table) and selecting the rows whose sum of E is equal to the value in field C; in this example the resulting table should be:

Table 3:
ADE
11b1.8
22b2.3
33a0.6

Total field E = value in field C = 4.7

View 2 Replies View Related

Running Total On Rows Between Groups

Dec 5, 2006

I want to write a stored procedure to add all of the flying time on a given airplane for each flight until now and then reset the total when it gets to a new airplane. I want to be able to pull from the table all flying time from a given date for a given airplane.

For example:

AircraftNo Date Flight_Hrs Total
38 3/1/06 1 4.5
38 5/10/06 1.5 3.5
38 7/10/06 2 2

100 8/19/03 4 8.5
100 10/04/04 3 4.5
100 9/17/06 1.5 1.5

I pulled the following code from the internet but I'm getting errors.

select AircraftNo
,Actual_departing_date,Flight_Hours
,sum(Flight_hrs) over partition by AircraftNo
order by Date
rows between current row and unbounded following) total
from (Select Distinct
F.AircraftNo
,F.Date
,C.Flight_Hrs
From GDB_01_4_Test.dbo.Flight_Log F,
GDB_01_4_Test.dbo.Flight_Cycle_Count C
Where F.Doc_No = C.Flight_log_Doc_No

Am I doing something wrong. I'm getting the following syntax errors (Using SQL 2000):

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'over'.
Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near 'Flight_log_Doc_No'.

View 5 Replies View Related

Possible To Access No. Rows In Groups Outside The Table?

Mar 22, 2007

Hi there,

I'm currently grouping data on some criteria, the way the data works basically means that there are between 2-3 groups guaranteed (no more).  In the Group Header I have CountRows(table1_group) thus giving me the total number of rows in each group at the top of each grouping.  However, I need to do a calculation in a textbox above the table using these row counts.  The method of grouping is not too difficult when using the VB syntax in the grouping expression but more difficult to get the same effect from the SQL side and hence I don't want to have separate datasets calling different queries to get the information that way.  Is there anyway to get access to these row counts on the groups?  Reporting Services can't know how many groups there will be before processing so I'm not sure that this is possible?  Ideally I suppose if there was a Group CountRows collection of some kind that could be accessed in an expression in the textbox or custom code then this might be possible. I could also add an invisible column and set the values to something specific depending on the group if there was a way to count the number of values in the table (unique values repeated in columns in a group, but unique to that group).

Any help is much appreciated,
Thanks.

View 4 Replies View Related

SQL Server 2012 :: Admission Dates Overlap And Summing Of Inpatient Costs

Jun 28, 2015

I've been struggling with this for some time. we have to group data based on Patients admission date and discharge date. If any Patients discharge date + 1 = admission date then we have group both rows into one row and sum costs from both the rows. Please check out the sample input and expected output for details.

Sample Input
PatientID AdmissionDate DischargeDate Cost
1009 27-07-2014 31-07-2014 1050
1009 01-08-2014 23-08-2014 1070
1009 31-08-2014 31-08-2014 1900
1009 01-09-2014 14-09-2014 1260
1009 01-12-2014 31-12-2014 2090
1024 07-06-2014 28-06-2014 1900
1024 29-06-2014 31-07-2014 2900
1024 01-08-2014 02-08-2014 1800

Expected Output
PatientId AdminssionDate DischargeDate Cost
1009 27-07-2014 23-08-2014 2120
1009 31-08-2014 14-09-2014 3160
1009 01-12-2014 31-12-2014 2090
1024 07-06-2014 02-08-2014 6600

Please Use the below script to generate the source table and fill them up with the sample data.

--Create Table
CREATE TABLE PatientProblem
(
PatientID INT,
AdmissionDate DATETIME,
DischargeDate DATETIME,
Cost MONEY

[Code] ......

View 9 Replies View Related

SQL Server 2012 :: Update Statement Will Not Update Data Beyond 7 Million Plus Rows Out Of 38 Millions Rows

Dec 12, 2014

I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).

SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0

View 5 Replies View Related

SQL Server 2012 :: Split Difference (number Of Nights) Between 2 Dates Into Respective Month Column

Sep 23, 2014

I'm using SQL Server 2012 and I need to run a query against my database that will output the difference between 2 dates (namely, DateOfArrival and DateOfDeparture) into the correct month column in the output.

Both DateOfArrival and DateOfDeparture are in the same table (let's say GuestStay). I will also need some other fields from this table and do some joins on some other tables but I will simplify things so as to solve my main problem here. Let's say the fields needed from the GuestStay table looks like below:

I need my query to output in the following format:

How to write this query?

View 9 Replies View Related

SQL 2012 :: Moving Log Files In HA Groups

Feb 11, 2014

Trying to find out if this is the best way to move log files in databases that are in an availability group.

remove the DB from the AG
Run alter database commands like you would normally to take offline ,move file,bring online ,etc
drop the db from secondary node
then rejoin the DB to the AG

Is that the only option for moving them when its in an avail group? cant find any other info on moving files in mirrors or HA groups

View 2 Replies View Related

SQL 2012 :: Database Can't Be In Two Availability Groups

Jun 30, 2014

I been trying to learn availability groups since I have not implemented it.

From my understanding you can have more than one group.

Lets pretend we have two groups in one instance:

1. Accounting
2. Engineering

From my understanding you can't make a database in two AG because it wouldn't make sense.

But lets pretending there is one database that both are used by accounting and engineering.

Would you have to make a third AG for future fail overs so that other databases in the other two group don't failover when not needed because when you fail over an AG all the databases inside it fail over.

View 3 Replies View Related

SQL 2012 :: Going From Cluster To Avail Groups

May 13, 2015

So, today we have the following:

Dallas: A 2 node Windows 2008 Cluster running SQL 2012 ENT cluster
Wash: A 2 node Windows 2008 Cluster running SQL 2012 ENT cluster

and I'm mirroring (synchronous, no witness) a database from Dallas to Wash.Crappy set up. I know.Now customer wants to have the database mirror to another server in Dallas.What are the high level steps to transform my two clusters to use Availability Groups and Always On?Do I need to basically start over and build a new environment?or can I transform my two disconnected cluster?

View 4 Replies View Related

SQL 2012 :: Multiple Availability Groups On One Instance

Aug 12, 2013

From what I understand, one SQL Server 2012 instance can host multiple availability groups. That should allow, for example, the following situation - one SQL Server 2012 instance containing three primary replica databases - each one part of a different availability group - and each availability group's secondary replica located on a separate SQL Server 2012 instance.

Can you have three SQL Server 2012 instances, each with one primary replica database and have all three secondary replica databases on one SQL Server 2012 instance? So instead of, as above, going from 1 primary server to 3 secondary servers, this time we're going from 3 primary servers to 1 secondary server? The one secondary server would then contain all three secondary replicas for each of the 3 separate primary replicas.

This would mean that the single server (where all three secondary replicas reside) was part of three separate Windows Server Failover Clustering clusters.

Is this scenario possible?

View 4 Replies View Related

SQL 2012 :: AlwaysON Availability Groups And Listeners

Feb 13, 2014

I have four instances and each instance have its own Availability Group with its own listener.

Would like to know if you can have one listener for multiple Availability groups?

View 2 Replies View Related

SQL 2012 :: Rebalancing Data In File Groups

Apr 28, 2014

I am looking for the easiest way of rebalancing data across multiple files.

Instead of creating a secondary filegroup and then dropping and recreating all indexes in the database which is going to take ages (we have a lot of tables and indexes), I am trying to just add more files to the primary file group and then rebalance data evenly between these.

I guessed that adding the new files to the primary file group and then rebuilding all indexes on a table should redistribute the table over these multiple file groups evenly. This is not the case though. It does rebalance data a bit but I still end up with the majority on the first file that existed.

I have attached the script I am running, maybe it is something in the create database/file statements that is the issue.

Basically what I am seeing is to start off with the table is 160MB. I then add the file groups and rebuild all indexes on the table. The first file is then about 100MB and each of the three other files are about 20MB. I would expect them all to be the same size.

View 4 Replies View Related

SQL 2012 :: Renaming AlwaysOn Availability Groups?

Apr 28, 2014

We had 3 Availability Groups set up in SQL 2012 last year but they were poorly named so I am just looking to rename them but there doesn't seem to be any command for it that I can find.Can they not be renamed once created? I guess I could just create new ones and move the DB's into them but just thought I would check!

View 1 Replies View Related

SQL 2012 :: Tables On Separate File Groups

Jul 30, 2014

We have a large Datawarehouse and the size is 50TB.. The tables are placed in filegroups based on the schema like fact, dimensions, raw data each sit on seperate filegroups. I am thinking will it make sense to seperate the large facts which are having billions of rows so that they reside on filegroups on their own..

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved