Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Tamar Granor
  Where is Tamar Granor?
 Elkins Park
 Pennsylvania - United States
 Tamar Granor
 To: Jonel Villanueva
  Where is Jonel Villanueva?
 sta. rosa
 Philippines
 Jonel Villanueva
 Tags
Subject: RE: Help report script result
Thread ID: 310963 Message ID: 310986 # Views: 51 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Monday, June 27, 2011 10:18:42 PM         
   


> Hi All,
>
> Guys, need your help to obtain this records using script. im using sql server 2005
> given the example table and output.
>
> 1.
>
> pull out based on ESN from Fmaster and shipdate
> 
> INSERT INTO FMASTER ( ESN nvarchar(25), rtype int)
> VALUES ('123',12-1-2010, 2)
> 
> INSERT INTO FRECORDS ( ESN nvarchar(25), Rtype int, operationtype int, auditDate datimetime)
> VALUES ('123','null',2,'1-12-2011')  
> VALUES ('123',3,11,'2-15-2011')  
> VALUES ('123','null',2,'4-12-2011')  
> VALlUES ('123',3,11,'5-20-2011')  
> 
> THE RESULT SHOULD BE LIKE THIS:
> 
> ESN|Rtype|shipdate|ReturnDate
> _________________________________
> 123|3|1-12-2011|2-15-2011
> 123|3|4-12-2011|5-20-2011
> 
> Rtype
> 2 = Shipdate
> 3 = Returndate
> 


Your sample doesn't match the description. Does RType indicate ship vs. return, as you say, or is RType actually the reason for the return, and OperationType indicates ship (2) vs. return (11)?

Also, how do you link different records in FRecords to each other to indicate that a particular ship date matches a particular return date?


> 2.
>
> 
> for the no. 2 it should be summarize based on shipdate and modeltype
> then count based on shipdate and null value.
> 
> INSERT INTO FSAMPLE ( ESN nvarchar(25), modeltype nvarchar(10), Rtype int, shipDate datimetime, returndate datetime)
> VALUES ('123',XV00,2,'1-12-2011','2-15-2011')  
> VALUES ('124',XV00,2,'1-12-2011','2-15-2011')  
> VALUES ('125',XV00,2,'1-25-2011','NULL')  
> VALUES ('126',XV00,2,1-20-2011','2-31-2011')
> 
> RESULT WILL BE LIKE THIS: 
> 
> shipmonth|modeltype|QtyShipped|ReturnFail|%Fail
> _________________________________________
> January|XV00|3|1|0.33
> of  
> 1/1/2011
> 
> 


Try something like:

SELECT Month(ShipDate) AS ShipMonth, Year(ShipDate) AS ShipYear, ;
       ModelType, ;
       COUNT(ESN) AS QtyShipped, COUNT(ReturnDate) AS ReturnFail ;
   FROM FSample ;
   GROUP BY 1, 2, 3


Then, you can calculate the average on the returned data. I've included the year here because, unless you're going to filter based on the dates, so you only have data for one year or less, you need it.

Tamar

ENTIRE THREAD

Help report script result Posted by Jonel Villanueva @ 6/27/2011 2:34:56 PM
RE: Help report script result Posted by Tamar Granor @ 6/27/2011 10:18:42 PM
RE: Help report script result Posted by Jonel Villanueva @ 6/28/2011 12:07:31 AM
RE: Help report script result Posted by Jonel Villanueva @ 6/28/2011 2:08:50 PM
RE: Help report script result Posted by Tamar Granor @ 6/28/2011 10:29:10 PM
RE: Help report script result Posted by Jonel Villanueva @ 7/3/2011 1:35:05 PM