> >
> >
> > 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