|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Beginning Balance QueryHello all,
I would like a total (beginning balance) field in a query be the total for the year up to the month based on another field within the query, so that when the fiscal_date = '5/1/2006' that I would have a field contain totals from '1/1/2006' thru '4/30/2006'. I would like to do this in one query, I am trying to populate a table with 6 years of data that would have a beginning balance field and then todays current amount based off of a Fiscal_Date field. My problem is I cannot figure out how to do a sum of prior data based off of another fields current date. Am I trying to do too much in one query to populate the table? Lyners wrote:
> Hello all, Without DDL, I don't know your actual table/field names, but something > I would like a total (beginning balance) field in a query be the total for > the year up to the month based on another field within the query, so that > when the fiscal_date = '5/1/2006' that I would have a field contain totals > from '1/1/2006' thru '4/30/2006'. I would like to do this in one query, I am > trying to populate a table with 6 years of data that would have a beginning > balance field and then todays current amount based off of a Fiscal_Date field. > > My problem is I cannot figure out how to do a sum of prior data based off of > another fields current date. Am I trying to do too much in one query to > populate the table? > like this should be close: SELECT Table2.FiscalDate, (SELECT SUM(Amount) FROM Table1 WHERE DATEPART(year, DateField) = DATEPART(year, Table2.FiscalDate) AND DATEPART(month, DateField) < DATEPART(month, Table2.FiscalDate)) AS FiscalAmount FROM Table2 Thank you Tracy,
Yes, I did something close to this. What I ended up doing was create 2 tablkes that were identical, and then used a cross join where one of my criteria is that a.date < b.date, then in the select statement I added a sum(a.amount). This way I sum all a.amount that is less than b.date. This way I have all of the b table fields and the total of the amount from the a table all together. Thank you, Lyners Show quote "Tracy McKibben" wrote: > Lyners wrote: > > Hello all, > > I would like a total (beginning balance) field in a query be the total for > > the year up to the month based on another field within the query, so that > > when the fiscal_date = '5/1/2006' that I would have a field contain totals > > from '1/1/2006' thru '4/30/2006'. I would like to do this in one query, I am > > trying to populate a table with 6 years of data that would have a beginning > > balance field and then todays current amount based off of a Fiscal_Date field. > > > > My problem is I cannot figure out how to do a sum of prior data based off of > > another fields current date. Am I trying to do too much in one query to > > populate the table? > > > > Without DDL, I don't know your actual table/field names, but something > like this should be close: > > SELECT > Table2.FiscalDate, > (SELECT SUM(Amount) FROM Table1 WHERE DATEPART(year, DateField) = > DATEPART(year, Table2.FiscalDate) AND DATEPART(month, DateField) < > DATEPART(month, Table2.FiscalDate)) AS FiscalAmount > FROM Table2 > > Why would you create a second identical table when a VIEW would most likely
have worked -without the issues of keeping 2 tables syncronized? -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "Lyners" <Lyn***@discussions.microsoft.com> wrote in message news:7FA08953-C425-43CD-A198-3CBC08EA229E@microsoft.com... > Thank you Tracy, > Yes, I did something close to this. What I ended up doing was create 2 > tablkes that were identical, and then used a cross join where one of my > criteria is that a.date < b.date, then in the select statement I added a > sum(a.amount). This way I sum all a.amount that is less than b.date. > > This way I have all of the b table fields and the total of the amount from > the a table all together. > > Thank you, > Lyners > > "Tracy McKibben" wrote: > >> Lyners wrote: >> > Hello all, >> > I would like a total (beginning balance) field in a query be the total >> > for >> > the year up to the month based on another field within the query, so >> > that >> > when the fiscal_date = '5/1/2006' that I would have a field contain >> > totals >> > from '1/1/2006' thru '4/30/2006'. I would like to do this in one query, >> > I am >> > trying to populate a table with 6 years of data that would have a >> > beginning >> > balance field and then todays current amount based off of a Fiscal_Date >> > field. >> > >> > My problem is I cannot figure out how to do a sum of prior data based >> > off of >> > another fields current date. Am I trying to do too much in one query to >> > populate the table? >> > >> >> Without DDL, I don't know your actual table/field names, but something >> like this should be close: >> >> SELECT >> Table2.FiscalDate, >> (SELECT SUM(Amount) FROM Table1 WHERE DATEPART(year, DateField) = >> DATEPART(year, Table2.FiscalDate) AND DATEPART(month, DateField) < >> DATEPART(month, Table2.FiscalDate)) AS FiscalAmount >> FROM Table2 >> >> Good point Arnie. I actually just created a large query that has 2 identical
select statements. The second select statement I total the amount field based on the cross join where clause. I am in the design phase of this report, so in the future, I will most likely create a view (faster) of the select query and just reference that instead of requerying everytime. Thanks for the performance pointer. Show quote "Arnie Rowland" wrote: > Why would you create a second identical table when a VIEW would most likely > have worked -without the issues of keeping 2 tables syncronized? > > -- > Arnie Rowland, YACE* > "To be successful, your heart must accompany your knowledge." > > *Yet Another Certification Exam > > > "Lyners" <Lyn***@discussions.microsoft.com> wrote in message > news:7FA08953-C425-43CD-A198-3CBC08EA229E@microsoft.com... > > Thank you Tracy, > > Yes, I did something close to this. What I ended up doing was create 2 > > tablkes that were identical, and then used a cross join where one of my > > criteria is that a.date < b.date, then in the select statement I added a > > sum(a.amount). This way I sum all a.amount that is less than b.date. > > > > This way I have all of the b table fields and the total of the amount from > > the a table all together. > > > > Thank you, > > Lyners > > > > "Tracy McKibben" wrote: > > > >> Lyners wrote: > >> > Hello all, > >> > I would like a total (beginning balance) field in a query be the total > >> > for > >> > the year up to the month based on another field within the query, so > >> > that > >> > when the fiscal_date = '5/1/2006' that I would have a field contain > >> > totals > >> > from '1/1/2006' thru '4/30/2006'. I would like to do this in one query, > >> > I am > >> > trying to populate a table with 6 years of data that would have a > >> > beginning > >> > balance field and then todays current amount based off of a Fiscal_Date > >> > field. > >> > > >> > My problem is I cannot figure out how to do a sum of prior data based > >> > off of > >> > another fields current date. Am I trying to do too much in one query to > >> > populate the table? > >> > > >> > >> Without DDL, I don't know your actual table/field names, but something > >> like this should be close: > >> > >> SELECT > >> Table2.FiscalDate, > >> (SELECT SUM(Amount) FROM Table1 WHERE DATEPART(year, DateField) = > >> DATEPART(year, Table2.FiscalDate) AND DATEPART(month, DateField) < > >> DATEPART(month, Table2.FiscalDate)) AS FiscalAmount > >> FROM Table2 > >> > >> > > >  |
|||||||||||||||||||||||
Other interesting topics