June 19, 2015 at 12:57 pm
Data will be selected from the table itself but C1 and C2 will be different and those values will be as explained in attached excel file.
Thanks
June 19, 2015 at 1:00 pm
sqlinterset (6/19/2015)
Data will be selected from the table itself but C1 and C2 will be different and those values will be as explained in attached excel file.Thanks
WITH WHAT FOR THE CRITERIA?????? Are we supposed to select ALL the data from the table??????
June 19, 2015 at 1:03 pm
Yes for all the data.
June 19, 2015 at 1:07 pm
sqlinterset (6/19/2015)
Yes for all the data.
Okay, so your actual table may have 10 years of data and you want us to use NOTHING for the WHERE criteria, correct??
June 19, 2015 at 1:07 pm
I am new here. Earlier posted way was told somewhere post, so i pasted like that. So below has improved format:
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
June 19, 2015 at 1:13 pm
IF your actual production table has data for the PAST 10 years, do you still want us to write a query that when run against this table will pull all ten years of data or is there some criteria we should use that will reduce the amount of data processed?????
June 19, 2015 at 1:14 pm
yes. typically my table has just data from 2012.
June 19, 2015 at 1:18 pm
my production table also has data from 2012 only. These table are just for data analysis so we are just pulling data from 2012.
June 19, 2015 at 1:22 pm
sqlinterset (6/19/2015)
my production table also has data from 2012 only. These table are just for data analysis so we are just pulling data from 2012.
What does 2012 have to with anything? Based on what you have posted the only data that should show up is 2015, 2014, and maybe 2013? There HAS to be some sort of criteria by which the data is being selected. A date range of some sort.
June 19, 2015 at 1:25 pm
Ok. Mostly data will be either from 2014 or 2015. so we can have where condition which just includes these two years.
June 19, 2015 at 1:25 pm
This query is probably pretty simple once we know what it is supposed to do. The biggest problem here is that you don't have a well defined question. Without a decent question we can't provide a decent answer. You might want to take a look at this article. http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2015 at 1:37 pm
I am not sure how better i can write :unsure::(
June 19, 2015 at 1:49 pm
Is there any other way i can explain my query? or if somebody is trying please respond.
June 19, 2015 at 2:05 pm
Based on this query i am trying to modify it for those case where C# (18) for 2015 exist say for (w5, d1, i1, t1, 8) but 2014 data doesn't for (w5, d1, i1, t1, 8) then in this case c1 should be 18 and and c2 should be 0.
Note: data for 2015 and 2014 exist for (w5, d1, i2, t2, 8)
WITH cteCs AS(
select *,
ROW_NUMBER() OVER(PARTITION BY [Week], [Month], [Dept], [Issue], [Type], [Dept age] ORDER BY [Year] DESC) rn
from #cs
)
SELECT [Week],
[Month],
CASE WHEN MAX( CASE WHEN rn = 2 THEN [C#] END) IS NULL
THEN
case
when (select count(*) from cteCs c2 where c2.[Year]=cteCs.[Year]+1 and c2.[Week]=cteCs.[Week] ) >0
then 0
ELSE MAX( CASE WHEN rn = 1 THEN [C #] END)
END
ELSE MAX( CASE WHEN rn = 1 THEN [C#] END)
ELSE MAX( CASE WHEN rn = 1 THEN [C#] END) END AS [C#1],
COALESCE( MAX(CASE WHEN rn = 2 THEN [C#] END), MAX( CASE WHEN rn = 1 THEN [C#] END), 0) AS [C#2],
[Dept],
[Issue],
[Type],
[Dept age]
FROM cteCs
GROUP BY [Week],
[Month],
[Dept],
[Issue],
[Type],
[Dept age];
But i am getting below erro:
sg 130, Level 15, State 1, Line 304
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
I am aware why this error occurs but is there any other way to write that requirement.
June 19, 2015 at 3:05 pm
No promises on performance since you really haven't provided anything that is probably remotely close to your actual production environment.
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;
go
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply