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 ««123»»

Need help on creating SQL statement for deriving Consecutive worked days. Expand / Collapse
Author
Message
Posted Wednesday, December 12, 2012 10:49 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
Hi Lyn,
Thanks for ur reply i will test that soon. Currently i just do not know how to implement my 8 SQL statements in SSIS. TROUBLED Just by using this will be able to help me derive my target table but my supervisore hopes to see a neat process using SSIS.

I did it by, using SQL statement 1 will create T1, then i use SQL statment to query from t1 to derive T2, then drop T1........to SQL8 to create TARGET_TABLE, drop T7. I know i have used a very noob way. How can i translate all these and use it in SSIS?
Post #1395989
Posted Thursday, December 13, 2012 1:19 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 4:34 PM
Points: 20,676, Visits: 32,269
First, you didn't ask for an SSIS solution, you asked for a SQL solution.
Second, use the right tool for the job. This isn't a job for SSIS, it is a job for SQL.
Third, I have another SQL solution:


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;



When you run the code above, compare the ConsecutiveD column (the expected results) to the dr column (the computed Consecutive Days column).



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 #1396041
Posted Thursday, December 13, 2012 1:46 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 lyn,
I tested your solutions and it works. However they keep prompt me "the OVER SQL constructs or statements is not supported". I just ignored that. Do u have any idea whats that?
And ya i am sry, i did not asked for an SSIS solution and i do know this is an SQL job. However i am asked to see if i can do this in SSIS, which i am researching for it now. So asking for ur opinion.

Anyway thanks for replying.

Thanks,
10e5x

P.S I joined 3 forums to learn on SQL server and this forum is the best among all. Fastest reply rate with accuracy. THANKS!!!
Post #1396051
Posted Thursday, December 13, 2012 2:25 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 4:34 PM
Points: 20,676, Visits: 32,269
10e5x (12/13/2012)
Hi lyn,
I tested your solutions and it works. However they keep prompt me "the OVER SQL constructs or statements is not supported". I just ignored that. Do u have any idea whats that?
And ya i am sry, i did not asked for an SSIS solution and i do know this is an SQL job. However i am asked to see if i can do this in SSIS, which i am researching for it now. So asking for ur opinion.

Anyway thanks for replying.

Thanks,
10e5x

P.S I joined 3 forums to learn on SQL server and this forum is the best among all. Fastest reply rate with accuracy. THANKS!!!


First, look with eye, try spelling my name correctly, it is spelled with 2 n's not one.

Second, what version of SQL Server are you running?

Third, what is the full error message you are getting? The dense_rank function has been available since SQL Server 2005 and should work without giving you any error messages.



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 #1396059
Posted Thursday, December 13, 2012 2:30 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 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
Post #1396061
Posted Thursday, December 13, 2012 2:37 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 4:34 PM
Points: 20,676, Visits: 32,269
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.



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 #1396064
Posted Thursday, December 13, 2012 2:46 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 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
Post #1396072
Posted Thursday, December 13, 2012 3:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:56 AM
Points: 2,372, Visits: 7,560
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



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1396083
Posted Thursday, December 13, 2012 7:50 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 4:34 PM
Points: 20,676, Visits: 32,269
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.



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 #1396196
Posted Thursday, December 13, 2012 8:02 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 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
Post #1396207
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse