Calculating Weeknumbers from field dates

Hi, I am sort of a script kiddie when it comes to creating views.  I would appreciate it if you could help me create the right SQL view for this situation.  I have a table of data which includes a date - field name is JRNLDATE

JRNLDATE is raw TEXT value results as follows: YYYYMMDD, as follows:

20131001
20131002
20131003 etc

I would like to calculate the week number in this case.  I am aware of the DATEPART(WEEK, GetDate())  function, but just dont understand how it is formatted into my query.    Also, I would like it if the week starts on a THURSDAY as opposed to another day of the week.

My current view is as follows:
SELECT        dbo.GLPOST.ACCTID, dbo.GLPOST.FISCALYR, dbo.GLPOST.FISCALPERD,
                         dbo.GLPOST.BATCHNBR, dbo.GLPOST.ENTRYNBR, dbo.GLPOST.TRANSNBR, dbo.GLPOST.JNLDTLDESC,  dbo.GLPOST.TRANSAMT,  dbo.vw_Accounts.ACSEGVAL02 AS STORE, dbo.GLPOST.JRNLDATE,
                         dbo.vw_Accounts.ACSEGVAL01 AS ACCT
FROM            dbo.GLPOST INNER JOIN
                         dbo.vw_Accounts ON dbo.GLPOST.ACCTID = dbo.vw_Accounts.ACCTID
WHERE        (dbo.vw_Accounts.ACSEGVAL01 = '3000')


How to i add the additional field without screwing everything up.

Thanks all.
Jim SmuldersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
>Also, I would like it if the week starts on a THURSDAY as opposed to another day of the week.
Tell us more about your years and months, specifically if the months are the same as calendar months, and if so explain the Thursday-beginning week logic, if that starts before the 1st, or after the 1st.

I have a couple of articles that can help you:  SQL Server Calendar Table speaks to how to build a custom calendar table as you have, and using that is SQL Server Calendar Table:  Fiscal Years, which explains how to create your own custom definitions of years, months, and weeks, and if you look at the code block below 'Hard:  4-4-5 Fiscal Year' you can copy-paste-modify that code to populate a column in your JRNLDATE table to pull this off.

Good luck.
Jim

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim SmuldersAuthor Commented:
Actually, I have developed a bit since I first posted above....I now have a working weekcalculator.  I just need to tweak it to work properly.

I am now stuck on the syntax for SET DATEFIRST

My new view query is as follows (simplified)

SET DATEFIRST 5
SELECT   dbo.GLPOST.ACCTID,   dbo.GLPOST.TRANSAMT,  dbo.vw_Accounts.ACSEGVAL02 AS STORE, dbo.GLPOST.JRNLDATE, DATEPART(week, CONVERT(datetime, CAST(dbo.GLPOST.JRNLDATE AS varchar(8)), 112)) AS WEEK
                       
FROM            dbo.GLPOST INNER JOIN
                         dbo.vw_Accounts ON dbo.GLPOST.ACCTID = dbo.vw_Accounts.ACCTID
WHERE        (dbo.vw_Accounts.ACSEGVAL01 = '3000')

When I run it, it appears to run, but when I save it, I get the following error: INCORRECT SYNTAX NEW THE KEYWORD 'SET' and it will not let me save the first line.

How do I properly integrate this SET command into the view query.

AND to answer your questions, Jim, this data is not really relevant to fiscal period, as there is too much data.  They want to cleanly compare each week to the same week the previous year, and if the data crosses fiscal periods, it is more important to report 7 full days, than splitting it between fiscals.  Also, I said Thursday above, but the week start is really Friday.

Sorry If i was to early in creating my query, as I am now more concerned with fixing the SET DATEFIRST COMMAND
Jim HornMicrosoft SQL Server Data DudeCommented:
>I am now stuck on the syntax for SET DATEFIRST
Looks fine, but you'll want to add a GO immediately after it.
Also be careful using this, as it will affect every query on the server that deals with weeks.

Which version of SQL Server are you running?  The code in my articles was tested on 2008R2 and 2012.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Brian CroweDatabase AdministratorCommented:
You can use @@DATEFIRST to attain the current system value and then reassert it at the end of your procedure if you don't want to affect anything else.

DECLARE @OriginalDateFirst INT;

SELECT @OriginalDateFirst = @@DATEFIRST;

SET DATEFIRST = 5;

<do stuff>

SET DATEFIRST = @OriginalDateFirst;
Jim SmuldersAuthor Commented:
Hi Brian, I tried your suggestion as is, but SQL (SQL2012) did not like it.  gets hung up on the = signs.

So I modified it, as follows...and it works (sort of)!!   (2 equal signs removed)

DECLARE @OriginalDateFirst INT;

SELECT @OriginalDateFirst = @@DATEFIRST;

SET DATEFIRST 5;

SELECT        dbo.GLPOST.ACCTID,  dbo.GLPOST.ENTRYNBR, dbo.GLPOST.TRANSNBR, dbo.GLPOST.JNLDTLDESC, dbo.GLPOST.JNLDTLREF, dbo.GLPOST.TRANSAMT, dbo.GLPOST.JRNLDATE, DATEPART(week, CONVERT(datetime, CAST(dbo.GLPOST.JRNLDATE AS varchar(8)), 112))
                         AS WEEK,
FROM            dbo.GLPOST INNER JOIN
                         dbo.vw_Accounts ON dbo.GLPOST.ACCTID = dbo.vw_Accounts.ACCTID
WHERE        (dbo.vw_Accounts.ACSEGVAL01 = '3000');

SET DATEFIRST  @OriginalDateFirst

However, now when I go to SAVE this - it does not like the first DECLARE statement and refuses to save the query.  I wonder if it really ran properly now!  I really appreciate your help, but wonder if you can review and advise.
Jim SmuldersAuthor Commented:
DECLARE statements cant be in a view .....how do I do this all within the view?
Jim HornMicrosoft SQL Server Data DudeCommented:
>My new view query is as follows (simplified)
>I am now stuck on the syntax for SET DATEFIRST

A modification to my earlier comment - Views cannot have declare statements OR set statements, or any kind of DDL, only SELECT statements, so to pull this off you'll have to write your view as a stored procedure, and execute that instead.

Or use my method to add the column to your JRNLDATE table, then you can query it like any other column without having to calculate it on the fly.
Jim SmuldersAuthor Commented:
I am really loath to do either a stored procedure or a table value function as I have not done them before, and I dont have a resource to check my work before inputting into the system.

What do you recommend one over the other?
Jim HornMicrosoft SQL Server Data DudeCommented:
Not really sure how to answer that, as it means knowing your skillsets and making a 'what's easiest' answer as opposed to a 'what's best' answer.

If you have this need once, I'm guessing you'll have it for multiple other places as well, so you might as well leverage your JRNLDATE calendar table by adding a column, populating it once (and only once), and then going forward just query the table instead of writing expressions every time you need it.
Scott PletcherSenior DBACommented:
Don't try to manipulate DATEFIRST, because that's just asking for trouble.  You could mess up a whole lot of date calcs in other code.

Besides, this task can be done with simple mathematical calcs.  The key is to start with a "base" date of a known Thursday.  We then know that every 7 days after that it is Thursday again :).

SQL's own base date, date 0, is 19000101, which is a Monday.  Thus, 19000104, date 3, is a known Thursday.

So, we calc the weeks difference from date 3 to the JRNLDATE, to get the relative week# from the base date, and to Jan_01 of that year, to get its relative week#, then we can subtract the two to get the relative week# for the current year.

This seems complex at first, but once you get used to the idea, it's pretty straight forward, and being just math calcs, it's very fast code, with no external function calls required.

Not sure how you specifically wanted to number weeks, so I assigned the first Thursday of the year as week1.  Adjust that as you prefer.


--here it is stand-alone, to demo the idea
SELECT glp.JRNLDATE,
(DATEDIFF(DAY, 3, glp.JRNLDATE) / 7) -
(DATEDIFF(DAY, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, glp.JRNLDATE), 0)) / 7) + 1 AS Week#
FROM (
    SELECT GETDATE() AS JRNLDATE UNION ALL
    SELECT '20150101' UNION ALL
    SELECT '20150115'
) AS glp


--and in the full sql SELECT

--and in the full sql SELECT, with an added alias name to help code understanding later
SELECT        glp.ACCTID, glp.FISCALYR, glp.FISCALPERD,
                         glp.BATCHNBR, glp.ENTRYNBR, glp.TRANSNBR, glp.JNLDTLDESC,  glp.TRANSAMT,  acc.ACSEGVAL02 AS STORE,
                         glp.JRNLDATE,
                         (DATEDIFF(DAY, known_Thursday_base_date, glp.JRNLDATE) / 7) -
                         (DATEDIFF(DAY, known_Thursday_base_date, DATEADD(YEAR, DATEDIFF(YEAR, 0, glp.JRNLDATE), 0)) / 7) + 1 AS Week#,
                         acc.ACSEGVAL01 AS ACCT
FROM            dbo.GLPOST glp INNER JOIN
                         dbo.vw_Accounts acc ON glp.ACCTID = acc.ACCTID
CROSS APPLY (
    SELECT '19000103' AS known_Thursday_base_date
) AS assign_alias_names
WHERE        (acc.ACSEGVAL01 = '3000')
Olaf DoschkeSoftware DeveloperCommented:
I can't contribute any better solutions, you just make me wonder why you'd want to get a week number of weeks starting on Thurdays?

A) It would be SET DATEFIRST 4 (1=Monday,...4 = Thursday...7=Sunday)
B) the definition of ISO weeks refers to Thursdays in this manner: The first week of a year is the first week ending after 1st January containing a Thursday - which means week 1 might also start in December). The ISO weeks themselves start on Monday. It's just one of a few ways to describe that most part of the week belongs to the year in question, as in the corner case the 1st January is a Thursday and Monday to Wednesday are 3 days of that week, Thursday to Sunday are the other 4 days.

That's where you might have your requirements wrong. If that's the case the simple way to get an ISO week number is DATEPART(ISO_WEEK, datetime), which also works for strings in the format 'YYYYMMDD', as they can be converted to datetime implicitly (without an explicit CONVERT or CAST).

Bye, Olaf.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Paul Walker, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.