getting custom week data from daily data

Question

Post reply

getting custom week data from daily data

scuby.me

Mr or Mrs. 500

Points: 587
More actions
October 21, 2014 at 9:56 am

#312459

Hi,
I want to select weekly data from daily data
lets say Today's date-10/23/2014(Thursday)
My data is in date time but i want to see only date
output should be from last week Thursday to this week Wednesday. similar for previous dates
Weekly sum(profit)
10/16 - 10/21 - $1000
10/9 - 10/15 - $4100
10/2 - 10/8 - $ 8038
--
--
--

Viewing 3 posts - 1 through 2 (of 2 total)

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

g.britton SSChampion Points: 13863 More actions · Answer 1

look up the CONVERT function

Gerald Britton, Pluralsight courses

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

I'm not sure that CONVERT alone can help.

Here's an example on how to group by weeks with unusual start days.

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10E+1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

cteTally(N) AS (SELECT TOP 30 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2

),

Calendar(caldate, profit) AS ( SELECT DATEADD( dd, N, '20141001'), N FROM cteTally

)

SELECT DATEADD(WK, DATEDIFF( WK, 0, caldate + 3), -4) bow,

DATEADD(WK, DATEDIFF( WK, 0, caldate + 3), 2) eow,

SUM(profit) profit

FROM Calendar

GROUP BY DATEADD(WK, DATEDIFF( WK, 0, caldate + 3), -4),

DATEADD(WK, DATEDIFF( WK, 0, caldate + 3), 2)

ORDER BY bow

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