Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Jonel Villanueva
  Where is Jonel Villanueva?
 sta. rosa
 Philippines
 Jonel Villanueva
 To: Tamar Granor
  Where is Tamar Granor?
 Elkins Park
 Pennsylvania - United States
 Tamar Granor
 Tags
Subject: RE: Help report script result
Thread ID: 310963 Message ID: 311051 # Views: 36 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Tuesday, June 28, 2011 2:08:50 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



Hi Tamar,

Please disregards my previous post.
by the way, I couldnt figure out on how to make the script properly.
Im looking the nearest or closest date from shipdate to returndate.


Insert into TABLE1(ESN varchar(10), Optn_type int, Auditdate datetime)
Values ('123','2','1-20-2011') --this is shipdate
Values ('123','11','2-10-2011') --returndate
Values ('123','2','3-25-2011') --shipdate
Values ('123','2','4-30-2011') --shipdate
Values ('123','11','5-15-2011') --returndate

The result should be like this:
ESN|Shipdate|Returndate
-----------------------------------
123|1-20-2011|2-10-2011
123|3-25-2011
123|4-30-2011|5-15-2011

2=shipdate
11=returndate




Thank you in advance..

Jonel

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