Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Joining year and month on the fly?

Author  Topic 

Rasta Pickles
Posting Yak Master

174 Posts
Posted - 2012-07-06 : 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 - 2012-07-06 : 11:16:46
We would have to understand the concept first...

"While the query is running.."

Got chuckle from that one
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-07-06 : 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-06 : 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/
Go to Top of Page

Rasta Pickles
Posting Yak Master

174 Posts
Posted - 2012-07-06 : 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
Go to Top of Page

Rasta Pickles
Posting Yak Master

174 Posts
Posted - 2012-07-07 : 12:47:08
Ok, manipulated Sunita's code but the output generated is

190501
190501
190501
190501
190501
190501


for my six test records?
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-07-07 : 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);
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-07 : 16:32:04
you still didnt tell us what output you're expecting to see though

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Rasta Pickles
Posting Yak Master

174 Posts
Posted - 2012-07-08 : 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.
Go to Top of Page

Rasta Pickles
Posting Yak Master

174 Posts
Posted - 2012-07-08 : 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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-08 : 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/
Go to Top of Page

Rasta Pickles
Posting Yak Master

174 Posts
Posted - 2012-07-10 : 00:41:58
Thank you anyway, I have learned a painful lesson
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-10 : 10:21:45
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Rasta Pickles
Posting Yak Master

174 Posts
Posted - 2012-07-10 : 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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-10 : 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/
Go to Top of Page

Rasta Pickles
Posting Yak Master

174 Posts
Posted - 2012-07-13 : 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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-13 : 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/
Go to Top of Page

Rasta Pickles
Posting Yak Master

174 Posts
Posted - 2012-07-15 : 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"?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-15 : 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/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site