June 18, 2015 at 11:46 am
I have below table:
IF OBJECT_ID('tempdb..#complaints') IS NOt NULL
DROP TABLe #complaints
--===== Create the test table with
create table #cs([Year] float,
[Week] float,
[Month] float,
[C#] float,
[Dept] nvarchar(255)
,[Issue] nvarchar(255), [Type] nvarchar(255), [Dept age] nvarchar(255))
--===== All Inserts into the IDENTITY column
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 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)
and i want to have a table in below format: (query for it)
Week, Month, C1#,c2# Dept,Issue, Type, Dept age
14, 4 188 187 d1 i1 t1 5
14, 4 452 451 d1 i1 t2 5
I.E. I WANT two columns C1# and C2#, where C1# contains data from 2015 and C2# contains data from previous year (2014). If 2015 data is not present, then C1# will contain data of 2014 and C2# will contain data of 2013.
Please help in maiing this query
June 18, 2015 at 12:09 pm
Try something like this:
with CTE_RN as
(
select
[Week],
[Month],
[C#],
[Dept],
[Issue],
[Type],
[Dept age],
ROW_NUMBER()
OVER(PARTITION BY [Week], [Month], [Dept], [Issue], [Type], [Dept age]
ORDER BY [Year] desc) as RN
from #cs
)
select
c1.[Week],
c1.[Month],
c1.[C#] as [C1#],
c2.[C#] as [C2#],
c1.[Dept],
c1.[Issue],
c1.[Type],
c1.[Dept age]
from CTE_RN as c1
left join CTE_RN as c2
on c2.[Week] = c1.[Week] and
c2.[Month] = c1.[Month] and
c2.[Dept] = c1.[Dept] and
c2.[Issue] = c1.[Issue] and
c2.[Type] = c1.[Type] and
c2.[Dept age] = c1.[Dept age] and
c2.[RN] = 2
where
c1.RN = 1
Hope this helps.
June 18, 2015 at 12:10 pm
Isn't this the same question that you asked before?
http://www.sqlservercentral.com/Forums/Topic1693743-3077-1.aspx
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 18, 2015 at 12:35 pm
sqlinterset (6/18/2015)
I have below table:IF OBJECT_ID('tempdb..#complaints') IS NOt NULL
DROP TABLe #complaints
--===== Create the test table with
create table #cs([Year] float,
[Week] float,
[Month] float,
[C#] float,
[Dept] nvarchar(255)
,[Issue] nvarchar(255), [Type] nvarchar(255), [Dept age] nvarchar(255))
--===== All Inserts into the IDENTITY column
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 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)
and i want to have a table in below format: (query for it)
Week, Month, C1#,c2# Dept,Issue, Type, Dept age
14, 4 188 187 d1 i1 t1 5
14, 4 452 451 d1 i1 t2 5
I.E. I WANT two columns C1# and C2#, where C1# contains data from 2015 and C2# contains data from previous year (2014). If 2015 data is not present, then C1# will contain data of 2014 and C2# will contain data of 2013.
Please help in maiing this query
This produces your desired result:
--===== Create the test table with
DECLARE @CS AS TABLE (
[Year] float,
[Week] float,
[Month] float,
[C#] float,
[Dept] nvarchar(255),
[Issue] nvarchar(255),
[Type] nvarchar(255),
[Dept Age] nvarchar(255)
)
--===== All Inserts into the IDENTITY column
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 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
--and i want to have a table in below format: (query for it)
--Week, Month, C1#,c2# Dept,Issue, Type, Dept age
--14, 4 188 187 d1 i1 t1 5
--14, 4 452 451 d1 i1 t2 5
SELECT C1.[Week], C1.[Month], SUM(C1.[C#]) AS [C1#], SUM(C2.[C#]) AS [C2#], C1.Dept, C1.Issue, C1.[Type], C1.[Dept Age]
FROM @CS AS C1
INNER JOIN @CS AS C2
ON C1.[Month] = C2.[Month]
AND C1.[Week] = C2.[Week]
AND C1.[Year] = C2.[Year] + 1
AND C1.Dept = C2.Dept
AND C1.Issue = C2.Issue
AND C1.[Type] = C2.[Type]
AND C1.[Dept Age] = C2.[Dept Age]
GROUP BY C1.[Week], C1.[Month], C1.Dept, C1.Issue, C1.Type, C1.[Dept Age]
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 18, 2015 at 1:03 pm
Drew that query was good, but after applying so many fields and partitioning on that basis made that query fail. e.g. in C1, if a that particular week (w1) and category (T1, D1, I1) 2015 data doesn't exist, but other category data exist for that w1 then it was keeping C1 as of 2014 data which is not my requirement. So if for particular week w1, and category combination (T1, D1, I1) data doesn't exist but for that w1, combination for other category exist then C1 should be null.
C1 should be of 2014 data for w1 when no data exist for that week for any category
June 18, 2015 at 1:58 pm
This is working but failing for specific conditions like where for a perticular week, if no data exist for a perticular category combination, then its assigning c1 as previous year value for that perticular category while it should be 0 or null.
E.g week 4, 2015 no data exist for combination ('D1', 'I4', 'T1', 5), so it should assign Null or 0 for this 2015, 4, d1, i4, t1, 5) and c2 should take this value.
c1 will be of w5, 2014 when there doesn't exist any data for that w5, 2015 for any other category combination.
Thanks
June 18, 2015 at 2:14 pm
sqlinterset (6/18/2015)
This is working but failing for specific conditions like where for a perticular week, if no data exist for a perticular category combination, then its assigning c1 as previous year value for that perticular category while it should be 0 or null.E.g week 4, 2015 no data exist for combination ('D1', 'I4', 'T1', 5), so it should assign Null or 0 for this 2015, 4, d1, i4, t1, 5) and c2 should take this value.
c1 will be of w5, 2014 when there doesn't exist any data for that w5, 2015 for any other category combination.
Thanks
It is now abundantly clear that we don't really have a good definition for all of the requirements, and that your sample data is inadequate for testing purposes. We need a complete and detailed list of exactly what the criteria need to be, including ALL conditions of each requirement.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 18, 2015 at 2:21 pm
Sorry for confusion, while testing few of the conditions came in picture. here is the updated one:
create table #cs([Year] float,
[Week] float,
[Month] float,
[C#] float,
[Dept] nvarchar(255)
,[Issue] nvarchar(255), [Type] nvarchar(255), [Dept age] nvarchar(255))
--===== All Inserts into the IDENTITY column
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 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, 14, 4, 199, d1, i3, t1, 8 UNION ALL,
select 2012, 14,4,200, d1, i5, t1, 8)
Week, Month, C1#,c2# Dept,Issue, Type, Dept age
14, 4 188 187 d1 i1 t1 5
14, 4 452 451 d1 i1 t2 5
14, 4, 0 199 d1, i3, t1, 8
14,4,0 0, d1, i5, t1, 8
Note: last two rows in original and desired tables are added. where it is 0 in desired table, that extra condition i am looking for.
Hope this will help. Thanks
June 18, 2015 at 2:28 pm
this is with more extra values where no data exist for w5, 2015 and only 2014, and 2013 exist in this condition C1 will be of 2014 and c2 will be of 2013:
create table #cs([Year] float,
[Week] float,
[Month] float,
[C#] float,
[Dept] nvarchar(255)
,[Issue] nvarchar(255), [Type] nvarchar(255), [Dept age] nvarchar(255))
--===== All Inserts into the IDENTITY column
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 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, 14, 5, 10, d1,I2, T4, 7 UNION ALL,
SELECT 2013, 14, 5, 111, d1,I2, T4, 7 UNION ALL,
select 2014, 14, 4, 199, d1, i3, t1, 8 UNION ALL,
select 2012, 14,4,200, d1, i5, t1, 8 UNION ALL )
Desired table:
Week, Month, C1#,c2# Dept,Issue, Type, Dept age
14, 4 188 187 d1 i1 t1 5
14, 4 452 451 d1 i1 t2 5
14, 4, 0 199 d1, i3, t1, 8
14,4,0 0, d1, i5, t1, 8
14, 5, 10 111, d1,I2, T4,7
This table includes all conditions.
June 18, 2015 at 2:31 pm
sqlinterset (6/18/2015)
Sorry for confusion, while testing few of the conditions came in picture. here is the updated one:create table #cs([Year] float,
[Week] float,
[Month] float,
[C#] float,
[Dept] nvarchar(255)
,[Issue] nvarchar(255), [Type] nvarchar(255), [Dept age] nvarchar(255))
--===== All Inserts into the IDENTITY column
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 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, 14, 4, 199, d1, i3, t1, 8 UNION ALL,
select 2012, 14,4,200, d1, i5, t1, 8)
Week, Month, C1#,c2# Dept,Issue, Type, Dept age
14, 4 188 187 d1 i1 t1 5
14, 4 452 451 d1 i1 t2 5
14, 4, 0 199 d1, i3, t1, 8
14,4,0 0, d1, i5, t1, 8
Note: last two rows in original and desired tables are added. where it is 0 in desired table, that extra condition i am looking for.
Hope this will help. Thanks
Um... you still didn't state what all the conditions are, and I can't say the conditions you've already stated are all that clear.
Please write one post with ALL the conditions and in clear, easily understood language, with nothing left to interpretation.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 18, 2015 at 8:21 pm
Below is the table and desired results:
[Week] float,
[Month] float,
[C#] float,
[Dept] nvarchar(255)
,[Issue] nvarchar(255), [Type] nvarchar(255), [Dept age] nvarchar(255))
--===== All Inserts into the IDENTITY column
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 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, 14, 4, 199, d1, i3, t1, 8 UNION ALL,
select 2012, 14,4,200, d1, i5, t1, 8 UNION ALL,
Select 2014, 15, 4,200, d1, i1, t1, 10 UNION ALL,
Select 2013, 15, 4, 400, d1, i1, t1, 12)
Week, Month, C1#,c2# Dept,Issue, Type, Dept age
14, 4 188 187 d1 i1 t1 5
14, 4 452 451 d1 i1 t2 5
15, 4, 200, 400, d1, i1, t1, 12
14, 4, 0 199 d1, i3, t1, 8
14,4,0 0, d1, i5, t1, 8
i.e
Condition 1)
I WANT two columns C1# and C2# in result table, where C1# contains data from 2015 for respective (Month, week, Dept, issue, type, dept age) combination and C2# contains data from previous year (2014) for respective (Month, week, Dept, issue, type, dept age) combination.
e.g. Week, Month, C1#,c2# Dept,Issue, Type, Dept age
14, 4 188 187 d1 i1 t1 5
Condition 2)
If 2015 data is not present for perticular week, then C1# will contain data of 2014 for same respective combination and C2# will contain data of 2013.
e.g.
Week, Month, C1#,c2# Dept,Issue, Type, Dept age
15, 4, 200, 400, d1, i1, t1, 12
Condition 3)
If for a perticular week 2015 data doesn't exist for any (Dept,Issue, Type, Dept age) combination but other (Dept,Issue, Type, Dept age) combination data exist for the same week in 2015 then c1 should be null or 0 for the combination where it doesn't exist
e.g
Week, Month, C1#,c2# Dept,Issue, Type, Dept age
14, 4, 0 199 d1, i3, t1, 8
14,4,0 0, d1, i5, t1, 8
Note: Condition 1 and 2 are meeting from the above posted query, but condition 3 is not meeting.
Hope this helps.and sorry for trouble.
Any solution wil be helpful.
June 19, 2015 at 6:31 am
Any help???
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply