Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 To: Tariq Mehmood
  Where is Tariq Mehmood?
 BAHAWALPUR
 Pakistan
 Tariq Mehmood
 Tags
Subject: RE: Complicated Query
Thread ID: 271665 Message ID: 271688 # Views: 43 # Ratings: 4
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Friday, July 30, 2010 12:35:07 PM         
   


> Dear Experts
>
> I have following table with data
>
>
> Create Cursor my_fox(t_SNO N(2),teacher c(10),days C(3),p0 c(6),p1 c(6),p2 c(6),p3 c(6),p4 c(6),p5 c(6),p6 c(6),p7 c(6))
> 
> Insert Into my_fox Values(1,"Cetin","Mon","A","B","C","D","E","F","G","H")
> Insert Into my_fox Values(1,"Cetin","Tue","","B","","D","","F","","H")
> Insert Into my_fox Values(1,"Cetin","Wed","A","","","D","E","","G","")
> Insert Into my_fox Values(2,"Anders","Mon","","","","","","","","")
> Insert Into my_fox Values(2,"Anders","Tue","A","","C","","E","","G","")
> Insert Into my_fox Values(2,"Anders","Wed","","B","C","","","F","","H")
> 

>
> I need following result
>
>

Lazy coder's generator.
*!*	Local ix, jx,lcSQL
*!*	Set Textmerge To Memvar lcSQL Noshow On
*!*	For ix=1 To 7
*!*	  For jx=0 To 7
*!*	\   ,d<<m.ix>>.p<<m.jx>> as <<LEFT(CDOW(DATE()-DOW(DATE(),2)+m.ix),3)>>p<<m.jx>> ;
*!*	  Endfor
*!*	Endfor

*!*	\   FROM my_fox t1 ;
*!*	For ix=1 To 7
*!*	\   Left Join
*!*	\\ (SELECT * FROM my_Fox WHERE days=='<< PROPER(LEFT(CDOW(DATE()-DOW(DATE(),2)+m.ix),3)) >>')
*!*	\\ d<<m.ix>> ON t1.t_SNO = d<<m.ix>>.t_SNO ;
*!*	Endfor

*!*	Set Textmerge To
*!*	Set Textmerge Off
*!*	_Cliptext = m.lcSQL



Select Distinct ;
  t1.t_SNO ;
  ,t1.teacher ;
  ,d1.p0 As Monp0 ;
  ,d1.p1 As Monp1 ;
  ,d1.p2 As Monp2 ;
  ,d1.p3 As Monp3 ;
  ,d1.p4 As Monp4 ;
  ,d1.p5 As Monp5 ;
  ,d1.p6 As Monp6 ;
  ,d1.p7 As Monp7 ;
  ,d2.p0 As Tuep0 ;
  ,d2.p1 As Tuep1 ;
  ,d2.p2 As Tuep2 ;
  ,d2.p3 As Tuep3 ;
  ,d2.p4 As Tuep4 ;
  ,d2.p5 As Tuep5 ;
  ,d2.p6 As Tuep6 ;
  ,d2.p7 As Tuep7 ;
  ,d3.p0 As Wedp0 ;
  ,d3.p1 As Wedp1 ;
  ,d3.p2 As Wedp2 ;
  ,d3.p3 As Wedp3 ;
  ,d3.p4 As Wedp4 ;
  ,d3.p5 As Wedp5 ;
  ,d3.p6 As Wedp6 ;
  ,d3.p7 As Wedp7 ;
  ,d4.p0 As Thup0 ;
  ,d4.p1 As Thup1 ;
  ,d4.p2 As Thup2 ;
  ,d4.p3 As Thup3 ;
  ,d4.p4 As Thup4 ;
  ,d4.p5 As Thup5 ;
  ,d4.p6 As Thup6 ;
  ,d4.p7 As Thup7 ;
  ,d5.p0 As Frip0 ;
  ,d5.p1 As Frip1 ;
  ,d5.p2 As Frip2 ;
  ,d5.p3 As Frip3 ;
  ,d5.p4 As Frip4 ;
  ,d5.p5 As Frip5 ;
  ,d5.p6 As Frip6 ;
  ,d5.p7 As Frip7 ;
  ,d6.p0 As Satp0 ;
  ,d6.p1 As Satp1 ;
  ,d6.p2 As Satp2 ;
  ,d6.p3 As Satp3 ;
  ,d6.p4 As Satp4 ;
  ,d6.p5 As Satp5 ;
  ,d6.p6 As Satp6 ;
  ,d6.p7 As Satp7 ;
  ,d7.p0 As Sunp0 ;
  ,d7.p1 As Sunp1 ;
  ,d7.p2 As Sunp2 ;
  ,d7.p3 As Sunp3 ;
  ,d7.p4 As Sunp4 ;
  ,d7.p5 As Sunp5 ;
  ,d7.p6 As Sunp6 ;
  ,d7.p7 As Sunp7 ;
  FROM my_fox t1 ;
  Left Join (Select * From my_fox Where days=='Mon') d1 On t1.t_SNO = d1.t_SNO ;
  Left Join (Select * From my_fox Where days=='Tue') d2 On t1.t_SNO = d2.t_SNO ;
  Left Join (Select * From my_fox Where days=='Wed') d3 On t1.t_SNO = d3.t_SNO ;
  Left Join (Select * From my_fox Where days=='Thu') d4 On t1.t_SNO = d4.t_SNO ;
  Left Join (Select * From my_fox Where days=='Fri') d5 On t1.t_SNO = d5.t_SNO ;
  Left Join (Select * From my_fox Where days=='Sat') d6 On t1.t_SNO = d6.t_SNO ;
  Left Join (Select * From my_fox Where days=='Sun') d7 On t1.t_SNO = d7.t_SNO ;
  INTO Cursor badDesign

Browse



Cetin Basoz

.Net has got better.Think about moving - check my blog:
Blog (main)
Blog (mirror)

COMPLETE THREAD

Complicated Query Posted by Tariq mehmood @ 7/30/2010 9:27:37 AM
RE: Complicated Query Posted by Borislav Borissov @ 7/30/2010 9:57:06 AM
RE: Complicated Query Posted by Tariq mehmood @ 7/30/2010 11:59:58 AM
RE: Complicated Query Posted by Borislav Borissov @ 7/30/2010 12:11:46 PM
RE: Complicated Query Posted by Tariq mehmood @ 7/30/2010 12:18:53 PM
RE: Complicated Query Posted by Borislav Borissov @ 7/30/2010 12:20:33 PM
RE: Complicated Query Posted by Anders Altberg @ 7/30/2010 10:18:54 AM
RE: Complicated Query Posted by Vivek Deodhar @ 7/31/2010 8:03:24 AM
RE: Complicated Query Posted by Anders Altberg @ 7/31/2010 11:45:49 AM
RE: Complicated Query Posted by Cetin Basoz @ 7/30/2010 12:35:07 PM