December 13, 2012 at 2:37 am
10e5x (12/13/2012)
Hi Lynn,Firstly, i am sorry, Lynn.
Secondly, i am using SQL Server 2005
Lastly, that sentence is the entire error sentence. It appears when i click the ! functions in query builder.
Regards,
10e5x
That explains why I don't see the error message. I don't use query builder, I write the t-sql statements directly. Query Builder doesn't support all the capabilities of SQL Server, such as the windowing functions row_count, rank, dense_rank, and ntile. I haven't used SSMS for SQL Server 2012 yet, so I don't know if it does yet or not.
Try posting the code and running it directly in a query window in SSMS, you should see that it runs without any problems.
December 13, 2012 at 2:46 am
Hi Lynn,
Yup i tried, it doesn't. Thanks for all the help over here, i have successfully created all the statements needed for my requirement and my target table is derived. Now i shall move on to see if it is possible to do in the SSIS way, which will be out of topic for this forum.
Thanks.
Appreciated,
10e5x
December 13, 2012 at 3:11 am
10e5x (12/12/2012)
Hi Cadavre,Thank you v much for ur help. Btw may i tried and your method returned my all 1 as numberofdays.
Oh yes u are right, i should have uploaded a sample data. I will do it v soon. Need to mask a few fields.
Thanks,
10e5x
That's because you originally said "date" when talking about entry, not datetime.
Change my code like this: -
SELECT PassM, CAST(EnterDt AS DATETIME) EnterDt
INTO #yourSampleData
FROM (SELECT 'Boo K K','5/1/2012 11:55:00 PM'
UNION ALL SELECT 'Boo K K','5/2/2012 11:30:00 PM'
UNION ALL SELECT 'Boo K K','5/4/2012 10:30:00 AM'
UNION ALL SELECT 'LIAW S','4/30/2012 11:48:52 PM'
UNION ALL SELECT 'LIAW S','5/1/2012 00:11:07 AM'
UNION ALL SELECT 'LIAW S','5/1/2012 11:59:07 AM'
UNION ALL SELECT 'LIAW S','5/1/2012 4:42:02 AM'
UNION ALL SELECT 'LIAW S','5/2/2012 1:10:09 AM'
UNION ALL SELECT 'LIAW S','5/2/2012 1:43:06 AM'
UNION ALL SELECT 'LIAW S','5/4/2012 2:17:47 AM'
)a(PassM,EnterDt);
SELECT PassM,
MIN(EnterDt) AS firstDateEntrySequence, MAX(EnterDt) AS lastDateEntrySequence,
DATEDIFF(dd,MIN(EnterDt),MAX(EnterDt))+1 AS NumberOfDaysInSequence
FROM (SELECT PassM, EnterDt,
DATEADD(dd, - ROW_NUMBER() OVER (PARTITION BY PassM ORDER BY EnterDt), EnterDt)
FROM (SELECT PassM, DATEADD(dd, DATEDIFF(dd, 0, EnterDt), 0)
FROM #yourSampleData
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, EnterDt), 0), PassM
) a(PassM, EnterDt)
GROUP BY PassM, EnterDt
) a(PassM, EnterDt, EntryGroup)
GROUP BY PassM, EntryGroup;
And it returns: -
PassM firstDateEntrySequence lastDateEntrySequence NumberOfDaysInSequence
------- ----------------------- ----------------------- ----------------------
LIAW S 2012-04-30 00:00:00.000 2012-05-02 00:00:00.000 3
Boo K K 2012-05-01 00:00:00.000 2012-05-02 00:00:00.000 2
LIAW S 2012-05-04 00:00:00.000 2012-05-04 00:00:00.000 1
Boo K K 2012-05-04 00:00:00.000 2012-05-04 00:00:00.000 1
December 13, 2012 at 7:50 am
Made a few mods with my code. Inserted the sample data into a temp table, zeroed out the ConsecutiveD column, modified my code to use the temp table and changed it to an update. Looks like it works and updates the underlying temp table.
I will be honest, the best way to do what you want is with SQL. If you want to do this in SSIS, do it using an Execute SQL Task and have it run the SQL code for you there.
December 13, 2012 at 8:02 am
Hi Lynn,
Thanks for all the effort, modification was great. U mention Execute SQL task? I tried that 3 days ago but failed. Maybe thats becuz my statements are messy. I will try that again later. U might be the best person to ask, it will sounds very dumb but i would like to ask u. Can i just chunk my 8 sql statements in a task? And what should the task return me? i read multiple tutorials on this, they mention to use return full dataset, and store it in a variable object. But no matter how i configure i still unable to do that. Let me try again later.
Thanks, no words can express my gratitude
January 9, 2013 at 6:00 pm
I know this is an slightly old thread but i have some qns on
nigelrivett's solutions. I know there are better solutions offered, especially by Lynn, but i have already used Nigelrivett's solutions and passed it on. Currently i am doing documentation on my statements and i realized although i roughly had an idea whats Nigel proposing but i do not fully uds his solution. I tried pm him but to no avail.
May some kind souls explain to me the use of 'num' and 'gap'? and how 'dte', 'num' and 'gap' works tgt to derive the consecutive days.. his approach is rather unique. Shld have used lynn's solution;-)
This is Nigelrivett's solution:
;with cte as
(
select *, dte = dateadd(dd,datediff(dd,0,dt),0) from @t
) ,
cte2 as
(
select *, num = (select count(distinct t2.dte) from cte t2 where t2.dte<=t1.dte and t1.s=t2.s) ,
gap = case when exists (select * from cte t3 where t3.dte = t1.dte-1 and t1.s = t3.s) then 0 else 1 end
from cte t1
)
select * ,
val = num - (select max(num) from cte2 t2 where t2.dte<=t1.dte and t2.gap=1 and t1.s=t2.s)+1
from cte2 t1
Any explanation are appreciated. Hopefully short and straight to the point. Thanks
January 9, 2013 at 6:27 pm
10e5x (1/9/2013)
I know this is an slightly old thread but i have some qns onnigelrivett's solutions. I know there are better solutions offered, especially by Lynn, but i have already used Nigelrivett's solutions and passed it on. Currently i am doing documentation on my statements and i realized although i roughly had an idea whats Nigel proposing but i do not fully uds his solution. I tried pm him but to no avail.
May some kind souls explain to me the use of 'num' and 'gap'? and how 'dte', 'num' and 'gap' works tgt to derive the consecutive days.. his approach is rather unique. Shld have used lynn's solution;-)
This is Nigelrivett's solution:
;with cte as
(
select *, dte = dateadd(dd,datediff(dd,0,dt),0) from @t
) ,
cte2 as
(
select *, num = (select count(distinct t2.dte) from cte t2 where t2.dte<=t1.dte and t1.s=t2.s) ,
gap = case when exists (select * from cte t3 where t3.dte = t1.dte-1 and t1.s = t3.s) then 0 else 1 end
from cte t1
)
select * ,
val = num - (select max(num) from cte2 t2 where t2.dte<=t1.dte and t2.gap=1 and t1.s=t2.s)+1
from cte2 t1
Any explanation are appreciated. Hopefully short and straight to the point. Thanks
First, you should still be able to. To understand Nigelrivett's code, compare the output from mine to his, I think you will see what the intermediate values are telling you.
One thing, CTEs do not start with a ;, it is a terminator not a begininator. The correct syntax is to ensure the statement PRECEDING the CTE is terminated with a ;.
with TestData as (
select
PassM,
cast(EnterDt as datetime) EnterDt,
ConsecutiveD
from
(values
('Boo K K','5/1/2012 11:55:00 PM', 1),
('Boo K K','5/2/2012 11:30:00 PM', 2),
('Boo K K','5/4/2012 10:30:00 AM', 1),
('LIAW S','4/30/2012 11:48:52 PM', 1),
('LIAW S','5/1/2012 00:11:07 AM', 2),
('LIAW S','5/1/2012 11:59:07 AM', 2),
('LIAW S','5/1/2012 4:42:02 AM', 2),
('LIAW S','5/2/2012 1:10:09 AM', 3),
('LIAW S','5/2/2012 1:43:06 AM', 3),
('LIAW S','5/4/2012 2:17:47 AM', 1)
)dt(PassM, EnterDt, ConsecutiveD)
)
, 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)),
ConsecutiveD
from
TestData
)
select
PassM,
EnterDt,
ConsecutiveD,
dr = dense_rank() over (partition by PassM, GrpDate order by dateadd(dd,datediff(dd,0,EnterDt),0))
from
GrpDates
order by
PassM,
EnterDt;
go
declare @t table (s varchar(10), dt datetime);
--insert @t select 'A', '20120101';
--insert @t select 'A', '20120102';
--insert @t select 'A', '20120102 01:00';
--insert @t select 'A', '20120103';
--insert @t select 'A', '20120105';
--insert @t select 'A', '20120106';
--insert @t select 'A', '20120106';
--insert @t select 'B', '20120101';
--insert @t select 'B', '20120103';
--insert @t select 'B', '20120104';
insert into @t
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');
with cte as
(
select *, dte = dateadd(dd,datediff(dd,0,dt),0) from @t
) ,
cte2 as
(
select *, num = (select count(distinct t2.dte) from cte t2 where t2.dte<=t1.dte and t1.s=t2.s) ,
gap = case when exists (select * from cte t3 where t3.dte = t1.dte-1 and t1.s = t3.s) then 0 else 1 end
from cte t1
)
select * ,
val = num - (select max(num) from cte2 t2 where t2.dte<=t1.dte and t2.gap=1 and t1.s=t2.s)+1
from cte2 t1;
go
January 9, 2013 at 9:12 pm
tried your way but i still dont uds... Especially the co-relation between num and gap in order to derive Consecutive_D. Sorry to be so noob. If u dont mind, can explain in english? Just roughly will do. Else nvm, thanks
January 9, 2013 at 10:22 pm
10e5x (1/9/2013)
tried your way but i still dont uds... Especially the co-relation between num and gap in order to derive Consecutive_D. Sorry to be so noob. If u dont mind, can explain in english? Just roughly will do. Else nvm, thanks
sdtdtenumgapval
Boo K K2012-05-01 23:55:00.0002012-05-01 00:00:00.000111
Boo K K2012-05-02 23:30:00.0002012-05-02 00:00:00.000202
Boo K K2012-05-04 10:30:00.0002012-05-04 00:00:00.000311
LIAW S2012-04-30 23:48:52.0002012-04-30 00:00:00.000111
LIAW S2012-05-01 00:11:07.0002012-05-01 00:00:00.000202
LIAW S2012-05-01 11:59:07.0002012-05-01 00:00:00.000202
LIAW S2012-05-01 04:42:02.0002012-05-01 00:00:00.000202
LIAW S2012-05-02 01:10:09.0002012-05-02 00:00:00.000303
LIAW S2012-05-02 01:43:06.0002012-05-02 00:00:00.000303
LIAW S2012-05-04 02:17:47.0002012-05-04 00:00:00.000411
The dates in the column dt are the full date and time, the dates in the column dte have been stripped of the the time values.
The value of num is the count of distinct date values (no time) where the date value is less or equal to the full value the value of s (PassM) in cte(t1) is equal to s (PassM) in cte(t2).
The value of gap, zero indicates no gap in dates between current date and a day earlier for matching values of s (PassM) and one if there is a gap or no previous date for values of s (PassM).
The value of val is one (1) if the value of gap is 1 and the value of num if the value of gap is zero (0).
Does that help with the understanding of the code?
January 9, 2013 at 10:34 pm
THANKS I FINALLY UNDERSTOOD!!!:)
Appreciated, Lynn!
January 9, 2013 at 10:55 pm
Prefer my code, doesn't have any triangular joins involved which can kill a server as the amount of data increases.
January 9, 2013 at 11:35 pm
Lynn Pettis (1/9/2013)
Prefer my code, doesn't have any triangular joins involved which can kill a server as the amount of data increases.
oh no, really? Then i will have to change to yours when i get the chance to edit my code. However every time i see your solutions, its so difficult to uds. In addition, i do not know why your solution already has the value for CONSECUTIVE_D from the very start. Isnt the value what we are trying to derive over here? Actually performance and efficiencyis very important to me, thats why i prefer yours too, but i really cant catch any balls for your statements. At least nigel's i can uds half of it.
January 10, 2013 at 12:02 am
10e5x (1/9/2013)
Lynn Pettis (1/9/2013)
Prefer my code, doesn't have any triangular joins involved which can kill a server as the amount of data increases.oh no, really? Then i will have to change to yours when i get the chance to edit my code. However every time i see your solutions, its so difficult to uds. In addition, i do not know why your solution already has the value for CONSECUTIVE_D from the very start. Isnt the value what we are trying to derive over here? Actually performance and efficiencyis very important to me, thats why i prefer yours too, but i really cant catch any balls for your statements. At least nigel's i can uds half of it.
I included the value for CONSECUTIVE_D in the test code to give me something to validate against. It was the easiest way to do it. Take that column out of the test data and run it if you doubt me.
As for understanding my code, I will have to leave that to you as an exercise. Read books online about the functions used, create some test data and see how they work. As you do this, ask questions about what you don't understand so that we can provide you with guidance to help you learn and understand without having it handed to you.
As for me, I know I learn and remember better if I have to figure out how something works and why rather than having someone just tell me.
If the code I had provided you was to be used in a production system I was working on, I would have commented it so that someone else coming along would be able to see what it was I was doing. Here, we need to you truely understand, that comes from working with it, tinkering to find out the hows and whys and asking questions to clarify understanding. Remember, you are the one who will have to support the code, not us.
January 10, 2013 at 12:32 am
Ok! thanks for the great help. will start testing out without the Consecutive column, not that i doubt u but would like to see it myself. I will heed your advice. Thanks
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply