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 12»»

problem inner joininig a derived column Expand / Collapse
Author
Message
Posted Friday, November 30, 2012 8:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 4, 2013 6:26 AM
Points: 99, Visits: 402
Hi, I'm trying to get a nice chart working, that gets data from SQL Server, but having trouble getting the right data...

I have a table, that have a value and a date... I want to sum all the values and group them by 12 months of the current year.

Se lets say i got the table [#a]... With id, recorddate and myvalue columns as shown below.

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

And i would like to get this result...
'2012-01-01' || '30'
'2012-02-01' || '70'
'2012-03-01' || '0'
'2012-04-01' || '0'
'2012-05-01' || '0'
'2012-06-01' || '0'
'2012-07-01' || '0'
'2012-08-01' || '50'
'2012-09-01' || '0'
'2012-10-01' || '0'
'2012-11-01' || '0'
'2012-12-01' || '130'

Ive almost got it to work... but i fail on the joining step where i join my CTE with a derived column... as seen below:

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


WITH myCTE (c)
AS
(
SELECT CAST(CAST(YEAR(GETDATE()) AS CHAR(4))+'-1-1' AS DATE) AS c
UNION ALL
SELECT CAST(CAST(YEAR(GETDATE()) AS CHAR(4))+'-2-1' AS DATE) AS c
UNION ALL
SELECT CAST(CAST(YEAR(GETDATE()) AS CHAR(4))+'-3-1' AS DATE) AS c
UNION ALL
SELECT CAST(CAST(YEAR(GETDATE()) AS CHAR(4))+'-4-1' AS DATE) AS c
UNION ALL
SELECT CAST(CAST(YEAR(GETDATE()) AS CHAR(4))+'-5-1' AS DATE) AS c
UNION ALL
SELECT CAST(CAST(YEAR(GETDATE()) AS CHAR(4))+'-6-1' AS DATE) AS c
UNION ALL
SELECT CAST(CAST(YEAR(GETDATE()) AS CHAR(4))+'-7-1' AS DATE) AS c
UNION ALL
SELECT CAST(CAST(YEAR(GETDATE()) AS CHAR(4))+'-8-1' AS DATE) AS c
UNION ALL
SELECT CAST(CAST(YEAR(GETDATE()) AS CHAR(4))+'-9-1' AS DATE) AS c
UNION ALL
SELECT CAST(CAST(YEAR(GETDATE()) AS CHAR(4))+'-10-1' AS DATE) AS c
UNION ALL
SELECT CAST(CAST(YEAR(GETDATE()) AS CHAR(4))+'-11-1' AS DATE) AS c
UNION ALL
SELECT CAST(CAST(YEAR(GETDATE()) AS CHAR(4))+'-12-1' AS DATE) AS c
)
SELECT DISTINCT c, SUM(myvalue)
FROM myCTE LEFT JOIN #a ON myCTE.c=#a.CAST(YEAR(#a.recorddate) as CHAR(4))+'-'+CAST(MONTH(#a.recorddate) as CHAR(2))+'-'+'01'
GROUP BY c

DROP TABLE #a


Is it possible to achieve it this way?
Post #1391360
Posted Friday, November 30, 2012 8:51 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:52 PM
Points: 504, Visits: 1,870
Try building a calendar table, joining on your dates. Then you can aggregate by month using the month values in the calendar table. See the following article for details about how to use calendar tables.
Post #1391392
Posted Friday, November 30, 2012 8:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:07 AM
Points: 2,386, Visits: 7,609
Not currently at my desk so the syntax might need correcting, but could you do something like this?

WITH CTE(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),
CTE2(N) AS (SELECT 1 FROM CTE x CROSS JOIN CTE y),
CTE3(N) AS (SELECT 1 FROM CTE2 x CROSS JOIN CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x CROSS JOIN CTE3 y),
CTE5(N) AS (SELECT 1 FROM CTE4 x CROSS JOIN CTE4 y),
CTE6(N) AS (SELECT 0 UNION ALL
SELECT TOP 97199 ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM CTE5),
TALLY(N) AS (SELECT DATEADD(month, N, 0)
FROM CTE6)
SELECT a.N AS recorddate, ISNULL(b.myValue,0) AS myValue
FROM TALLY a
OUTER APPLY (SELECT DATEADD(month, DATEDIFF(month, 0, [recorddate]), 0), SUM([myvalue])
FROM [#a] c
WHERE DATEADD(month, DATEDIFF(month, 0, [recorddate]), 0) = a.N
GROUP BY DATEADD(month, DATEDIFF(month, 0, [recorddate]), 0)
) b(recorddate,myValue)
CROSS APPLY (SELECT MIN(DATEADD(month, DATEDIFF(month, 0, [recorddate]), 0)),
MAX(DATEADD(month, DATEDIFF(month, 0, [recorddate]), 0))
FROM [#a]) d(minDate,maxDate)
WHERE a.N >= d.minDate AND a.N <= d.maxDate;




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1391395
Posted Friday, November 30, 2012 9:05 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:52 PM
Points: 504, Visits: 1,870
To follow-up my earlier suggestion, note that Cadavre's approach uses a tally table. A calendar table is a specialized version of a tally table.

It's a powerful tool. In addition to the article on calendar tables, you may find the following articles about tally tables give you a helpful addition to your querying tools.

Jeff Moden on Tally Tables

Stefan Krzywicki on the use of Tally Tables - I

Stefan Krzywicki on the use of Tally Tables - II
Post #1391402
Posted Friday, November 30, 2012 9:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:07 AM
Points: 2,386, Visits: 7,609
kl25 (11/30/2012)
To follow-up my earlier suggestion, note that Cadavre's approach uses a tally table. A calendar table is a specialized version of a tally table.


In fact, my CTE tally table is a calendar table (note: TALLY(N) AS (SELECT DATEADD(month, N, 0) FROM CTE6) ) of months starting at 1900-01-01 and ending with 9999-12-01 (total is 97,200 rows, so I probably went overboard with the CTEs that build the sequential number lists )



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1391407
Posted Friday, November 30, 2012 9:16 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:52 PM
Points: 504, Visits: 1,870
In fact, my CTE tally table is a calendar table (note: TALLY(N) AS (SELECT DATEADD(month, N, 0) FROM CTE6) ) of months starting at 1900-01-01 and ending with 9999-12-01 (total is 97,200 rows, so I probably went overboard with the CTEs that build the sequential number lists )


You're right. Given the approach used in the initial query, it didn't seem that the OP was familiar with calendar or tally tables. Just wanted to provide further background using the articles cited. You've given the OP a great example of why tally and calendar tables are powerful tools.
Post #1391413
Posted Monday, December 3, 2012 6:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 4, 2013 6:26 AM
Points: 99, Visits: 402

In fact, my CTE tally table is a calendar table (note: TALLY(N) AS (SELECT DATEADD(month, N, 0) FROM CTE6) ) of months starting at 1900-01-01 and ending with 9999-12-01 (total is 97,200 rows, so I probably went overboard with the CTEs that build the sequential number lists [Tongue] )


Thanks once again for an excellent reply. The table worked well with the real data. However there's some weird hiccups with the query. If i execute the query multiple times, it goes into for ever executing mode... i.e cant complete the execution.


Here's the slightly modified query:

WITH CTE(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a(N)),
CTE2(N) AS (SELECT 1 FROM CTE x CROSS JOIN CTE y),
CTE3(N) AS (SELECT 1 FROM CTE2 x CROSS JOIN CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x CROSS JOIN CTE3 y),
CTE5(N) AS (SELECT 1 FROM CTE4 x CROSS JOIN CTE4 y),
CTE6(N) AS (SELECT 0 UNION ALL
SELECT TOP 97199 ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM CTE5),
TALLY(N) AS (SELECT DATEADD(month, N, 0)
FROM CTE6)
SELECT a.N AS recorddate, ISNULL(b.myValue,0) AS myValue
FROM TALLY a
OUTER APPLY (SELECT DATEADD(month, DATEDIFF(month, 0, f.recorddate), 0), SUM(e.numvalue)
FROM admin_tasks as e INNER JOIN admin_tasks as f ON e.id=f.numvalue
WHERE DATEADD(month, DATEDIFF(month, 0, f.recorddate), 0) = a.N AND f.actionid=5 AND f.numtype=1
GROUP BY DATEADD(month, DATEDIFF(month, 0, f.recorddate), 0)
) b(recorddate,myValue)
CROSS APPLY (SELECT MIN(DATEADD(month, DATEDIFF(month, 0, h.recorddate), 0)),
MAX(DATEADD(month, DATEDIFF(month, 0, h.recorddate), 0))
FROM admin_tasks as g INNER JOIN admin_tasks as h ON g.id=h.numvalue WHERE h.actionid=5 AND h.numtype=1) d(minDate,maxDate)
WHERE a.N >= d.minDate AND a.N <= d.maxDate
ORDER BY recorddate ASC


And here's the statistics IO & time

(11 row(s) affected)
Table 'admin_tasks'. Scan count 17, logical reads 8099, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 11, logical reads 28546, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 187 ms, elapsed time = 257 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.



Any thought on why the query stagnates?

UPDATE: Checked some variables and getting 97k rows was a slight overkill. Changing it to 2050 rows made a big difference, and no more hickups. :)
Post #1391913
Posted Monday, December 3, 2012 8:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 4, 2013 6:26 AM
Points: 99, Visits: 402
A followup question: if i only need the years span of 2000-2050, how do i select them instead of

SELECT TOP 97199 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM CTE5 

Thanks
Post #1391989
Posted Monday, December 3, 2012 9:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:07 AM
Points: 2,386, Visits: 7,609
memymasta (12/3/2012)
A followup question: if i only need the years span of 2000-2050, how do i select them instead of

SELECT TOP 97199 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM CTE5 

Thanks


Well, there are 611 months between 2000-01-01 and 2050-12-01, so you'd want to change the code to something like this: -
CTE6(N) AS (SELECT 0 UNION ALL
SELECT TOP 611 ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM CTE5),
TALLY(N) AS (SELECT DATEADD(month, N, CAST('2000' AS DATETIME))
FROM CTE6)

Note the change to the "tally" section, which starts the calendar at 2000-01-01 instead of the previous version which started at 1900-01-01.


--EDIT--
memymasta (12/3/2012)
Any thought on why the query stagnates?

UPDATE: Checked some variables and getting 97k rows was a slight overkill. Changing it to 2050 rows made a big difference, and no more hickups. :)

Without seeing an actual execution plan, no. I'd imagine it'll be an indexing issue. You could also try changing the CTE to a physical table of all of the months, which when properly indexes may give some performance boost.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1392029
Posted Monday, December 3, 2012 10:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:29 PM
Points: 1,945, Visits: 3,121

Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise.

CREATE TABLE Report_Periods
(report_name VARCHAR(30) NOT NULL PRIMARY KEY,
report_start_date DATE NOT NULL,
report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (report_start_date <= report_end_date),
etc);

These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL.

SELECT R.report_name, SUM(F.my_value) AS month_value_tot
FROM Foobar, Report_Periods AS R
WHERE F.recording_date
BETWEEN R.report_start_date AND R.report_end_date
GROUP BY R.report_name;



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1392051
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse