Arrear month display - 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

General SQL Server Forums

New to SQL Server Programming

Arrear month display

Author

Topic

R.Prabu
Starting Member

33 Posts

Posted - 2008-07-01 : 14:44:41

I am having RentTable

Fields are Month, Year

Table Having The Records who is paid the rent in the month

Name Month Year
A 2 2007
A 3 2007
A 4 2007

I need to display the month which months 'A' is Not Paid

Give me result

Regards,
Prabu R

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-01 : 14:51:36

I hacked into your computer and I can see there are other tables that you aren't telling us about. Please list all the tables that are involved in this query and what is in each table. Sample data would be very helpful.

Jim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-01 : 14:53:21

Use a LEFT OUTER JOIN to your table that contains all of your renters.

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

Subscribe to my blog

R.Prabu
Starting Member

33 Posts

Posted - 2008-07-01 : 15:01:07

My Table name is IncomeDetails in this table having

Name TypeofIncome Month Year
---- ------------- ---- ----
A Rent 2 2007
A Rent 3 2007

I need to display Remaining arrears months except 2 and 3 I am also having the month Table The DDL Is

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HSSPMS_Tbl_Month](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Month] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_HSSPMS_Tbl_Month] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

tell me how to display Arrears month

Regards,
Prabu R

R.Prabu
Starting Member

33 Posts

Posted - 2008-07-01 : 16:12:55

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-01 : 16:18:39

Did you see my post?

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

Subscribe to my blog

R.Prabu
Starting Member

33 Posts

Posted - 2008-07-01 : 16:26:42

Yes But I cant able to understand.

Regards,
Prabu R

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-01 : 16:29:39

Is this what you are looking for?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102770

R.Prabu
Starting Member

33 Posts

Posted - 2008-07-01 : 19:25:07

This my Query but not working

Select Distinct TBD.PropertyId, INC.IncomeType, TLM.Title + ' ' + TM.FirstName + ',' + TM.Surname As 'TenantName', INC.RefCode as 'TenantCode', Mth.Month, INC.OwnerId,
REPLACE(ISNULL(PM.ApartmentNo, ''), ISNULL(PM.ApartmentNo,''), ISNULL(PM.ApartmentNo, '') + ',') +
REPLACE(ISNULL(PM.FloorNumber, ''), ISNULL(PM.FloorNumber,''), ISNULL(PM.FloorNumber, '') + ',')
+ PM.[HouseName / No] + ',' + PM.[Street Line1] + ', ' +
Replace(ISNULL(PM.[Street Line2],'') ,ISNULL(PM.[Street Line2],''),ISNULL(PM.[Street Line2],'') + ',') +
PM.[City / Town] + ', ' + PM.[County / State] + ', ' + CTM.CountryName +', ' + PM.[Post / Zip code] As 'PropertyAddress'
From HSSPMS_Tbl_Month as Mth
INNER Join HSSPMS_Tbl_IncomeDetails AS INC On OwnerId=@OwnerId
INNER JOIN HSSPMS_TblTenantMasterBankDetails AS TBD ON INC.RefCode = TBD.TenantCode
INNER JOIN HSSPMS_TblTenantMaster As TM ON INC.RefCode = TM.TenantCode
INNER JOIN HSSPMS_Tbl_PropertyMaster AS PM ON TBD.PropertyId=PM.PropertyId
INNER Join HSSPMS_Tbl_Country CTM On CTM.CountryId = PM.Country
INNER Join HSSPMS_Tbl_Title AS TLM ON TLM.Id = TM.Title
Where INC.Year= @Year and Mth.Id
Not In
(Select Month From HSSPMS_Tbl_IncomeDetails
Where IncomeType = 'Rent' And Year <= Year(getDate()) And Year= @Year)

I need year wise who are all not paid the month
in "HSSPMS_Tbl_IncomeDetails" Table Having who are all paid the Rent.

HSSPMS_Tbl_IncomeDetails this table having fields

Month, Year, TypeofIncome and i need to join the other tables for some getting other information.

But i need who are not paid the rent

Help me any one.

Regards,
Prabu R

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 23:56:58

Unless you explain with some data from your tables and output you require,you're really making it hard for somebody trying to help you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources