SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


problem inner joininig a derived column


problem inner joininig a derived column

Author
Message
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14687 Visits: 11848
A calendar table is not really needed for this:


select
YearMonth = dateadd(mm,datediff(mm,0,[recorddate]),0),
MyValueSum = sum([myvalue])
from
#a
group by
dateadd(mm,datediff(mm,0,[recorddate]),0)
order by
dateadd(mm,datediff(mm,0,[recorddate]),0)



Results:
YearMonth                MyValueSum
----------------------- -----------
2012-01-01 00:00:00.000 30
2012-02-01 00:00:00.000 70
2012-08-01 00:00:00.000 50
2012-12-01 00:00:00.000 130

CapnHector
CapnHector
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3321 Visits: 1789
Michael Valentine Jones (12/3/2012)
A calendar table is not really needed for this:


select
YearMonth = dateadd(mm,datediff(mm,0,[recorddate]),0),
MyValueSum = sum([myvalue])
from
#a
group by
dateadd(mm,datediff(mm,0,[recorddate]),0)
order by
dateadd(mm,datediff(mm,0,[recorddate]),0)



Results:
YearMonth                MyValueSum
----------------------- -----------
2012-01-01 00:00:00.000 30
2012-02-01 00:00:00.000 70
2012-08-01 00:00:00.000 50
2012-12-01 00:00:00.000 130


The op would also like "0" for the intervening months so we have to figure out some way to accomplish that. a calendar table to join to seems to be the easiest from what i can see.


For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2
Greg Snidow
Greg Snidow
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4383 Visits: 2494
memymasta (11/30/2012)
Ive almost got it to work... but i fail on the joining step where i join my CTE with a derived column...


When you say you fail, what exactly does that mean? In your ON criteria, you've got...

ON myCTE.c=#a.CAST(YEAR(#a.recorddate)



change it to (take out the extra '#a.' alias, and leave the rest the same)...

ON myCTE.c=CAST(YEAR(#a.recorddate)



And it worked for me. The only differece being there are NULL's where you have zero's in your mock results. It looks like there are some better suggestions any way, but I was just curious if this was the only 'failure' you were experiencing.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
memymasta
memymasta
Mr or Mrs. 500
Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)

Group: General Forum Members
Points: 507 Visits: 402
@Cadavre - Thats that did the trick!

@CELKO - Very useful insight, i like it. One thing i don't understand is:
That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year.

Do you store that as date?
SELECT CAST('2012-01-00' AS DATE)


This don't seem to work.

@Greg Snidow - Ah yes! Your small change made it work. Updated the code a bit and it looks really good now.


CREATE TABLE [#a](
[id] [int] NOT NULL,
[recorddate] [date] NULL,
[myvalue] [int] NULL,
) ON [PRIMARY]

GO

INSERT INTO [#a] (id, [recorddate], [myvalue])
SELECT 1,'2012-01-02',10 UNION ALL
SELECT 2,'2012-01-13',20 UNION ALL
SELECT 3,'2012-02-02',30 UNION ALL
SELECT 4,'2012-02-24',40 UNION ALL
SELECT 5,'2012-08-02',50 UNION ALL
SELECT 6,'2012-12-01',60 UNION ALL
SELECT 7,'2012-12-28',70
GO
WITH myCTE (c)
AS
(
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+1,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+2,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+3,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+4,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+5,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+6,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+7,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+8,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+9,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+10,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+11,0)
)
SELECT DISTINCT c, ISNULL(SUM(myvalue),0) as myValue
FROM myCTE LEFT JOIN #a ON myCTE.c=CAST(YEAR(#a.recorddate) as CHAR(4))+'-'+CAST(MONTH(#a.recorddate) as CHAR(2))+'-'+'01'
GROUP BY c
GO
DROP TABLE #a


CapnHector
CapnHector
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3321 Visits: 1789
memymasta (12/4/2012)
@Cadavre - Thats that did the trick!

@CELKO - Very useful insight, i like it. One thing i don't understand is:
That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year.

Do you store that as date?
SELECT CAST('2012-01-00' AS DATE)


This don't seem to work.

@Greg Snidow - Ah yes! Your small change made it work. Updated the code a bit and it looks really good now.


CREATE TABLE [#a](
[id] [int] NOT NULL,
[recorddate] [date] NULL,
[myvalue] [int] NULL,
) ON [PRIMARY]

GO

INSERT INTO [#a] (id, [recorddate], [myvalue])
SELECT 1,'2012-01-02',10 UNION ALL
SELECT 2,'2012-01-13',20 UNION ALL
SELECT 3,'2012-02-02',30 UNION ALL
SELECT 4,'2012-02-24',40 UNION ALL
SELECT 5,'2012-08-02',50 UNION ALL
SELECT 6,'2012-12-01',60 UNION ALL
SELECT 7,'2012-12-28',70
GO
WITH myCTE (c)
AS
(
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+1,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+2,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+3,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+4,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+5,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+6,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+7,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+8,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+9,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+10,0)
UNION ALL
SELECT DATEADD(mm, DATEDIFF(yy, 0, GETDATE())*12+11,0)
)
SELECT DISTINCT c, ISNULL(SUM(myvalue),0) as myValue
FROM myCTE LEFT JOIN #a ON myCTE.c=CAST(YEAR(#a.recorddate) as CHAR(4))+'-'+CAST(MONTH(#a.recorddate) as CHAR(2))+'-'+'01'
GROUP BY c
GO
DROP TABLE #a



A couple things i may change. first is your calendar table, what happens when you want to start at a different date. the version below to me has a smaller maintenance area when it comes to changing the date range you want in the report. the second change is the ON clause, i replaced your string concatenation to some date math to get the first of the month so we have like data types.

WITH cteTally(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(N)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))y(N)),

myCTE (c) AS (SELECT TOP 12 DATEADD(MM,N,DATEADD(YY, DATEDIFF(YY,0,GETDATE()),0))
FROM cteTally)

SELECT c, ISNULL(SUM(myvalue),0) as myValue
FROM myCTE
LEFT JOIN #a
ON myCTE.c = DATEADD(MM,DATEDIFF(MM,0,#a.recorddate),0)
GROUP BY c



EDIT: had Tally (my persisted tally table) and not the cteTally.

If you have no idea what a tally table is check out this great article by Jeff Moden http://www.sqlservercentral.com/articles/T-SQL/62867/


For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2
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