Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


software adds comp_companyid that distors my sql results


software adds comp_companyid that distors my sql results

Author
Message
parthmeister
parthmeister
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 65
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
2019 21932.360
2020 46448.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
2020 11082.720 1228
2019 12575.520 1229
2020 2159.850 1229
2020 7973.050 1231
2020 1890.000 1232
2019 9356.840 1234
2020 10794.310 1234

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
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478

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
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5832 Visits: 11403
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24180 Visits: 37950
What software are you using that adds another column to your query?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
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
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2268 Visits: 7824
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
2019 21932.360
2020 46448.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
2020 11082.720 1228
2019 12575.520 1229
2020 2159.850 1229
2020 7973.050 1231
2020 1890.000 1232
2019 9356.840 1234
2020 10794.310 1234

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);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search