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
 Tags
Subject: Help report script result
Thread ID: 310963 Message ID: 310963 # Views: 57 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Monday, June 27, 2011 2:34:56 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



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



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