Greetings. I would like to select data from 3 tables, Customer, Periods, and Sales.
My Sales table has a Customer and a Period and a Sales Value, and I can select data as follows....
Select C.CustomerCode, C.CustomerTitle, S.Periodno, P.PeriodName, S.PeriodSales from ((Customers C Left Outer join CSales S on C.UniqueId =S.CustomerId) left outer join Periods P on S.Periodno=P.PeriodNumber)
But if a Customer only has sales in Period 1 and 3 I only get data for Periods 1 and 3
Is there any way that I can get data for periods 1 2 and 3 (with Period 2 showing 0 value)
Note: Also tried the following without sucess
Select C.CustomerCode, C.CustomerTitle, S.Periodno, P.PeriodName, S.PeriodSales from ((Periods P left outer join CSales S on P.PeriodNumber = S.Periodno) left outer join Customers C on S.CustomerId=C.UniqueId)
Select C.CustomerCode, C.CustomerTitle, S.Periodno, P.PeriodName, S.PeriodSales
from Customers AS C
Left Outer join CSales AS S
on S.CustomerId = C.UniqueId
left outer join Periods AS P
on P.PeriodNumber = S.Periodno
WHERE S.CustomerId IN
(
SELECT DISTINCT C.UniqueId
FROM Periods AS P
JOIN CSales AS S
ON S.Periodno = P.PeriodNumber
WHERE C.UniqueId = S.CustomerId
-- AND P.PeriodNumber >= @StartPeriod AND P.PeriodNumber <= @EndPeriod
)
This will select any customer who has any sales within the period (I've added a commented-out range for Period Number, without that it will any customer with any sale in any period)
Please format any code in your posts using the </> button to make it easier for all of us to read it
Thanks - tried that and it didnt give me all periods either.
To clarify further.... say the data in Customers is C1 and in Periods we have 1, 2, 3 and 4
If the data in CSales for C1 is $100 in Period 1 and $50 in Period 3 I am trying to find a selection that will give me 3 records....
C1 Period1 $100
C1 Period2 $0
C1 Period3 $50
C1 Period4 $0
More information and an additional idea that might work.
There is a field called Uniqueid in CSales that consists of the CustomerCode concatinated with the Periodno.
If we can select all periods for all Customers then that would work.
How to change the following so that it would run?
`
Select C.CustomerCode, C.CustomerTitle, S.Periodno,
S.PeriodSales from Customers C Left Outer join CSales S on
RTrim(C.UniqueId)+str(Periodnumber) =S.UniqueId where Periodnumber in (Select Periodnumber from Periods)
This solution works....I forced a join between the Customer and Period tables (by using unrelated fields but made them equate)
Select C.CustomerCode, C.CustomerTitle, P.Periodnumber, S.PeriodSales from Customers C inner join Periods P on C.Groupno-C.Groupno = P.Periodnumber-P.Periodnumber Left Outer join CSales S on RTrim(C.UniqueId)+Ltrim(str(P.Periodnumber,10)) =S.UniqueId Order by C.CustomerCode, P.Periodnumber
Select C.CustomerCode, C.CustomerTitle, S.Periodno, P.PeriodName, S.PeriodSales
from Periods AS P
join Customers AS C
ON C.UniqueId IN
(
SELECT DISTINCT S.CustomerId
FROM Periods AS P
JOIN CSales AS S
ON S.Periodno = P.PeriodNumber
-- WHERE P.PeriodNumber >= @StartPeriod AND P.PeriodNumber <= @EndPeriod
)
Left Outer join CSales AS S
on S.Periodno = P.PeriodNumber
AND S.CustomerId = C.UniqueId
It would help if you posted some sample data - in the form of a CREATE TABLE DDL for the three tables and some INSERT statements to put the sample data in them, then folk here can use that to test solutions. Otherwise we will all, separately, be having to do that and you won't get answers from people that don't have time to do that ... in my case without that example data I'm just guessing and my code probably has syntax errors too ... as I definitely don't have time to create sample data as well ...
