ISOWK from startdate to GetDate()

Question

0.00/5 (No votes)

See more:

For reporting numbers by week I have a table.
In this table a datetime field 'occurred' is found.
If I do a

SQL

select year(occurred) * 100 + datepart(isowk, occurred) from myTable

I get the results like
201139
201140
201143
201146
...
201224
and so on
As you can see I miss the weeks between 40 and 43 and between 43 and 46.
How can I create a temporary table during a query that has all the yearweek combinations from the first occurred to GetDate() without missing a week and without double results.
In that case I can use multiple tables with datefields to count by week and join on that value

Posted 21-Jun-12 2:28am

Herman<T>.Instance

Add a Solution

2 solutions

Solution 1

Try something like this:

SQL

USE [YourDataBaseName];

DECLARE @minOcc DATETIME
DECLARE @aToday DATETIME

--get min. value for occured field
SELECT @minOcc = MIN([occured])
--get today
SET @aToday = GETDATE()

--if temporary table exists, delete it
IF NOT OBJECT_ID(N'#Some',N'U') IS NULL
    DROP TABLE #Some

--create temporary table
CREATE TABLE #Some (myVal INT)

--insert not duplicated data into temporary table
INSERT INTO #Some (myVal)
SELECT DISTINCT YEAR([occurred]) * 100 + DATEPART(isowk, [occurred]) AS myVal
FROM myTable
WHERE [occurred] BETWEEN @minOcc AND @aToday

--do what you want do to 

DROP TABLE #Some

Posted 22-Jun-12 10:39am

Maciej Los

Comments

Herman<T>.Instance 24-Jun-12 15:52pm

I see what you try to do but when there are no occured's (your where clause) in a week I still will not see them and my wish: without missing a week and without double results

Maciej Los 27-Jun-12 18:04pm

First of all, i use 2 variables. One is to get the minimum of [occured] and the second to get today date. Then i use select with DISTINCT option, which is the guarantee of non-duplicates.
Thank you for vote ;)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Herman<T>.Instance · Accepted Answer · 2012-06-27T03:00:00

Solved it myself;

SQL

declare @firstDate DateTime
declare @today DateTime
declare @checkDate DateTime

DECLARE @Yearweek TABLE
(
  YearWeekValue int
)

set @today = getDate()

SELECT @firstDate = min([occurred])
  FROM MyTable
  set @checkDate = @firstDate

  while @checkDate < @today
  begin
    print (datepart(year,@checkDate) * 100 + datepart(isowk, @checkDate))
         insert into @Yearweek
         select (datepart(year,@checkDate) * 100 + datepart(isowk, @checkDate))
        set @checkDate = DATEADD(dd, 7, @checkDate)
  end

  select * from @Yearweek

method used;
- determine firstdate in data (I know the MIN-function can lead/will to tablescan and that index on datetime field used in order by with a select top 1 is faster)
- determine today
- have a datetime value that runs between the two dates

- while the inbetween date is not larger than today
- insert the yearweek value in the created temp table
- add 7 days to the in between date to miss not any week

in the end select the data from the temp table.