We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Query Empty Data.

Medium Priority
315 Views
Last Modified: 2012-05-07
Hi,

I want to query empty data like below:

1. Day (01 to Last Date of The Month)
2. BegBalance.
3. TotalDebit.
4. TotalCredit.
5. EndBalance.

If last date of the month is 30 than the result should be 30 rows.

Is it possible to do it?

Thank you.
Comment
Watch Question

Commented:
The question is not entirely clear, but here's some T-SQL which may help.

SELECT * FROM [table] WHERE [Day] <= 30;

This SQL command will return all records whose day is less or equal to 30.

Author

Commented:
Hi nasserd,

There is no where clause here.
A table with empty data, except the day of the date.

Thank you.
CERTIFIED EXPERT

Commented:
Yes, it should be possible - what database are you working with?
The biggest problem is where you get the opening balance for day 1 of the month - if it's not stored anywhere there will need to be a query over every record before the first of the month in order to establish the value.
In SQL Server I'd probably create a table-returning function that has a parameter to indicate the month you want reporting, in other varieties of SQL database the approache may differ.

Author

Commented:
Hi MikeToole,

I use Ms SQL.

The biggest problem is where you get the opening balance for day 1 of the month - if it's not stored anywhere there will need to be a query over every record before the first of the month in order to establish the value.
Don't worry about this, there is table for this, but never mind this.

Thank you.

Author

Commented:
Hi MikeToole,

Could you provide sample how to do it?

What in my mind is :

1. Create a temporary that store the date of the month.
2. Query the temporary to get the result.

I don't know how to do point 1 in a simple way.
Other better methods are welcome.

Thank you.

Commented:
emi_sastra:
Can you please explain in more detail what you are looking for? I am not what do you mean by "querying empty data". May be explaining a little more about what you are trying to achieve and why will help.
Thx

Author

Commented:
Hi pssandhu,

I want to have an empty table (not really empty), since it has data on it.

Day    Beg Balance     Total Debit     Total Credit     End Balance
01
02
03
04
...
30

I could do it using :

1. Creating a structure using datatable.
2. Add rows to it ie 30 rows and replace Day with date.

I just wonder we could do it using query.

Thank you.

Commented:
Okay, I think we are on the same page. Couple of quick questions tho -

1. Do you as want to insert dates as one time load or dynamically load for next month at end of each month?

2. I am assuming that all the fields are going to be empty except "Opening Balance" field for the first day of the Month. Where is that value coming from? Do we need to handle it dynamically as well?

P.

Author

Commented:
1. Do you as want to insert dates as one time load or dynamically load for next month at end of each month?
I just use parameter of month to get it and the query generate it the while day of the month.

2. I am assuming that all the fields are going to be empty except "Opening Balance" field for the first day of the Month. Where is that value coming from? Do we need to handle it dynamically as well?
Never mind the Opening Balance, I just need it empty.

Thank you.
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Hi pssandhu,

May be my request ridiculous, but any way I could learn how to do it when it comes the similar cases.

Thank you very much for your help.

Commented:
No problem!
There is no straight answer to your question but as they say - the more you practice the more you'll learn. Read articles/books and put them to use as much as possible. You cannot prepare for all situation you are going to run into so the best thing you can do is being as comfortable as possible in working with this enviornment.  With time you'll keep getting better and better with the knowledge you have accumulated over the months/years.
The next best bet is Google. Google your problem and sometimes you will find solution that are exactly what you are looking for and sometimes you'll get some indirect help that you can use in your case.
Happy coding.
P.

Author

Commented:
Thank you.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.