Finding consecutive weeks in a year

[jeffshex]

I'm trying to see if there is a way to do this.

I have a table that contains the following fields:
ClientID, Year, WeekNumber, Hours1 and Hours2

Is there a way I can do a query where I can lookup by the Client ID to see if there are any consecutive week numbers in the same year if either of the hours fields contains more than 30?

I'm completely open to suggestions, table reconstruction, etc... This is still in the design phase, so I'm open to ideas!!!

-J

 

[PHV]

Something like this ?
SELECT A.ClientID, A.Year, A.WeekNumber, A.Hours1, A.Hours2, B.WeekNumber, B.Hours1, B.Hours2
FROM yourTable AS A INNER JOIN yourTable AS B ON A.ClientID = B.ClientID AND A.Year = B.Year
WHERE B.WeekNumber = A.WeekNumber+1
AND (A.Hours1>30 OR A.Hours2>30 OR B.Hours1>30 OR B.Hours2>30)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jeffshex]

Not bad, its a great start.

It seems that if I have 4 weeks, it only returns 3 rows.

Table A       Table B
WeekNum       Week Num
------------------------
12             13
13             14
14             15

It shows me what I need to know, but for dumping into a report, I would like to have them in seperate rows.
I don't know if I'm asking for a miracle.

Any ideas for tweaking this?

 

[PHV]

I would like to have them in seperate rows
Like this ?
SELECT A.ClientID, A.Year, A.WeekNumber, A.Hours1, A.Hours2
FROM yourTable AS A INNER JOIN yourTable AS B ON A.ClientID = B.ClientID AND A.Year = B.Year
WHERE B.WeekNumber = A.WeekNumber+1
AND (A.Hours1>30 OR A.Hours2>30 OR B.Hours1>30 OR B.Hours2>30)
UNION
SELECT B.ClientID, B.Year, B.WeekNumber, B.Hours1, B.Hours2
FROM yourTable AS A INNER JOIN yourTable AS B ON A.ClientID = B.ClientID AND A.Year = B.Year
WHERE B.WeekNumber = A.WeekNumber+1
AND (A.Hours1>30 OR A.Hours2>30 OR B.Hours1>30 OR B.Hours2>30)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jeffshex]

It get's closer each time.
It lists the consecutvie weeks, but it still shows the consecutive week with hours under 30, making that week ineligible for counting. I can do some tweaking and get it under control.

I'm just wondering if you could give me a brief explination on how that union query brings that stuff together.
I see the 2 temp tables, but I am just not able to completely wrap my mind as to what's going on there.

Thanks again.

 

[lespaul]

a UNION takes two queries with the same number of fields in the select statement and combines them into a single result set:

SELECT Field1, Field2, Field3, "" FROM Table1
UNION
SELECT "", FieldB, FieldC, FieldD FROM Table2

using "" you can create placeholders in order to ensure that there are the same number of fields in each SELECT.

If you use UNION then duplicates are removed, if you want to retain duplicate records use UNION ALL.

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[jeffshex]

Cool.

Is there any way to indicate the consecutive week number?
For example:

SSN    Year   WeekNum   Consecutive
-------------------------------------
###    2006   14        1
###    2006   15        2
###    2006   16        3
###    2006   25        1
###    2006   26        2

and so on...

Just need a good way of finding those at 4 weeks.

-Jeff

 

[PHV]

What have you so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jeffshex]

Ok...

I have the inital query that does the union. The hours criteria wasn't working for me so I removed that criteria.
I did a query based off of that union query that then only shows with >= 30 hours.
So I get the week number, ID, year, hours1, hours2.
Is there another sort of code or something that I can specifiy if A.week+1 = B.week then increment by 1.
That's where I'm not sure if that's something that a query can do or if I'm going to have to look into doing some module or something.

 

[PHV]

Without seeing your actual SQL code(s) I'm afraid I'll suggest nothing.

 

[jeffshex]

Ok
Here is part 1:

SELECT A.SSN, A.Year, A.WeekNumber, A.JobSearchHours, A.JobReadinessHours
FROM tblJSJRVerification AS A INNER JOIN  tblJSJRVerification AS B ON A.SSN = B.SSN AND A.Year = B.Year
WHERE B.WeekNumber = A.WeekNumber+1
UNION SELECT B.SSN, B.Year, B.WeekNumber, B.JobSearchHours, B.JobReadinessHours
FROM  tblJSJRVerification AS A INNER JOIN  tblJSJRVerification AS B ON A.SSN = B.SSN AND A.Year = B.Year
WHERE B.WeekNumber = A.WeekNumber+1;

Then part2:

SELECT JSJR_Part1.SSN, JSJR_Part1.Year, JSJR_Part1.WeekNumber, JSJR_Part1.JobSearchHours, JSJR_Part1.JobReadinessHours
FROM JSJR_Part1
WHERE (((JSJR_Part1.Year)=2006) AND ((JSJR_Part1.JobSearchHours)>20)) OR (((JSJR_Part1.Year)=2006) AND ((JSJR_Part1.JobReadinessHours)>20));

That's where I am at.

 

[PHV]

Does the below query correct the hours criteria issue ?

SELECT A.SSN, A.Year, A.WeekNumber, A.JobSearchHours, A.JobReadinessHours
FROM tblJSJRVerification AS A INNER JOIN  tblJSJRVerification AS B ON A.SSN = B.SSN AND A.Year = B.Year
WHERE B.WeekNumber = A.WeekNumber+1 AND A.Year = 2006
AND (A.JobSearchHours>20 OR A.JobReadinessHours>20)
UNION SELECT B.SSN, B.Year, B.WeekNumber, B.JobSearchHours, B.JobReadinessHours
FROM  tblJSJRVerification AS A INNER JOIN  tblJSJRVerification AS B ON A.SSN = B.SSN AND A.Year = B.Year
WHERE B.WeekNumber = A.WeekNumber+1 AND B.Year = 2006
AND (B.JobSearchHours>20 OR B.JobReadinessHours>20)

The goal is to have one query instead of two.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jeffshex]

Yes it is correct.