Performance enhancement through one view

[dwight1]

Can any one suggest how i can unite the following view into one single view. I am having twelve views for twelve months. I am looking to enhance the performance by making a single view (if it's possible).

SELECT Name, Jan, BRANCH, ACTYPE
FROM dbo.DDAcctsMonthly2
WHERE (Jan IS NOT NULL)

SELECT Name, Feb, BRANCH, ACTYPE
FROM dbo.DDAcctsMonthly2
WHERE (Feb IS NOT NULL)

SELECT Name, Mar, BRANCH, ACTYPE
FROM dbo.DDAcctsMonthly2
WHERE (Mar IS NOT NULL)

Thanks

Dwight

 

[SQLDenis]

Create view vDDAcctsMonthly2
as
SELECT Name, Jan, BRANCH, ACTYPE
FROM dbo.DDAcctsMonthly2
WHERE (Jan IS NOT NULL)
UNION ALL
SELECT Name, Feb, BRANCH, ACTYPE
FROM dbo.DDAcctsMonthly2
WHERE (Feb IS NOT NULL)
UNIOn ALL
SELECT Name, Mar, BRANCH, ACTYPE
FROM dbo.DDAcctsMonthly2
WHERE (Mar IS NOT NULL)

This won't enhance performance, you should partition the table accros 12 tables (Horizontal Partitioning) then you will see performance gains

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[dwight1]

SQLDENIS,

Thank you. I did run the view as per your suggestion but that would club all the months into one. For example,

Name Jan CostCenter
XXX 123 (Jan) ABC
XXX 234 (Feb) ABC
xxx 456 (Mar) ABC

I want the solution in the following way

Name Jan Feb Mar Costcenter
XXX 123 234 456 ABC

The main reason i had twelve views was to have twelve months separated in the columns.

I did run a case statement and due to the chaning number of costcenters i get the solution in the following format.

Name Costcenter Jan Feb Mar
XXX ABC 123
XYZ CDE 234
xzz efg 564
XXX ABC 423
XYZ CDE 234
xzz efg 574
XXX ABC 123
XYZ CDE 234
xzz efg 564

upto december. I tried to group them with no results. I think this is because the costcenters change/increase from month to month.
Well the problem is resolved but required 12 queries, if can be done other way it would help.

Thanks again for your help.

Dwight

 

[SQLDenis]

What ypu want is a crosstab query, take a look at

http://www.aspfaq.com/show.asp?id=2462

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[druer]

Do the Branch and ActType values vary by month or are those fixed fields like the Name column? In other words can you pull the values from a single table, or do they vary based on the month? Assuming they can be fixed you can issue the following:

select t1.Name, t1.Branch, t1.ActType, t1.Jan, t2.Feb, t3.March, t4.April etc.
FROM dbo.DDAcctsMonthly2 WHERE (Jan IS NOT NULL) as t1
left outer join
(select name, feb from dbo.ddAccsMonthly2 where feb is not null) as t2 on t1.name = t2.name
left outer join
(select name, march from dbo.ddAccsMonthly2 where march is not null) as t3

Notice that each of the months is simply selected as a result set itslef that are then used as TABLE variables and are joined on the Name field. The first table where JAN results are pulled needs pulls the Name, Branch and AcctType and the January value, while the others only need to pull the Name for the join, and the value for that month. I've set it up with outer join assuming that if a Name has no value for a particular month you will still want to pull the other months data.

 

[maswien]

try this:

select 
       Name, 
       BRANCH, 
       ACTYPE,
       (case when Jan IS NOT NULL then Jan else null end ) as Jan,
       (case when Feb IS NOT NULL then Feb else null end ) as Feb,
       (case when Mar IS NOT NULL then Mar else null end ) as Mar
FROM  dbo.DDAcctsMonthly2

 

[druer]

Much better solution than mine.

 

[dwight1]

Thank you for your reply.

Druer, you are right. The name of the branches change, that is where i am having problem.

SQLDenis, I did run a case statement and because of the changing branches i end up getting branch and relevant month data in a different record (even after grouping).

Maswein, Thanks for your suggestion.

Dwight

 

[druer]

Can you share the table format for the actual data tables with us? The more I thought about the solutions we offered the more I began to think there may be 12 different tables for the values or something like JanData, FebData or something, otherwise you could have just selected Acct, Jan, Feb because using the case statement would not be needed as it would already output NULL if the value was NULL and would output the data if it was there.

I don't want to give up on this, but I think we'll need the actual table layouts and some sample data to proceed.