merge 2 SQL views for 1 data source

[SundancerKid]

Hi everyone,

I have 2 views with the same column information in each. They each have a unique key to reference each one. I would like to merge these tables together and so that all of the data is summerized. One Table is sales data and the other table is payroll data. Everything will summarize ok in except the dates do not match. One tables sales date is 05/21/2004 and the payroll date is 05/14/2004.

Table Sales

Unique Key as MyKey
Sales Person as SLS
Account Person as ACCT
GLDate as MyDate
Sales amount as AMT
0 as PayAmt

Table Payroll

Unique Key as MyKey
Sales Person as SLS
Account Person as ACCT
CheckDate as MyDate
0 as AMT
CheckAmt as PayAmt

I wrote a Stored procedure to combine both of these tables together.

Now I need to summarize these fields so that everything is summarized into one line to print one one line using Crystal 9.

In Crystal I have a parameter to select a date range i.e. 05/01/2004 thru 05/31/2004. I need the information that was accumulated in both of these tables and like keys matched together.

I thought about creating a view that calls the stored procedure that I created. The crystal would call the view. How do I access my data using the date range that is the problem. I guess what I am looking for is some preprocessing routine that includes the date and then converts the date to mm/yy then the two tables would match.

I am just rambling. any suggestions would be helpfull.

Thanks,

Rick

 

[SundancerKid]

I went the Crystal way. I summarized using the groups and then recalulated my Gross Margin on the Group Totals using a formula.

Thanks.

 

[fredp1]

Just for your info,

You could create a new view which has a UNION ALL statement with the sales and payroll views. It works like appending the results from each view.

Fred