The below routine first populates the week table :
CREATE TABLE week](
[week_key] [bigint] IDENTITY(1,1) NOT NULL,
[calendar_year] [int] NULL,
[week_number] [char](2) NULL,
[start_date] [datetime] NULL,
[end_date] [datetime] NULL
) ON [PRIMARY]
Rountine Populataing the week table :
declare
@iYear int
, @iWeek int
, @iDate smalldatetime
select
@iYear = 2000
while
@iYear < 2020
begin
set @iWEek = 1
set @iDate = '01 Jan ' + str(@iYear,4)
while Datename(weekday, @iDate) <> 'Saturday'
set @iDate =dateadd(Day, 1, @iDate)
set @idate =dateadd(week, -1, @iDate)
while year(dateadd(week, 1, @iDate)) <= @iYear
begin
insert into week
select @iYear, @iWEek, @iDate, dateadd(day,6 , @iDate)
set @iDate = dateadd(week, 1 , @iDate)
set @iWeek = @iWeek + 1
end
set @iYEar = @iYEar + 1
end
select * from week
where end_date=DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 4)
After the week table is populated ...for every week I need to run the query and store its output in an excel destination. The excel file name should also
contain the week number for which the query was run.
Below is the query that I run :
select '120' as Club,
shop.city as fac,
substring(customer.first_name, 1, 25) as first_name,
convert(char(10), service_date.date, 101) as tran_date,
' ' as tran_id,
fact.invoice_number as inv_no,
substring(customer.membership_number, 1, 16) as mem_no,
author.employee_name as clubopt1,
tech.employee_name as clubopt2,
service_date.date as service_date,
(select week_number from dw_aaama.dbo.napa_dsra_week where end_date=DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 4)) as week_number,
(select calendar_year from dw_aaama.dbo.napa_dsra_week where end_date=DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 4)) as calendar_year,
(select start_date from week
where end_date=DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 4)) as start_date,
(select end_date from week where end_date=DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 4)) as end_date
from fact
join customer on customer.customer_key = fact.customer_key
join shop on shop.shop_key = fact.shop_key
join service_item on service_item.service_item_key = fact.service_item_key
join service_date on service_date.date_key = fact.service_date_key
join author on author.employee_key = fact.service_author_key
join tech on tech.employee_key = fact.service_technician_key
where fact.item_sequence = 1
and service_date.date between (select start_date from week
where end_date=DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 4))
and (select end_date from week where end_date=DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 4))
I apply distinct on the week field and I will always get one row back which indicates the week number I am running the query for.
Please advice
EVA05
Dynamically generate Excel File
