Report using two dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Report using two dates

Author

Topic

Chamark
Starting Member

28 Posts

Posted - 2010-08-22 : 19:01:55

I have a report that I need to create that provides data for two different dates that are in one table:

CREATE TABLE [dbo].[t_kpi_Data](
[DataSource_ID] [int] NOT NULL,
[YearMonth] [datetime] NOT NULL,
[Actual_Month] [float] NULL,
[Forecast_Month] [float] NULL,
[Target_Month] [float] NULL,
[Actual_YTD] [float] NULL,
[Forecast_YTD] [float] NULL,
[Target_YTD] [float] NULL,

I tried using a sub query

Select Actual_Month, Target_Month,

(Select Actual_YTD, Target_YTD
From t_kpi_data
Where Yearmonth = '06/01/2009' AND Datasource_ID = '629')

From t_kpi_Data
Where Yearmonth = '06/01/2010' AND DataSource_ID = '629'

And I get this error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Looking for direction on how to to write this query - thanks for any help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-23 : 03:08:13

Select Actual_Month, Target_Month,case when Yearmonth = '06/01/2009' then Actual_YTD end,
case when Yearmonth = '06/01/2010' then Target_YTD end
From t_kpi_Data
Where Yearmonth = '06/01/2010' AND DataSource_ID = '629'

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 05:00:26

[code]Select d.Actual_Month, d.Target_Month,
k1.Actual_YTD, k1.Target_YTD
From t_kpi_Data d
INNER JOIN (Select Actual_YTD, Target_YTD
From t_kpi_data) k1
ON k1.Yearmonth = DATEADD(yy,-1,d.Yearmonth)
AND k1.Datasource_ID = d.Datasource_ID
Where d.Yearmonth = '06/01/2010'
AND d.DataSource_ID = '629'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Chamark
Starting Member

28 Posts

Posted - 2010-08-23 : 08:54:51

Thanks to both of you Madhivanan & visakh16 - I really appreciate the help with this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 08:57:11

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources