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: 373380 # Views: 61 # Ratings: 1
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Thursday, March 21, 2013 8:35:54 PM         
   


> Hello All
> I am getting this error
>
> Error: Command contains unrecognized phrase/keyword
>
> The program below if to show for the last 5 years by month the quantity sold for each stock item
>
> they want to see in a spreadsheet format the following layout
>
> ItemKey, ItemDescription, Jan_13, Feb_13 etc for 2013 then coniuing Jan_12,feb_12,mar_12 etc etc
> It will end up as a spreadsheet with 5 * 12 columns +3 columns
> I wanted to use in the IFF line "Jan" + y which is the last 2 char of the year
>
> Don't know how else I can make the lines different. Each loop i will add to a cursor that will store year after year
>
>
>
> cdte= YEAR(DATE())
> sdte= cdte - 5
> stdte = '01/01/'+ALLTRIM(STR(sdte))
> dte = CTOD(stdte)
> etdte = '31/12/'+ALLTRIM(STR(YEAR(DATE())))
> dte1= CTOD(etdte)
>
> SQLEXEC(oConnection1,"select * from arlinh where documentdate >= ?dte and documentdate <= ?dte1","tmp")
> SQLEXEC(oConnection1,"select itemkey ,itemdescription1 from inmast","itm")
> x=sdte
> y= RIGHT(ALLTRIM(STR(x)),2)
> DO WHILE x <= YEAR(DATE())
>
>
> SELECT itm.itemkey,itm.itemdescription1,x as yr, ;
> SUM(IIF (month(documentdate) == 1, qtyshipped, 0)) as "Jan_"+y, ;
> SUM(IIF (month(documentdate) == 2, qtyshipped, 0)) as "Feb_"+y, ;
> SUM(IIF (month(documentdate) == 3, qtyshipped, 0)) as "Mar_"+y, ;
> SUM(IIF (month(documentdate) == 4, qtyshipped, 0)) as "Apr_"+y, ;
> SUM(IIF (month(documentdate) == 5, qtyshipped, 0)) as "May_"+y, ;
> SUM(IIF (month(documentdate) == 6, qtyshipped, 0)) as "Jun_"+y, ;
> SUM(IIF (month(documentdate) == 7, qtyshipped, 0)) as "Jul_"+y, ;
> SUM(IIF (month(documentdate) == 8, qtyshipped, 0)) as "Aug_"+y, ;
> SUM(IIF (month(documentdate) == 9, qtyshipped, 0)) as "Sep_"+y, ;
> SUM(IIF (month(documentdate) == 10, qtyshipped, 0)) as "Oct_+y, ;
> SUM(IIF (month(documentdate) == 11, qtyshipped, 0)) as "Nov_"+y, ;
> SUM(IIF (month(documentdate) == 12, qtyshipped, 0)) as "Dec_"+y ;
> FROM itm ;
> LEFT JOIN ;
> (SELECT itemkey, documentdate, qtyshipped FROM tmp) AS A ;
> ON itm.itemkey = A.itemkey WHERE YEAR(documentdate) = x ;
> GROUP BY 1 ,2,3 INTO CURSOR SalesData

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

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