June 23, 2015 at 10:21 am
yes you are right
June 23, 2015 at 10:31 am
It would be helpful to get the output here, and provide a sample table.
create table mydata (month, year, data)
insert mydata select 07, 2015, 3)
...
That makes it easier for us to discuss what you need and show results that work.
If you have the year, order by year and then month. That should get you a rolling year for the top 12.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 23, 2015 at 10:41 am
sqlinterset (6/23/2015)
yes you are right
Does this help?
create table #cs
(
[Year] float,
[Week] float,
[Month] float,
[C#] float,
[Dept] nvarchar(255),
[Issue] nvarchar(255),
[Type] nvarchar(255),
[Dept age] nvarchar(255)
);
INSERT INTO #cs
([Year], [Week], [Month], [C#], [Dept], [Issue], [Type], [Dept age])
SELECT 2015, 14, 4, 188, 'D1', 'I1', 'T1', 5 UNION ALL
SELECT 2015, 14, 4, 452, 'd1', 'I1', 'T2', 5 UNION ALL
SELECT 2015, 14, 4, 63, 'd1', 'I1', 'T1', 6 UNION ALL
SELECT 2015, 14, 4, 9, 'd1', 'I2', 'T1', 7 UNION ALL
select 2015, 14, 4, 11, 'd1', 'i8', 't4', 10 UNION ALL
SELECT 2014, 14, 4, 187, 'D1', 'I1', 'T1', 5 UNION ALL
SELECT 2014, 14, 4, 451, 'd1', 'I1', 'T2', 5 UNION ALL
SELECT 2014, 14, 4, 62, 'd1', 'I1', 'T1', 6 UNION ALL
SELECT 2014, 14, 4, 10, 'd1', 'I2', 'T1', 7 UNION ALL
SELECT 2014, 15, 4, 10, 'd1','I2', 'T4', 7 UNION ALL
SELECT 2013, 15, 4, 111, 'd1','I2', 'T4', 7 UNION ALL
select 2014, 14, 4, 199, 'd1', 'i3', 't1', 8 UNION ALL
Select 2013, 14, 4, 18, 'd1', 'i8', 't4', 10;
--select * from #cs;
with DateRange1 as (
select distinct
[Year],[Month],[Week]
from
#cs),
DateRange as (
select
[Year],[Month],[Week],
rn = row_number() over (order by [Year] desc,[Month] desc,[Week] desc)
from
DateRange1),
MaxDate as (
select
[Year] MaxYear, [Year] - 2 MinYear, [Month] CMonth, [Week] CWeek
from
DateRange
where
rn = 1),
DataElements as (
select distinct
[Dept], [Issue], [Type], [Dept age]
from
#cs
cross join MaxDate
where
([Year] = MaxYear and [Month] <= CMonth and [Week] <= CWeek) or
([Year] = MinYear and [Month] >= CMonth and [Week] > CWeek) or
([Year] < MaxYear and [Year] > 2013)
),
BaseElements as (
select distinct
[Year], [Month], [Week], [Dept], [Issue], [Type], [Dept age]
from
DateRange1
cross join DataElements
cross join MaxDate
where
([Year] = MaxYear and [Month] <= CMonth and [Week] <= CWeek) or
([Year] = MinYear and [Month] >= CMonth and [Week] > CWeek) or
([Year] < MaxYear and [Year] > 2013)
), BaseData as (
select
be.*,
cs.[C#],
md.MaxYear
from
BaseElements be
left outer join #cs cs
on (be.[Year] = cs.[Year] and
be.[Month] = cs.[Month] and
be.[Week] = cs.[Week] and
be.Dept = cs.Dept and
be.Issue = cs.Issue and
be.[Type] = cs.[Type] and
be.[Dept age] = cs.[Dept age])
cross join MaxDate md
), FinalData as (
select
bd1.[Year],
bd1.[Week],
bd1.[Month],
bd1.Dept,
bd1.Issue,
bd1.[Type],
bd1.[Dept age],
bd1.[C#] C1,
bd2.[C#] C2
from
BaseData bd1
inner join BaseData bd2
on (bd1.[Month] = bd2.[Month] and
bd1.[Week] = bd2.[Week] and
bd1.Dept = bd2.Dept and
bd1.Issue = bd2.Issue and
bd1.[Type] = bd2.[Type] and
bd1.[Dept age] = bd2.[Dept age] and
bd1.[Year] = bd1.MaxYear and
bd2.[Year] = bd1.MaxYear - 1)
union all
select
bd1.[Year],
bd1.[Week],
bd1.[Month],
bd1.Dept,
bd1.Issue,
bd1.[Type],
bd1.[Dept age],
bd1.[C#] C1,
bd2.[C#] C2
from
BaseData bd1
inner join BaseData bd2
on (bd1.[Month] = bd2.[Month] and
bd1.[Week] = bd2.[Week] and
bd1.Dept = bd2.Dept and
bd1.Issue = bd2.Issue and
bd1.[Type] = bd2.[Type] and
bd1.[Dept age] = bd2.[Dept age] and
bd1.[Year] = bd1.MaxYear - 1 and
bd2.[Year] = bd1.MaxYear - 2)
)
select
[Year],
[Week],
[Month],
[Dept],
[Issue],
[Type],
[Dept age],
isnull(C1,0) C1,
isnull(C2,0) C2
from FinalData
where
C1 is not null or C2 is not null
order by
[Year],[Week]; -- << See the ORDER BY
go
June 23, 2015 at 10:53 am
Actually ordering in ssms is being done through this way. But when i am pasting same query in excel pivot tables then this is giving data in normal order as from Jan to dec though i want from July to Dec and then Jan to Jun.
June 23, 2015 at 10:58 am
sqlinterset (6/23/2015)
Actually ordering in ssms is being done through this way. But when i am pasting same query in excel pivot tables then this is giving data in normal order as from Jan to dec though i want from July to Dec and then Jan to Jun.
Sorry, but Excel is one thing I know very little about.
June 23, 2015 at 11:02 am
sqlinterset (6/23/2015)
Actually ordering in ssms is being done through this way. But when i am pasting same query in excel pivot tables then this is giving data in normal order as from Jan to dec though i want from July to Dec and then Jan to Jun.
just a thought
add a new column to your data source.......use sql code to set it to 1 for Jul-Dec months and 2 for others....ose this as prt of your pivot table
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 23, 2015 at 11:10 am
sqlinterset (6/23/2015)
Actually ordering in ssms is being done through this way. But when i am pasting same query in excel pivot tables then this is giving data in normal order as from Jan to dec though i want from July to Dec and then Jan to Jun.
Are you pasting in the year? Without that, you can't pivot appropriately.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 23, 2015 at 11:14 am
Please see attachement
June 23, 2015 at 11:16 am
No. There will always be 52 rows (as 52 weeks are there). In coding i can use current month.
June 23, 2015 at 11:24 am
sqlinterset (6/23/2015)
Please see attachement
That is the same thing you posted earlier. Without the YEAR in the mix you aren't going to get the sort order the way you want it.
June 23, 2015 at 11:36 am
sqlinterset (6/23/2015)
Please see attachement
what version of excel are you using?
in 2013 you can manually move rows to sort your requirements
and to echo others...without a YEAR this may prove troublesome for you...what are you expecting when we get to July this year?.....or is your data source only ever a rolling 52 week extract?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 23, 2015 at 11:40 am
J Livingston SQL (6/23/2015)
sqlinterset (6/23/2015)
Please see attachementwhat version of excel are you using?
in 2013 you can manually move rows to sort your requirements
and to echo others...without a YEAR this may prove troublesome for you...what are you expecting when we get to July this year?.....or is your data source only ever a rolling 52 week extract?
Unless you include the year in your query or consider it when creating a custom column to sort on, you aren't going to have anything to sort by in your Excel file unless you do it manually.
June 24, 2015 at 1:11 am
declare @currentWeek int =26
select ...
from (...) as t -- selects exactly 52 rows you need
order by case when @currentweek >= t.week then 100+t.week else t.week
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply