how to get first available date in a month- TSql query

in the following,
if there are no records for the 1st of the month, then it is not populated.. how can you then go for the second date and if need be, for 3rd date and so forth...

 select (backup_size)/(1024.*1024*1024) DBSizeinGB,  
 CONVERT(VARCHAR(10),backup_start_date,110)  backup_date from msdb..backupset where database_name = 'Kirubai' and type = 'd' and name is not NULL
 and  SUBSTRING (CONVERT(VARCHAR(10),backup_start_date,110),4,2)= '01'
LVL 5
25112Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
You'll need to add to your query a set that includes every day, and then LEFT JOIN on what you have above to numbers for those days, or NULL if there are none.  If you don't have such a set handy check out my article on How to Build a SQL Calendar Table. and download/execute the code at the bottom of the article to create your own.

Good luck.
RayData AnalystCommented:
Jim is correct on the answer for your method, but why are you looking for a specific date?  Could you not simply pull for all dates in a range of dates?  In other words, why would you query for each day individually instead of simply pulling all the data that you need at once?
25112Author Commented:
Jim/Ray, let me clarify.

I am looking for the size of database on the first backup on the month over the past history (years).. so that is why I am sarisifed to just get all the '1st's.. like Jan 1st, Feb 1st, Mar 1st etc.. but if Mar 1st backup failed, I'd rather not have a NULL, but want it to navigate to March 2nd.. (basically first valid backup in msdb history set).

so in that case, the report will be
Jan 1st, Feb 1st, Mar 2nd, April 1st, May 4th. (Assuming in May the backups failed the first 3 days).. and so forth.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you probably can try something like this:

Select * from
(
select (backup_size)/(1024.*1024*1024) DBSizeinGB,  
 CONVERT(VARCHAR(10),backup_start_date,110)  backup_date,
ROW_NUMBER() OVER ( PARTITION BY Year(backup_start_date), Month(backup_start_date) ORDER BY backup_start_date ) idx
from msdb..backupset
where database_name = 'Kirubai' and type = 'd' and name is not NULL
) a
Where idx = 1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.