| Author |
 Topic  |
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
133 Posts |
 Posted - 07/06/2012 : 11:11:25
|
Hi all,
Hope someone can help,
I have the following bit of code
SELECT DATEPART(YEAR, PURCHASE_DATE) AS TRANSACTION_YEAR, DATEPART(MONTH, PURCHASE_DATE) AS TRANSACTION_MONTH,
CASE WHEN DATEPART(YEAR, DUE_DATE) >= DATEPART(YEAR, PURCHASE_DATE) OR TRANSACTION_TYPE_ID IN (15, 222) THEN DATEPART(YEAR, DUE_DATE)
ELSE DATEPART(YEAR, PURCHASE_DATE) END AS RELEASE_YEAR,
CASE WHEN (DATEPART(YEAR, DUE_DATE) = DATEPART(YEAR, PURCHASE_DATE) AND DATEPART(MONTH, DUE_DATE) >= DATEPART(MONTH, PURCHASE_DATE)) OR TRANSACTION_TYPE_ID IN (15, 222) THEN DATEPART(MONTH, DUE_DATE)
WHEN DATEPART(YEAR, DUE_DATE) > DATEPART(YEAR, PURCHASE_DATE) OR TRANSACTION_TYPE_ID IN (15, 222) THEN DATEPART(MONTH, DUE_DATE)
ELSE
DATEPART(MONTH, PURCHASE_DATE) END AS RELEASE_MONTH,
While the query is running, I'd like it to create an artificial column that combines RELEASE_YEAR and RELEASE_MONTH but in a YYYYMM
format.
Is this possible?
Many thanks  |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 07/06/2012 : 11:16:46
|
We would have to understand the concept first...
"While the query is running.."
Got chuckle from that one |
 |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/06/2012 : 11:25:48
|
I think Rasta means in the same query, Brett.
You can do something like shown below, where you will need to replace RELEASE_YEAR and RELEASE_MONTH with the corresponding expressions for those (exactly the same as you have in the current query).
If that looks too long, you can make your current query into a CTE or sub-query and then use the RELEASE_YEAR and RELEASE_MONTH in the outer query:CAST(YEAR(RELEASE_YEAR) AS VARCHAR(4)) +
RIGHT('0'+CAST(MONTH(RELEASE_MONTH) AS VARCHAR(2)),2);
BTW: Brett, welcome back from your long absence |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 07/06/2012 : 11:35:37
|
i would have done this at front end if possible using formatting functions as I can think of this as more of presentation related requirement
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
133 Posts |
Posted - 07/06/2012 : 14:01:29
|
quote:
Originally posted by sunitabeck
I think Rasta means in the same query, Brett.
You can do something like shown below, where you will need to replace RELEASE_YEAR and RELEASE_MONTH with the corresponding expressions for those (exactly the same as you have in the current query).
If that looks too long, you can make your current query into a CTE or sub-query and then use the RELEASE_YEAR and RELEASE_MONTH in the outer query:CAST(YEAR(RELEASE_YEAR) AS VARCHAR(4)) +
RIGHT('0'+CAST(MONTH(RELEASE_MONTH) AS VARCHAR(2)),2);
BTW: Brett, welcome back from your long absence
Thank you Sunita, I'm sure "Brett's" comments were intended to be helpful but, alas, I didn't understand them at all  |
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
133 Posts |
Posted - 07/07/2012 : 12:47:08
|
Ok, manipulated Sunita's code but the output generated is
190501
190501
190501
190501
190501
190501
for my six test records? |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/07/2012 : 12:55:15
|
I might have given you the wrong information. Not on purpose, I swear! 
Remove the YEAR and MONTH functions:CAST(RELEASE_YEAR AS VARCHAR(4)) +
RIGHT('0'+CAST(RELEASE_MONTH AS VARCHAR(2)),2);
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 07/07/2012 : 16:32:04
|
you still didnt tell us what output you're expecting to see though
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
133 Posts |
Posted - 07/08/2012 : 02:02:56
|
quote:
Originally posted by visakh16
you still didnt tell us what output you're expecting to see though
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
RELEASE_YEAR & RELEASE_MONTH in a new column in the format YYYYMM.
I will apply Sunita's code later and report back. |
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
133 Posts |
Posted - 07/08/2012 : 06:22:47
|
Brilliant Sunita that works a treat!
My code looks like this
SELECT DATEPART(YEAR, PURCHASE_DATE) AS TRANSACTION_YEAR, DATEPART(MONTH, PURCHASE_DATE) AS TRANSACTION_MONTH,
CAST(DATEPART(YEAR, PURCHASE_DATE) AS VARCHAR(4)) + RIGHT('0'+CAST(DATEPART(MONTH, PURCHASE_DATE) AS VARCHAR(2)),2) AS TMY,
CASE WHEN DATEPART(YEAR, DUE_DATE) >= DATEPART(YEAR, PURCHASE_DATE) THEN DATEPART(YEAR, DUE_DATE)
ELSE DATEPART(YEAR, PURCHASE_DATE) END AS RELEASE_YEAR,
CASE WHEN (DATEPART(YEAR, DUE_DATE) = DATEPART(YEAR, PURCHASE_DATE) AND DATEPART(MONTH, DUE_DATE) >= DATEPART(MONTH, PURCHASE_DATE)) THEN DATEPART(MONTH, DUE_DATE)
WHEN DATEPART(YEAR, DUE_DATE) > DATEPART(YEAR, PURCHASE_DATE) THEN DATEPART(MONTH, DUE_DATE)
ELSE
DATEPART(MONTH, PURCHASE_DATE) END AS RELEASE_MONTH,
CAST(CASE WHEN DATEPART(YEAR, DUE_DATE) >= DATEPART(YEAR, PURCHASE_DATE) THEN DATEPART(YEAR, DUE_DATE)
ELSE DATEPART(YEAR, PURCHASE_DATE) END AS VARCHAR(4)) + RIGHT('0'+CAST(CASE WHEN (DATEPART(YEAR, DUE_DATE) = DATEPART(YEAR, PURCHASE_DATE) AND DATEPART(MONTH, DUE_DATE) >= DATEPART(MONTH, PURCHASE_DATE)) THEN DATEPART(MONTH, DUE_DATE)
WHEN DATEPART(YEAR, DUE_DATE) > DATEPART(YEAR, PURCHASE_DATE) THEN DATEPART(MONTH, DUE_DATE)
ELSE
DATEPART(MONTH, PURCHASE_DATE) END AS VARCHAR(2)),2) AS RMY
FROM dbo.Test
and I've now been asked  to subtract RMY from TMY.
Because they're both of type VARCHAR that ain't gonna happen
Should I have approached this mess in a different manner?
Your time, as always, is much appreciated! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 07/08/2012 : 16:09:31
|
if you want to do calculations between date values you should keep them as datetime itself rather than doing conversions like this. You should do formatting bits only at front end, if at all possible, which was my original suggestion. The reason is exactly what you face now. Once you change them to varchar you'll have tough time implementing date manipulations as in below case.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
133 Posts |
Posted - 07/10/2012 : 00:41:58
|
Thank you anyway, I have learned a painful lesson  |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 07/10/2012 : 10:21:45
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
133 Posts |
Posted - 07/10/2012 : 15:20:50
|
Thank you, the limitations of SQL never cease to amaze me as I navigate the learning curve.
It's all good stuff.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 07/10/2012 : 15:38:59
|
quote:
Originally posted by Rasta Pickles
Thank you, the limitations of SQL never cease to amaze me as I navigate the learning curve.
It's all good stuff.
How does this translate to limitation of dbms?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
133 Posts |
Posted - 07/13/2012 : 16:03:51
|
quote:
Originally posted by visakh16
How does this translate to limitation of dbms?
Because it doesn't do what I want it to do? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 07/13/2012 : 23:24:40
|
quote:
Originally posted by Rasta Pickles
quote:
Originally posted by visakh16
How does this translate to limitation of dbms?
Because it doesn't do what I want it to do?
you want dbms to behave like a front end app? enabling you to do formatting functionality. the basic purpose of dbms is data storage,management and availability. The front end apps are ones where you do formatting to get data displayed in format you want
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
133 Posts |
Posted - 07/15/2012 : 13:44:15
|
quote:
Originally posted by visakh16
you want dbms to behave like a front end app? enabling you to do formatting functionality. the basic purpose of dbms is data storage,management and availability. The front end apps are ones where you do formatting to get data displayed in format you want
What if there is no "front end app"? |
Edited by - Rasta Pickles on 07/15/2012 13:45:33 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 07/15/2012 : 22:04:14
|
quote:
Originally posted by Rasta Pickles
quote:
Originally posted by visakh16
you want dbms to behave like a front end app? enabling you to do formatting functionality. the basic purpose of dbms is data storage,management and availability. The front end apps are ones where you do formatting to get data displayed in format you want
What if there is no "front end app"?
so are you telling there's no presentation layer at all for users? are your users end users or sql developers?
You should at least have a presentation layer like reporting tool,access form or excel spreadsheet rather than opening db directly to users isnt it?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Joining year and month on the fly?
