July 12, 2019 at 10:10 am
Hi,
I have a Date calendar table which has a range of dates between 01/01/2000 to 31/12/2050.
It current has various Flag fields like Current Day, Previous Day, Month To Date where they will show a 1 or 0 where it is set to 1 if condition is matched.
I want to add a field "Current Week" and "Previous Week" flag but not sure what to use as a condition?
So for the example below, this is what I am doing to get the Flags for CurrentDayFlag and PreviousDayFlag :
Declare @TodaysDate Date
Declare @YesterdayDate Date
Declare @CurrentYear int
Set @TodaysDate = GETDATE()
--Set @TodaysDate = '2018-12-30 00:00:00.000'
Set @YesterdayDate = DATEADD(D,-1,@TodaysDate)
Set @CurrentYear = YEAR(@TodaysDate)
Select
c.DateKey
,case when convert(date,c.DateKey) = @TodaysDate THEN 1 ELSE 0 END as CurrentDay
,case when convert(date,c.DateKey) = @YesterdayDate THEN 1 ELSE 0 END as PreviousDay
FROM
[dbo].[Calendar] c
So at time of writing this Today's date = 12th July 2019, so the for the row on the table dbo.Calendar where they DateKey = '2019-07-12 00:00:00' the field CurrentDay will show as 1 and the field PreviousDay will be 0, for the DateKey = '2019-07-11 00:00:00' the field CurrentDay will show as 0 and the field PreviousDay will be 1
For the Current Week Flag I want the Dates between 08-Jul-2019 to 14-Jul-2019 to be set to 1
and
For the Previous Week I want the Dates between 01-Jul-2019 to 07-Jul-2019 to be set to 1
This is because the Monday is the start of the week and Sunday is the last day of the week.
If Today's date was the 07th July 2019, I would expect the following:
For the Current Week Flag I want the Dates between 01-Jul-2019 to 07-Jul-2019 to be set to 1
and
For the Previous Week I want the Dates between 24-Jun-2019 to 30-Jun-2019 to be set to 1
Thanks
July 12, 2019 at 3:15 pm
Have you tried using DATEPART(week)?
John
July 12, 2019 at 5:04 pm
Perhaps this code will point you in the right direction
WeekNum = DATEDIFF(dd, '1753', [YourDate]) /7 *7 -- Always works for week start on Monday
July 16, 2019 at 6:20 pm
>> I have a Date calendar table which has a range of dates between 01/01/2000 to 31/12/2050. <<
Unfortunately, you never read a book on SQL. The only display format allowed in the language is 'yyyy-mm-dd"; which you may know some local dialect. Yes, Microsoft kept the COBOL programmers happy by giving them the ability to use the convert () function for display purposes. SQL programmers do not use it. The same thing applies to the money data type, but that's another story
>> It current has various Flag [sic] fields [sic] like Current Day, Previous Day, Month To Date where they will show a 1 or 0 where it is set to 1 if the condition is matched. <<
The term field has a special meaning in SQL that you also don't know. Also, we are no longer writing assembly language in which we had to use flags. Instead in SQL, we prefer to discover the state of being in the schema with predicates, not with code.
>> I want to add a field "Current Week" and "Previous Week" flag but not sure what to use as a condition? <<
You do realize terms like "current" and "previous" are relative? You're probably also too young to remember why we used to put a comma at the front of each line of code on punch cards. This was so we could rearrange the deck of punch cards. Essentially you are not writing SQL at all, the declarative language, but you're using it to mimic procedural code in Fortran or COBOL. Declarative languages do not like local variables, but you write them one assignment statement after the other. Also, the old Sybase GETDATE() should be CURRENT_TIMESTAMP, the ANSI/ISO Standard. We also use semicolons in this language. Finally, the old Sybase CONVERT() should be the ANSI/ISO Standard "CAST (<expression> AS <data type>)"
>> So for the example below, this is what I am doing to get the Flags for CurrentDayFlag and PreviousDayFlag :<<
Virtually everything you posted is fundamentally wrong. Let's beat it up line by line and hope that you can learn something
DECLARE @todays_date DATE;
DECLARE @yesterday_date DATE;
DECLARE @current_year INTEGER; ---this is a temporal unit of measure, not a quantity.
Since SQL is a declarative language, these local variables are a really bad idea and then they get worse.
SET @todays_date = CURRENT_TIMESTAMP; -- redundant! use system function
SET @yesterday_date = DATEADD(D, -1, @todays_date);-- redundant! use the actual expression
SET @current_year = YEAR(@todays_date); -- wrong data type.
Look at the redundancy! If you ever had a database course, not just SQL, they would have stressed the function of a database is to reduce redundancy not increase it. This means everywhere you have the @todays_date you could have replaced it with a call to CURRENT_TIMESTAMP. But you're probably too young to know why anybody would write code this redundant. The original hardware on early IBM machines had to use registers for many things. These were hardware constructs that had to be loaded from storage because the languages would not directly load from that storage into the programs.
You probably never read the ISO 11179 naming rules or the metadata committee rules. There is no such crap in RDBMS as a "date_key" because the affix "_date" is what we call an attribute property. This is basic data modeling. A key is metadata and we never mix data and metadata. A valid data element name must tell something is by its nature, never, never, never how it is used in a particular invocation. Looking at your code:
SELECT C.key_date,
CASE WHEN CAST (C.key_date AS DATE) = @todays_date THEN 1 ELSE 0 END AS current_redundant_flg,
CASE WHEN CAST (C.key_date AS DATE) = @yesterday_date THEN 1 ELSE 0 END AS previous_redundant_flg
FROM Calendar AS C;
Ignoring the fact that it's completely wrong, why isn't your "key_date" already a date? You not only use convert instead of cast, but you shouldn't have used either. Brent's rule states that you should store data the way it's used and use data the way it's stored.
>> So at time of writing this Today's date = 12th July 2019, so the for the row on the table Calendar where the key_date = '2019-07-12' the field [sic] current day will show as 1 and the field [sic] previous day will be 0, for the key_date = '2019-07-11' the field [sic] current's_redundant_flg will show as 0 and the field [sic] previous_redundant_flg will be 1 <<
No. Your approach is fundamentally wrong. SQL is a data language, not a procedural language. This means 80 to 90% of the real work in the DDL. This is why we ask people actually post DDL instead of doing narrative descriptions. In this case, if you had bothered to read the ISO 8601 standards (second most popular ANSI/ISO standard after the metric system), you would have found that there is a display format for four weeks within a year. It looks like this "yyyyWww-d" where "yyyy" is the calendar year, "W" is a separator, "ww" is the week within the year (01 to 52 or 53"), the – is a punctuation mark, and "d" is the day within the week (1 = Monday, 7 = Sunday . This particular format is very popular in the Nordic countries and you can download calendars for it to match up to the conventional ISO 8601 date format. Now it's very easy given a particular date to find which week it falls in
Please post DDL and follow ANSI/ISO standards when asking for help.
July 16, 2019 at 7:37 pm
It seems like you want a table where the flags auto update based on that current date. I think you might better phrase what you're trying to do and show this in a table or set of data. SQL Server won't change the values in the table, so the "as of the time of this writing" doesn't make sense.
We can do some tricks with views or computed columns, but why? Maybe you can explain the reasoning for your table changes.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply