February 2, 2016 at 7:07 am
I have a variable, TempQuarterlyPeriod, which has values 1, 2, 3 or 4. Based on the contents of TempQuarterlyPeriod I want to update two other values, TempStartDate and TempEndDate in the following manner:
IF TempQuarterlyPeriod = 1 THEN TempStartDate = '01/01' + Year, TempEndDate = '31/03' + Year
IF TempQuarterlyPeriod = 2 THEN TempStartDate = '01/04' + Year, TempEndDate = '30/06' + Year
and so on for 3 and 4.
I'm getting stumped with the syntax. I'm not even sure if this is the right way of doing this. Can someone please advise?
Thanks in advance.
February 2, 2016 at 7:17 am
M Joomun (2/2/2016)
I have a variable, TempQuarterlyPeriod, which has values 1, 2, 3 or 4. Based on the contents of TempQuarterlyPeriod I want to update two other values, TempStartDate and TempEndDate in the following manner:IF TempQuarterlyPeriod = 1 THEN TempStartDate = '01/01' + Year, TempEndDate = '31/03' + Year
IF TempQuarterlyPeriod = 2 THEN TempStartDate = '01/04' + Year, TempEndDate = '30/06' + Year
and so on for 3 and 4.
I'm getting stumped with the syntax. I'm not even sure if this is the right way of doing this. Can someone please advise?
Thanks in advance.
From what you posted I don't see a variable anywhere. What is TempQuarterlyPeriod? Is that a column in your table? What is TempEndDate? Is it a date or datetime column in your table? There are likely some easier ways to get beginning and end dates for quarters than this. If you concatenating string values to make a datetime I would highly recommend you use the ANSI standard YYYYMMDD. The way you have this relies on a specific dateformat in your connection.
I will be happy to help you here but it is not very clear to me what you are trying to do and what the problem is.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 2, 2016 at 7:28 am
Tha CASE is actually IF structure. It's up to you what is more easy for you to use.
Igor Micev,My blog: www.igormicev.com
February 2, 2016 at 7:37 am
I think you're confusing column names with variables. Either way, this should get you started:
DECLARE @BaseDate date;
SET @BaseDate = '19000101';
WITH SampleData ([Year], TempQuarterlyPeriod) AS (
SELECT 2000, 1 UNION ALL
SELECT 2018, 4 UNION ALL
SELECT 2008, 3 UNION ALL
SELECT 2016, 2
)
SELECT
[Year]
,TempQuarterlyPeriod
,DATEADD(MONTH,(TempQuarterlyPeriod-1)*3,DATEADD(YEAR,[Year]-1900,@BaseDate)) AS TempStartDate
,DATEADD(DAY,-1,DATEADD(MONTH,TempQuarterlyPeriod*3,DATEADD(YEAR,[Year]-1900,@BaseDate))) AS TempEndDate
FROM
SampleData;
John
February 2, 2016 at 7:38 am
Igor Micev (2/2/2016)
Tha CASE is actually IF structure. It's up to you what is more easy for you to use.
Can you explain this? I don't think I understand what you are trying to say.
Case is an expression which is used to determine the value of a single column. IF is used to control the flow of statements. They are NOT the same thing at all.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 2, 2016 at 7:46 am
Sean Lange (2/2/2016)
M Joomun (2/2/2016)
I have a variable, TempQuarterlyPeriod, which has values 1, 2, 3 or 4. Based on the contents of TempQuarterlyPeriod I want to update two other values, TempStartDate and TempEndDate in the following manner:IF TempQuarterlyPeriod = 1 THEN TempStartDate = '01/01' + Year, TempEndDate = '31/03' + Year
IF TempQuarterlyPeriod = 2 THEN TempStartDate = '01/04' + Year, TempEndDate = '30/06' + Year
and so on for 3 and 4.
I'm getting stumped with the syntax. I'm not even sure if this is the right way of doing this. Can someone please advise?
Thanks in advance.
From what you posted I don't see a variable anywhere. What is TempQuarterlyPeriod? Is that a column in your table? What is TempEndDate? Is it a date or datetime column in your table? There are likely some easier ways to get beginning and end dates for quarters than this. If you concatenating string values to make a datetime I would highly recommend you use the ANSI standard YYYYMMDD. The way you have this relies on a specific dateformat in your connection.
I will be happy to help you here but it is not very clear to me what you are trying to do and what the problem is.
Sorry, I didn't explain properly. TempQuarterlyPeriod (tinyint), TempStartDate and TempEndDate (both DateTime) are all variables in a single table, tblTemporaryData. I want to update the two date variables based on what's stored in TempQuarterlyPeriod.
So if TempQuarterlyPeriod = 1, then TempStartDate = '01/01/2016' and TempEndDate = '31/01/2016' (in other words the start and end dates of Quarter 1)
I'm not sure what is the best way of achieving this.
Thanks for your help.
February 2, 2016 at 8:28 am
M Joomun (2/2/2016)
Sean Lange (2/2/2016)
M Joomun (2/2/2016)
I have a variable, TempQuarterlyPeriod, which has values 1, 2, 3 or 4. Based on the contents of TempQuarterlyPeriod I want to update two other values, TempStartDate and TempEndDate in the following manner:IF TempQuarterlyPeriod = 1 THEN TempStartDate = '01/01' + Year, TempEndDate = '31/03' + Year
IF TempQuarterlyPeriod = 2 THEN TempStartDate = '01/04' + Year, TempEndDate = '30/06' + Year
and so on for 3 and 4.
I'm getting stumped with the syntax. I'm not even sure if this is the right way of doing this. Can someone please advise?
Thanks in advance.
From what you posted I don't see a variable anywhere. What is TempQuarterlyPeriod? Is that a column in your table? What is TempEndDate? Is it a date or datetime column in your table? There are likely some easier ways to get beginning and end dates for quarters than this. If you concatenating string values to make a datetime I would highly recommend you use the ANSI standard YYYYMMDD. The way you have this relies on a specific dateformat in your connection.
I will be happy to help you here but it is not very clear to me what you are trying to do and what the problem is.
Sorry, I didn't explain properly. TempQuarterlyPeriod (tinyint), TempStartDate and TempEndDate (both DateTime) are all variables in a single table, tblTemporaryData. I want to update the two date variables based on what's stored in TempQuarterlyPeriod.
So if TempQuarterlyPeriod = 1, then TempStartDate = '01/01/2016' and TempEndDate = '31/01/2016' (in other words the start and end dates of Quarter 1)
I'm not sure what is the best way of achieving this.
Thanks for your help.
Variables are NOT in a table, those would be columns. What would help the most is to provide ddl and sample data so we aren't guessing at what your table and data is like.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 2, 2016 at 8:39 am
A bit simpler than the code posted by John. The FROM part is only generating sample data and it's not really a part of the solution.
SELECT *,
TempStartDate = DATEADD( QQ, TempQuarterlyPeriod - 1, CAST(Year AS char(4))),
TempEndDate = DATEADD( DD, -1, DATEADD( QQ, TempQuarterlyPeriod, CAST(Year AS char(4))))
FROM (VALUES(2014),(2015),(2016),(2017))y(Year),
(VALUES(1),(2),(3),(4))q(TempQuarterlyPeriod);
February 2, 2016 at 8:47 am
M Joomun (2/2/2016)
Sean Lange (2/2/2016)
M Joomun (2/2/2016)
I have a variable, TempQuarterlyPeriod, which has values 1, 2, 3 or 4. Based on the contents of TempQuarterlyPeriod I want to update two other values, TempStartDate and TempEndDate in the following manner:IF TempQuarterlyPeriod = 1 THEN TempStartDate = '01/01' + Year, TempEndDate = '31/03' + Year
IF TempQuarterlyPeriod = 2 THEN TempStartDate = '01/04' + Year, TempEndDate = '30/06' + Year
and so on for 3 and 4.
I'm getting stumped with the syntax. I'm not even sure if this is the right way of doing this. Can someone please advise?
Thanks in advance.
From what you posted I don't see a variable anywhere. What is TempQuarterlyPeriod? Is that a column in your table? What is TempEndDate? Is it a date or datetime column in your table? There are likely some easier ways to get beginning and end dates for quarters than this. If you concatenating string values to make a datetime I would highly recommend you use the ANSI standard YYYYMMDD. The way you have this relies on a specific dateformat in your connection.
I will be happy to help you here but it is not very clear to me what you are trying to do and what the problem is.
Sorry, I didn't explain properly. TempQuarterlyPeriod (tinyint), TempStartDate and TempEndDate (both DateTime) are all variables in a single table, tblTemporaryData. I want to update the two date variables based on what's stored in TempQuarterlyPeriod.
So if TempQuarterlyPeriod = 1, then TempStartDate = '01/01/2016' and TempEndDate = '31/01/2016' (in other words the start and end dates of Quarter 1)
I'm not sure what is the best way of achieving this.
Thanks for your help.
Unless you're going to index/filter by those columns, I'd keep them as computed columns. However, the design might be wrong, the quarter should be an attribute of a date and not the other way around. I don't have enough information to support my statements, but I'd encourage you to analyse the possibilities.
February 2, 2016 at 9:02 am
Luis Cazares (2/2/2016)
Unless you're going to index/filter by those columns, I'd keep them as computed columns. However, the design might be wrong, the quarter should be an attribute of a date and not the other way around. I don't have enough information to support my statements, but I'd encourage you to analyse the possibilities.
I think you might be right about the design; I'll go back to my design and rethink.
Thanks.
February 2, 2016 at 9:15 am
Luis Cazares (2/2/2016)
A bit simpler than the code posted by John. The FROM part is only generating sample data and it's not really a part of the solution.
SELECT *,
TempStartDate = DATEADD( QQ, TempQuarterlyPeriod - 1, CAST(Year AS char(4))),
TempEndDate = DATEADD( DD, -1, DATEADD( QQ, TempQuarterlyPeriod, CAST(Year AS char(4))))
FROM (VALUES(2014),(2015),(2016),(2017))y(Year),
(VALUES(1),(2),(3),(4))q(TempQuarterlyPeriod);
Yes, and faster, too. I didn't think of using QQ. I thought the character conversions in your version might have slowed it down, but it doesn't - I'd say yours is still about twice as fast as mine in terms of CPU time. Here's how I tested, in case you're interested.
SET STATISTICS TIME ON;
WITH N10 AS (
SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) N1(n)
)
,N100 AS (
SELECT t1.n FROM N10 t1 CROSS JOIN N10 t2
)
,N10000(n) AS (
SELECT ROW_NUMBER() OVER (ORDER BY t1.n)
FROM N100 t1 CROSS JOIN N100 t2
)
,Periods AS (
SELECT 1752 + t1.n AS [Year], q.TempQuarterlyPeriod
FROM N10000 t1
CROSS JOIN (VALUES(1),(2),(3),(4))q(TempQuarterlyPeriod)
WHERE t1.n < 8247 -- so that we don't get any years over 9999
)
SELECT *,
TempStartDate = DATEADD( QQ, TempQuarterlyPeriod - 1, CAST([Year] AS char(4))),
TempEndDate = DATEADD( DD, -1, DATEADD( QQ, TempQuarterlyPeriod, CAST([Year] AS char(4))))
FROM Periods;
DECLARE @BaseDate date;
SET @BaseDate = '17520101';
WITH N10 AS (
SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) N1(n)
)
,N100 AS (
SELECT t1.n FROM N10 t1 CROSS JOIN N10 t2
)
,N10000(n) AS (
SELECT ROW_NUMBER() OVER (ORDER BY t1.n)
FROM N100 t1 CROSS JOIN N100 t2
)
,Periods AS (
SELECT 1752 + t1.n AS [Year], q.TempQuarterlyPeriod
FROM N10000 t1
CROSS JOIN (VALUES(1),(2),(3),(4))q(TempQuarterlyPeriod)
WHERE t1.n < 8247 -- so that we don't get any years over 9999
)
SELECT [Year]
,TempQuarterlyPeriod
,DATEADD(MONTH,(TempQuarterlyPeriod-1)*3,DATEADD(YEAR,[Year]-1752,@BaseDate)) AS TempStartDate
,DATEADD(DAY,-1,DATEADD(MONTH,TempQuarterlyPeriod*3,DATEADD(YEAR,[Year]-1752,@BaseDate))) AS TempEndDate
FROM Periods;
John
February 2, 2016 at 12:52 pm
Based on the title I am going to assume that you actually want to update data in a table.
You already got some very clever solutions based on smart calculations using the TempQuarterlyPeriod column/variable. Problem is that these may obfuscate your code more than you or your successor wants.
Here is a version that, whiule clunkier and perhaps slightly slower, has the advantage of being (in my opinion) easier to understand:
UPDATE YourTable
SET StartDate = CASE TempQuarterlyPeriod
WHEN 1 THEN '01/01'
WHEN 2 THEN '01/04'
(...)
END + Year,
EndDate = CASE TempQuarterlyPeriod
WHEN 1 THEN '31/03'
WHEN 2 THEN '30/06'
(...)
END + Year
WHERE (...);
That being said - if the StartDate and EndDate columns are date or datetime, then the above will cause an implicit data type conversion based on a date format that is not guaranteed to be safe; SQL Server might under some conditions consider it as mm/dd/yyyy and you will get incorrect data or errors. Please consider using the safe format "yyyymmdd" instead. (Which you can get by using "CAST(Year AS char(4)) + CASE (...)", with the appropriate changed forms for the mmdd part). And consider wrapping the entire thing in an explicit CAST to better document what you are doing. Or alternatively, use an explicit CONVERT and use the style parameter to specify the date format used.
February 3, 2016 at 3:30 am
Thanks very much for all the replies and in this thread. I've gone back over the application and decided to alter the design ever so slightly so I' don't need to be updating data in this way.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply