Need a good idea and help to execute it :)

[Bokazoit]

Each quarter of a year I enter new data into my database. Among the data is 'Year' and 'Quarter'. In my reports I only need the last four quarters. So now I need these quarters:

Q204 - Q104 - Q403 - Q303

When September is finished I get new quarterly data, and therefor my reports should only include:

Q304 - Q204 - Q104 - Q403

I have no real good idea to solve this puzzle but assume it can be handlede some way in a query or?

Anyway, any help is really appreciated

Inadvance tx

 

[Bokazoit]

I have rewritten my question a bit. Perhaps this gives a better overview

I succeded in making my database (with help from you in here ) but I have a small issue that you might be able to help me with.

I have a query that looks like this:

SELECT TOP 250 test.ButiksID, test.Butiksnavn, Sum(test.Q2) AS [Total Q2], Sum(test.Q1) AS [Total Q1], Sum(test.Q4) AS [Total Q4], Sum(test.Q3) AS [Total Q3], Sum([q2]+[q1]+[q4]+[q3]) AS Total, Kædetabel.Kæde
FROM test INNER JOIN Kædetabel ON test.ButiksID=Kædetabel.ButiksID
GROUP BY test.ButiksID, test.Butiksnavn, Kædetabel.Kæde
ORDER BY Sum([q2]+[q1]+[q4]+[q3]) DESC;

Each quarter of a year I enter new data into my database. Among the data is 'Year' (not seen or used in the query) and 'Quarter' (Q1-Q4 above). In my reports I only need the last four quarters. So now I need these quarters:

Q204 - Q104 - Q403 - Q303 (already in the database and in my query above)

When September is finished I get new quarterly data, and therefor my reports should only include:

Q304 - Q204 - Q104 - Q403

So Q303 will not be needed in this query anymore, instead Q304 is needed. In January 2005 the query should only include:

Q404 - Q304 - Q204 - Q104

Etc.

I have no real good idea to solve this puzzle but assume it can be handlede some way in a query or?

Anyway, any help is really appreciated

 

[r937]

it looks to me like your table has only 4 quarters in it

therefore you must be "shifting" the data when a new quarter starts

therefore i don't understand what the problem is

i would suggest a redesign but obviously you must already have application logic in place that shifts q2 to q1, q3 to q2, q4 to q3, and zeros q4 to get ready for the new quarter



rudy

http://r937.com/

SQL Consulting

 

[MichaelRed]

Use a layered set of queries. First to just zelect the previous 'year' of data (using the previous quarter end date). Seconf to group by quarter.

Then combine the two as a single query.



MichaelRed

 

[Bokazoit]

@r937: Yes that's true, but after September 2004 I will have 3.Quarter to add to my data, and then I need a way to automate this, so that I don't have to delete data.

@r937 & MichaelRed: I'm not that hardcore a user, so can you explain in detail? And it's okey to suggest a redesign