• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

Access query for two tables

Hi Experts,
    I'm working on a  query which will require getting data from two tables and display.
two tables are Products and ProductSales. their common field is productID, and a product can have many productsales values.
structure of ProductSales table is something like this
ProductID               Period      Sales
PHHDAPDXXXX            154           1
PHHDAPDXXXX            155           1
PHHDAPDXXXX            156           10

Period are stored in a table called Periods

my query will want to know the most recent 12 months sales of this product counting from currenty period, so eventually the query data should display :

ProductID             monthq0      monthq1 ...      monthq9      monthq10      monthq11      monthq12
 PHHDAPDXXXX    0                  0                          5            4        -46                   0

how do i achieve this?
0
miketonny
Asked:
miketonny
  • 5
2 Solutions
 
sshah254Commented:
Look into creating a crosstab query using the 3 tables.

Ss
0
 
miketonnyAuthor Commented:
thank you for pointing out the direction, however i'm having problem with the query

TRANSFORM Sum(PeriodSales) AS SumOfPeriodSales
SELECT PRODUCTS.ProductCode
FROM PRODUCTS INNER JOIN PSALES ON PRODUCTS.UniqueID = PSALES.ProductID
WHERE (((PSALES.PeriodNo) In (select top 10 periodNo FROM [PSALES] group by periodno order by periodno desc)))
GROUP BY PRODUCTS.ProductCode
ORDER BY PSALES.PeriodNo DESC 
PIVOT SALES.PeriodNo;

Open in new window

that's a test query i got for the tables, but when i execute it never finish running, i can tell the cpu is running at max as task manager shows it's at 100%.
if i took 'select top 10 periodNo FROM [PSALES] group by periodno order by periodno desc' out it runs pretty fast but then that's not the result i wanted, because there are 200 periods in the Psales table.
0
 
miketonnyAuthor Commented:
also i tried in sql server with this
SELECT ProductCode, [157] as period0, [156] as period1, [155] as period2, [154] as period3, [153] as period4
FROM (select periodno, PeriodSales, t.ProductCode from PSALES p Inner join PRODUCTS t ON p.ProductID = t.UniqueID where PeriodNo in (select top 5 periodno from PSALES group by PeriodNo order by PeriodNo desc)) as s
pivot
(
max(periodsales) for periodno in ([157], [156], [155], [154], [153])
) AS PivotTable

Open in new window


which works fine, but then i'm not sure how to turn 157,156..153 into dynamic numbers as periods are going to increase over the time, anyone know how to deal with this?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
miketonnyAuthor Commented:
I managed to get this far:
DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
SELECT  @listCol = STUFF(( 
SELECT DISTINCT Top 5 '],[' + ltrim(periodno) FROM PSALES Group By periodno ORDER BY '],[' + ltrim(periodno)desc 
FOR XML PATH('')), 1, 2,'') + ']'


set @query = 
'SELECT ProductCode, ' + @listcol + '
FROM (select periodno, PeriodSales, t.ProductCode from PSALES p inner join PRODUCTS t ON p.ProductID = t.UniqueID) as s 
pivot (max(periodsales) for periodno in (' +@listcol + ')) 
AS PivotTable'

execute @query

Open in new window


but when i run this it gives me the error:

The name 'SELECT ProductCode, [157],[156],[155],[154],[153]
FROM (select periodno, PeriodSales, t.ProductCode from PSALES p inner join PRODUCTS t ON p.ProductID = t.UniqueID) as s
pivot (max(periodsales) for periodno in ([157],[156],[155],[154],[153]))
AS PivotTable' is not a valid identifier.
0
 
SharathData EngineerCommented:
You missed the brackets.
DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
SELECT  @listCol = STUFF(( 
SELECT DISTINCT Top 5 '],[' + ltrim(periodno) FROM PSALES Group By periodno ORDER BY '],[' + ltrim(periodno)desc 
FOR XML PATH('')), 1, 2,'') + ']'


set @query = 
'SELECT ProductCode, ' + @listcol + '
FROM (select periodno, PeriodSales, t.ProductCode from PSALES p inner join PRODUCTS t ON p.ProductID = t.UniqueID) as s 
pivot (max(periodsales) for periodno in (' +@listcol + ')) 
AS PivotTable'

execute (@query)

Open in new window

0
 
miketonnyAuthor Commented:
thanks, that runs well.
is that possible i can change the names of columns instead  of using the variable?
i want the column heading look like this:
ProductCode   month0 month1 month2 ....
0
 
miketonnyAuthor Commented:
I will close this one and start another question as advices in here are sufficient to answer my original question. thank you all for the support
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now