March 23, 2012 at 8:22 am
Hi all,
I have been working off of Jeff Moden's article 'Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs' to create a dynamic cross tab.
My problem is that the data I am working with doesn't have dates. It has month and year in two separate columns.
I need to come up with a way to present this data so that it shows in a
SalesRep 1_2010 2_2010...3_2012
ABC 3965.32 250.20... 523.89
format.
Using the article I have come up with the tally table showing the month and the year in separate columns along with N however, in the aggregation, I don't know how to make the fields come out as above without hardcoding it in. And of course, this needs to be dynamic so that the client can input any month and year to any month and year and see the amount paid to each rep.
Here is what I have so far:
These tables are just the data needed for this query not complete tables.
CREATE TABLE a
( SalesRep varchar(200)
comm_month int
comm_year int
comm_total Decimal )
Create Table b
(
SR_Name varchar(200)
)
insert into a (salesRep, comm_month, comm_year, comm_total)
Values ('ABC',1, 2010, 365.23),
('DEF',2, 2010, 52.20),
('GHI',3, 2011, 8645.21)
Insert into b (SR_name)
Values ('JKL'),
('ABC'),
('DEF'),
('GHI')
Declare @StartDate datetime
Declare @EndDate datetime
Declare @StartMonth varchar(2)
Declare @EndMonth varchar(2)
Declare @StartYear varchar(5)
Declare @EndYear varchar(5)
Set @StartMonth = '1'
Set @EndMonth = '12'
Set @StartYear = '2010'
Set @EndYear = '2012'
Set @StartDate = '1/1/2010'
Set @EndDate = '12/1/2011'
select case when Grouping(sr_name) = 1 Then 'CommPaid' Else SR_Name end as SR_Name,
SUM(case when comm_Month = '1' and comm_Year = '2010' then commPaid else 0 end) as [1_2010],
SUM(case when comm_Month = '2' and comm_Year = '2010' then commPaid else 0 end) as [2_2010],
SUM(case when comm_month = '3' and comm_year = '2010' then commpaid else 0 end) as [3_2010],
SUM(case when comm_month = '4' and comm_year = '2010' then commpaid else 0 end) as [4_2010],
---More hard coding here
SUM(commPaid) as CommPaid
From
(Select b.SR_Name, a.comm_month, a.comm_year, SUM(round(a.comm_total,0)) as CommPaid
from a
inner join b
on a.SalesRep = b.SR_Name
where a.Comm_Month >= @StartMonth and Comm_Month <= @EndMonth
And a.Comm_Year >= @StartYear and Comm_Year <= @EndYear
Group by a.Comm_Month, a.Comm_Year, b.SR_Name) d
Group by SR_Name with Rollup
I have tired casting the int columns to varchar as the output (ie cast(comm_month as varchar(3))+'_'+cast(comm_year as varchar(5)) but it throws an error.
Does anyone have any idea how I can get the int columns of comm_month and comm_year to comeout the way desired? (ie 3_2012)
Thanks SO much for the articles Jeff and thanks everyone in advance for your help!
March 23, 2012 at 8:25 am
Hello and welcome to SSC!
I'd like to be able to help, but it seems you've forgot to post readily consumable sample data and ddl scripts.
If you could read this article[/url] about the best way to post DDL and sample data then edit your post to include it, it would be extremely useful for the unpaid volunteers of this site.
Thanks!
March 23, 2012 at 8:47 am
Sorry about that!
I have edited it as needed (hopefully)
and thanks for your help--very much appreciated.
March 23, 2012 at 9:12 am
NuB2Sql (3/23/2012)
Sorry about that!I have edited it as needed (hopefully)
and thanks for your help--very much appreciated.
No problem 🙂
OK, firstly I've fixed your create table statement and formatted the whole query -
CREATE TABLE a (SalesRep VARCHAR(200), comm_month INT, comm_year INT, comm_total DECIMAL);
CREATE TABLE b (SR_Name VARCHAR(200));
INSERT INTO a (salesRep, comm_month, comm_year, comm_total)
VALUES ('ABC', 1, 2010, 365.23), ('DEF', 2, 2010, 52.20), ('GHI', 3, 2011, 8645.21);
INSERT INTO b (SR_name)
VALUES ('JKL'), ('ABC'), ('DEF'), ('GHI');
DECLARE @StartDate DATETIME, @EndDate DATETIME, @StartMonth VARCHAR(2), @EndMonth VARCHAR(2),
@StartYear VARCHAR(5), @EndYear VARCHAR(5);
SET @StartMonth = '1';
SET @EndMonth = '12';
SET @StartYear = '2010';
SET @EndYear = '2012';
SET @StartDate = '1/1/2010';
SET @EndDate = '12/1/2011';
SELECT
CASE WHEN Grouping(sr_name) = 1 THEN 'CommPaid' ELSE SR_Name END AS SR_Name,
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '2' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [2_2010],
SUM(CASE WHEN comm_month = '3' AND comm_year = '2010' THEN commpaid ELSE 0 END) AS [3_2010],
SUM(CASE WHEN comm_month = '4' AND comm_year = '2010' THEN commpaid ELSE 0 END) AS [4_2010], ---More hard coding here
SUM(commPaid) AS CommPaid
FROM (SELECT b.SR_Name, a.comm_month, a.comm_year, SUM(round(a.comm_total, 0)) AS CommPaid
FROM a
INNER JOIN b ON a.SalesRep = b.SR_name
WHERE a.Comm_Month >= @StartMonth AND Comm_Month <= @EndMonth AND a.Comm_Year >= @StartYear
AND Comm_Year <= @EndYear
GROUP BY a.Comm_Month, a.Comm_Year, b.SR_Name) d
GROUP BY SR_Name WITH Rollup;
On my system, this produces: -
SR_Name 1_2010 2_2010 3_2010 4_2010 CommPaid
---------- ----------------- ----------------- ----------------- ----------------- -----------------
ABC 365 0 0 0 365
DEF 0 52 0 0 52
GHI 0 0 0 0 8645
CommPaid 365 52 0 0 9062
I take it you want this to be more dynamic?
So, this would build it up as dynamic SQL
DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = COALESCE(@SQL,'') + ',' + CHAR(13) + CHAR(10) +
'SUM(CASE WHEN comm_Month = ' +CHAR(39)+ CONVERT(VARCHAR(2),comm_month) +CHAR(39)+
' AND comm_Year = '+CHAR(39)+ CONVERT(VARCHAR(4),comm_year) +CHAR(39)+' THEN commPaid ELSE 0 END) AS ' +
QUOTENAME(CONVERT(VARCHAR(2),comm_month)+'_'+CONVERT(VARCHAR(4),comm_year))
FROM (SELECT b.SR_Name, a.comm_month, a.comm_year, SUM(round(a.comm_total, 0)) AS CommPaid
FROM a
INNER JOIN b ON a.SalesRep = b.SR_name
WHERE a.Comm_Month >= @StartMonth AND Comm_Month <= @EndMonth AND a.Comm_Year >= @StartYear
AND Comm_Year <= @EndYear
GROUP BY a.Comm_Month, a.Comm_Year, b.SR_Name) d;
SET @SQL = 'SELECT CASE WHEN Grouping(sr_name) = 1 THEN '+CHAR(39)+'CommPaid'+CHAR(39)+' ELSE SR_Name END AS SR_Name' +
@SQL + ',' + CHAR(13) + CHAR(10) + 'SUM(commPaid) AS CommPaid ' +
'FROM (SELECT b.SR_Name, a.comm_month, a.comm_year, SUM(round(a.comm_total, 0)) AS CommPaid ' + CHAR(13) + CHAR(10) +
'FROM a' + CHAR(13) + CHAR(10) +
'INNER JOIN b ON a.SalesRep = b.SR_name' + CHAR(13) + CHAR(10) +
'WHERE a.Comm_Month >= @StartMonth AND Comm_Month <= @EndMonth AND a.Comm_Year >= @StartYear' + CHAR(13) + CHAR(10) +
'AND Comm_Year <= @EndYear'+ CHAR(13) + CHAR(10) +
'GROUP BY a.Comm_Month, a.Comm_Year, b.SR_Name) d'+ CHAR(13) + CHAR(10) +
'GROUP BY SR_Name WITH Rollup;';
That produces this -
SELECT CASE WHEN Grouping(sr_name) = 1 THEN 'CommPaid' ELSE SR_Name END AS SR_Name,
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '2' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [2_2010],
SUM(CASE WHEN comm_Month = '3' AND comm_Year = '2011' THEN commPaid ELSE 0 END) AS [3_2011],
SUM(commPaid) AS CommPaid FROM (SELECT b.SR_Name, a.comm_month, a.comm_year, SUM(round(a.comm_total, 0)) AS CommPaid
FROM a
INNER JOIN b ON a.SalesRep = b.SR_name
WHERE a.Comm_Month >= @StartMonth AND Comm_Month <= @EndMonth AND a.Comm_Year >= @StartYear
AND Comm_Year <= @EndYear
GROUP BY a.Comm_Month, a.Comm_Year, b.SR_Name) d
GROUP BY SR_Name WITH Rollup;
So, now we want to use sp_executesql to pass in the parameters.
EXECUTE sp_executesql @SQL, N'@StartDate DATETIME, @EndDate DATETIME, @StartMonth VARCHAR(2), @EndMonth VARCHAR(2),
@StartYear VARCHAR(5), @EndYear VARCHAR(5)', @StartDate=@StartDate, @EndDate=@EndDate, @StartMonth=@StartMonth,
@EndMonth=@EndMonth, @StartYear=@StartYear, @EndYear=@EndYear;
Put it all together: -
CREATE TABLE a (SalesRep VARCHAR(200), comm_month INT, comm_year INT, comm_total DECIMAL);
CREATE TABLE b (SR_Name VARCHAR(200));
INSERT INTO a (salesRep, comm_month, comm_year, comm_total)
VALUES ('ABC', 1, 2010, 365.23), ('DEF', 2, 2010, 52.20), ('GHI', 3, 2011, 8645.21);
INSERT INTO b (SR_name)
VALUES ('JKL'), ('ABC'), ('DEF'), ('GHI');
DECLARE @StartDate DATETIME, @EndDate DATETIME, @StartMonth VARCHAR(2), @EndMonth VARCHAR(2),
@StartYear VARCHAR(5), @EndYear VARCHAR(5);
SET @StartMonth = '1';
SET @EndMonth = '12';
SET @StartYear = '2010';
SET @EndYear = '2012';
SET @StartDate = '1/1/2010';
SET @EndDate = '12/1/2011';
DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = COALESCE(@SQL,'') + ',' + CHAR(13) + CHAR(10) +
'SUM(CASE WHEN comm_Month = ' +CHAR(39)+ CONVERT(VARCHAR(2),comm_month) +CHAR(39)+
' AND comm_Year = '+CHAR(39)+ CONVERT(VARCHAR(4),comm_year) +CHAR(39)+' THEN commPaid ELSE 0 END) AS ' +
QUOTENAME(CONVERT(VARCHAR(2),comm_month)+'_'+CONVERT(VARCHAR(4),comm_year))
FROM (SELECT b.SR_Name, a.comm_month, a.comm_year, SUM(round(a.comm_total, 0)) AS CommPaid
FROM a
INNER JOIN b ON a.SalesRep = b.SR_name
WHERE a.Comm_Month >= @StartMonth AND Comm_Month <= @EndMonth AND a.Comm_Year >= @StartYear
AND Comm_Year <= @EndYear
GROUP BY a.Comm_Month, a.Comm_Year, b.SR_Name) d;
SET @SQL = 'SELECT CASE WHEN Grouping(sr_name) = 1 THEN '+CHAR(39)+'CommPaid'+CHAR(39)+' ELSE SR_Name END AS SR_Name' +
@SQL + ',' + CHAR(13) + CHAR(10) + 'SUM(commPaid) AS CommPaid ' +
'FROM (SELECT b.SR_Name, a.comm_month, a.comm_year, SUM(round(a.comm_total, 0)) AS CommPaid ' + CHAR(13) + CHAR(10) +
'FROM a' + CHAR(13) + CHAR(10) +
'INNER JOIN b ON a.SalesRep = b.SR_name' + CHAR(13) + CHAR(10) +
'WHERE a.Comm_Month >= @StartMonth AND Comm_Month <= @EndMonth AND a.Comm_Year >= @StartYear' + CHAR(13) + CHAR(10) +
'AND Comm_Year <= @EndYear'+ CHAR(13) + CHAR(10) +
'GROUP BY a.Comm_Month, a.Comm_Year, b.SR_Name) d'+ CHAR(13) + CHAR(10) +
'GROUP BY SR_Name WITH Rollup;';
EXECUTE sp_executesql @SQL, N'@StartDate DATETIME, @EndDate DATETIME, @StartMonth VARCHAR(2), @EndMonth VARCHAR(2),
@StartYear VARCHAR(5), @EndYear VARCHAR(5)', @StartDate=@StartDate, @EndDate=@EndDate, @StartMonth=@StartMonth,
@EndMonth=@EndMonth, @StartYear=@StartYear, @EndYear=@EndYear;
How's that?
March 23, 2012 at 10:05 am
Thank you VERY much! finally something is making sense!
however, here are the errors i am getting when I try your code:
The number of elements in the select list exceeds the maximum allowed number of 4096 elements.
Msg 102, Level 15, State 1, Line 5398
Incorrect syntax near 'd'.
Msg 319, Level 15, State 1, Line 5399
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
(Sorry have no idea how to post errors here so i centered them.
I think the main one i am concerened about is the first one--exceeding the maxium allowed num. I am working on SQL Server 2008 (i know it's obvious since the post is in that forum but still 🙂 )
Any ideas what I did wrong?
March 23, 2012 at 10:17 am
NuB2Sql (3/23/2012)
Thank you VERY much! finally something is making sense!however, here are the errors i am getting when I try your code:
Msg 1056, Level 15, State 1, Line 2The number of elements in the select list exceeds the maximum allowed number of 4096 elements.
Msg 102, Level 15, State 1, Line 5398
Incorrect syntax near 'd'.
Msg 319, Level 15, State 1, Line 5399
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
(Sorry have no idea how to post errors here so i centered them.
I think the main one i am concerened about is the first one--exceeding the maxium allowed num. I am working on SQL Server 2008 (i know it's obvious since the post is in that forum but still 🙂 )
Any ideas what I did wrong?
Guessing that we are creating too many columns in the cross tabs, so I probably missed a filter in the first @sql assignment.
What do you get when you execute this against your real data?
DECLARE @StartDate DATETIME, @EndDate DATETIME, @StartMonth VARCHAR(2), @EndMonth VARCHAR(2),
@StartYear VARCHAR(5), @EndYear VARCHAR(5);
SET @StartMonth = '1';
SET @EndMonth = '12';
SET @StartYear = '2010';
SET @EndYear = '2012';
SET @StartDate = '1/1/2010';
SET @EndDate = '12/1/2011';
DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = COALESCE(@SQL,'') + ',' + CHAR(13) + CHAR(10) +
'SUM(CASE WHEN comm_Month = ' +CHAR(39)+ CONVERT(VARCHAR(2),comm_month) +CHAR(39)+
' AND comm_Year = '+CHAR(39)+ CONVERT(VARCHAR(4),comm_year) +CHAR(39)+' THEN commPaid ELSE 0 END) AS ' +
QUOTENAME(CONVERT(VARCHAR(2),comm_month)+'_'+CONVERT(VARCHAR(4),comm_year))
FROM (SELECT b.SR_Name, a.comm_month, a.comm_year, SUM(round(a.comm_total, 0)) AS CommPaid
FROM a
INNER JOIN b ON a.SalesRep = b.SR_name
WHERE a.Comm_Month >= @StartMonth AND Comm_Month <= @EndMonth AND a.Comm_Year >= @StartYear
AND Comm_Year <= @EndYear
GROUP BY a.Comm_Month, a.Comm_Year, b.SR_Name) d;
SELECT @SQL FOR XML PATH('stuff');
March 23, 2012 at 10:50 am
What do you get when you execute this against your real data?
<stuff>,
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND comm_Year = '2010' THEN commPaid ELSE 0 END) AS [1_2010],
SUM(CASE WHEN comm_Month = '1' AND com
March 23, 2012 at 11:40 am
Ah-ha!!
Told you it was my fault 😀
OK, I don't have my sandbox turned on at home, so can't give you tested code, but basically it's because we are selecting duplicates.
So, you need to do something like this: -
DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = COALESCE(@SQL,'') + ',' + CHAR(13) + CHAR(10) +
'SUM(CASE WHEN comm_Month = ' +CHAR(39)+ CONVERT(VARCHAR(2),comm_month) +CHAR(39)+
' AND comm_Year = '+CHAR(39)+ CONVERT(VARCHAR(4),comm_year) +CHAR(39)+' THEN commPaid ELSE 0 END) AS ' +
QUOTENAME(CONVERT(VARCHAR(2),comm_month)+'_'+CONVERT(VARCHAR(4),comm_year))
FROM (SELECT DISTINCT a.comm_month, a.comm_year
FROM a
INNER JOIN b ON a.SalesRep = b.SR_name
WHERE a.Comm_Month >= @StartMonth AND Comm_Month <= @EndMonth AND a.Comm_Year >= @StartYear
AND Comm_Year <= @EndYear
GROUP BY a.Comm_Month, a.Comm_Year, b.SR_Name) d;
March 23, 2012 at 12:08 pm
WOW!!!!!!!! Amazing!!!
Thank you SOOOOOOOOOOOOOOOOOOOOOOOOO much!
it worked. if you didn't figure that out already there, i said it, it worked! 🙂
You are awesome! I have been fighting with this for 2 weeks now. I initially used pivots and got it to pivot correctly except i couldn't get it in the correct format. then found the article and thought it was a blessing and got so far (and quickly execution time too!) then got stuck and here it is. all done. thanks!
March 23, 2012 at 1:45 pm
NuB2Sql (3/23/2012)
WOW!!!!!!!! Amazing!!!Thank you SOOOOOOOOOOOOOOOOOOOOOOOOO much!
it worked. if you didn't figure that out already there, i said it, it worked! 🙂
You are awesome! I have been fighting with this for 2 weeks now. I initially used pivots and got it to pivot correctly except i couldn't get it in the correct format. then found the article and thought it was a blessing and got so far (and quickly execution time too!) then got stuck and here it is. all done. thanks!
Heh.. See? I told you to post it in the PM you sent me. I'm not the only show in town on this site. There are a couple dozen real heavy hitters that can get to you before I can. 🙂
Welcome aboard.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 23, 2012 at 1:48 pm
I really do appreciate the help. i am now going through the code to understand EVERYTHING that is going on...haven't worked with COALESCE and some other functions so this has been a real learning experience!
Thanks again!
April 19, 2012 at 9:42 am
Alright guys, here's what's going on. the code above worked as long as the data required was for a full year starting from Jan. if i ran the above code with 3/2011 - 3/2012, i only got two rows (here is snipet of the result:
3_2011 3_2012
40790
830
3150
The result that i was trying to get was along the lines of:
3_2011 4_2011 5_2011...2_2012 3_2012
So I figured i need to use a Tally table (again referencing the article from Jeff). I made these changes to the code:
WHERE exists
(select N,
DATEPART(mm,convert(char(11), dateAdd(mm, n-1, @startDate),100)) as M,
DATEPART(yyyy,convert(char(11), dateAdd(mm, n-1, @startDate),100)) as y
from dbo.Tally where N <= DATEDIFF(mm, @startDate, @endDate) and
a.Comm_Month >= DATEPART(mm,convert(char(11), dateAdd(mm, N-1, @startDate),100))
AND Comm_Month <= DATEPART(mm,convert(char(11), dateAdd(mm, N-1, @startDate),100))
AND a.Comm_Year >= DATEPART(yyyy,convert(char(11), dateAdd(mm, N-1, @startDate),100))
AND a.Comm_Year <= DATEPART(yyyy,convert(char(11), dateAdd(mm, N-1, @startDate),100)))
GROUP BY a.Comm_Month, a.Comm_Year, b.SR_Name) d;
here is the whole section of code that i put this in:
select --COALESCE(@SQL,'') + ',' + CHAR(13) + CHAR(10) +
'SUM(CASE WHEN comm_Month = ' +CHAR(39)+ CONVERT(VARCHAR(2),comm_month) +CHAR(39)+
' AND comm_Year = '+CHAR(39)+ CONVERT(VARCHAR(4),comm_year) +CHAR(39)+' THEN commPaid ELSE 0 END) AS ' +
QUOTENAME(CONVERT(VARCHAR(2),comm_month)+'_'+CONVERT(VARCHAR(4),comm_year))
FROM (SELECT Distinct a.comm_month, a.comm_year--, SUM(round(a.comm_total, 0)) AS CommPaid
FROM a
INNER JOIN b ON a.SalesRep = b.SR_name
WHERE exists
(select N,
DATEPART(mm,convert(char(11), dateAdd(mm, n-1, @startDate),100)) as M,
DATEPART(yyyy,convert(char(11), dateAdd(mm, n-1, @startDate),100)) as y
from dbo.Tally where N <= DATEDIFF(mm, @startDate, @endDate) and
a.Comm_Month >= DATEPART(mm,convert(char(11), dateAdd(mm, N-1, @startDate),100))
AND Comm_Month <= DATEPART(mm,convert(char(11), dateAdd(mm, N-1, @startDate),100))
AND a.Comm_Year >= DATEPART(yyyy,convert(char(11), dateAdd(mm, N-1, @startDate),100))
AND a.Comm_Year <= DATEPART(yyyy,convert(char(11), dateAdd(mm, N-1, @startDate),100)))
GROUP BY a.Comm_Month, a.Comm_Year, b.SR_Name) d;
this returns 0 rows/columns...can anyone tell me what i did wrong please?
As always, thanks very much for the help. 🙂
April 19, 2012 at 12:02 pm
sorry I didn't tell you guys how i created the Tally table:
CREATE TABLE [dbo].[Tally](
[N] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Tally_N] PRIMARY KEY CLUSTERED
(
[N] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
Straight from Jeff Moden's code 🙂
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply