Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

software adds comp_companyid that distors my sql results Expand / Collapse
Author
Message
Posted Sunday, March 10, 2013 9:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 9:42 PM
Points: 6, Visits: 61
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
Post #1429055
Posted Monday, March 11, 2013 8:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 2,836, Visits: 5,066

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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1429300
Posted Monday, March 11, 2013 4:55 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 9:16 PM
Points: 4,576, Visits: 8,347
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

Post #1429537
Posted Monday, March 11, 2013 5:24 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 23,215, Visits: 31,895
What software are you using that adds another column to your query?



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)
Post #1429543
Posted Tuesday, March 12, 2013 3:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 2,836, Visits: 5,066
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1429640
Posted Tuesday, March 12, 2013 3:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:40 PM
Points: 1,787, Visits: 5,692
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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1429642
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse