INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL YTD query SSMS into IBM DB2

T-SQL YTD query SSMS into IBM DB2

(OP)
Hi,
I am trying to build a YTD query for my reporting solution the following query in DB2, and the following query is from SSMS. Can anyone help me writing this qury in DB2? I will appreciate any help and guidance.

For example if the report runs on April 7th, 2016, so it will take the data from Jan 1st, 2016 to March 31st, 2016.
Moreover, when the report run on Jan 7th, 2017, so it will take the data from Jan 1st, 2016 to Dec 31st, 2016.
When it will run on Feb, 7th 2017, so it will take the data from Jan 1st, 2017 to Jan 31st, 2017.

--First of Every Month
where date >= case when month(getdate())=1 then convert(varchar(4),year(getdate())-1)+ '/01/01'
else convert(varchar(4),year(getdate()))+ '/01/01'end


--end of every month
where date < case when month(getdate())=1 then convert(varchar(4),year(getdate())-1)+ '/01/01'
else convert(varchar(4),year(getdate()))+ '/' +convert(varchar(2),month(getdate()))+'/01' end

Thanks and Regards,
Fka0006

RE: T-SQL YTD query SSMS into IBM DB2

Hi fka006,
I've not been to this site in absolutely ages so was pleased to find a nice problem on my return!

The main problem you have with writing this in SQL is that the end day of the previous month can change from 31, to 30 or 28 and 29. What you therefore need to do is build a date of the 1st day of the run month, then subtract 1 day from it. This will give you your end date. In the following example I've used 7th Jan 2017 as a hard coded literal, this will need to change to your run date variable or the system variable CURRENT_DATE:

CODE

DATE(SUBSTR(CHAR('2017-01-07'),1,7)||'-01') - 1 DAY 

Once you have this date, you can work on it further to extract the year of that run month and concatenate it with the 1st of January of that year (whatever it happens to be, so:

CODE

DATE(YEAR(DATE(SUBSTR(CHAR('2017-01-07'),1,7)||'-01') - 1 DAY)||'-01-01') 

If you then stitch these two dates into a BETWEEN statement, you should get the data you are after.

Hope this helps.
Marc

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close