Help With SQL Command

[MikeL91]

I have 2 files that are the result of a cursor from a select cnt of a state field. One is ran the 1st of the month, and the second is ran the 30th of the month. I want to generate a file from the 2 files and add 2 fields Difference, and Percent.

FileA.dbf (1st of the month)

State Qty
AZ 250
AK 190
CA 5,050
CO 1,200

FileB.dbf (30th of the month)

State Qty
AZ 275
AK 268
CA 6,285
CO 1,182


This is the result of a SQL select I am trying to create:

State Qty StateB QtyB Diff Percent
AZ 250 AZ 275 25 9.09%
AK 190 AK 268 78 29.10%
CA 5,050 CA 6,285 1,235 19.65%
CO 1,200 CO 1,182 -18 -1.52%

I welcome any and all tips, and thank you in advance,
Mike

 

[bborissov]

SELECT NVL(FileA.State,[Uknown]) AS State, NVL(FileA.Qty,00000.000) AS Qty;
       NCL(FileB.State, [Uknown]) AS StateB, NVL(FileB.Qty,00000.000) AS QtyB,;
NVL(FileB.Qty,00000.000)-NVL(FileA.Qty,00000.000) AS Diff,;
???? How you get these %
FROM FileB;
FULL JOIN FileA ON FileB.State = FileA.State
INTO CURSOR crsTest

not tested

Borislav Borissov

 

[MikeL91]

Thanks Borislav,
this is what worked from that:

SELECT FileA.State AS State,FileA.Qty AS Qty,;
FileB.State AS StateB, FileB.Qty AS QtyB,;
NVL(FileB.Qty,00000.000)-NVL(FileA.Qty,00000.000) AS Diff ;
FROM FileB ;
FULL JOIN FileA ON FileB.State = FileA.State ;
INTO CURSOR crsTest

I am going to try to use Excel & Automate it to format the percent.

Thanks again.
-Mike

 

[bborissov]

No problems with claculation, just don't know how you get it:
ie. you want this result
AZ 250 AZ 275 25 9.09%

BUT the percent must be:
(275/250)*100 - 100 = 10%, so I ask HOW you get these percents?

Borislav Borissov

 

[JimWWinter]

Probably (25/275)*100. But that's not the percentage change, your formula gives that!

Regards,
Jim

 

[MikeL91]

Jim is correct, I was using excel, so the file grew by 25 AZ records in row 1, and that is a 9.09% total increase 25/275 and then format for %. The SQL command you gave me gives me the 275-250 25 so I am going to use excel & automate it to do the % calc.


thanks again.

 

[JimWWinter]

Not quite sure what you're planning to do here, but the formula Borislav gave you gives you the correct rate of growth, 10%. Percent increase is always based on the starting value, in this case, 250.

Regards,
Jim