Group all related items in one row - DDL ATTACHED

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Group all related items in one row - DDL ATTACHED

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2010-02-10 : 18:17:28

I need to create a temp table in some fashion and i am wondering the best way to do it. So, here goes

I need to look in a certain table, like these two for instance

SELECT COUNT(*) FROM dbo.RegistrationQuestion WHERE RegistrationGuid IN (SELECT Guid from Registration WHERE RaceGuid='04010abc-83dd-4aaf-abb1-0177eac5d562')
SELECT COUNT(*) FROM RegistrationAdditionalFee WHERE RegistrationGuid IN (SELECT Guid from Registration WHERE RaceGuid='04010abc-83dd-4aaf-abb1-0177eac5d562')

When the Counts return >0 I need to create a temp table that has all the fields of both tables in one table. I know this seems ridiculous, but bear with me. Basically, I have about 5 or 6 tables like the above. If any table has rows in it, I need to create a new table that has all fields from any table that has a count greater than 0. Make sense? Is it even possible?

For clarity's sake, I am ultimately trying to do the following

I have the following SQL. I am trying to get the data to return on one row per a.Guid (AthleteID). I thought about trying to do this on the client, but its been a no go. So, I am back to trying to get it right in the SQL. Any direction would be great. Also, here are two links, the first is what I am getting for results and the second is what I would like

SELECT 
r.Guid as RegistrationID
,a.Guid as AthleteID
,a.Firstname as FName
,a.LastName as LName
,a.Gender as Sex
,a.Address1 as Addr
,a.City
,a.[State] as ST
,a.Zip
,a.MedicalConditions as Medical
,a.Email
,a.DayPhone
,a.EveningPhone
,re.Guid
,re.USATNumber
,re.TShirtSize
,re.Division as Div
, CASE r.PaperRegistration
	WHEN 1 THEN 'Paper'
	ELSE
		CASE r.PayByCheck
			WHEN 1 THEN 'Check'
			ELSE 'CC'
			END
	END as Paytype
,e.Name as [Event]
,TotalFee
,r.OnlineFee
,IsNull(rd.TotalDiscount,0.00) as TotalDiscount
,r.Date 
,ra.Name
,ra.Qty
,ra.Fee
,ra.Total
,rrl.Name
,rrl.LegName
,rrl.MedicalConditions
,rrl.DateOfBirth
,rrl.DateOfBirth
,rq.QuestionText
,rq.QuestionResponse
FROM Registration r
INNER JOIN RegistrationEvent re ON re.RegistrationGuid = r.Guid
INNER JOIN [Event] e ON e.Guid = re.EventGuid
INNER JOIN Athlete a ON a.AccountGuid = r.AccountGuid AND a.Guid = re.AthleteGuid
LEFT JOIN RegistrationDiscount rd ON rd.RegistrationGuid = r.Guid
LEFT JOIN RegistrationRelayLeg rrl ON rrl.AthleteGuid = a.Guid
LEFT JOIN RegistrationQuestion rq ON rq.AthleteGuid = a.Guid
LEFT JOIN RegistrationAdditionalFee ra ON ra.RegistrationGuid = r.Guid
WHERE TotalFee >0 
and r.RaceGuid='04010abc-83dd-4aaf-abb1-0177eac5d562'
ORDER BY r.Guid, a.Guid, a.Lastname

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 00:30:50

try the below


SELECT RegistrationID
,AthleteID
,FName
,LName
,Sex
,Addr
,City
,ST
,Zip
,Medical
,Email
,DayPhone
,EveningPhone
,Guid
,.USATNumber
,TShirtSize
,Division as Div
,Paytype
,[Event]
,TotalFee
,OnlineFee
,TotalDiscount
,Date 
,Name
,Qty
,Fee
,Total
,Name1
,LegName
,MedicalConditions
,DateOfBirth
,DateOfBirth
,QuestionText
,QuestionResponse
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY r.Guid ORDER BY rrl.DateOfBirth DESC) AS rn,
r.Guid as RegistrationID
,a.Guid as AthleteID
,a.Firstname as FName
,a.LastName as LName
,a.Gender as Sex
,a.Address1 as Addr
,a.City
,a.[State] as ST
,a.Zip
,a.MedicalConditions as Medical
,a.Email
,a.DayPhone
,a.EveningPhone
,re.Guid
,re.USATNumber
,re.TShirtSize
,re.Division as Div
, CASE r.PaperRegistration
	WHEN 1 THEN 'Paper'
	ELSE
		CASE r.PayByCheck
			WHEN 1 THEN 'Check'
			ELSE 'CC'
			END
	END as Paytype
,e.Name as [Event]
,TotalFee
,r.OnlineFee
,IsNull(rd.TotalDiscount,0.00) as TotalDiscount
,r.Date 
,ra.Name as Name1
,ra.Qty
,ra.Fee
,ra.Total
,rrl.Name
,rrl.LegName
,rrl.MedicalConditions
,rrl.DateOfBirth
,rrl.DateOfBirth
,rq.QuestionText
,rq.QuestionResponse
FROM Registration r
INNER JOIN RegistrationEvent re ON re.RegistrationGuid = r.Guid
INNER JOIN [Event] e ON e.Guid = re.EventGuid
INNER JOIN Athlete a ON a.AccountGuid = r.AccountGuid AND a.Guid = re.AthleteGuid
LEFT JOIN RegistrationDiscount rd ON rd.RegistrationGuid = r.Guid
LEFT JOIN RegistrationRelayLeg rrl ON rrl.AthleteGuid = a.Guid
LEFT JOIN RegistrationQuestion rq ON rq.AthleteGuid = a.Guid
LEFT JOIN RegistrationAdditionalFee ra ON ra.RegistrationGuid = r.Guid
WHERE TotalFee >0 
and r.RaceGuid='04010abc-83dd-4aaf-abb1-0177eac5d562'
)t
WHERE rn=1
ORDER BY RegistrationID, AthleteID, LName

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-11 : 06:45:01

FULL OUTER JOIN ??

Kristen
Test

22859 Posts

Posted - 2010-02-11 : 06:48:51

e.g.


SELECT *
FROM Registration r
    FULL OUTER JOIN RegistrationDiscount AS rd ON rd.RegistrationGuid = r.Guid
    FULL JOIN RegistrationRelayLeg rrl ON rrl.AthleteGuid = a.Guid
    FULL JOIN RegistrationQuestion rq ON rq.AthleteGuid = a.Guid
    FULL JOIN RegistrationAdditionalFee ra ON ra.RegistrationGuid = r.Guid
WHERE TotalFee >0 
      and r.RaceGuid='04010abc-83dd-4aaf-abb1-0177eac5d562'

you can COALESCE columns from different tables to get a single column output - e.g. where a row may existing in TableA, TableB or both, but if in both you only want the value from TableA:

COALESCE(TableA.ColName1, TableB.ColName2) AS NewColName

Harry C
Posting Yak Master

148 Posts

Posted - 2010-02-11 : 09:28:54

Thank you to both of you for the replies. Kristen, the method you suggested did not give me any output that was discernibly different than what I had before.

visakh16, your method gave me almost exactly what I needed. With some minor mods I was able to get it to work. I really appreciate both of you taking the time to answer this as I have been attempting to do this for a long time to no avail. Very much appreciated. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 09:33:36

quote:
Originally posted by Harry C

Thank you to both of you for the replies. Kristen, the method you suggested did not give me any output that was discernibly different than what I had before.

visakh16, your method gave me almost exactly what I needed. With some minor mods I was able to get it to work. I really appreciate both of you taking the time to answer this as I have been attempting to do this for a long time to no avail. Very much appreciated. Thanks!

Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-11 : 09:35:18

"Kristen, the method you suggested did not give me any output that was discernibly different than what I had before.

"Yeah, thinking about it a bit more that will indeed be the case.

FULL OUTER JOIN will give you rows from the other tables if Registration does NOT have a row for r.RaceGuid='04010abc-83dd-4aaf-abb1-0177eac5d562' - which (as I understand it) is not possible in this case.

Sorry about that, I'll go and get a stronger cup of coffee!

Harry C
Posting Yak Master

148 Posts

Posted - 2010-02-11 : 11:35:33

Ok, I have one minor problem.

Here is the SQL I ended up with. The issue is that for each of those Left Join tables, there could be (and usually is) more than one row.

So I could have one athleteID and they could have many rows in the RegistrationRelayLeg or RegistrationQuestion (or any other left join table), but the problem is that this query is only giving me the first hit on that row. Is there anyway I could modify this so that it would give me back all the rows (and still only in one row?) Thanks

SELECT RegistrationID
,AthleteID
,FName
,LName
,Sex
,Addr
,City
,ST
,Zip
,Medical
,Email
,DayPhone
,EveningPhone
,USATNumber
,TShirtSize
,Div
,Paytype
,[Event]
,RaceDate
,TotalFee
,OnlineFee
,TotalDiscount
,FeeName
,Qty
,Fee
,Total
,LegName
,RelayName
,MedicalConditions
,DateOfBirth
--,DateOfBirth
,QuestionText
,QuestionResponse
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY a.Guid ORDER BY a.DateOfBirth DESC) AS rn,
r.Guid as RegistrationID
,a.Guid as AthleteID
,a.Firstname as FName
,a.LastName as LName
,a.Gender as Sex
,a.Address1 as Addr
,a.City
,a.[State] as ST
,a.Zip
,a.MedicalConditions as Medical
,a.Email
,a.DayPhone
,a.EveningPhone
,re.USATNumber
,re.TShirtSize
,re.Division as Div
, CASE r.PaperRegistration
	WHEN 1 THEN 'Paper'
	ELSE
		CASE r.PayByCheck
			WHEN 1 THEN 'Check'
			ELSE 'CC'
			END
	END as Paytype
,e.Name as [Event]
,r.Date as RaceDate
,TotalFee
,r.OnlineFee
,IsNull(rd.TotalDiscount,0.00) as TotalDiscount
,ra.Name as FeeName
,ra.Qty
,ra.Fee
,ra.Total
,rrl.LegName
,rrl.Name as RelayName
,rrl.MedicalConditions
,rrl.DateOfBirth
,rq.QuestionText
,rq.QuestionResponse
FROM Registration r
INNER JOIN RegistrationEvent re ON re.RegistrationGuid = r.Guid
INNER JOIN [Event] e ON e.Guid = re.EventGuid
INNER JOIN Athlete a ON a.AccountGuid = r.AccountGuid AND a.Guid = re.AthleteGuid
LEFT JOIN RegistrationDiscount rd ON rd.RegistrationGuid = r.Guid
LEFT JOIN RegistrationRelayLeg rrl ON rrl.AthleteGuid = a.Guid
LEFT JOIN RegistrationQuestion rq ON rq.AthleteGuid = a.Guid
LEFT JOIN RegistrationAdditionalFee ra ON ra.RegistrationGuid = r.Guid
WHERE TotalFee >0 
and r.RaceGuid='04010abc-83dd-4aaf-abb1-0177eac5d562'
)t
WHERE rn=1
ORDER BY RegistrationID, AthleteID, LName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 11:46:56

what do you mean all the rows (and still only in one row?)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Harry C
Posting Yak Master

148 Posts

Posted - 2010-02-11 : 11:54:31

what I mean is there is one AthleteID per row, which is great. But in the actual data for RegistrationRelayLeg that althlete may have three rows, but the query is only pulling back to the first row. I need all three and still in one row for that AthleteID. Make any sense?

So, this query returns one RelayLegName Per AthleteID, but there are three in my DB. If I just search on the results in RegistrationRelayLeg for that Athlete, I get three results..

AthleteGuid LegName Name DateOfBirth AcceptWaiver
BEC2A987-20FD-4279-BD75-528686D0AB7C Cyclist Cyclist 1945-04-04 1
BEC2A987-20FD-4279-BD75-528686D0AB7C Swimmer Swimmer 1987-05-03 1
BEC2A987-20FD-4279-BD75-528686D0AB7C Runner Runner 1984-01-01 1

SELECT RegistrationID, AthleteID
,FName
,LName
,LegName
,RelayName
,MedicalConditions
,DateOfBirth
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY a.Guid ORDER BY a.DateOfBirth DESC) AS rn,
r.Guid as RegistrationID
,a.Guid as AthleteID
,a.Firstname as FName
,a.LastName as LName
,rrl.LegName
,rrl.Name as RelayName
,rrl.MedicalConditions
,rrl.DateOfBirth
FROM Registration r
INNER JOIN RegistrationEvent re ON re.RegistrationGuid = r.Guid
INNER JOIN [Event] e ON e.Guid = re.EventGuid
INNER JOIN Athlete a ON a.AccountGuid = r.AccountGuid AND a.Guid = re.AthleteGuid
LEFT JOIN RegistrationRelayLeg rrl ON rrl.AthleteGuid = a.Guid
WHERE TotalFee >0 
and r.RaceGuid='04010abc-83dd-4aaf-abb1-0177eac5d562'
and a.Guid='BEC2A987-20FD-4279-BD75-528686D0AB7C'
)t
WHERE rn=1
ORDER BY RegistrationID, AthleteID, LName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 12:07:52

ok. then use 3rd scenario in link below

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Harry C
Posting Yak Master

148 Posts

Posted - 2010-02-11 : 12:17:00

quote:
Originally posted by visakh16

ok. then use 3rd scenario in link below

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Would that give me the RelayLegs as a Comma List all in one field? I can't return the results that way. I need each one returned in a separate field. And its not just the RelayLeg table, its all the Left Joined tables. I have to think this through....

Harry C
Posting Yak Master

148 Posts

Posted - 2010-02-11 : 20:06:30

Here is a DDL if anyone wants to take a look. Thanks.

I don't know the number rows that could ultimately be returned for each LEFT JOIN. In practice, it should be no more than 10 per Left JOIN though. For the reason, I must go with the ubiquitous answer of "That is what the client wants"

They want to see in the HTML (a rad grid) and export it into Excel and CSV as well. At that point I figured its better to do this in the SQL rather than on the client.

So, as requested, here is a script that will populate the tables and let you see the real output. The results you will get are all from ONE registration. You should get five rows returned in the SQL. Three rows are for the related rows in RegistrationRelayleg and two rows are from related rows in RegistrationQiestion.

More or less, I want all that to appear in one row, but I obviously dont want to repeat items like Athlete.FirstName, Athlete.LastName, etc...Only the differing rows from the LEFT JOIN tables should be repeated.

The image is a basic mockup of what the output should be (its not complete, but you get the idea). Thanks for taking a look


CREATE TABLE [dbo].[#Registration](
        [Guid] [uniqueidentifier] NOT NULL,
        [AccountGuid] [uniqueidentifier] NOT NULL,
        [RaceGuid] [uniqueidentifier] NOT NULL,
        [Date] [datetime] NULL,
        [TotalFee] [money] NOT NULL,
        [WaiverInitials] [nvarchar](50) NULL,
        [OnlineFee] [money] NOT NULL,
        [PayByCheck] [bit] NOT NULL,
        [IsPaid] [bit] NOT NULL,
        [PaperRegistration] [bit] NOT NULL
        )
        
        INSERT INTO #Registration 
        SELECT '7ef8b580-5166-4ae2-b1fb-447d15f50bc5',  '914a0a9e-0b3c-46e3-ad96-8920717d8081', '04010abc-83dd-4aaf-abb1-0177eac5d562'
        ,'2010-02-10 10:59:46.490', 2047.9950,  'HC',   0.0000, 0,      1,      0
        
        CREATE TABLE [dbo].[#Event](
        [Guid] [uniqueidentifier] NOT NULL,
        [RaceGuid] [uniqueidentifier] NOT NULL,
        [Name] [nvarchar](200) NOT NULL,
        [Fee] [money] NOT NULL,
        [IsRelay] [bit] NOT NULL,
        [MaxEntrants] [int] NOT NULL,
        [FromDate] [date] NULL,
        [ToDate] [date] NULL,
        [DisplayOrder] [int] NOT NULL,
        [Deleted] [bit] NOT NULL,
        [Disabled] [bit] NOT NULL
        )
        
        INSERT INTO #Event
        SELECT 'ba5dcb0c-b726-4f76-bdc0-1bffe6ca6697',  '04010abc-83dd-4aaf-abb1-0177eac5d562', 'Test Relay Event',     125.0000,       1,      4,      NULL,   NULL,   2,      0,      0
        UNION ALL
        SELECT '75843b81-662e-4b1f-b73e-2e3dd8be184e',  '04010abc-83dd-4aaf-abb1-0177eac5d562', 'Triathlon Boot Camp',  575.0000,       0,      100     ,NULL,  NULL,   0,      0,      0
        
        CREATE TABLE [dbo].[#Athlete](
        [Guid] [uniqueidentifier] NOT NULL,
        [AccountGuid] [uniqueidentifier] NOT NULL,
        [Firstname] [nvarchar](50) NOT NULL,
        [Middlename] [nvarchar](50) NULL,
        [Lastname] [nvarchar](50) NOT NULL,
        [Email] [nvarchar](max) NOT NULL,
        [DayPhone] [nvarchar](50) NOT NULL,
        [EveningPhone] [nvarchar](50) NOT NULL,
        [MobilePhone] [nvarchar](50) NULL,
        [Address1] [nvarchar](200) NOT NULL,
        [Address2] [nvarchar](200) NULL,
        [Address3] [nvarchar](200) NULL,
        [City] [nvarchar](200) NULL,
        [State] [nvarchar](2) NOT NULL,
        [Zip] [nvarchar](15) NOT NULL,
        [Gender] [nvarchar](50) NULL,
        [DateOfBirth] [date] NULL,
        [IsAccountOwner] [bit] NOT NULL,
        [MedicalConditions] [varchar](500) NULL,
        [Country] [nvarchar](150) NULL
        )       
        
        INSERT INTO #Athlete
        SELECT 'bec2a987-20fd-4279-bd75-528686d0ab7c'
        ,       '914a0a9e-0b3c-46e3-ad96-8920717d8081'
        ,       'jeremy'
        , ''
        ,               'T'
        ,       'test@lin-mark.com'
        ,       '856-555-0010'
        ,       '856-555-0010'
        ,       '856-555-0010'  
        ,               '123 test'
        ,       '123 Test'
                ,       '123 Test'
        ,               'Mantua'
        ,       'NJ'
        ,       '09980'
        ,       'M'
        ,       '1982-01-26'
        ,       0
        , 'none'
        ,               'United States of America'
                

CREATE TABLE [dbo].[#RegistrationEvent](
        [Guid] [uniqueidentifier] NOT NULL,
        [RegistrationGuid] [uniqueidentifier] NOT NULL,
        [RaceGuid] [uniqueidentifier] NOT NULL,
        [EventGuid] [uniqueidentifier] NULL,
        [AthleteGuid] [uniqueidentifier] NOT NULL,
        [TShirtSize] [nvarchar](100) NULL,
        [Division] [nvarchar](100) NULL,
        [IsSelected] [bit] NOT NULL,
        [RaceDetailsCompleted] [bit] NOT NULL,
        [USATNumber] [nvarchar](9) NULL,
        [Paid] [bit] NOT NULL   
        )
        
        INSERT INTO #RegistrationEvent
        SELECT '87c9a8c3-9cd9-4341-8795-b114a26df8f0',  '7ef8b580-5166-4ae2-b1fb-447d15f50bc5', '04010abc-83dd-4aaf-abb1-0177eac5d562', 'ba5dcb0c-b726-4f76-bdc0-1bffe6ca6697'  ,'bec2a987-20fd-4279-bd75-528686d0ab7c',        'small',        '5K Competitve Walk Age Grouper',       1,      1,      NULL,   0

CREATE TABLE [dbo].[#RegistrationRelayLeg](
        [Guid] [uniqueidentifier] NOT NULL,
        [RegistrationGuid] [uniqueidentifier] NOT NULL,
        [RegistrationEventGuid] [uniqueidentifier] NOT NULL,
        [RelayLegGuid] [uniqueidentifier] NOT NULL,
        [AthleteGuid] [uniqueidentifier] NOT NULL,
        [LegName] [nvarchar](200) NOT NULL,
        [Name] [nvarchar](200) NOT NULL,
        [DateOfBirth] [date] NOT NULL,
        [USATNumber] [nvarchar](50) NULL,
        [AcceptWaiver] [bit] NOT NULL,
        [EmergencyContactName] [nvarchar](200) NOT NULL,
        [EmergencyContactNumber] [nvarchar](50) NOT NULL,
        [EmailAddress] [nvarchar](200) NULL,
        [TshirtSize] [nvarchar](50) NULL,
        [MedicalConditions] [nvarchar](500) NULL
        )       
        
        INSERT INTO #RegistrationRelayLeg
SELECT '82d030d8-ec2f-4992-92ff-5349b5ffa67d',  '7ef8b580-5166-4ae2-b1fb-447d15f50bc5', '876d80b9-0399-4854-92c2-da0a359dd995', 'bb30f1f6-c358-45c5-849f-0b311c189f0d', 'bec2a987-20fd-4279-bd75-528686d0ab7c', 'Cyclist',      'Cyclist',      '1945-04-04',   NULL,   1,      'test', 'test', 'test', NULL,   'test'
UNION ALL
SELECT '36a9e472-4fac-456e-ba93-944c9e52d8cb',  '7ef8b580-5166-4ae2-b1fb-447d15f50bc5', '876d80b9-0399-4854-92c2-da0a359dd995', 'a5ddb0ce-b4ed-49e6-954b-a9013cbf9258', 'bec2a987-20fd-4279-bd75-528686d0ab7c', 'Swimmer',      'Swimmer',      '1987-05-03',   NULL,   1,      'none', '555-555-5555', 'hcwork@verizon.net',   NULL,   'none'
UNION ALL
SELECT '6cc8b51f-299a-44bb-949e-e9a88142abe7',  '7ef8b580-5166-4ae2-b1fb-447d15f50bc5', '876d80b9-0399-4854-92c2-da0a359dd995', 'b6652d7c-c883-4531-bb0d-f4d8a4793da6', 'bec2a987-20fd-4279-bd75-528686d0ab7c', 'Runner',       'Runner',       '1984-01-01',   NULL,   1,      'none', 'none', 'none', NULL,   'none'  
        
        
CREATE TABLE [dbo].[#RegistrationQuestion](
        [Guid] [uniqueidentifier] NOT NULL,
        [RaceGuid] [uniqueidentifier] NOT NULL,
        [AthleteGuid] [uniqueidentifier] NOT NULL,
        [RegistrationGuid] [uniqueidentifier] NOT NULL,
        [QuestionGuid] [uniqueidentifier] NOT NULL,
        [QuestionText] [nvarchar](300) NOT NULL,
        [QuestionType] [nvarchar](50) NOT NULL,
        [QuestionResponse] [nvarchar](1000) NULL,
        [DisplayOrder] [int] NOT NULL
        )       

INSERT INTO #RegistrationQuestion
SELECT 'e41befbc-b4d3-4a65-962d-de81cd5a89fd',  '04010abc-83dd-4aaf-abb1-0177eac5d562', 'bec2a987-20fd-4279-bd75-528686d0ab7c', '7ef8b580-5166-4ae2-b1fb-447d15f50bc5', '479bd03e-1478-4c73-a52c-a6aebbed7809', 'Do You Like Tris',     'True/False',   'yes',  1
UNION ALL
SELECT '003a7f2b-2aea-44a8-aa6e-f10c474cc029',  'c22618ef-d2df-4cb2-9263-df97d0ebb0c2', 'bec2a987-20fd-4279-bd75-528686d0ab7c', '7ef8b580-5166-4ae2-b1fb-447d15f50bc5', 'c5bae87f-ea7c-4438-a88e-f988575c00b2,',        'If You Are Part Of A Team Then Enter Your Team Name (Type NA if not applicable)',      'Fill in',      'ee',   3


SELECT ROW_NUMBER() OVER(PARTITION BY re.AthleteGuid ORDER BY a.DateOfBirth DESC) AS rn,
r.Guid as RegistrationID
,a.Guid as AthleteID
,a.Firstname as FName
,a.LastName as LName
,a.Gender as Sex
,a.Address1 as Addr
,a.City
,a.[State] as ST
,a.Zip
,a.MedicalConditions as Medical
,a.Email
,a.DayPhone
,a.EveningPhone
,re.USATNumber
,re.TShirtSize
,re.Division as Div
, CASE r.PaperRegistration
        WHEN 1 THEN 'Paper'
        ELSE
                CASE r.PayByCheck
                        WHEN 1 THEN 'Check'
                        ELSE 'CC'
                        END
        END as Paytype
,e.Name as [Event]
,r.Date as RaceDate
,TotalFee
,r.OnlineFee
--,IsNull(rd.TotalDiscount,0.00) as TotalDiscount
--,ra.Name as FeeName
--,ra.Qty
--,ra.Fee
--,ra.Total
,rrl.LegName
,rrl.Name as RelayName
,rrl.MedicalConditions
,rrl.DateOfBirth
,rq.QuestionText
,rq.QuestionResponse
FROM #Registration r
INNER JOIN #RegistrationEvent re ON re.RegistrationGuid = r.Guid
INNER JOIN [#Event] e ON e.Guid = re.EventGuid
INNER JOIN #Athlete a ON a.AccountGuid = r.AccountGuid AND a.Guid = re.AthleteGuid
--LEFT JOIN RegistrationDiscount rd ON rd.RegistrationGuid = r.Guid
LEFT JOIN RegistrationRelayLeg rrl ON rrl.AthleteGuid = a.Guid
LEFT JOIN RegistrationQuestion rq ON rq.AthleteGuid = a.Guid
--LEFT JOIN RegistrationAdditionalFee ra ON ra.RegistrationGuid = r.Guid
WHERE TotalFee >0 
and r.RaceGuid='04010abc-83dd-4aaf-abb1-0177eac5d562'
        
        DROP TABLE #Registration
        DROP TABLE #Event
        DROP TABLE #Athlete
        DROP TABLE #RegistrationEvent
        DROP TABLE #RegistrationRelayLeg
        DROP TABLE #RegistrationQuestion

Harry C
Posting Yak Master

148 Posts

Posted - 2010-02-17 : 16:48:08

Anyone want to give this a crack? I would appreciate it. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources