select unique data from layered data

Question

select unique data from layered data

Ed Casmer

SSC Enthusiast

Points: 117
More actions
August 30, 2004 at 4:51 pm

#162013

I am building a financial numbers tracking application. There are 3 types of numbers that I will be tracking: Actuals,
Monthly Estimates, and Yearly Estimates. At the beginning of the fiscal year, users will log into the application
and enter numbers for each month of the fiscal year. These will be stored in a YearlyNumbers table. Each month,
while it is current, users will enter their monthly numbers and these will be stored in the MonthlyNumbers table. Each
month, the user will also enter the previous month's actual numbers and these will be stored in the ActualNumbers table.
Each set of numbers is tied to a project specific service item. Different users will be managing different projects
and therefore will have different service items.
I need to create a report that presents the most relevant data to the user. For example, if the user were to run
the report today, I should present the data from the ActualNumbers table for months 1-7, the data from the
MonthlyNumbers table for month 8, and data from the YearlyNumbers table for months 9-12. Ultimately, I will
need the flexibility to user defined time ranges, but the data should be retrieved following the previously
described concept.
Can this be done in a single select statement involving all 3 tables? Should I select the data into a temp table
first and work with it that way? In either case, can you tell me what the sql statements would be? Another possible
issue that I have to take into consideration is that there may be different serviceItem IDs in the tables. In a certain
month a user adds a new service item to his project. This would be reflected in the MonthlyNumbers table and neither
of the other tables would have any numbers for this service item. When the actuals are entered during the following
month, the new service item would then be added to the ActualNumbers table. But the YearlyNumbers table would never
reflect the new service item. Can this be managed amongst the other issues I am currently presenting?
All 3 tables hold the same exact columns. This made me debate whether I should have one table that has an
additional column in it that can distinguish the data as actuals, monthly, or yearly. I am not opposed to this, but
some late night thought process convinced me to have the separate tables. The columns my tables currently have
are: uniqueID (int), serviceItemID (int), amount (money), comment (varchar), month (int), year (int).
Any help will be greatly appreciated and thanks in advance! Please let me know if you need any additional information.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 1

I think you need something like:

SELECT * FROM ActualNumbers 
WHERE [month]=MONTH(GETDATE()) AND [year]=YEAR(GETDATE())
UNION ALL

SELECT * FROM MonthlyNumbers 
WHERE [month]<MONTH(GETDATE()) AND [year]=YEAR(GETDATE())
UNION ALL
SELECT * FROM YearlyNumbers 
WHERE [month]>MONTH(GETDATE()) AND [year]=YEAR(GETDATE()-1)

Razvan

Ed Casmer SSC Enthusiast Points: 117 More actions · Answer 2

Thanks, this definitely gets me going in the right direction. I have been playing with this statement trying to modify it more specifically to what I need which will be querying our fiscal year. This runs from July through June. We also typically show one additional quarter of data with it. So my queries run from March of current year through June of following year. Where I continue to have a problem is that I might not have actual numbers yet for a given month or really up to 2 months, so I need to grab those values out of the monthlynumbers table and then all others from the yearlynumbers

table.

So I need to modify the middle statement to get the current month and potentially any previous months that are not already in the actualnumbers table. I tried something like the following, but it actually makes things worse by giving me duplicates of each row from the monthly table. I am only dealing with one serviceitem for now to make the data easier for me to deal with. That parameter will be taken off when doing the actual report.

SELECT actualnumbersID as ID, serviceid, amount, comment, month, year

FROM ActualNumbers

WHERE ([Month] >= 4) AND ([Year] = 2004) AND (ServiceID = 474)

UNION All

SELECT mn.monthlynumbersID as ID, mn.serviceid, mn.amount, mn.comment, mn.month, mn.year

FROM monthlynumbers mn, actualnumbers an

WHERE (mn.[Month] >= 4) AND (mn.[Year] = 2004) AND (mn.ServiceID = 474) AND

(mn.[Month] <= MONTH(GETDATE())) AND (mn.[Year] = YEAR(GETDATE())) and

(mn.month <> an.month and mn.year <> an.year and

an.serviceid=474)

UNION All

SELECT yearlynumbersID as ID, serviceid, amount, comment, month, year

FROM yearlynumbers

WHERE ([Month] > MONTH(GETDATE())) AND ([Year] = YEAR(GETDATE() - 1)) AND

(ServiceID = 474) OR ([Month] <= 6) AND ([Year] = 2005) AND (ServiceID = 474)

How can I get the correct data when I am not entirely sure where I am going to find it? For example, if I were to run this report today through the first week or two of semptember, I would get months 4-7 from ActualNumbers, month 8 from MonthlyNumbers, and then 9 through 6 (of 2005) from the YearlyNUmbers table. Next week, if I were to run the report I would still get 4-7 from ActualNumbers, now I would get 8 and 9 from MonthlyNUmbers (as month 8 has not had it's actual numbers entered yet), and then months 10 through 6 would come from the YearlyNumbers table.

Again, thanks in advance.