Hi Sergey,
This query is suppose to combine another table into a wide table. From my questions before about getting records from going down to going across:
id timefield field1 field2 field3 field4
1 01/01/00 15:45 hi 0 check field
1 01/01/00 7:48 bye 1 double main
2 01/01/00 14:51 hi 0 check field
2 01/01/00 8:48 bye 1 NULL main
3 NULL get 0 fake NULL
3 NULL get2 1 fake NULL
4 01/01/00 16:05 get1 1 fake NULL
need to get into this form:
id amtime amfield1 amfield2 amfield3 amfield4 am1time am1field1 am1field2 am1field3 am1field4 pmtime pmfield1 pmfield2 pmfield3 pmfield4 pm1time pm1field1 pm1field2 pm1field3 pm1field4
1 7:48AM hi 0 check field 3:45PM bye 1 double main
2 8:48AM hi 0 check field 2:51PM bye 1 main
3 get 0 fake get2 1 fake
4 4:05PM get1 1 fake
field2 is the TripAsgn_PickUp and id is the stutrip_stu_autoid. id identifies the records to group together, while field2 says if they are am or pm and timefield is the order within the am or pm.
Hope this makes more sense. I am still figuring this whole thing out.
Beth
>Hi Beth,
>
>Can you explain in plain english what query suppose to do and what column defins 'morning trips' or 'afternoon trips'.
>>
>>I have a Sql Select statement that I have been steadily working on. I have it doing mostly what I want, but now I need it to get the records needed. Here is the join string:
>>
>>FROM
>>(SELECT distinct * FROM #bptest where TripAsgn_PickUp = 1) tab1
>>LEFT JOIN
>>(SELECT distinct * FROM #bptest where TripAsgn_PickUp = 1) tab2
>> ON tab1.stutrip_stu_autoid = tab2.stutrip_stu_autoid AND tab2.RunSrv_TimeAtSrv > tab1.RunSrv_TimeAtSrv
>>LEFT JOIN
>>(SELECT distinct * FROM #bptest where TripAsgn_PickUp = 1) tab3
>> ON tab2.stutrip_stu_autoid = tab3.stutrip_stu_autoid AND tab3.RunSrv_TimeAtSrv > tab2.RunSrv_TimeAtSrv
>>LEFT JOIN
>>(SELECT distinct * FROM #bptest where TripAsgn_PickUp = 1) tab4
>> ON tab3.stutrip_stu_autoid = tab4.stutrip_stu_autoid AND tab4.RunSrv_TimeAtSrv > tab3.RunSrv_TimeAtSrv
>>LEFT JOIN
>>(SELECT distinct * FROM #bptest where TripAsgn_PickUp = 1) tab5
>> ON tab4.stutrip_stu_autoid = tab5.stutrip_stu_autoid AND tab5.RunSrv_TimeAtSrv > tab4.RunSrv_TimeAtSrv
>>LEFT JOIN
>>(SELECT distinct * FROM #bptest where TripAsgn_PickUp = 1) tab6
>> ON tab5.stutrip_stu_autoid = tab6.stutrip_stu_autoid AND tab6.RunSrv_TimeAtSrv > tab5.RunSrv_TimeAtSrv
>>LEFT Join
>>(SELECT distinct * FROM #bptest where TripAsgn_PickUp = 0) tab7 && This one is the problem
>> ON tab1.stutrip_stu_autoid = tab7.stutrip_stu_autoid AND tab7.RunSrv_TimeAtSrv > tab1.RunSrv_TimeAtSrv
>>LEFT JOIN
>>(SELECT distinct * FROM #bptest where TripAsgn_PickUp = 0) tab8
>> ON tab7.stutrip_stu_autoid = tab8.stutrip_stu_autoid AND tab8.RunSrv_TimeAtSrv > tab7.RunSrv_TimeAtSrv
>>LEFT JOIN
>>(SELECT distinct * FROM #bptest where TripAsgn_PickUp = 0) tab9
>> ON tab8.stutrip_stu_autoid = tab9.stutrip_stu_autoid AND tab8.RunSrv_TimeAtSrv > tab8.RunSrv_TimeAtSrv
>>LEFT Join
>>(SELECT distinct * FROM #bptest where TripAsgn_PickUp = 0) tab10
>> ON tab9.stutrip_stu_autoid = tab10.stutrip_stu_autoid AND tab10.RunSrv_TimeAtSrv > tab9.RunSrv_TimeAtSrv
>>LEFT JOIN
>>(SELECT distinct * FROM #bptest where TripAsgn_PickUp = 0) tab11
>> ON tab10.stutrip_stu_autoid = tab11.stutrip_stu_autoid AND tab11.RunSrv_TimeAtSrv > tab10.RunSrv_TimeAtSrv
>>LEFT JOIN
>>(SELECT distinct * FROM #bptest where TripAsgn_PickUp = 0) tab12
>> ON tab11.stutrip_stu_autoid = tab12.stutrip_stu_autoid AND tab12.RunSrv_TimeAtSrv > tab11.RunSrv_TimeAtSrv
>>
>>tab7 looks back at tab1, because this is the start of the afternoon trips. I can get the morning trips to show up, even though the runsrv_timeatsrv is null, and I need the afternoon trips to do the same. Any ideas on how I can do this?
>>
>>TIA
>>Beth
