Converting Year to Date Data to Month to Date

[ram]

I have the following problem:

I havea production table of all my agents the production comes in as a
download each month but it is in Year to date totals. I would like to know
how I change the data so that it is showing month to date production.

i have changed the data to cross tab with the date as the column headings.
The I created a select query and subtracted Jan from Feb to get the Feb
production.

I need to get this data in a normalized table formate with the Production
date in one column and the monthly production in a second column.

Thanks in advance for any help

 

[Allen Browne]

Use a subquery to calculate the previous production amonnt, and subtract it
from the total production amount.

This example ignore the years, but gives the idea:

SELECT TheMonth,
Production - (SELECT Sum(Production) AS SumOfProduction
FROM MyImportTable AS Dupe
WHERE MyImportTable.TheMonth < Dupe.TheMonth)
AS MonthAmount
FROM MyImportTable;

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

 

[viktor chuzhakin]

Use a subquery to calculate the previous production amonnt, and subtract
it from the total production amount.

This example ignore the years, but gives the idea:

SELECT TheMonth,
Production - (SELECT Sum(Production) AS SumOfProduction
FROM MyImportTable AS Dupe
WHERE MyImportTable.TheMonth < Dupe.TheMonth)
AS MonthAmount
FROM MyImportTable;

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

 

[ram]

Hi All,

I tried following the suggestions from the prior post but haven't been
succesfull
I would like help with a subquery that figures out the monthly hours.

I have the following



Table1

Employee Date1 Year to Date Hours

Bill 1/1/08 10

Bill 2/1/08 20

Bill 3/1/08 30

Bill 4/1/08 40

Bill 5/1/08 50

Bill 6/1/08 60





The code I'm currently using is below adds the preceding hours instead of
subtracts.





SELECT TABLE1.Employee, TABLE1.Date1, TABLE1.YTD, (SELECT Sum(YTD) AS SumOfYTD
FROM TABLE1 AS Dupe
WHERE TABLE1.Date1 >=Dupe.Date1 AND Table1.Employee = Dupe.Employee )
AS MTD
FROM TABLE1;


The output from the code above is shown below
TableMTD Employee Date1 YTD MTD
Bill 1/1/2008 10 10
Bill 2/1/2008 20 30
Bill 3/1/2008 30 60
Bill 4/1/2008 40 100
Bill 5/1/2008 50 150
Bill 6/1/2008 60 210


Thanks for any help

 

[John W. Vinson]

The code I'm currently using is below adds the preceding hours instead of
subtracts.

Well... so subtract it.

SELECT TABLE1.Employee, TABLE1.Date1, TABLE1.YTD, [Date1] - (SELECT Sum(YTD)
AS SumOfYTD
FROM TABLE1 AS Dupe
WHERE TABLE1.Date1 < Dupe.Date1 AND Table1.Employee = Dupe.Employee )
AS MTD
FROM TABLE1;


This assumes that there is only one year's data in the table; you may need an
additional criterion:

SELECT TABLE1.Employee, TABLE1.Date1, TABLE1.YTD, [Date1] - (SELECT Sum(YTD)
AS SumOfYTD
FROM TABLE1 AS Dupe
WHERE TABLE1.Date1 < Dupe.Date1 AND Table1.Employee = Dupe.Employee )

AS MTD
WHERE Date1 > DateSerial(Year(Date()), 1, 1)
FROM TABLE1;

 

[ram]

Hello I tried your suggestion howere I still have been successfull. The end
result should be 10 for each date in the MTD Field. Thanks for any help on
showing me what I'm doing wrong.


SELECT TABLE1.Employee, TABLE1.Date1, TABLE1.YTD, [YTD]- (SELECT Sum(YTD)
AS SumOfYTD
FROM TABLE1 AS Dupe
WHERE TABLE1.Date1 > Dupe.Date1 AND Table1.Employee = Dupe.Employee )

AS MTD
From Table1
WHERE [Date1] > DateSerial(Year(Date()), 1, 1);

The code I'm currently using is below adds the preceding hours instead of
subtracts.

Well... so subtract it.

SELECT TABLE1.Employee, TABLE1.Date1, TABLE1.YTD, [Date1] - (SELECT Sum(YTD)
AS SumOfYTD
FROM TABLE1 AS Dupe
WHERE TABLE1.Date1 < Dupe.Date1 AND Table1.Employee = Dupe.Employee )
AS MTD
FROM TABLE1;


This assumes that there is only one year's data in the table; you may need an
additional criterion:

SELECT TABLE1.Employee, TABLE1.Date1, TABLE1.YTD, [Date1] - (SELECT Sum(YTD)
AS SumOfYTD
FROM TABLE1 AS Dupe
WHERE TABLE1.Date1 < Dupe.Date1 AND Table1.Employee = Dupe.Employee )

AS MTD
WHERE Date1 > DateSerial(Year(Date()), 1, 1)
FROM TABLE1;