Need help in query

Question

Post reply

Need help in query

snsingh

Right there with Babe

Points: 793
More actions
September 25, 2014 at 1:28 am

#290429

Hi Expert,
I have a result set like this (4 coulums PEriod,Sense,mt,Total)
Period Sense mt Total
2014_S27 CPT 1 215
2014_S27 CPT 0 318
2014_s28 CPT 1 219
2014_s28 CPT 0 405
i want to add another column in final resultset say "Previous Period total" and remove the all two rows for previous period so my result will look like
Perid Sense mt Total PreToal
2014_S28 CPT 1 219 215
2014_s28 CPT 0 405 318
as always your help is much appreciated.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Not entirely sure where your columns start and stop...

Could you provide a CREATE TABLE script and some INSERT scripts to populate your table?

Or something like this? It's just hard to tell what you mean without being able to see it properly.

SELECT Period, wk, mt, Total

FROM (

SELECT 2014 AS Period

,27 AS wk

, 1 AS mt

, 215 As Total

UNION ALL

SELECT 2014,27, 0, 318

UNION ALL

SELECT 2014, 28, 1, 219

UNION ALL

SELECT 2014, 28, 0, 405) x

ORDER BY Period, wk;

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

this sounds like you want to UNPIVOT the table and change rows into columns.

Using PIVOT and UNPIVOT

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

What defines period to be "Previous"

We can see that the logic applied quite simple: "2014_S27" is previous to "2014_S28". But, is this always the format?

If yes (two last digits do define this relationship), then you can do just this:

;with CurrentResutSet(Period, Sense, mt, Total) AS

(

select '2014_S27', 'CPT', 1, 215

union all select '2014_S27', 'CPT', 0, 318

union all select '2014_s28', 'CPT', 1, 219

union all select '2014_s28', 'CPT', 0, 405

)

SELECT C1.Period, C1.Sense, C1.mt, C1.Total, C2.Total AS PreTotal

FROM CurrentResutSet C1

JOIN CurrentResutSet C2

ON LEFT(C2.Period, 6) = LEFT(C1.Period, 6)

AND RIGHT(C2.Period,2) = RIGHT(C1.Period,2) - 1

AND C2.Sense = C1.Sense

AND C2.mt = C1.mt

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

Another Approach:

;with

CurrentResutSet(Period, Sense, mt, Total) AS

(

select '2014_S27', 'CPT', 1, 215 union all

select '2014_S27', 'CPT', 0, 318 union all

select '2014_s28', 'CPT', 1, 219 union all

select '2014_s28', 'CPT', 0, 405

),

prep AS

(

SELECT r1.*,

rn = row_number() over (partition by r1.Period ORDER BY r1.mt),

rnk = dense_rank() over (order by period)

FROM CurrentResutSet r1

)

SELECT p1.Period, p1.Sense, p1.mt, p1.Total, PreTotal = p0.Total

FROM prep p1

JOIN prep p0 ON p1.mt=p0.mt

WHERE p1.Total<>p0.Total AND p1.rnk = 2;

Double-check this as I am not 100% sure I understand the requirement.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001