Help in query

Question

Help in query

sqlinterset

Ten Centuries

Points: 1341
More actions
June 19, 2015 at 7:58 am

#319180

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)
Desired Result:
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 Requirements
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.

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Nice attempt at posting ddl and sample data. Unfortunately the table definition is incomplete and the sample data won't run as posted.

Why in the world are you storing Year and the other columns as floats??? Why not use a date or datetime column? The biggest challenge I have here is that it is not at all clear what the logic is to get your expected results.

_______________________________________________________________

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/

sqlinterset Ten Centuries Points: 1341 More actions · Answer 2

Here is the updated table:

create table #cs([Year] float,

[Week] float,

[Month] float,

[C#] float,

[Dept] nvarchar(255)

,[Issue] nvarchar(255), [Type] nvarchar(255), [Dept age] nvarchar(255))

This is how data is stored since long time.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

sqlinterset (6/19/2015)
Here is the updated table:
create table #cs([Year] float,
[Week] float,
[Month] float,
[C#] float,
[Dept] nvarchar(255)
,[Issue] nvarchar(255), [Type] nvarchar(255), [Dept age] nvarchar(255))
This is how data is stored since long time.

Storing integers as floats is crazy. :w00t:

Your sample data is not usable because the query is nowhere close to running as posted. I could clean it up but it still isn't clear what the logic needs to be. Can you try to explain more clearly what the business rules are here?

_______________________________________________________________

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/

sqlinterset Ten Centuries Points: 1341 More actions · Answer 4

Ok. So here are the better explanation (hope this will help)

i want a result table with below columns:

Week, Month, C1#,c2# Dept,Issue, Type, Dept age

where C1 is C# for perticular week, year (2015, w1) if data for that perticular week in 2015 exist for any combination of (Dept,Issue, Type, Dept age) but if for a perticular combination data in 2015 doesn't exist then c1 will be null. If there doesn't exist any data for any combination of (Dept,Issue, Type, Dept age) in (2015,w1) then c1 will take value of 2014.

C2 is C# for previous year in respective week (2014, w1) for respective c1 conbination of (Dept,Issue, Type, Dept age). Same as above: if for perticular combination of (Dept,Issue, Type, Dept age), data doesn't exist in (2014,w1) then it will be null.

hope this helps let me know for any confusion.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Taking your various posts I was able to piece together ddl and sample data.

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 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

select *

from #cs

What doesn't make sense is the output. I can't even begin to comprehend what you are trying to do.

_______________________________________________________________

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/

sqlinterset Ten Centuries Points: 1341 More actions · Answer 6

tell me what confusion you have. I will start explaining in that terms.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

sqlinterset (6/19/2015)
tell me what confusion you have. I will start explaining in that terms.

Nothing about your expected results makes sense to me at this point. You have 12 rows in your base table but only 5 coming out and the logic for the values I am 100% not getting.

_______________________________________________________________

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/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

First attempt on getting this query working using Sean's sample data.

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 0

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];

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

sqlinterset (6/19/2015)
tell me what confusion you have. I will start explaining in that terms.

Just how many different threads have you started asking this very same question???

Are wondering why you aren't getting much help?? Could be because people are all over the place trying to help but those trying don't see what others are doing as well.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Question, how do we determine what year, week, month we are running the query for or is this based on the data in the table?

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 11

Sean Lange (6/19/2015)
Storing integers as floats is crazy. :w00t:

BWAA-HAA!!!! Storing date info in INTs is almost as crazy! 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlinterset Ten Centuries Points: 1341 More actions · Answer 12

Here is the updated table, inputs and result table (in attachment). In last column of result table, i have explained the reason for few specific rows. and in C1, C2 columns i have placed year in () from where the data is coming:

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 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)

I hope this will help!!

Sean Lange SSC Guru Points: 286590 More actions · Answer 13

sqlinterset (6/19/2015)
Here is the updated table, inputs and result table (in attachment). In last column of result table, i have explained the reason for few specific rows. and in C1, C2 columns i have placed year in () from where the data is coming:
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 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)
I hope this will help!!

You need to pay attention to details when you post. Please look at that mess of a table definition and data. I cleaned it once for you because what you posted won't work. I am beginning to get the feeling you don't really want any help.

_______________________________________________________________

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/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Still have NO IDEA how you are wanting us to query the data.

This isn't what you need but it is the only way I can seem to explain what we need:

Are we doing this:

SELECT * FROM YourTable; -- Actual query will generate the required result set

OR

SELECT * FROM YourTable WHERE ??????; -- Where the ?'s are the criteria by which the data is selected