Query for due months - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query for due months

Author

Topic

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-05-13 : 15:09:29

Hi All,

I need to display the rent not paid months in the year. How can I form the query?

Help me.

Kamal.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-13 : 15:11:30

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-05-13 : 15:19:52

Sorry,

I have a table RentMaster the fields are

Id Month Year Rent
1 Jan 2007 2500
2 Feb 2007 2500
3 Mar 2007 2500
4 Apr 2007 2500
5 May 2007 2500
6 Jun 2007 2500
7 Jul 2007 2500

In year 2007 the rent arrears months are Aug,Sep,Oct,Nov and Dec.

My requirement is, to get the rent arrears month from the above table.

Kindly help me.

Kamal,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 15:45:27

And where do you store the payments?

E 12°55'05.25"
N 56°04'39.16"

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-05-13 : 16:15:52

I need to display only arrears months.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 16:32:05

[code]DECLARE @Sample TABLE (ID INT, Month CHAR(3), Year SMALLINT, Rent INT)

INSERT @Sample
SELECT 1, 'Jan', 2007, 2500 UNION ALL
SELECT 2, 'Feb', 2007, 2500 UNION ALL
SELECT 3, 'Mar', 2007, 2500 UNION ALL
SELECT 4, 'Apr', 2007, 2500 UNION ALL
SELECT 5, 'May', 2007, 2500 UNION ALL
SELECT 6, 'Jun', 2007, 2500 UNION ALL
SELECT 7, 'Jul', 2007, 2500

SELECT v.theMonth,
2007
FROM (
SELECT CONVERT(CHAR(3), DATEADD(MONTH, Number, '19000101'), 107) AS theMonth
FROM master..spt_values
WHERE Type = 'p'
AND Number < 12
) AS v
LEFT JOIN @Sample AS s ON s.Month = v.theMonth
AND s.Year = 2007
WHERE s.ID IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

R.Prabu
Starting Member

33 Posts

Posted - 2008-07-01 : 18:42:35

I am aslo Having the Month Table in the above query how to form the query

help me

Regards,
Prabu R

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-02 : 00:52:37

quote:
Originally posted by R.Prabu

I am aslo Having the Month Table in the above query how to form the query

help me

Regards,
Prabu R

Replace the first derived table by your month table in the query given above.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources