get the all remaining month - 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

get the all remaining month

Author

Topic

R.Prabu
Starting Member

33 Posts

Posted - 2008-07-01 : 13:35:10

My query is in below

Select INC.RefCode, INC.Month, INC.Year From HSSPMS_Tbl_IncomeDetails AS INC ,
HSSPMS_TblTenantMasterBankDetails AS TBD Where IncomeType = 'Rent'
And Year <= Year(getDate()) And INC.RefCode = TBD.TenantCode

My Result is

RefCode Month Year
----------------------------------
280ca0 5 2008
85270b 5 2008
280ca0 7 2008
a05830 10 2007
a05830 11 2007
280ca0 7 2007
280ca0 8 2007

I need to display all the remaining months Except the above month and year, this upto display the Current Month And Year

Give me the solution any one knows

Regards,
Prabu R

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 13:44:25

Make a month table variable

declare @months table
(Month int)

INSERT INTO @Months
SELECT 1
UNION ALL
SELECT 2
...
UNION ALL
SELECT 12

Select COALESCE(INC.RefCode,'Unknown'), mnth.Month, mnth.Year 
From (
SELECT y.Year,m.Month
FROM(SELECT DISTINCT Year FROM HSSPMS_Tbl_IncomeDetails)y
CROSS JOIN @months m)mnth
LEFT JOIN HSSPMS_Tbl_IncomeDetails AS INC 
ON INC.Year= mnth.Year
AND INC.Month=mnth.Month
LEFT JOIN HSSPMS_TblTenantMasterBankDetails AS TBD 
ON INC.RefCode = TBD.TenantCode
Where (IncomeType = 'Rent' OR IncomeType IS NULL)
And mnth.Year <= Year(getDate())

R.Prabu
Starting Member

33 Posts

Posted - 2008-07-01 : 14:15:30

i am asking

Now the Result is

Month Year
----- ----
3 2007
4 2007
5 2008
8 2008
10 2008

I need

Month Year
----- ----
1 2007
2 2007
5 2007
6 2007
7 2007
8 2007
9 2007
10 2007
11 2007
12 2007
1 2008
2 2008
3 2008
4 2008
6 2008
7 2008
9 2008
11 2008
12 2008

I 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

Regards,
Prabu R

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 14:26:03

quote:
Originally posted by R.Prabu

i am asking

Now the Result is

Month Year
----- ----
3 2007
4 2007
5 2008
8 2008
10 2008

I need

Month Year
----- ----
1 2007
2 2007
5 2007
6 2007
7 2007
8 2007
9 2007
10 2007
11 2007
12 2007
1 2008
2 2008
3 2008
4 2008
6 2008
7 2008
9 2008
11 2008
12 2008

I 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

Regards,
Prabu R

Do you think we can see or guess your tables? why dont you specify this in beginning? if you have a month table then use it instead of table variable

Select COALESCE(INC.RefCode,'Unknown'), mnth.Month, mnth.Year 
From (
SELECT y.Year,m.Month
FROM(SELECT DISTINCT Year FROM HSSPMS_Tbl_IncomeDetails)y
CROSS JOIN HSSPMS_Tbl_Month m)mnth
LEFT JOIN HSSPMS_Tbl_IncomeDetails AS INC 
ON INC.Year= mnth.Year
AND INC.Month=mnth.Month
LEFT JOIN HSSPMS_TblTenantMasterBankDetails AS TBD 
ON INC.RefCode = TBD.TenantCode
Where (IncomeType = 'Rent' OR IncomeType IS NULL)
And mnth.Year <= Year(getDate())

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources