Alternate rows

Question

Post reply

Alternate rows

sharonsql2013

SSCertifiable

Points: 5908
More actions
July 29, 2015 at 8:22 am

#310252

I have a query that brings in data to be displayed in a chart.
Since there are lot of values , I just want to bring in alternate rows but i don't want to miss the last row
So if the Query shows data for 31 weeks - I get 31 rownumbers, I want it to display data from 31 weeks going back
31, 29,27,25.... Can someone advise how to achieve this ?
Here is the query that returns , Weeknumber as yyyy-wk , Rows starting Jan 2015 as week1 and Counts.
Select * from (SELECT
CONCAT(DATEPART(YYYY,Order_Date) , '-',right('0' + CAST(DATEPART(Wk,Order_Date) AS Varchar),2))WekkNumber
,ROW_NUMBER() OVER (ORDER BY CONCAT(DATEPART(YYYY,Order_Date) , '-',right('0' + CAST(DATEPART(Wk,Order_Date) AS Varchar),2))) AS Row
,SUM([People_Count]) as PeopleCount
FROM dbo.Group
WHERE LEFT([Year_Month],4) = 2015
GROUP BY Order_Date
)A

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

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

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 1

Came up with two separate queries one for last week and one for alternate weeks (%2) and did a Union. That helped

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 736212

July 29, 2015 at 10:19 am

#1815798

Modulus 2 was what I was going to suggest as well.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

sharonsql2013 (7/29/2015)
Came up with two separate queries one for last week and one for alternate weeks (%2) and did a Union. That helped

None of it will work if you're closer to the beginning of the year than 31 weeks and you'll only get a partial week for the first week of the year 6 out of 7 years on a 100 year average.

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

sgmunson SSC Guru Points: 110640 More actions · Answer 4

sharonsql2013 (7/29/2015)
I have a query that brings in data to be displayed in a chart.
Since there are lot of values , I just want to bring in alternate rows but i don't want to miss the last row
So if the Query shows data for 31 weeks - I get 31 rownumbers, I want it to display data from 31 weeks going back
31, 29,27,25.... Can someone advise how to achieve this ?
Here is the query that returns , Weeknumber as yyyy-wk , Rows starting Jan 2015 as week1 and Counts.
Select * from (SELECT
CONCAT(DATEPART(YYYY,Order_Date) , '-',right('0' + CAST(DATEPART(Wk,Order_Date) AS Varchar),2))WekkNumber
,ROW_NUMBER() OVER (ORDER BY CONCAT(DATEPART(YYYY,Order_Date) , '-',right('0' + CAST(DATEPART(Wk,Order_Date) AS Varchar),2))) AS Row
,SUM([People_Count]) as PeopleCount
FROM dbo.Group
WHERE LEFT([Year_Month],4) = 2015
GROUP BY Order_Date
)A

There are a couple of potential problems here:

1.) For weeks whose week number is even, are you just discarding that data entirely? That seems like a particularly problematic approach, and basing a chart on only half of your data going back more than 6 months seems a lot closer to criminally negligent than common sense. If you don't want to ignore it entirely, are you then perhaps looking to have two-week periods to report on? (e.g. weeks 31 and 30 appear under 31, weeks 29 and 28 under 29, etc...)

2.) You are using a GROUP BY on Order_Date, but your query seeks to only select a combination of year and week number. This might not give you what you thought you were asking for, as it will return multiple values for the same week number if there are multiple order date values within a given week number. I can't tell from your description if this is what you're looking to have take place.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)