Looking to input dummy records into Select Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Looking to input dummy records into Select Query

Author

Topic

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-04-06 : 12:03:26

Hey I have the following query

SELECT *
FROM financial INNER JOIN
financialActivityType ON financial.financialActivityTypeID = financialActivityType.ID INNER JOIN
sorpType ON financial.sorpTypeID = sorpType.ID INNER JOIN
organisationRegulatory ON financial.inkexID = organisationRegulatory.inkexID
WHERE (financial.inkexID = 63) AND (financial.financialActivityTypeID < 17) AND (financial.sorpTypeID = 7)
ORDER BY financial.financialActivityTypeID, financial.sorpTypeID

Which returns data as

2 118 63 7 1 199052 Voluntary income Current Total 2009 2008
2 119 63 7 2 0 Income from fundraising activities Current Total 2009 2008
2 120 63 7 3 10830 Investment income Current Total 2009 2008
2 121 63 7 4 265950 Income from charitable activities Current Total 2009 2008

And this is fine but where u see the value "7" this is a column called sorpTypeID which has value prior year, and another column in this table "8" is current year. The reason why 8 isnt returned in the above table is because there is no values for "8". But In the case when there is values I would still like to do this imaganery join so data looks like

2 118 63 7 1 199052 Voluntary income Current Total 2009 2008
2 118 63 8 1 - Voluntary income Prior Total 2009 2008
2 119 63 7 2 0 Income from fundraising activities Current Total 2009 2008
2 119 63 8 2 - Income from fundraising activities Prior Total 2009 2008

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-06 : 12:19:44

What is the rule for determining the text that I have highlighted below in red? If you can post the DDL for the tables, that would make it much easier to understand.

2 118 63 7 1 199052 Voluntary income Current Total 2009 2008
2 118 63 8 1 - Voluntary income Prior Total 2009 2008
2 119 63 7 2 0 Income from fundraising activities Current Total 2009 2008
2 119 63 8 2 - Income from fundraising activities Prior Total 2009 2008

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-04-06 : 12:25:19

2 118 63 8 1 - Voluntary income Prior Total 2009 2008

Voluntary income = The Same Text as the row above would have in this column
Prior Total = This can be hardcoded
2009 = Same As Row Above
2008 = Same As Row Above

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-06 : 12:39:57

Without knowing the DDL tables, it's next to impossible to write the query or even offer any suggestions. However, the general idea would be as follows:

a) Use a Calendar table that lists all the years that you want the data for
b) Left join the results of your current query to the Calendar table
c) Where there are no rows from the right table, use appropriate logic to fill in the data.

Another alternative would be to perform an outer apply against the calendar table using the results of your current query.

If you can post the DDL for the tables, many of the people on this forum may be able to offer more specific help. Brett's post here http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx has instructions on how to get the DDL for the tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources