Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » problem inner joininig a derived column
Add to briefcase
15 posts, Page 2 of 2
««12

problem inner joininig a derived column Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Michael Valentine Jones
Posted Monday, December 03, 2012 10:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 2,945, Visits: 10,517
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
Post #1392068
CapnHector
Posted Monday, December 03, 2012 11:34 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:07 AM
Points: 935, Visits: 1,709
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

Jeremy Oursler
Post #1392094
Greg Snidow
Posted Monday, December 03, 2012 3:29 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:41 AM
Points: 1,561, Visits: 2,316
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.
Post #1392166
memymasta
Posted Tuesday, December 04, 2012 2:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:15 AM
Points: 99, Visits: 399
@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
Post #1392291
CapnHector
Posted Tuesday, December 04, 2012 10:50 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:07 AM
Points: 935, Visits: 1,709
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

Jeremy Oursler
Post #1392619
« Prev Topic | Next Topic »

Add to briefcase
15 posts, Page 2 of 2
««12

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.