July 24, 2015 at 2:10 am
Assume I have a query like this
select
a.period, a.kode_lang, a.namaprod,
a.kode_comp, a.kode_type, a.kodesalur, a.company,
sum(a.sales) as sales,
sum(isnull(b.salestahunlalu, 0)) as SalesLastYear
into
nv..BI_DATA2015_vbaru
from
nv..bi_data2015_v1 a
left join
(select
period, kode_lang, namaprod, kode_comp, kode_type,
kodesalur, company,
sum(sales) salestahunlalu
from
nv..bi_data2015_v1
group by
period, kode_lang, namaprod, kode_comp, kode_type, kodesalur, company) as b on a.kode_lang = b.kode_lang
and a.namaprod = b.namaprod and a.kode_comp = b.kode_comp
and a.kode_type = b.kode_type and a.kodesalur = b.kodesalur
and a.company=b.company
and (convert(int, left(a.period, 4)) = convert(int, left(b.period, 4)) + 1
and right(a.period, 2) = right(b.period, 2))
group by
a.period, a.kode_lang, a.namaprod, a.kode_comp,
a.kode_type, a.kodesalur, a.company
And finally when I compare this month vs last year same month is different, for example the result :
Sales SalesLastYear Period
--------------------------------------
173248252827 0 201312
185136805606 73023677114 201412
in SalesLastYear 201412 is must be : 173248252827
Can anybody help me ?
Regards yM
July 24, 2015 at 6:44 am
yayan.m (7/24/2015)
Assume I have a query like this
selecta.period, a.kode_lang, a.namaprod,
a.kode_comp, a.kode_type, a.kodesalur, a.company,
sum(a.sales) as sales,
sum(isnull(b.salestahunlalu, 0)) as SalesLastYear
into
nv..BI_DATA2015_vbaru
from
nv..bi_data2015_v1 a
left join
(select
period, kode_lang, namaprod, kode_comp, kode_type,
kodesalur, company,
sum(sales) salestahunlalu
from
nv..bi_data2015_v1
group by
period, kode_lang, namaprod, kode_comp, kode_type, kodesalur, company) as b on a.kode_lang = b.kode_lang
and a.namaprod = b.namaprod and a.kode_comp = b.kode_comp
and a.kode_type = b.kode_type and a.kodesalur = b.kodesalur
and a.company=b.company
and (convert(int, left(a.period, 4)) = convert(int, left(b.period, 4)) + 1
and right(a.period, 2) = right(b.period, 2))
group by
a.period, a.kode_lang, a.namaprod, a.kode_comp,
a.kode_type, a.kodesalur, a.company
And finally when I compare this month vs last year same month is different, for example the result :
Sales SalesLastYear Period
--------------------------------------
173248252827 0 201312
185136805606 73023677114 201412
in SalesLastYear 201412 is must be : 173248252827
Can anybody help me ?
Regards yM
Have you tried running your query from your LEFT JOIN separately with a WHERE clause that just gets data for 201312 ?
That might help determine if it's a query problem, or a data problem.
As we have no way to test this query against sample data, there's not much more than guesswork available to us, so you
really should consider posting DDL for the tables involved, as well as INSERT statements that have sample data that will deliver
the results you are seeing if we run this query against that sample data.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 24, 2015 at 11:27 am
Have you tried running your query from your LEFT JOIN separately with a WHERE clause that just gets data for 201312 ?
That might help determine if it's a query problem, or a data problem.
Can you give me an example query please ?
Regards
yM
July 24, 2015 at 11:36 am
yayan.m (7/24/2015)
Have you tried running your query from your LEFT JOIN separately with a WHERE clause that just gets data for 201312 ?
That might help determine if it's a query problem, or a data problem.
Can you give me an example query please ?
Regards
yM
Run the following in SSMS:
select
period, kode_lang, namaprod, kode_comp, kode_type,
kodesalur, company,
sum(sales) salestahunlalu
from
nv..bi_data2015_v1
WHERE left(period, 4) = '2013'
AND right(period, 2) = '12'
group by
period, kode_lang, namaprod, kode_comp, kode_type, kodesalur, company
Compare this result to the result you are getting from the original query for the same period. If they are the same, then you have a data problem, or a misunderstanding of what your data represents. Without considerably greater detail, we can't help you with the latter, as we have no background or details.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 24, 2015 at 12:30 pm
select
period, kode_lang, namaprod, kode_comp, kode_type,
kodesalur, company,
sum(sales) salestahunlalu
from
nv..bi_data2015_v1
WHERE left(period, 4) = '2013'
AND right(period, 2) = '12'
group by
period, kode_lang, namaprod, kode_comp, kode_type, kodesalur, company
i have Check with this your query the result : 214076381893, but when in row salesLastYear 201506 with my query : 94295126008not 214076381893
Sales SalesLastYear Period
214076381893 0 201406
219036727799 94295126008 201506
July 24, 2015 at 2:25 pm
yayan.m (7/24/2015)
select
period, kode_lang, namaprod, kode_comp, kode_type,
kodesalur, company,
sum(sales) salestahunlalu
from
nv..bi_data2015_v1
WHERE left(period, 4) = '2013'
AND right(period, 2) = '12'
group by
period, kode_lang, namaprod, kode_comp, kode_type, kodesalur, company
i have Check with this your query the result : 214076381893, but when in row salesLastYear 201506 with my query : 94295126008not 214076381893
Sales SalesLastYear Period214076381893 0 201406
219036727799 94295126008 201506
I think the problem is that you're effectively double grouping. Try the following:
WITH SALES AS (
SELECT
period, kode_lang, namaprod,
kode_comp, kode_type, kodesalur, company,
SUM(sales) AS sales
FROM
nv..bi_data2015_v1
GROUP BY
period, kode_lang, namaprod, kode_comp,
kode_type, kodesalur, company
)
SELECT A.*, B.sales AS salestahunlalu
INTO
nv..BI_DATA2015_vbaru
FROM SALES AS A
LEFT OUTER JOIN SALES AS B
ON A.kode_lang = B.kode_lang
AND A.namaprod = B.namaprod
AND A.kode_comp = B.kode_comp
AND A.kode_type = B.kode_type
AND A.kodesalur = B.kodesalur
AND A.company = B.company
AND (CONVERT(int, LEFT(A.period, 4)) = CONVERT(int, LEFT(B.period, 4)) + 1
AND RIGHT(A.period, 2) = RIGHT(B.period, 2))
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 26, 2015 at 3:59 am
I think the problem is that you're effectively double grouping. Try the following:
WITH SALES AS (
SELECT
period, kode_lang, namaprod,
kode_comp, kode_type, kodesalur, company,
SUM(sales) AS sales
FROM
nv..bi_data2015_v1
GROUP BY
period, kode_lang, namaprod, kode_comp,
kode_type, kodesalur, company
)
SELECT A.*, B.sales AS salestahunlalu
INTO
nv..BI_DATA2015_vbaru
FROM SALES AS A
LEFT OUTER JOIN SALES AS B
ON A.kode_lang = B.kode_lang
AND A.namaprod = B.namaprod
AND A.kode_comp = B.kode_comp
AND A.kode_type = B.kode_type
AND A.kodesalur = B.kodesalur
AND A.company = B.company
AND (CONVERT(int, LEFT(A.period, 4)) = CONVERT(int, LEFT(B.period, 4)) + 1
AND RIGHT(A.period, 2) = RIGHT(B.period, 2))
The result still not the same, as you know my raw data namaprod in some period is not same such as number namaprod in 201406 is little than 201506.
for example in 201406 name prod : a,b,c,d,e,f but in 201506 a,b,c,f,g,h
Please advice.
Regards
yM
July 26, 2015 at 1:44 pm
yayan.m (7/26/2015)
Please advice.
We're only guessing here because we don't actually have your data. It's time for you to read and heed the article at the first link in my signature line below so we can help you better. Take the time... it'll be worth it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply