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

Query Help - difference between last and current year Expand / Collapse
Author
Message
Posted Thursday, February 11, 2010 6:08 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 11:37 AM
Points: 328, Visits: 843
hi
Thanx a lot, I can see the variance now, but every count is shown in a different column I want everything YTD2009 / YTD2010 / and Variance in same Column, As I have to use it in a group column, Can you make it all in same column ....
Thanks again



---------------------------------------------------

Thanks
Post #864423
Posted Thursday, February 11, 2010 6:43 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:53 PM
Points: 22,511, Visits: 30,236
Not to be a kill joy, but don't you think you should look at the code provided and see if you can make the necessary changes to format the output to your requirements.

We don't mind helping, but, you need to remember that most of us are volunteers giving of our own time and effort to help you. You really shouldn't treat as free contract labor.

If you have problems making the changes, show us what you have done and we will be glad to assist you.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #864426
Posted Friday, February 12, 2010 6:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 11:37 AM
Points: 328, Visits: 843
ok let me try, anyways thanx a lot


---------------------------------------------------

Thanks
Post #864581
Posted Friday, February 12, 2010 7:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 11:37 AM
Points: 328, Visits: 843
Hey Thanks Jeffery ...

I just used unpivot in the query you provided and got it the way I wanted in one column....Thanks again


Declare @curYearStart datetime
,@prevYearStart datetime;

Set @curYearStart = dateadd(year, datediff(year, 0, getdate()), 0);
Set @prevYearStart = dateadd(year, -1, @curYearStart);





With currentYTD (Area, Source, Total)
As
(
Select Area
,Src
,count(Distinct Account_No)
From #dim1
Where Filter_Code Not In ('4', '9', 'Unknown')
And [Date] >= @curYearStart
And [Date] < dateadd(day, datediff(day, 0, getdate()), 0)
Group By Area,Src
), previousYTD (Area, Source, Total)
As (Select Area
,Src
,count(Distinct Account_No)
From #dim1
Where Filter_Code Not In ('4', '9', 'Unknown')
And [Date] >= @prevYearStart
And [Date] < @curYearStart
Group By Area
,Src
)

SELECT area,source,heading, cnt
FROM
(
Select curYTD.Area
,curYTD.Source
,curYTD.Total As 'YTD 2010'
,prevYTD.Total As 'YTD 2009'
,(curYTD.Total - prevYTD.Total) As Variance
From currentYTD curYTD
Join previousYTD prevYTD On prevYTD.Area = curYTD.Area
And prevYTD.Source = curYTD.Source

) p

UNPIVOT
(cnt FOR heading IN
([YTD 2010],[YTD 2009],Variance)
)AS unpvt




---------------------------------------------------

Thanks
Post #864608
Posted Friday, February 12, 2010 7:23 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:53 PM
Points: 22,511, Visits: 30,236
Great. Now, keep an eye on this thread as you may find others that may show you another way to accomplish the task as well. This will give you something to compare against your solution.


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #864620
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse