How to return a quarter for every person

Question

Post reply

How to return a quarter for every person

Patrick Allmond

Mr or Mrs. 500

Points: 563
More actions
May 19, 2005 at 1:18 pm

#89010

If I have a person table and a fiscal quarter table (for rows for each year) is there a way to merge the two so I can get four rows returned for each employee? There is nothing to "JOIN" the two.
The eventual goal is to JOIN it with a third table of actvities and each activity will match up with an employeeid and quarter. We want to get ALL four quarters for ALL employees - even if there were no actvities for a quarter. In other words we want to report on quarters for employees that did nothing for the quarters as well as the multiple activities a person might have had in a quarter.
TIA.
Kindest Regards,
Patrick Allmond
batteryfuel.com
For all of your laptop, camera, power tool and portable battery needs.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You mean something like this?

Select O.name, dtQuarters.DateStart, dtQuarters.DateEnd from dbo.SysObjects O cross join

(

Select '2005/01/01' as DateStart, '2005/03/31' as DateEnd

union all

Select '2005/04/01' as DateStart, '2005/06/30' as DateEnd

union all

Select '2005/07/01' as DateStart, '2005/09/30' as DateEnd

union all

Select '2005/10/01' as DateStart, '2005/12/31' as DateEnd

) dtQuarters

Order by O.Name, dtQuarters.DateStart

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

If you already have a Quarter table, this may work as well.

CREATE TABLE #Person( [ID] integer,

[Name] varchar(20))

INSERT INTO #Person

SELECT 1, 'Joe Smith'

UNION ALL

SELECT 2, 'Jane Jones'

CREATE TABLE #Quarter( Qrtr smalldatetime)

INSERT INTO #Quarter

SELECT '01/01/2005'

UNION ALL

SELECT '04/01/2005'

UNION ALL

SELECT '07/01/2005'

UNION ALL

SELECT '10/01/2005'

SELECT [ID], Qrtr

FROM #Person, #Quarter

DROP TABLE #Person

DROP TABLE #Quarter

I wasn't born stupid - I had to study.

Patrick Allmond Mr or Mrs. 500 Points: 563 More actions · Answer 3

Absolutely incredible. Thank you very much. I had no idea what a cross join was or that it even existed. We are cheering in the streets here.

Kindest Regards,

Patrick Allmond
batteryfuel.com
For all of your laptop, camera, power tool and portable battery needs.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Actually what Farrell proposed is the same solution than mine with an older syntaxe, if you don't specify a join in a query with multiple tables, then a cross join is executed.

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

Correct. I was just working under the assumption they had a populated Quarter table...

I wasn't born stupid - I had to study.

Patrick Allmond Mr or Mrs. 500 Points: 563 More actions · Answer 6

Your assumption is correct. We felt we had to prepop a quarter table to get out what we wanted.

Kindest Regards,

Patrick Allmond
batteryfuel.com
For all of your laptop, camera, power tool and portable battery needs.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

You don't HAVE TO but it's better than doing a derived table like I did since it can be modified in the table instead of in the procs...