Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: chris preston
  Where is chris preston?
 Kingston
 Jamaica
 chris preston
 Tags
Subject: RE: join 2 varables in IIF statement
Thread ID: 373376 Message ID: 373475 # Views: 38 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Friday, March 22, 2013 11:51:44 AM         
   


> >
> >
> > CREATE CURSOR Dates  (ddate Date) 
> > FOR i = 2011 TO 2013
> >   FOR j = 1 TO 12 
> >   INSERT INTO Dates VALUES ( DATE(m.i, m.j, 1)) 
> >   NEXT
> > NEXT
> >  SELECT itemkey, '_'+TRANSFORM(YEAR(Dates.ddate))+'-'+PADL(MONTH(Dates.ddate),2,'0'), SUM(qtyshipped)
> >   FROM Tmp ;
> >    RIGHT JOIN Dates ON YEAR(Dates.ddate)=YEAR(Tmp.documentdate) AND MONTH(Dates.ddate)=MONTH( Tmp.documentdate) ;
> >  GROUP BY 1,2 ORDER BY 2 DESC INTO CURSOR Q1 
> >  DO (_Genxtab) WITH 'Q2'
> > 

> > * here you can join Q2 with Itm on itemkey and add Itemdescription , into a cursor Salesdata.
> >
> > -Anders
>
> Hell Anders
> I am getting an " error building sort keys" i made a change to the year(dates) = year(tmp) but i still go the error


Try again with
 SELECT Tmp.itemkey, '_'+TRANSFORM(YEAR(Tmp.documentdate)+'-'+PADL(MONTH(Tmp.documentdate),2,'0') From Tmp ...


If that gets too complex I have a third way. Include the expression
'_'+TRANSFORM(YEAR(Dates.ddate))+'-'+PADL(MONTH(Dates.ddate),2,'0') as column in Dates and refer to that column in the query Q1.
ALTER TABLE Dates Add yearmonth C(10) 
UPDATE Dates SET yearmonth = '_'+TRANSFORM(YEAR(Dates.ddate))+'-'+PADL(MONTH(Dates.ddate),2,'0')

-Anders

ENTIRE THREAD

join 2 varables in IIF statement Posted by chris preston @ 3/21/2013 7:36:06 PM
RE: join 2 varables in IIF statement Posted by Anders Altberg @ 3/21/2013 8:35:54 PM
RE: join 2 varables in IIF statement Posted by chris preston @ 3/21/2013 11:19:08 PM
RE: join 2 varables in IIF statement Posted by Anders Altberg @ 3/22/2013 11:51:44 AM
RE: join 2 varables in IIF statement Posted by chris preston @ 3/22/2013 4:05:22 PM
RE: join 2 varables in IIF statement Posted by Cetin Basoz @ 3/22/2013 2:10:51 PM