InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 15:25:59 GMT20http://www.sqlservercentral.com/Forums/Topic1407051-148-1.aspx[quote][b]10e5x (1/24/2013)[/b][hr]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 itselfI am trying to draft out a table for better understanding.Any help?Thanks in advance[/quote]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.Fri, 25 Jan 2013 06:44:36 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1407051-148-1.aspxHi 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 itselfI am trying to draft out a table for better understanding.Any help?Thanks in advanceThu, 24 Jan 2013 23:59:37 GMT10e5xhttp://www.sqlservercentral.com/Forums/Topic1407051-148-1.aspxHi 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:-DWed, 16 Jan 2013 01:58:25 GMT10e5xhttp://www.sqlservercentral.com/Forums/Topic1407051-148-1.aspx[quote][b]Lynn Pettis (1/15/2013)[/b][hr][quote][b]10e5x (1/15/2013)[/b][hr]Lynn, i am still trying. Mind explaining the difference between dense_rank() and row_number() if both using partion by?[/quote]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, [b][url]http://www.sqlservercentral.com/articles/T-SQL/71550/[/url][/b], as it discusses the main concepts behind how my code works as well.[/quote]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:[quote]order by dateadd(dd,datediff(dd,0,EnterDt),0)) * -1[/quote]maybe its becuz there are too many combination of dateadd plus datediff that confused me, but i do not uds the use of * -1If you dont mind, kindly clear my doubts when free, though i am dying to know. ThanksWed, 16 Jan 2013 00:39:27 GMT10e5xhttp://www.sqlservercentral.com/Forums/Topic1407051-148-1.aspxThanks Lynn, i am reading up the articles.Tue, 15 Jan 2013 20:37:57 GMT10e5xhttp://www.sqlservercentral.com/Forums/Topic1407051-148-1.aspx[quote][b]10e5x (1/15/2013)[/b][hr]Lynn, i am still trying. Mind explaining the difference between dense_rank() and row_number() if both using partion by?[/quote]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, [b][url]http://www.sqlservercentral.com/articles/T-SQL/71550/[/url][/b], as it discusses the main concepts behind how my code works as well.Tue, 15 Jan 2013 20:15:52 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1407051-148-1.aspxLynn 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? PleaseThanks, you have been a great helpTue, 15 Jan 2013 19:10:25 GMT10e5xhttp://www.sqlservercentral.com/Forums/Topic1407051-148-1.aspxKUDOS to Lynn!!! Your solutions always worked and its very efficient. I am still understanding the code(digesting a heavy meal).Thanks!!!:)Tue, 15 Jan 2013 18:30:24 GMT10e5xhttp://www.sqlservercentral.com/Forums/Topic1407051-148-1.aspxLynn, i am still trying. Mind explaining the difference between dense_rank() and row_number() if both using partion by?Tue, 15 Jan 2013 18:14:35 GMT10e5xhttp://www.sqlservercentral.com/Forums/Topic1407051-148-1.aspxModified slightly to put the test data into a table and use that table as input to the code:[code="sql"]create table #TestData ( -- create temp table for the test data PassM varchar(32), EnterDt datetime);goinsert into #TestDataselect PassM, cast(EnterDt as datetime) EnterDtfrom(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)gowith 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 GrpDatesorder by PassM, EnterDt;godrop table #TestData; -- cleanup after running code, drop the temp tablego[/code]Tue, 15 Jan 2013 18:09:18 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1407051-148-1.aspxSimply done using the code I provided you on this [url=http://www.sqlservercentral.com/Forums/Topic1395412-149-1.aspx][b]thread[/b][/url].[code="sql"]with TestData as (select PassM, cast(EnterDt as datetime) EnterDtfrom(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 GrpDatesorder by PassM, EnterDt;[/code]Tue, 15 Jan 2013 17:59:45 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1407051-148-1.aspxThis 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:[quote]PASS_M ENTER_DT CONSECUTIVE_DBoo 5/1/2012 11:55:00 PM 1Boo 5/2/2012 11:30:00 PM 2Boo 5/4/2012 10:30:00 AM 1Boo 5/4/2012 01:30:00 PM 1LIAW 4/30/2012 11:48:52 PM 1LIAW 5/1/2012 00:11:07 AM 2LIAW 5/1/2012 11:59:07 AM 2LIAW 5/1/2012 4:42:02 AM 2LIAW 5/2/2012 1:10:09 AM 3LIAW 5/2/2012 1:43:06 AM 3LIAW 5/4/2012 2:17:47 AM 1LIAW 5/5/2012 3:00:00 AM 1[/quote]This is what i want:[quote]PASS_M ENTER_DT CONSECUTIVE_D FirstEntryBoo 5/1/2012 11:55:00 PM 1 5/1/2012 11:55:00 PMBoo 5/2/2012 11:30:00 PM 2 5/1/2012 11:55:00 PMBoo 5/4/2012 10:30:00 AM 1 5/4/2012 10:30:00 AMBoo 5/4/2012 01:30:00 PM 1 5/4/2012 10:30:00 AMLIAW 4/30/2012 11:48:52 PM 1 4/30/2012 11:48:52 PMLIAW 5/1/2012 00:11:07 AM 2 4/30/2012 11:48:52 PMLIAW 5/1/2012 11:59:07 AM 2 4/30/2012 11:48:52 PMLIAW 5/1/2012 4:42:02 AM 2 4/30/2012 11:48:52 PMLIAW 5/2/2012 1:10:09 AM 3 4/30/2012 11:48:52 PMLIAW 5/2/2012 1:43:06 AM 3 4/30/2012 11:48:52 PMLIAW 5/4/2012 2:17:47 AM 1 5/4/2012 2:17:47 AMLIAW 5/5/2012 3:00:00 AM 1 5/4/2012 2:17:47 AM[/quote]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?Tue, 15 Jan 2013 17:38:53 GMT10e5xhttp://www.sqlservercentral.com/Forums/Topic1407051-148-1.aspxHi 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.ThanksTue, 15 Jan 2013 00:32:21 GMT10e5x