November 1, 2011 at 1:37 pm
I having issues with a simple query.
I need to GROUP BY in the OUTER Query by Company Name, AccountNumber, County, Territory, etc. I have my GROUP BY in the wrong place and get an error.:w00t:
In the INNER Query I'm performing the following:
(SELECT AccountNumber, COUNT(*) AS SalesCount
FROM ProducerTracking
WHERE SubmitYear = DATEPART(YEAR,DATEADD(year,-1,GETDATE()))
GROUP BY AccountNumber) AS Cnt ON PT.AccountNumber = Cnt.AccountNumber
The Submit Year returns 2010 and the SalesCount for the previous Year.
I have a number of other columns that I need to Pivot on, CurrentYTD, CurrentMTD, PreviousMonth, PreviousWeek, etc.
I would greatly appreciate any help.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 1, 2011 at 1:43 pm
I'd have to see the whole query to really nail this one down.
Is it in the form of?:
select CompanyName, AccountNumber, County, Territory, SalesCount
from dbo.Accounts as PT
left outer join
(SELECT AccountNumber, COUNT(*) AS SalesCount
FROM ProducerTracking
WHERE SubmitYear = DATEPART(YEAR,DATEADD(year,-1,GETDATE()))
GROUP BY AccountNumber) AS Cnt ON PT.AccountNumber = Cnt.AccountNumber
Or is it more complex than that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 1, 2011 at 4:34 pm
GSquared,
Thanks for your response.
It is very complicated. Many of the headings are dynamic based on the current date, e,g, 2010 (Last Year), Year To Date (2011 YTD), Month to Date (MTD), Last Month (Sep 2011), etc.
It is currently being performed in a Complicated Excel Macro.
I was going to try and do it in SSIS but I have been informed that you can't do this in SSIS.
I showed my boss and she is going to let me use the existing Very Complex Macro Code since I have created all but one other Sheet from SSIS.
Thanks again.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 2, 2011 at 6:31 am
Yeah, dynamic column-headers is a pain.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2011 at 6:43 am
Many of the headings are dynamic based on the current date, e,g, 2010 (Last Year), Year To Date (2011 YTD), Month to Date (MTD), Last Month (Sep 2011), etc.
I'll just mention this, though I'm sure you've considered it already.
If the SSIS job could be made to populate a specific cell in the Excel spreadsheet with 'Run Date' or whatever (and it most certainly can), your column headings sound as if they could all be derived from this date via standard Excel formulae - no need for any macros.
Just an idea.
November 2, 2011 at 9:51 am
The boss does not want to go with the Excel MAcros.
Back to using SSIS.
The following generates the column headers for the first two aggregates:
SELECT DATEPART(YEAR,DATEADD(year,-1,GETDATE())) AS Year,
CAST(DATEPART(YEAR,DATEADD(year,0,GETDATE())) AS VARCHAR(35)) + ' YTD' AS YTD
YearYTD
20102011 YTD
I Still have:
NOV 2011 MTD, OCT 2011, SEPT 2011, Week of OCT 30, Week of OCT 23, Week of OCT 16, Week of OCT 9
Then I have to populate the Report & Column Headers.
Write aggregates for each period inside an Outer Loop with AccountNumbers, CompanyName, etc.
Thanks for all of the Help!
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy