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)