Combining tables

Here's an issue I am having, I am currently getting data from a customer table, sales table, and a definitions table, it has 2 inner joins, It creates a temporary output table. Now, this table only includes data from the customers that actually had sales. I need a way to select into that temp table all the customers that are not currently in that table. Is there a way to do this? Any help would be greatly appreciated.

Jim

Hi,

therefore better use a LEFT JOIN which will be also applied to those customers which don′t have any entries in the joined tables. But this is just a common approach which *could* fit to your needs. Before giving you more information or advices please post the query you are using here.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

I have tried a left join with no avalial, Actually I have tried every join I could think of. Basically the stored procedure I am using to create the table uses 4 join statements to evaluate a number of sales totals, it creates a temp table, but no matter how may times I evaluate the statement or at what point, I cannot seem to get the left join to act any differently than an inner or right.

Included in the stored procedure I am using, perhaps someone out there will see something I do not.

ALTER PROCEDURE [dbo].[sp_get_minimumData] (@.StartDate as DateTime, @.StopDate as DateTime, @.prdGroup as char(1))

AS

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_minimum_data]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[tmp_minimum_data]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_Current_Minimums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[tmp_Current_Minimums]

SELECT dbo.CustomerBase.qb_seq AS qb_seq, dbo.CustomerBase.CustQbName AS Customer,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_1_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_1) ELSE '0.00' END AS SalesItem1,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_2_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_2) ELSE '0.00' END AS SalesItem2,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_3_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_3) ELSE '0.00' END AS SalesItem3,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_4_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_4) ELSE '0.00' END AS SalesItem4,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_5_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_5) ELSE '0.00' END AS SalesItem5,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_6_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_6) ELSE '0.00' END AS SalesItem6,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_7_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_7) ELSE '0.00' END AS SalesItem7,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_8_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_8) ELSE '0.00' END AS SalesItem8,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_9_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_9) ELSE '0.00' END AS SalesItem9,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_10_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_10) ELSE '0.00' END AS SalesItem10,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_11_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_11) ELSE '0.00' END AS SalesItem11,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_12_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_12) ELSE '0.00' END AS SalesItem12,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_13_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_13) ELSE '0.00' END AS SalesItem13,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_14_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_14) ELSE '0.00' END AS SalesItem14,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_15_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_15) ELSE '0.00' END AS SalesItem15,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_16_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_16) ELSE '0.00' END AS SalesItem16,

CASE WHEN dbo.tbl_ItemizerMap.tax_item_1_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_1) ELSE '0.00' END AS TaxItem1,

CASE WHEN dbo.tbl_ItemizerMap.tax_item_2_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_2) ELSE '0.00' END AS TaxItem2,

CASE WHEN dbo.tbl_ItemizerMap.tax_item_3_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_3) ELSE '0.00' END AS TaxItem3,

CASE WHEN dbo.tbl_ItemizerMap.tax_item_4_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_4) ELSE '0.00' END AS TaxItem4,

CASE WHEN dbo.tbl_ItemizerMap.tax_item_5_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_5) ELSE '0.00' END AS TaxItem5,

CASE WHEN dbo.tbl_ItemizerMap.tax_item_6_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_6) ELSE '0.00' END AS TaxItem6,

CASE WHEN dbo.tbl_ItemizerMap.tax_item_7_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_7) ELSE '0.00' END AS TaxItem7,

CASE WHEN dbo.tbl_ItemizerMap.tax_item_8_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_8) ELSE '0.00' END AS TaxItem8,

CASE WHEN dbo.tbl_ItemizerMap.service_charge_min = 'True' THEN SUM(dbo.tbl_BatchArchive.service_charge) ELSE '0.00' END AS ServiceChargeItem,

CASE WHEN dbo.tbl_ItemizerMap.auto_service_charge_min = 'True' THEN SUM(dbo.tbl_BatchArchive.auto_service_charge) ELSE '0.00' END AS AutoServiceChargeItem,

dbo.CustomerBase.minPeriodGroup

INTO tmp_minimum_data

FROM dbo.tbl_BatchArchive INNER JOIN

dbo.tbl_ItemizerMap ON dbo.tbl_BatchArchive.rvc_num = dbo.tbl_ItemizerMap.rvc_num AND

dbo.tbl_BatchArchive.qb_FullName <> dbo.tbl_ItemizerMap.cash_account_name INNER JOIN

dbo.CustomerBase ON dbo.tbl_BatchArchive.qb_seq = dbo.CustomerBase.qb_seq

WHERE (dbo.tbl_BatchArchive.bus_date BETWEEN @.StartDate AND @.StopDate) AND (dbo.CustomerBase.minPeriodGroup = @.prdGroup)

GROUP BY dbo.tbl_BatchArchive.qb_FullName, dbo.tbl_BatchArchive.bus_date, dbo.tbl_ItemizerMap.sales_item_1_min, dbo.tbl_ItemizerMap.sales_item_2_min,

dbo.tbl_ItemizerMap.sales_item_3_min, dbo.tbl_ItemizerMap.sales_item_4_min, dbo.tbl_ItemizerMap.sales_item_5_min,

dbo.tbl_ItemizerMap.sales_item_6_min, dbo.tbl_ItemizerMap.sales_item_7_min, dbo.tbl_ItemizerMap.sales_item_8_min,

dbo.tbl_ItemizerMap.sales_item_9_min, dbo.tbl_ItemizerMap.sales_item_10_min, dbo.tbl_ItemizerMap.sales_item_11_min,

dbo.tbl_ItemizerMap.sales_item_12_min, dbo.tbl_ItemizerMap.sales_item_13_min, dbo.tbl_ItemizerMap.sales_item_14_min,

dbo.tbl_ItemizerMap.sales_item_15_min, dbo.tbl_ItemizerMap.sales_item_16_min, dbo.tbl_ItemizerMap.tax_item_1_min,

dbo.tbl_ItemizerMap.tax_item_2_min, dbo.tbl_ItemizerMap.tax_item_3_min, dbo.tbl_ItemizerMap.tax_item_4_min,

dbo.tbl_ItemizerMap.tax_item_5_min, dbo.tbl_ItemizerMap.tax_item_6_min, dbo.tbl_ItemizerMap.tax_item_7_min,

dbo.tbl_ItemizerMap.tax_item_8_min, dbo.tbl_ItemizerMap.service_charge_min, dbo.tbl_ItemizerMap.auto_service_charge_min,

dbo.CustomerBase.minPeriodGroup, dbo.CustomerBase.CustQbName, dbo.CustomerBase.qb_seq

SELECT dbo.CustomerBase.qb_seq, dbo.CustomerBase.CustomerAccount, dbo.tmp_minimum_data.Customer,

SUM(dbo.tmp_minimum_data.SalesItem1) + SUM(dbo.tmp_minimum_data.SalesItem2)

+ SUM(dbo.tmp_minimum_data.SalesItem3) + SUM(dbo.tmp_minimum_data.SalesItem4) + SUM(dbo.tmp_minimum_data.SalesItem5)

+ SUM(dbo.tmp_minimum_data.SalesItem6) + SUM(dbo.tmp_minimum_data.SalesItem7) + SUM(dbo.tmp_minimum_data.SalesItem8)

+ SUM(dbo.tmp_minimum_data.SalesItem9) + SUM(dbo.tmp_minimum_data.SalesItem10) + SUM(dbo.tmp_minimum_data.SalesItem11)

+ SUM(dbo.tmp_minimum_data.SalesItem12) + SUM(dbo.tmp_minimum_data.SalesItem13) + SUM(dbo.tmp_minimum_data.SalesItem14)

+ SUM(dbo.tmp_minimum_data.SalesItem15) + SUM(dbo.tmp_minimum_data.SalesItem16) + SUM(dbo.tmp_minimum_data.TaxItem1)

+ SUM(dbo.tmp_minimum_data.TaxItem2) + SUM(dbo.tmp_minimum_data.TaxItem3) + SUM(dbo.tmp_minimum_data.TaxItem4)

+ SUM(dbo.tmp_minimum_data.TaxItem5) + SUM(dbo.tmp_minimum_data.TaxItem6) + SUM(dbo.tmp_minimum_data.TaxItem7)

+ SUM(dbo.tmp_minimum_data.TaxItem8) + SUM(dbo.tmp_minimum_data.ServiceChargeItem) + SUM(dbo.tmp_minimum_data.AutoServiceChargeItem) AS ValueTwardMinimum, dbo.CustomerBase.minValue,

CASE WHEN dbo.CustomerBase.minValue - (SUM(dbo.tmp_minimum_data.SalesItem1) + SUM(dbo.tmp_minimum_data.SalesItem2)

+ SUM(dbo.tmp_minimum_data.SalesItem3) + SUM(dbo.tmp_minimum_data.SalesItem4) + SUM(dbo.tmp_minimum_data.SalesItem5)

+ SUM(dbo.tmp_minimum_data.SalesItem6) + SUM(dbo.tmp_minimum_data.SalesItem7) + SUM(dbo.tmp_minimum_data.SalesItem8)

+ SUM(dbo.tmp_minimum_data.SalesItem9) + SUM(dbo.tmp_minimum_data.SalesItem10) + SUM(dbo.tmp_minimum_data.SalesItem11)

+ SUM(dbo.tmp_minimum_data.SalesItem12) + SUM(dbo.tmp_minimum_data.SalesItem13) + SUM(dbo.tmp_minimum_data.SalesItem14)

+ SUM(dbo.tmp_minimum_data.SalesItem15) + SUM(dbo.tmp_minimum_data.SalesItem16) + SUM(dbo.tmp_minimum_data.TaxItem1)

+ SUM(dbo.tmp_minimum_data.TaxItem2) + SUM(dbo.tmp_minimum_data.TaxItem3) + SUM(dbo.tmp_minimum_data.TaxItem4)

+ SUM(dbo.tmp_minimum_data.TaxItem5) + SUM(dbo.tmp_minimum_data.TaxItem6) + SUM(dbo.tmp_minimum_data.TaxItem7)

+ SUM(dbo.tmp_minimum_data.TaxItem8) + SUM(dbo.tmp_minimum_data.ServiceChargeItem) + SUM(dbo.tmp_minimum_data.AutoServiceChargeItem))> 0 THEN

dbo.CustomerBase.minValue - (SUM(dbo.tmp_minimum_data.SalesItem1) + SUM(dbo.tmp_minimum_data.SalesItem2)

+ SUM(dbo.tmp_minimum_data.SalesItem3) + SUM(dbo.tmp_minimum_data.SalesItem4) + SUM(dbo.tmp_minimum_data.SalesItem5)

+ SUM(dbo.tmp_minimum_data.SalesItem6) + SUM(dbo.tmp_minimum_data.SalesItem7) + SUM(dbo.tmp_minimum_data.SalesItem8)

+ SUM(dbo.tmp_minimum_data.SalesItem9) + SUM(dbo.tmp_minimum_data.SalesItem10) + SUM(dbo.tmp_minimum_data.SalesItem11)

+ SUM(dbo.tmp_minimum_data.SalesItem12) + SUM(dbo.tmp_minimum_data.SalesItem13) + SUM(dbo.tmp_minimum_data.SalesItem14)

+ SUM(dbo.tmp_minimum_data.SalesItem15) + SUM(dbo.tmp_minimum_data.SalesItem16) + SUM(dbo.tmp_minimum_data.TaxItem1)

+ SUM(dbo.tmp_minimum_data.TaxItem2) + SUM(dbo.tmp_minimum_data.TaxItem3) + SUM(dbo.tmp_minimum_data.TaxItem4)

+ SUM(dbo.tmp_minimum_data.TaxItem5) + SUM(dbo.tmp_minimum_data.TaxItem6) + SUM(dbo.tmp_minimum_data.TaxItem7)

+ SUM(dbo.tmp_minimum_data.TaxItem8) + SUM(dbo.tmp_minimum_data.ServiceChargeItem) + SUM(dbo.tmp_minimum_data.AutoServiceChargeItem)) ELSE 0 END AS InvoiceAmmount

INTO tmp_Current_Minimums

FROM dbo.tmp_minimum_data INNER JOIN

dbo.CustomerBase ON dbo.CustomerBase.qb_seq = dbo.tmp_minimum_data.qb_seq

WHERE (dbo.tmp_minimum_data.SalesItem1 + dbo.tmp_minimum_data.SalesItem2 + dbo.tmp_minimum_data.SalesItem3 + dbo.tmp_minimum_data.SalesItem4

+ dbo.tmp_minimum_data.SalesItem5 + dbo.tmp_minimum_data.SalesItem6 + dbo.tmp_minimum_data.SalesItem7 + dbo.tmp_minimum_data.SalesItem8

+ dbo.tmp_minimum_data.SalesItem9 + dbo.tmp_minimum_data.SalesItem10 + dbo.tmp_minimum_data.SalesItem11 + dbo.tmp_minimum_data.SalesItem12

+ dbo.tmp_minimum_data.SalesItem13 + dbo.tmp_minimum_data.SalesItem14 + dbo.tmp_minimum_data.SalesItem15 + dbo.tmp_minimum_data.SalesItem16

+ dbo.tmp_minimum_data.TaxItem1 + dbo.tmp_minimum_data.TaxItem2 + dbo.tmp_minimum_data.TaxItem3 + dbo.tmp_minimum_data.TaxItem4 + dbo.tmp_minimum_data.TaxItem5

+ dbo.tmp_minimum_data.TaxItem6 + dbo.tmp_minimum_data.TaxItem7 + dbo.tmp_minimum_data.TaxItem8 + dbo.tmp_minimum_data.ServiceChargeItem + dbo.tmp_minimum_data.AutoServiceChargeItem <> 0) AND

(dbo.CustomerBase.minValue - (dbo.tmp_minimum_data.SalesItem1 + dbo.tmp_minimum_data.SalesItem2 + dbo.tmp_minimum_data.SalesItem3 + dbo.tmp_minimum_data.SalesItem4

+ dbo.tmp_minimum_data.SalesItem5 + dbo.tmp_minimum_data.SalesItem6 + dbo.tmp_minimum_data.SalesItem7 + dbo.tmp_minimum_data.SalesItem8

+ dbo.tmp_minimum_data.SalesItem9 + dbo.tmp_minimum_data.SalesItem10 + dbo.tmp_minimum_data.SalesItem11 + dbo.tmp_minimum_data.SalesItem12

+ dbo.tmp_minimum_data.SalesItem13 + dbo.tmp_minimum_data.SalesItem14 + dbo.tmp_minimum_data.SalesItem15 + dbo.tmp_minimum_data.SalesItem16

+ dbo.tmp_minimum_data.TaxItem1 + dbo.tmp_minimum_data.TaxItem2 + dbo.tmp_minimum_data.TaxItem3 + dbo.tmp_minimum_data.TaxItem4 + dbo.tmp_minimum_data.TaxItem5

+ dbo.tmp_minimum_data.TaxItem6 + dbo.tmp_minimum_data.TaxItem7 + dbo.tmp_minimum_data.TaxItem8 + dbo.tmp_minimum_data.ServiceChargeItem + dbo.tmp_minimum_data.AutoServiceChargeItem)

<> 0) AND (dbo.CustomerBase.minValue <> 0)

GROUP BY dbo.tmp_minimum_data.Customer, dbo.CustomerBase.minValue, dbo.CustomerBase.CustomerAccount, dbo.CustomerBase.qb_seq

DROP TABLE tmp_minimum_data

ALTER TABLE dbo.tmp_Current_Minimums ADD

changed Boolean NULL,

reason nvarchar(50) NULL

SELECT * FROM tmp_Current_Minimums

|||

Hi

I think LEFT and INNER joins behave the same in 2 cases:

1. No data is missing from the left joined table

2. You are using the left joined table in the WHERE clause. In this case , try to add your criteria to the LEFT JOIN ON criteria.

NB

|||Join to the customer using a NOT EXISTS (in the temp table). That will pull back all the rows from customer not already in the temp table.|||I would like to thank everyone, I ended up using a Cursor and some creative data manipulation to do what I needed, but thank you all for your assistance.

Combining 2 sql records on one detail line

Hi,
Is there a way to take 2 sql records from a data set and combine them
on one detail line. My data set looks like
sales lane store_no week year
9930.04 2 C196 50 2006
7276.24 3 C196 50 2006
In reporting services I want to have a table that shows store, lane2
sales, lane3 sales, week, year. When I do this now I get 2 detail rows
one that shows lane2 sales and another that shows lane 3 sales. I have
tried using a matrix which does a nice job of pivoting the data but
then throws off the way I want my report to be layed out. In Crystal
report I could do calculations and running totals behind the scenes
and then drop the result of that on the report the way I want it. How
can I do that with RS? As a side note I have tried using calculated
fields in RS but they crash my Visual Stuido if I do any kind of
calulation or IIF statement.
Any help is appreciatedHey you can do this pivoting using sql query itself, so that the result set
will look like the way you want
Amarnath
"mcgrawc@.checkers.com" wrote:
> Hi,
> Is there a way to take 2 sql records from a data set and combine them
> on one detail line. My data set looks like
> sales lane store_no week year
> 9930.04 2 C196 50 2006
> 7276.24 3 C196 50 2006
> In reporting services I want to have a table that shows store, lane2
> sales, lane3 sales, week, year. When I do this now I get 2 detail rows
> one that shows lane2 sales and another that shows lane 3 sales. I have
> tried using a matrix which does a nice job of pivoting the data but
> then throws off the way I want my report to be layed out. In Crystal
> report I could do calculations and running totals behind the scenes
> and then drop the result of that on the report the way I want it. How
> can I do that with RS? As a side note I have tried using calculated
> fields in RS but they crash my Visual Stuido if I do any kind of
> calulation or IIF statement.
> Any help is appreciated
>|||I realize that using SQL I could pivot the data and I have written
some code to do just that however I was hoping to find a way in
Reporting Services to do this just like I could in Crystal Reports. My
company wants to use RS to replace our crystal reports but I am
finding either through my lack of experience with RS that some of our
reports are beyond what RS can currently give. I personally love some
of the RS features but miss my the ease of my Crystal formulas,
running totals, etc. I have several other reports that I have stopped
working on temporarily that have this same problem and I really am not
looking to write code for all of them to pivot the data into something
RS can use. If anyone has a report solution I would love to hear it.
Chadwick|||Have you tried using the RuningValue function in RS?
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"chadwick" wrote:
> I realize that using SQL I could pivot the data and I have written
> some code to do just that however I was hoping to find a way in
> Reporting Services to do this just like I could in Crystal Reports. My
> company wants to use RS to replace our crystal reports but I am
> finding either through my lack of experience with RS that some of our
> reports are beyond what RS can currently give. I personally love some
> of the RS features but miss my the ease of my Crystal formulas,
> running totals, etc. I have several other reports that I have stopped
> working on temporarily that have this same problem and I really am not
> looking to write code for all of them to pivot the data into something
> RS can use. If anyone has a report solution I would love to hear it.
> Chadwick
>

Combined Chart (One series with Line and One Bar)

Is it possible to create combined charts with the Report Designer Chart?
I want to have one series charted as bar (sales) and the other series as
line (i.e market share).
Want to do it in one chart (not to overlay one chart over the other)
Thanx.Please read this related newsgroup posting:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=c229b25c-b2dc-41ac-923d-decbb253dc6e&sloc=en-us
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Minas Papageorgiou" <MinasPapageorgiou@.discussions.microsoft.com> wrote in
message news:8AD71606-450E-4E5D-B4DD-12CA9A4A49C5@.microsoft.com...
> Is it possible to create combined charts with the Report Designer Chart?
> I want to have one series charted as bar (sales) and the other series as
> line (i.e market share).
> Want to do it in one chart (not to overlay one chart over the other)
> Thanx.
>

combine values in view?

Is there an easy way to do this?
I need to pull sales values for each store and make one record per month.
This used to be easy, as I was provided only one sales record per month per
store, but now I've been told that SOME of the stores have a "secondary" ID
from which to pull sales numbers, and these numbers need to be added to the
number from their PRIMARY number before displaying them. I'm not sure how
to do this.
Here is some sample data (cols don't align well here - sorry):
STORES:
ID1 ID2 STORE
100 AAA A-STORE
200 B-STORE
300 CCC C-STORE
400 D-STORE
(note that B-Store and D-Store have only ONE ID, not a secondary ID)
SALES:
ID SALES MONTH YEAR
100 5.00 01 2004
AAA 5.00 01 2004
200 5.00 01 2004
300 5.00 01 2004
CCC 5.00 01 2004
400 5.00 01 2004
(note that sales values from IDs "100" and "AAA" both belong to "A-Store",
and "300" and "CCC" belong to "C-Store"; also that there is a set of sales
records for each month & year so those fields need to be accounted for.)
I need a view that will show:
ID SALES MONTH YEAR
100 10.00 01 2004
200 5.00 01 2004
300 10.00 01 2004
400 5.00 01 2004
(note that IDs 100 and 300 show the combined sales of both their primary AND
secondary IDs)
Is this easily doable? If yes, how'!!
Thanks!
-RThere are some data integrity problems here, and in particular,
if the ID2 values are not unique, this is something of a mess, but
if the data is not messed up, something like this should work:
select ID, sum(SALES) as SALES, [MONTH], [YEAR]
from (
select ID, SALES, [MONTH], [YEAR]
from SALES
union all
select ST.ID1, SA.SALES, SA.[MONTH], SA.[YEAR]
from SALES AS SA join STORES AS ST
on SA.ID = ST.ID2
) S
group by ID, [MONTH], [YEAR]
If the data is messed up, this query could provide completely
wrong information. You would be better off keeping track
of the store IDs differently:
-- primary IDs only, with store attributes
CREATE TABLE STORES (
ID char(3) primary key,
StoreName varchar(30),
.. other attributes of a store
)
-- all IDs, primary and alternate, for stores, with
-- the primary storeID for each
CREATE TABLE STORE_IDS (
ID char(3) primary key,
storeID char(3) references STORES(ID)
-- put an index on storeID to support the FK
)
The foreign key on SALES would now link to this
second table instead of the first. The query would
be different, too - something like this:
select ST.ID, sum(SA.SALES) as SALES, SA.[MONTH], SA.[YEAR]
from STORES as ST
join STORE_IDS as I
on I.storeID = ST.ID
join SALES as SA
on SA.ID = I.ID
group by ST.ID, SA.[MONTH], SA.[YEAR]
Steve Kass
Drew University
r wrote:

>Is there an easy way to do this?
>I need to pull sales values for each store and make one record per month.
>This used to be easy, as I was provided only one sales record per month per
>store, but now I've been told that SOME of the stores have a "secondary" ID
>from which to pull sales numbers, and these numbers need to be added to the
>number from their PRIMARY number before displaying them. I'm not sure how
>to do this.
>Here is some sample data (cols don't align well here - sorry):
>STORES:
>ID1 ID2 STORE
>100 AAA A-STORE
>200 B-STORE
>300 CCC C-STORE
>400 D-STORE
>(note that B-Store and D-Store have only ONE ID, not a secondary ID)
>SALES:
>ID SALES MONTH YEAR
>100 5.00 01 2004
>AAA 5.00 01 2004
>200 5.00 01 2004
>300 5.00 01 2004
>CCC 5.00 01 2004
>400 5.00 01 2004
>(note that sales values from IDs "100" and "AAA" both belong to "A-Store",
>and "300" and "CCC" belong to "C-Store"; also that there is a set of sales
>records for each month & year so those fields need to be accounted for.)
>I need a view that will show:
>ID SALES MONTH YEAR
>100 10.00 01 2004
>200 5.00 01 2004
>300 10.00 01 2004
>400 5.00 01 2004
>(note that IDs 100 and 300 show the combined sales of both their primary AN
D
>secondary IDs)
>Is this easily doable? If yes, how'!!
>Thanks!
>-R
>
>
>sqlsql

Column Chart Y-axis Values Not Rendering

I'm trying to create a column chart and am having trouble with the y-axis values. I want to chart sales dollars by month, with the dollars on the y-axis.

The datasource is a datatable object with 2 columns, Month & Amount. I dragged the Month column into the category area, and the Amount column into the data fields area

The resulting chart shows a column for each month, but all months have a value of '1' rather than the actual values in the table. For testing, I dropped a table control on the page and the amount values show correctly (=Fields!Amount.Value).

Is the chart showing a count, perhaps? Any help is appreciated.

Maybe the Amount value is a formatted string, rather than a numeric value?

Try the following expression for the data point value in the chart: =Sum(CDbl(Fields!Amount.Value))

-- Robert

|||Thanks, Robert. That was it.

Column Chart Y-axis Values Not Rendering

I'm trying to create a column chart and am having trouble with the y-axis values. I want to chart sales dollars by month, with the dollars on the y-axis.

The datasource is a datatable object with 2 columns, Month & Amount. I dragged the Month column into the category area, and the Amount column into the data fields area

The resulting chart shows a column for each month, but all months have a value of '1' rather than the actual values in the table. For testing, I dropped a table control on the page and the amount values show correctly (=Fields!Amount.Value).

Is the chart showing a count, perhaps? Any help is appreciated.

Maybe the Amount value is a formatted string, rather than a numeric value?

Try the following expression for the data point value in the chart: =Sum(CDbl(Fields!Amount.Value))

-- Robert

|||Thanks, Robert. That was it.