January 11, 2021 at 5:53 pm
Hi ,I have Week_Number and Year_Week as below ,I need to get a value of 2 every time when i subtract the 2 week number columns . Thank you in advance for the help
;WITH CTE
AS (
SELECT 49 AS A1, '202049' AS A2 , 51 AS B1, '202051' AS B2 UNION ALL
SELECT 50 , '202050' ,52 AS B1, '202052' AS B2 UNION ALL
SELECT 51 , '202051' ,53 AS B1, '202053' AS B2 UNION ALL
SELECT 52 , '202052' ,01 AS B1, '202101' AS B2 UNION ALL
SELECT 53 , '202053' ,02 AS B1, '202102' AS B2 UNION ALL
SELECT 01 , '202101' ,03 AS B1, '202103' AS B2 UNION ALL
SELECT 02 , '202102' ,04 AS B1, '202104' AS B2 UNION ALL
SELECT 03 , '202103' ,05 AS B1, '202105' AS B2
)
SELECT A1-B1 FROM CTE
January 11, 2021 at 7:58 pm
This will need to be tested with many more rows/conditions - but should get you started:
SELECT iif(B1 < A1, B1+A1+(max(A1) over()-A1), B1) - A1 FROM CTE
I assumed you wanted a positive number...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 14, 2021 at 10:40 pm
. Did you know there's an ISO 8601 standard for the week within a year display format of the date? It looks like "yyyyW[0-5][0-9]-[0-7]", where "yyyy" is the calendar year, followed by a W as punctuation, followed by 01 to 52 or 53 for the number of the week within the year, a – as punctuation, followed by the day within that week (1=Monday, 7= Sunday). You can download calendars in this format off the Internet a little searching. It's popular in Scandinavian countries. There is no need to invent your own temporal system. You also have a serious design flaw in that a single temporal event is represented in two columns; this violates First Normal Form which says a column must be atomic and not split like this.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply