how to find the dates which are not entered in the table

Question

I've a table
TaxPayment
- userName (varchar)
- userId (varchar)
- taxAmt (float)
- paidDate (datetime)

insert into taxPayment 
(
@userName 'public1',@userId 'public_100',@taxAmt 5000,@paidDate '02/02/2012'
)

insert into taxPayment 
(
@userName 'public1',@userId 'public_100',@taxAmt 5000,@paidDate '02/01/2012'
)

here the user public1 has paid tax only for the months jan & feb.
how can I write stored procedure so that the result will display the months or the years for which a particular user has not paid the tax?

Answer 1

You need to have a table with all the months for the year (say, beginning of the month and end of the month).

Then you can do this select statement:

select Cal.MonthStart, Cal.MonthEnd from Calendar Cal LEFT JOIN TaxPayment TP on TP.paidDate between Cal.MonthStart and Cal.MonthEnd and UserID = @UserID

WHERE UserID IS NULL -- this will return months when the user forgot to pay

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

@ Naomi N thank you for the reply.

what if I have to make entry of the paid amount in the year wise also. I think it will not be optimal to make all the entities of the months of 2012,2011,2010...

Is there any other way to solve this problem? 

Answer 3

I don't think there is another way to solve this problem. Creating such calendar table with all the months is a very simple task and it's quick, so I don't see a problem of populating the table with many months/years.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

Hello,

As this thread is related to the creation of a stored procedure and is not related to a strict problem of SQL Server Data Access , i will move it to the Transact-SQL Forum where it will interest more people.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 5

Hi,

Move done.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 6

The following article deals with finding gaps in periodic data:

http://www.sqlusa.com/bestpractices2005/advancedsql/gapfill/

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 7

A useful idiom is a report period calendar. It gives a name to a range of dates. 

CREATE TABLE Report_Periods
(report_name VARCHAR(30) NOT NULL PRIMARY KEY,
 report_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
 report_end_date DATE NOT NULL,
 CONSTRAINT date_ordering
 CHECK (report_start_date <= report_end_date),
 etc);

These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can be gaps between them; we have to wait a year between each “Annual Going out Of Business Sale!” and there might be long stretches of time without any special sales. But we want to know their ranges so that the table is fairly constant once it is created. 

I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL.  Oh, you might want to quit using your dialect format and never use FLOAT for currency amounts. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 8

Thank you everyone for the valuable answers.

As i am sick and can not go ahead with my works, I can not tell you whether your answers solved my problem.

I will post it as soon as I go with my works.

Thank You.