SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Demanding and difficult logic qns PART 2. Sorry but i need help.


Demanding and difficult logic qns PART 2. Sorry but i need help.

Author
Message
10e5x
10e5x
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 180
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
Attachments
AccessTable part2.docx (25 views, 13.00 KB)
10e5x
10e5x
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 180
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_M ENTER_DT CONSECUTIVE_D
Boo 5/1/2012 11:55:00 PM 1
Boo 5/2/2012 11:30:00 PM 2
Boo 5/4/2012 10:30:00 AM 1
Boo 5/4/2012 01:30:00 PM 1
LIAW 4/30/2012 11:48:52 PM 1
LIAW 5/1/2012 00:11:07 AM 2
LIAW 5/1/2012 11:59:07 AM 2
LIAW 5/1/2012 4:42:02 AM 2
LIAW 5/2/2012 1:10:09 AM 3
LIAW 5/2/2012 1:43:06 AM 3
LIAW 5/4/2012 2:17:47 AM 1
LIAW 5/5/2012 3:00:00 AM 1


This is what i want:

PASS_M ENTER_DT CONSECUTIVE_D FirstEntry
Boo 5/1/2012 11:55:00 PM 1 5/1/2012 11:55:00 PM
Boo 5/2/2012 11:30:00 PM 2 5/1/2012 11:55:00 PM
Boo 5/4/2012 10:30:00 AM 1 5/4/2012 10:30:00 AM
Boo 5/4/2012 01:30:00 PM 1 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 AM 2 4/30/2012 11:48:52 PM
LIAW 5/1/2012 11:59:07 AM 2 4/30/2012 11:48:52 PM
LIAW 5/1/2012 4:42:02 AM 2 4/30/2012 11:48:52 PM
LIAW 5/2/2012 1:10:09 AM 3 4/30/2012 11:48:52 PM
LIAW 5/2/2012 1:43:06 AM 3 4/30/2012 11:48:52 PM
LIAW 5/4/2012 2:17:47 AM 1 5/4/2012 2:17:47 AM
LIAW 5/5/2012 3:00:00 AM 1 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?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96395 Visits: 38981
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;




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96395 Visits: 38981
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




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
10e5x
10e5x
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 180
Lynn, i am still trying. Mind explaining the difference between dense_rank() and row_number() if both using partion by?
10e5x
10e5x
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 180
KUDOS to Lynn!!! Your solutions always worked and its very efficient. I am still understanding the code(digesting a heavy meal).

Thanks!!!Smile
10e5x
10e5x
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 180
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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96395 Visits: 38981
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
10e5x
10e5x
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 180
Thanks Lynn, i am reading up the articles.
10e5x
10e5x
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 180
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search