CODEWITHSUNDEEP
sqlsql-server
Celvin
Celvin

Reputation: 15

Getting the week number as alias in sql analysis

this is my first question here. Hopefully I´m clear enough what I´m searching for.

My problem is following: On this analysis I want to get from the last 7 weeks, the summarized prices of each week. Its working with out any problems, but now I would like to add the weeks number of each week as alias.

In my tests I was using for example something like this:

DECLARE @week7 varchar(10)
SET @week7 = DATEPART(wk, GetDate())

One of my problems is, that I´m not allowed to work with "EXEC".

This is just an example of my analysis:

SELECT DISTINCT(
SELECT SUM(Price)
FROM tblBookingdata
WHERE(Datum BETWEEN DATEADD(wk, -7, DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) AND DATEADD(wk, -6, DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))))) AS '7 weeks ago', (
SELECT SUM(Price)
FROM tblBookingdata
WHERE(Datum BETWEEN DATEADD(wk, -6, DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) AND DATEADD(wk, -5, DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))))) AS '6 weeks ago'

I would like the column name to show the week number from each sub select. That the output would be for example for this week: 40 (as column name) and 900 as price summary.

So I tried to work here with DECLARE and assign @week7 for example with the current week number. But here I got stuck, due it seems like I need to work here with EXEC.

Is this only possible with "EXEC" or are there any other solutions to solve this? I was looking in the www, but currently I´m stucking a bit. Thankful for every help! :)

Upvotes: 0

Views: 182

Answers (2)

SteveC
SteveC

Reputation: 6015

I think you're looking for something like this. The prior 7 weeks are calculated from GETDATE based on a numbers table with 1, 2, 3, ... 7. Then the booking Prices are summarized by week where the Datum is within the prior 7 weeks. This will display NULL in price_sum if there were no sales that week.

drop table if exists #tblBookingdata;
go
create table #tblBookingdata(
    Datum        date not null,
    Price        int not null);
go

;with 
weeks_cte(wk) as (
    select datepart(wk, dateadd(wk, w*-1,  getdate()))
    from (values (1),(2),(3),(4),(5),(6),(7)) v(w)),
bookings_cte(wk, price_sum) as (
    select datepart(wk, Datum), sum(Price) 
    from #tblBookingdata
    where Datum>dateadd(wk, -7, getdate())
    group by datepart(wk, Datum))
select *
from weeks_cte wc
     left join bookings_cte b on wc.wk=b.wk;

Upvotes: 1

KyleUp
KyleUp

Reputation: 1703

I think the DateDiff function is your friend here. Are you using SQL Server? This won't display a row for the week if there are zero records in that week, but this should be close to what you want.

select WeeksAgo, sum(Price) as Price from (
    select
        Price
        ,Datediff(wk, Datum, getDate()) as WeeksAgo
        ,Datum --not used
    from
        tblBookingdata
)DataByWeek
where WeeksAgo between 0 and 7 --should this be 0-6?
group by WeeksAgo

Upvotes: 1

Related Questions