software adds comp_companyid that distors my sql results

Question

Post reply

software adds comp_companyid that distors my sql results

parthmeister

SSC Veteran

Points: 230
More actions
March 10, 2013 at 9:17 pm

#272097

Hi Folks,
i am having the sql below:
SELECT TOP (100) SAMINC.dbo.OESTATS.YR as YEAR , SUM(SAMINC.dbo.OESTATS.SALESAMTF) AS TotalSales
FROM SAMINC.dbo.OESTATS INNER JOIN
dbo.Company ON SAMINC.dbo.OESTATS.LINVCUST = dbo.Company.Comp_IdCust COLLATE Latin1_General_BIN
GROUP BY SAMINC.dbo.OESTATS.YR
that generated the results below:
YEAR TotalSales
201921932.360
202046448.040
it is find in SQL, the software that i am using automatically adds a field called comp_companyid to it so the sql becomes
SELECT TOP (100) SAMINC.dbo.OESTATS.YR AS YEAR, SUM(SAMINC.dbo.OESTATS.SALESAMTF) AS TotalSales, dbo.Company.Comp_CompanyId
FROM SAMINC.dbo.OESTATS INNER JOIN
dbo.Company ON SAMINC.dbo.OESTATS.LINVCUST = dbo.Company.Comp_IdCust COLLATE Latin1_General_BIN
GROUP BY SAMINC.dbo.OESTATS.YR, dbo.Company.Comp_CompanyId
and the results are diffrent because of it as below:
YEAR TOTALSales comp_companyid
202011082.7201228
201912575.5201229
20202159.8501229
20207973.0501231
20201890.0001232
20199356.8401234
202010794.3101234
as it is a requirement to add the comp_companyid it completely distors my results that i am looking for.
any way to acheive the first result by tweaking the second tsql?
please advise
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

any way to acheive the first result by tweaking the second tsql?
please advise

In short: No, there is no way, without stopping your "software" to change your original query.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Sergiy SSC Guru Points: 110206 More actions · Answer 2

You may try to use WITH ROLLUP option in your query.

It won't remove all the rows with CompanyID's from the final recordset, but will add 2 records to it"

YEAR CompanyID TotalSales

2019 NULL 21932.360

2020 NULL 46448.040

_____________
Code for TallyGenerator

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

What software are you using that adds another column to your query?

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

Sergiy (3/11/2013)
You may try to use WITH ROLLUP option in your query.
It won't remove all the rows with CompanyID's from the final recordset, but will add 2 records to it"
YEAR CompanyID TotalSales
2019 NULL 21932.360
2020 NULL 46448.040

That will not give the same results as query takes top 100 rows based on different grouping condition...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 5

parthmeister (3/10/2013)
Hi Folks,
i am having the sql below:
SELECT TOP (100) SAMINC.dbo.OESTATS.YR as YEAR , SUM(SAMINC.dbo.OESTATS.SALESAMTF) AS TotalSales
FROM SAMINC.dbo.OESTATS INNER JOIN
dbo.Company ON SAMINC.dbo.OESTATS.LINVCUST = dbo.Company.Comp_IdCust COLLATE Latin1_General_BIN
GROUP BY SAMINC.dbo.OESTATS.YR
that generated the results below:
YEAR TotalSales
201921932.360
202046448.040
it is find in SQL, the software that i am using automatically adds a field called comp_companyid to it so the sql becomes
SELECT TOP (100) SAMINC.dbo.OESTATS.YR AS YEAR, SUM(SAMINC.dbo.OESTATS.SALESAMTF) AS TotalSales, dbo.Company.Comp_CompanyId
FROM SAMINC.dbo.OESTATS INNER JOIN
dbo.Company ON SAMINC.dbo.OESTATS.LINVCUST = dbo.Company.Comp_IdCust COLLATE Latin1_General_BIN
GROUP BY SAMINC.dbo.OESTATS.YR, dbo.Company.Comp_CompanyId
and the results are diffrent because of it as below:
YEAR TOTALSales comp_companyid
202011082.7201228
201912575.5201229
20202159.8501229
20207973.0501231
20201890.0001232
20199356.8401234
202010794.3101234
as it is a requirement to add the comp_companyid it completely distors my results that i am looking for.
any way to acheive the first result by tweaking the second tsql?
please advise

Have you tried this as your first query, in the hope that the software will just add Comp_CompanyId to the outer SELECT?

SELECT a.[YEAR] , a.[TotalSales]

FROM (

SELECT TOP (100) SAMINC.dbo.OESTATS.YR as YEAR , SUM(SAMINC.dbo.OESTATS.SALESAMTF) AS TotalSales

FROM SAMINC.dbo.OESTATS INNER JOIN

dbo.Company ON SAMINC.dbo.OESTATS.LINVCUST = dbo.Company.Comp_IdCust COLLATE Latin1_General_BIN

GROUP BY SAMINC.dbo.OESTATS.YR

ORDER BY {you need to decide what order by you should be using to get the correct TOP 100}

) AS [a]

ORDER BY a.[Year],a.[TotalSales]

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);