January 15, 2013 at 12:32 am
Hi all,
Please read the following attached word doc. Inside there is a target table with 6 columns which is what i want to achieve. However now i am only able to derive 5 out of the 6 columns. Base on the 5 columns, i should be able to derive the 6th but i do not know how after thinking for days. The 6th column which is FirstEntry should record down what is the First Entry Date time for that consecutive days. I dk how to explain in words. Hope some1 out there will be able to uds my table.
Anyone can help me derive the 6th columns? Hopefully using simple sql.
Thanks
January 15, 2013 at 5:38 pm
This is rare... after 8 hrs no one replied, maybe i am too demanding or the scenario is not clear enough? Therefore i made up another example:
This is what i have:
PASS_MENTER_DT CONSECUTIVE_D
Boo 5/1/2012 11:55:00 PM1
Boo 5/2/2012 11:30:00 PM2
Boo 5/4/2012 10:30:00 AM1
Boo 5/4/2012 01:30:00 PM1
LIAW 4/30/2012 11:48:52 PM 1
LIAW 5/1/2012 00:11:07 AM2
LIAW 5/1/2012 11:59:07 AM2
LIAW 5/1/2012 4:42:02 AM2
LIAW 5/2/2012 1:10:09 AM3
LIAW 5/2/2012 1:43:06 AM3
LIAW 5/4/2012 2:17:47 AM1
LIAW 5/5/2012 3:00:00 AM1
This is what i want:
PASS_MENTER_DT CONSECUTIVE_D FirstEntry
Boo 5/1/2012 11:55:00 PM1 5/1/2012 11:55:00 PM
Boo 5/2/2012 11:30:00 PM2 5/1/2012 11:55:00 PM
Boo 5/4/2012 10:30:00 AM1 5/4/2012 10:30:00 AM
Boo 5/4/2012 01:30:00 PM1 5/4/2012 10:30:00 AM
LIAW 4/30/2012 11:48:52 PM 1 4/30/2012 11:48:52 PM
LIAW 5/1/2012 00:11:07 AM2 4/30/2012 11:48:52 PM
LIAW 5/1/2012 11:59:07 AM2 4/30/2012 11:48:52 PM
LIAW 5/1/2012 4:42:02 AM2 4/30/2012 11:48:52 PM
LIAW 5/2/2012 1:10:09 AM3 4/30/2012 11:48:52 PM
LIAW 5/2/2012 1:43:06 AM3 4/30/2012 11:48:52 PM
LIAW 5/4/2012 2:17:47 AM1 5/4/2012 2:17:47 AM
LIAW 5/5/2012 3:00:00 AM1 5/4/2012 2:17:47 AM
An logic i have in mind is to:
((take the entry_dt minus away the number of consecutive days) + 1)to derive the date of the firstEntry, then from there i will derive the time by getting the earliest time with that same date. However this logic is flawed, as wont work for numerous same consecutive days.
Anybody know what i am talking about?
January 15, 2013 at 5:59 pm
Simply done using the code I provided you on this thread.
with TestData as (
select
PassM,
cast(EnterDt as datetime) EnterDt
from
(values
('Boo K K','5/1/2012 11:55:00 PM'),
('Boo K K','5/2/2012 11:30:00 PM'),
('Boo K K','5/4/2012 10:30:00 AM'),
('LIAW S','4/30/2012 11:48:52 PM'),
('LIAW S','5/1/2012 00:11:07 AM'),
('LIAW S','5/1/2012 11:59:07 AM'),
('LIAW S','5/1/2012 4:42:02 AM'),
('LIAW S','5/2/2012 1:10:09 AM'),
('LIAW S','5/2/2012 1:43:06 AM'),
('LIAW S','5/4/2012 2:17:47 AM')
)dt(PassM, EnterDt)
)
, GrpDates as (
select
PassM,
EnterDt,
GrpDate = dateadd(dd, dense_rank() over (partition by PassM order by dateadd(dd,datediff(dd,0,EnterDt),0)) * -1, dateadd(dd,datediff(dd,0,EnterDt),0))
from
TestData
)
select
PassM,
EnterDt,
ConsecutiveD = dense_rank() over (partition by PassM, GrpDate order by dateadd(dd,datediff(dd,0,EnterDt),0)),
FirstEntry = min(EnterDt) over (partition by PassM, GrpDate)
from
GrpDates
order by
PassM,
EnterDt;
January 15, 2013 at 6:09 pm
Modified slightly to put the test data into a table and use that table as input to the code:
create table #TestData ( -- create temp table for the test data
PassM varchar(32),
EnterDt datetime);
go
insert into #TestData
select
PassM,
cast(EnterDt as datetime) EnterDt
from
(values
('Boo K K','5/1/2012 11:55:00 PM'),
('Boo K K','5/2/2012 11:30:00 PM'),
('Boo K K','5/4/2012 10:30:00 AM'),
('LIAW S','4/30/2012 11:48:52 PM'),
('LIAW S','5/1/2012 00:11:07 AM'),
('LIAW S','5/1/2012 11:59:07 AM'),
('LIAW S','5/1/2012 4:42:02 AM'),
('LIAW S','5/2/2012 1:10:09 AM'),
('LIAW S','5/2/2012 1:43:06 AM'),
('LIAW S','5/4/2012 2:17:47 AM')
)dt(PassM, EnterDt)
go
with GrpDates as (
select
PassM,
EnterDt,
GrpDate = dateadd(dd, dense_rank() over (partition by PassM order by dateadd(dd,datediff(dd,0,EnterDt),0)) * -1, dateadd(dd,datediff(dd,0,EnterDt),0))
from
#TestData
)
select
PassM,
EnterDt,
ConsecutiveD = dense_rank() over (partition by PassM, GrpDate order by dateadd(dd,datediff(dd,0,EnterDt),0)),
FirstEntry = min(EnterDt) over (partition by PassM, GrpDate)
from
GrpDates
order by
PassM,
EnterDt;
go
drop table #TestData; -- cleanup after running code, drop the temp table
go
January 15, 2013 at 6:14 pm
Lynn, i am still trying. Mind explaining the difference between dense_rank() and row_number() if both using partion by?
January 15, 2013 at 6:30 pm
KUDOS to Lynn!!! Your solutions always worked and its very efficient. I am still understanding the code(digesting a heavy meal).
Thanks!!!:)
January 15, 2013 at 7:10 pm
Lynn i have been really trying very hard. Now i uds your solutions up to 75% i guess. However would u help me to uds your solution more by having comments or tell me your thought process? Please
Thanks, you have been a great help
January 15, 2013 at 8:15 pm
10e5x (1/15/2013)
Lynn, i am still trying. Mind explaining the difference between dense_rank() and row_number() if both using partion by?
First thing you need to do is to read about the ROW_NUMBER(), RANK(), DENSE_RANK() functions in Books Online. Work through the samples they provide, then move on to some of your own data.
Once you have a good understanding of these functions and what they are doing, we can work on understanding the slightly more complex aspects of them and how I used them.
Also, you should take the time to read this article, http://www.sqlservercentral.com/articles/T-SQL/71550/, as it discusses the main concepts behind how my code works as well.
January 15, 2013 at 8:37 pm
Thanks Lynn, i am reading up the articles.
January 16, 2013 at 12:39 am
Lynn Pettis (1/15/2013)
10e5x (1/15/2013)
Lynn, i am still trying. Mind explaining the difference between dense_rank() and row_number() if both using partion by?First thing you need to do is to read about the ROW_NUMBER(), RANK(), DENSE_RANK() functions in Books Online. Work through the samples they provide, then move on to some of your own data.
Once you have a good understanding of these functions and what they are doing, we can work on understanding the slightly more complex aspects of them and how I used them.
Also, you should take the time to read this article, http://www.sqlservercentral.com/articles/T-SQL/71550/, as it discusses the main concepts behind how my code works as well.
Hi lynn,
I have finish reading the article by Jeff, and a few more on dense rank(), rank() and row number() and uds that the main diff of dense rank is that is will return the same ranking if there are duplicate value based on the order by. I have uds how ur solution work but there is this part that i do not get it. The way u derive GRPDATE. at this part:
order by dateadd(dd,datediff(dd,0,EnterDt),0)) * -1
maybe its becuz there are too many combination of dateadd plus datediff that confused me, but i do not uds the use of * -1
If you dont mind, kindly clear my doubts when free, though i am dying to know. Thanks
January 16, 2013 at 1:58 am
Hi Lynn, i finally got it!!! what a smart use of * -1 to ensure consecutive dates and same dates fall to the same group date. THANKS:-D
January 24, 2013 at 11:59 pm
Hi lynn,
Not sure if u will see this. I deployed my system using your solution to the UAT. Everything works great. The solution was efficient. However after some round of testing, the user decided to add in new business requirement.
Lets say with the same sample data, just that on top of entry datetime, each record now has a additional of exit datetime. I already derived a new column DURATION by getting the datediff from these two fileds.
The user want to check if it is possible to add in another criteria in order for the records to be considered as a consecutive day. Meaning if the duration is more than 6 hours, and with no gap, then it will be consider consecutive. SO now it no longer only depends on the entry_dt itself
I am trying to draft out a table for better understanding.
Any help?
Thanks in advance
January 25, 2013 at 6:44 am
10e5x (1/24/2013)
Hi lynn,Not sure if u will see this. I deployed my system using your solution to the UAT. Everything works great. The solution was efficient. However after some round of testing, the user decided to add in new business requirement.
Lets say with the same sample data, just that on top of entry datetime, each record now has a additional of exit datetime. I already derived a new column DURATION by getting the datediff from these two fileds.
The user want to check if it is possible to add in another criteria in order for the records to be considered as a consecutive day. Meaning if the duration is more than 6 hours, and with no gap, then it will be consider consecutive. SO now it no longer only depends on the entry_dt itself
I am trying to draft out a table for better understanding.
Any help?
Thanks in advance
First, requirements always change. Second, based on what you have posted, nope, can't help. Third, we are volunteers on ssc. We don't get paid to help, we do it for free as a way to give back to the community.
The code you deployed is yours now and your responsibility to support. You are the one that needs to support it and modify it when needed. You need help with modifying it? You need to show me that you have made a good faith effort to make the appropriate changes and explain where you having problems implementing the change(s) required. This means posting DDL, sample data, expected results, what you have done so far to meet the new requirements, explaining what is and isn't working.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply