?
Solved

Query Empty Data.

Posted on 2009-06-27
13
Medium Priority
?
285 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.
0
Comment
Question by:emi_sastra
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 4

Expert Comment

by:nasserd
ID: 24728104
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.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24728168
Hi nasserd,

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

Thank you.
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 24728325
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.
0
Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

 
LVL 1

Author Comment

by:emi_sastra
ID: 24728333
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.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24728540
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.
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24728680
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
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24729905
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.
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24729981
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.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24729987
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.
0
 
LVL 17

Accepted Solution

by:
pssandhu earned 2000 total points
ID: 24730384
Please see if this script does what you are looking for. If it does, you can put this into  stored procedure and pass values to it everytime you need records inserted into the table.
Hope this helps.
P.

-- CREATE TABLE FOR TESTING
CREATE TABLE #TestTable (
Date	Datetime,
BeginBalance Money,
TotalCredit Money,
TotalDebit Money,
EndBalance Money
)
 
-- DECLARE VARIABLES TO HOLD USER INPUT AND NO. OF DAYS
DECLARE @Mth TINYINT,
		@Year Varchar(5), 
		@NumOfDays TINYINT, 
		@Counter TINYINT
 
-- SET DEFAULT VALUE
SET @Counter = 1
 
-- SET MONTH AND YEAR VALUE (USER INPUT)
SET @Mth = 4
SET @Year = '2008'
 
/**
   1. Differentiate between 30 day month vs 31 days
   2. Also check to see if it is a leap year or not for February
**/
IF @Mth IN (1,3,5,7,8,10,12)
  BEGIN
   SET @NumOfDays = 31
  END
ELSE
  BEGIN
   IF @Mth = 2
     BEGIN
       IF MONTH(Dateadd(day, 1, @Year+'-02-28')) = 3
          BEGIN SET @NumOfDays = 28 END
       ELSE 
          BEGIN SET @NumOfDays = 29 END
     END 
   ELSE 
      BEGIN SET @NumOfDays = 30 END
  END
 
-- INSERT DATES INTO OUR TEST TABLE
While @Counter <= @NumOfDays
 BEGIN
 
   INSERT INTO #TestTable (Date)
   SELECT CAST(@Year+'-'+Cast(@Mth as varchar(2))+'-'+Cast(@Counter as varchar(2)) as Datetime)
 
   SET @Counter = @Counter + 1
 END
 
SELECT	*
FROM	#TestTable
 
DROP TABLE #TestTable

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24730410
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.
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24730435
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.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24730471
Thank you.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month11 days, 18 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question