Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Command Table of Month and Year performance

In my database I have several tables that contain a stat date. The output requirements are for each month as below.

Jan-15

Feb-15

Mar-15

Apr-15

May-15

Jun-15

But my data might have months where there is no data so those months of course will not produce rows.

I DO NOT have the ability to create a table to left join so that my output will meet requirements and creating a table to link to in excel is not an option either. So after many hours of working , as a novice, with SQL I came up with this command table and left join to another command table where I convert the date in my stats table to date(year, month and change day to 1).

select

a.StatDate

     from(

     select CAST(dateadd(yy,nummonths.a,(dateadd(m,numyears.a,{d '2009-12-1'}) )) as DATE) as StatDate

          from

          (select 1 as a union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11 union select 12 ) as nummonths,

(select 1 as a union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10  union select 11 union select 12 ) as numyears

) as a

This all works quite nicely but my question is in regards to how crystal reports will process this and the effect on performance for large datasets. Does CR run this query for every record it matches on the left join?

I hope this question makes sense.

Not what you were looking for? View more on this topic or Ask a question