Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Demanding and difficult logic qns PART 2. Sorry but i need help. Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2013 12:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, 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


  Post Attachments 
AccessTable part2.docx (9 views, 13.12 KB)
Post #1407051
Posted Tuesday, January 15, 2013 5:38 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, 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?
Post #1407524
Posted Tuesday, January 15, 2013 5:59 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 20,734, Visits: 32,500
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;





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)
Post #1407530
Posted Tuesday, January 15, 2013 6:09 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 20,734, Visits: 32,500
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





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)
Post #1407533
Posted Tuesday, January 15, 2013 6:14 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
Lynn, i am still trying. Mind explaining the difference between dense_rank() and row_number() if both using partion by?
Post #1407536
Posted Tuesday, January 15, 2013 6:30 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
KUDOS to Lynn!!! Your solutions always worked and its very efficient. I am still understanding the code(digesting a heavy meal).

Thanks!!!:)
Post #1407538
Posted Tuesday, January 15, 2013 7:10 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, 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
Post #1407544
Posted Tuesday, January 15, 2013 8:15 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 20,734, Visits: 32,500
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.



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)
Post #1407554
Posted Tuesday, January 15, 2013 8:37 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
Thanks Lynn, i am reading up the articles.
Post #1407556
Posted Wednesday, January 16, 2013 12:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, 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
Post #1407635
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse