October 14, 2016 at 5:38 am
HI I have the following Columns as below
FrankStartDateFrankEndDateTrumpStratDate TrumpEnddate
2016-01-13 NULL NULL NULL
NULL 2016-01-142016-01-14 NULL
NULL 2016-01-14NULL NULL
NULL 2016-01-15NULL NULL
2016-01-16 NULL NULL 2016-01-16
2016-01-17 NULL NULL NULL
NULL 2016-01-19NULL NULL
I Am trying to achieve results like below
Frank Number of Days:
DATEDIFF(DAY, '2016-01-13', '2016-01-14')
DATEDIFF(DAY,'2016-01-16','2016-01-19')
Trump Number of Days:
DATEDIFF(DAY,'2016-01-14','2016-01-16')
Can some one suggest me with a solution Thanks in Advance
October 14, 2016 at 6:41 am
When supplying data, you really should provide a DDL and Insert statement. Providing a copy and paste of the data is often far from useful, especially when you data doesn't line up with your column headings. It makes it very difficult to read, and can cause people to make wrong assumptions about your table. See my link in my signature for some advice.
I'm made a DDL based on your pasted output:
CREATE TABLE #StartEndDates (FrankStartDate DATE,
FrankEndDate DATE,
TrumpStartDate DATE,
TrumpEndDate DATE);
INSERT INTO #StartEndDates
VALUES ('2016-01-13', NULL, NULL, NULL),
(NULL, '2016-01-14', '2016-01-14', NULL),
(NULL, '2016-01-14', NULL, NULL),
(NULL, '2016-01-15', NULL, NULL),
('2016-01-16', NULL, NULL, '2016-01-16'),
('2016-01-17', NULL, NULL, NULL),
(NULL, '2016-01-19', NULL, NULL);
DROP TABLE #StartEndDates;
The data here, however, looks very messy. Why does Frank end for 3 days and never start, and then starts twice before finishing? Although I'm not answering the question, my first thought would be that the data could use a clean, and the table structure changed. Why not have a column for the person, rather than separate columns for the two users? What happens if a 3rd is added?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 14, 2016 at 10:38 am
Thank You and from next time i will follow in having the DDL statements pasted.
Yes the data is very messy . I need to delete or need to consider the first End Date and Ignore the remaining dates
It would be fine if i have a solution to delete records that have End Date continuously more than once in a row
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply