Multiple Dates - One Query - 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

Multiple Dates - One Query

Author

Topic

Chamark
Starting Member

28 Posts

Posted - 2010-08-19 : 12:20:07

Beginner using SQL2008 - Attempting to get this result out of one query. Multiple dates are required. Would like to know if this approach is best or try something else? I get syntax errors near k,t,v and don't know why as well as multi-part indentifer not bound on k.Actual_YTD ? Any help or suggestions are greatly appreciated.

Result should look like this:

[Category][KPI][YTD Actual][YTD Target][% Diff][YTD 2009][Month Actual][Month Target][Month 2009]

Code

SELECT t_kpi_Categories.CatName AS Category,
t_kpi_DataSources_AliasNames.AliasName AS KPI,
k.Actual_YTD AS [YTD Actual],
t_kpi_Data.Target_YTD AS [YTD Target],
(NULLIF(k.[Actual_YTD],0) - NULLIF([Target_YTD],0)) / t_kpi_Data.Target_YTD * t_kpi_DataSources.CompareUpDown AS [% Diff],
v.Actual_YTD AS [YTD 2009],
k.[Actual_Month] AS [Month Actual],
[Target_Month] AS [Month Target],
t.Actual_Month AS [Month 2009 Actual],
t_kpi_DataSources.CompareUpDown AS UD,
t_kpi_DataSources.Units_ID,
t_kpi_DataSources.DecimalPlaces

FROM t_kpi_DataSources INNER JOIN
t_kpi_Data AS t_kpi_Data ON t_kpi_DataSources.ID = t_kpi_Data.DataSource_ID INNER JOIN
t_kpi_SAT_SCMeta ON t_kpi_Data.DataSource_ID = t_kpi_SAT_SCMeta.TDAF_ID INNER JOIN
t_kpi_Categories ON t_kpi_SAT_SCMeta.Cat_ID = t_kpi_Categories.CatID INNER JOIN
t_kpi_DataSources_AliasNames ON t_kpi_SAT_SCMeta.TDAF_ID = t_kpi_DataSources_AliasNames.AliasID

k OUTER APPLY
(SELECT Actual_Month
FROM [SPARS].[dbo].[t_kpi_Data]
WHERE YearMonth = DATEADD(yy, - 1, k.YearMonth) )

t OUTER APPLY
(SELECT Actual_YTD
FROM [SPARS].[dbo].[t_kpi_Data]
WHERE YearMonth = DATEADD(yy, - 1, k.YearMonth) )

v WHERE t_kpi_SAT_SCMeta.SC_Name LIKE '%DB Exec%' AND k.YearMonth = '06/01/2010'
ORDER BY t_kpi_SAT_SCMeta.order_of_appearance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-19 : 13:17:55

i think you've an extra alias k. No need of that since you're not enclosing main query in a derived table.

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

Chamark
Starting Member

28 Posts

Posted - 2010-08-19 : 16:07:01

Thanks for getting back to me visakh16. Still pretty new at this. When you say extra alias k what are you referencing?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-20 : 05:31:26

quote:
Originally posted by Chamark

Thanks for getting back to me visakh16. Still pretty new at this. When you say extra alias k what are you referencing?

The k before the first OUTER APPLY

Madhivanan

Failing to plan is Planning to fail

Chamark
Starting Member

28 Posts

Posted - 2010-08-20 : 10:29:40

Thanks madhivanan. Obviously I am in over my head - but that's the way we learn - I was attempting to use alias' and the OUTER APPLY to get my dates - both current and previous year in one query for all categories. I tried sub queries but found I was limited to one result - so I need some direction. Again thanks to you and visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 11:09:16

can you state your requirement with some sample data? you've a few joins to get current data. wont you require same conditions while finding last year data also?

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

Chamark
Starting Member

28 Posts

Posted - 2010-08-20 : 12:04:34

It would be like creating multipe Select statements in one query to get different dates of same column - to get these results

YTD June 2010 Year End 2010 (YTD)
-----------------------------------------------------------------------------------------------------------------------------
Category | actual | target | % Diff | 2009 |Actual| Target | 2009 |Projection (Dec) | Target(Dec) | YE 2009 (Dec)
Income $123 $128 x% $135 $10 $11 $13 $143 $153 $148
Cost $xxx $xxx x% $xxx $xx $xx $xx $xxx $xxx $xxx

Category is in one table and data is in another table - data columns are:

Actual_Month, Forecast_month, Target_Month, Actual_YTD, Forecast_YTD, Target_YTD - I need data for a particular month for 2010 & previous year 2009. I just need to know the structure of the query to get these results. Again thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 12:12:57

[code]
SELECT t_kpi_Categories.CatName AS Category,
t_kpi_DataSources_AliasNames.AliasName AS KPI,
t.Actual_YTD AS [YTD Actual],
t_kpi_Data.Target_YTD AS [YTD Target],
(NULLIF(k.[Actual_YTD],0) - NULLIF([Target_YTD],0)) / t_kpi_Data.Target_YTD * t_kpi_DataSources.CompareUpDown AS [% Diff],
t.Actual_YTD AS [YTD 2009],
<main table alis here>.[Actual_Month] AS [Month Actual],
[Target_Month] AS [Month Target],
t.Actual_Month AS [Month 2009 Actual],
t_kpi_DataSources.CompareUpDown AS UD,
t_kpi_DataSources.Units_ID,
t_kpi_DataSources.DecimalPlaces

FROM t_kpi_DataSources INNER JOIN
t_kpi_Data AS t_kpi_Data ON t_kpi_DataSources.ID = t_kpi_Data.DataSource_ID INNER JOIN
t_kpi_SAT_SCMeta ON t_kpi_Data.DataSource_ID = t_kpi_SAT_SCMeta.TDAF_ID INNER JOIN
t_kpi_Categories ON t_kpi_SAT_SCMeta.Cat_ID = t_kpi_Categories.CatID INNER JOIN
t_kpi_DataSources_AliasNames ON t_kpi_SAT_SCMeta.TDAF_ID = t_kpi_DataSources_AliasNames.AliasID
OUTER APPLY
(SELECT Actual_Month,Actual_YTD
FROM [SPARS].[dbo].[t_kpi_Data]
WHERE YearMonth = DATEADD(yy, - 1, YearMonth) )t
WHERE t_kpi_SAT_SCMeta.SC_Name LIKE '%DB Exec%' AND YearMonth = '06/01/2010'
ORDER BY t_kpi_SAT_SCMeta.order_of_appearance
[/code]

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

Chamark
Starting Member

28 Posts

Posted - 2010-08-20 : 13:06:15

I'm sorry to be a pain - I know I am close on this - what would be the main table alias here be?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 13:16:04

i dont know. it depends on what table that column comes from which you alone are aware of

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

Chamark
Starting Member

28 Posts

Posted - 2010-08-20 : 13:51:13

OK I put in the table name and got everything except the 2009 columns - both YTD & Month - returns NULLs -
OUTER APPLY
(SELECT Actual_Month,Actual_YTD
FROM [SPARS].[dbo].[t_kpi_Data]
WHERE YearMonth = DATEADD(yy, - 1, YearMonth) )t
WHERE t_kpi_SAT_SCMeta.SC_Name LIKE '%DB Exec%' AND YearMonth = '06/01/2010'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 14:01:13

are you sure table has data for previous year?

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

Chamark
Starting Member

28 Posts

Posted - 2010-08-20 : 14:20:28

yes - actual_month for 2009 has -0.21086 and actual_YTD 2009 has -4.681

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources