February 26, 2008 at 9:16 am
I don't know if a pivot table is the way to go, or if there is something better. I was asked to provide a list of the last 10 sundays and did it like so:
declare @start as varchar(11)
-- calculate most recent sunday (past)
set @start = dateadd(d, (1-datepart(dw,getdate())), getdate())
select
dateadd(d, 0, @start)
, dateadd(d, -7, @start)
, dateadd(d, -14, @start)
, dateadd(d, -21, @start)
, dateadd(d, -28, @start)
, dateadd(d, -35, @start)
, dateadd(d, -42, @start)
, dateadd(d, -49, @start)
, dateadd(d, -56, @start)
, dateadd(d, -63, @start)
, dateadd(d, -70, @start)
The problem is they don't want a single row with 10 columns, but they want a single column with 10 rows. What would be the best way to do this? The other option I thought of was a temp table with a bunch of inserts, but that seemed like overkill.
February 26, 2008 at 9:29 am
[font="Arial"]Hello,
Try
declare @start as varchar(11)
-- calculate most recent sunday (past)
set @start = dateadd(d, (1-datepart(dw,getdate())), getdate())
select char(10) + char(13)
,dateadd(d, 0, @start), char(10) + char(13)
, dateadd(d, -7, @start), char(10) + char(13)
, dateadd(d, -14, @start), char(10) + char(13)
, dateadd(d, -21, @start), char(10) + char(13)
, dateadd(d, -28, @start), char(10) + char(13)
, dateadd(d, -35, @start), char(10) + char(13)
, dateadd(d, -42, @start), char(10) + char(13)
, dateadd(d, -49, @start), char(10) + char(13)
, dateadd(d, -56, @start), char(10) + char(13)
, dateadd(d, -63, @start), char(10) + char(13)
, dateadd(d, -70, @start)
This uses the carriage return and line feed to create the rows.
It would be better if you created a table and loaded the dates in it with a date in each row IMO.
Regards,
Terry
[/font]
February 26, 2008 at 9:45 am
Thanks for the reply but that didn't seem to work. I wound up with a single row with alternating blank columns and the date
,'', '2008-2-24','','2008-2-17',''
etc.
February 26, 2008 at 10:00 am
Bob - try this one:
declare @start datetime
set @start = dateadd(d, (1-datepart(dw,getdate())), getdate())
select dateadd(week,-1*number,@start) from spt_values where type='p' and number<11
I'm using the spt_values system table as a "tally" table.
Also - Just be careful with using DATEPART with DW (day of the week). The value you get back is dependent on the SET DATEFIRST value. Anything changing that value will make your code fall down.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 26, 2008 at 10:45 am
Thanks, that worked great!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply