Find the last 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

Find the last month

Author

Topic

issam
Starting Member

30 Posts

Posted - 2007-09-11 : 12:31:22

Hi,

I have a table shows 12 month and the status of each month if it's closed of not, how to find the last month closed in a SQL Staement, the file looks like this:-

year 1 2 3 4 5 6 7 8 9 10 11 12
2007 T T T T T T T T F F F F

Best Regards

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-11 : 12:36:51

The columns names are numbers?

It'd be nice if you had normalized data

Post the DDL for the table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 12:37:48

You have 13 columns? And the table is not normalized?

SELECT CHARINDEX('F', [1] + [2] + [3] + [4] + [5] + [6] + [7] +

+ [9] + [10] + [11] + [12])
FROM Table1

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 12:40:11

SELECT [Year],
NULLIF(CHARINDEX('F', [1] + [2] + [3] + [4] + [5] + [6] + [7] + + [9] + [10] + [11] + [12]) - 1, -1)
FROM Table1

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

issam
Starting Member

30 Posts

Posted - 2007-09-11 : 12:44:22

No. the field name are not numbers its just a example

issam
Starting Member

30 Posts

Posted - 2007-09-11 : 12:55:28

I try the statment like this and i have an error :

SELECT [year],
NULLIF(CHARINDEX('F', [mon_1] + [mon_2] + [mon_3] + [mon_4] + [mon_5] + [mon_6] + [mon_7] + [mon_8] + [mon_9] + [mon_10] + [mon_11] + [mon_12])-1,-1)
FROM mumonth

the error:-
Msg 8117, Level 16, State 1, Line 6
Operand data type bit is invalid for add operator.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 13:06:51

Now we are getting somewhere!
Do you think table layout is an important part of this puzzle?

SELECT [year],
NULLIF(CHARINDEX('F', CAST(mon_1 AS CHAR(1)) + CAST(mon_2 AS CHAR(1)) + CAST(mon_3 AS CHAR(1)) + CAST(mon_4 AS CHAR(1)) + CAST(mon_5 AS CHAR(1)) + CAST(mon_6 AS CHAR(1)) + CAST(mon_7 AS CHAR(1)) + CAST(mon_8 AS CHAR(1)) + CAST(mon_9 AS CHAR(1)) + CAST(mon_10 AS CHAR(1)) + CAST(mon_11 AS CHAR(1)) + CAST(mon_12 AS CHAR(1))) - 1, -1)
FROM mumonth

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

issam
Starting Member

30 Posts

Posted - 2007-09-11 : 13:19:46

I have a result as folows:-

2007 null

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 13:22:03

Of course! You don't store 'F' nor 'T' in a bit column, do you?

SELECT [year],
NULLIF(CHARINDEX('1', CAST(mon_1 AS CHAR(1)) + CAST(mon_2 AS CHAR(1)) + CAST(mon_3 AS CHAR(1)) + CAST(mon_4 AS CHAR(1)) + CAST(mon_5 AS CHAR(1)) + CAST(mon_6 AS CHAR(1)) + CAST(mon_7 AS CHAR(1)) + CAST(mon_8 AS CHAR(1)) + CAST(mon_9 AS CHAR(1)) + CAST(mon_10 AS CHAR(1)) + CAST(mon_11 AS CHAR(1)) + CAST(mon_12 AS CHAR(1))) - 1, -1)
FROM mumonth

You might have to replace the red 1 with a zero, depending on your business logic.
You are not very cooperative.

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

issam
Starting Member

30 Posts

Posted - 2007-09-11 : 13:34:23

still, I have the same result but with 0

2007 0

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-11 : 13:38:35

maybe you'd cvare to post the DDfnL and some sample data and the expected results

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 13:39:33

Did you try to run the query again, AFTER replacing the red 1 with a zero?

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-11 : 13:47:03

[code]
select
a.[Year],
[Last_Month_Closed] =
(
select
[Last_Month_Closed] = nullif(max(bb.Month_No),0)
from
(
select Month_No = case when a.Month_01 = 'T' then 1 else 0 end union all
select Month_No = case when a.Month_02 = 'T' then 2 else 0 end union all
select Month_No = case when a.Month_03 = 'T' then 3 else 0 end union all
select Month_No = case when a.Month_04 = 'T' then 4 else 0 end union all
select Month_No = case when a.Month_05 = 'T' then 5 else 0 end union all
select Month_No = case when a.Month_06 = 'T' then 6 else 0 end union all
select Month_No = case when a.Month_07 = 'T' then 7 else 0 end union all
select Month_No = case when a.Month_08 = 'T' then 8 else 0 end union all
select Month_No = case when a.Month_09 = 'T' then 9 else 0 end union all
select Month_No = case when a.Month_10 = 'T' then 10 else 0 end union all
select Month_No = case when a.Month_11 = 'T' then 11 else 0 end union all
select Month_No = case when a.Month_12 = 'T' then 12 else 0 end
) bb
)
from
MyTable a
order by
a.[Year]
[/code]

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 13:48:07

Works for me. Maybe you are not using Microsoft SQL Server?

DECLARE	@Sample TABLE
	(
		[Year] SMALLINT,
		Mon_1 BIT,
		Mon_2 BIT,
		Mon_3 BIT,
		Mon_4 BIT,
		Mon_5 BIT,
		Mon_6 BIT,
		Mon_7 BIT,
		Mon_8 BIT,
		Mon_9 BIT,
		Mon_10 BIT,
		Mon_11 BIT,
		Mon_12 BIT
	)

INSERT	@Sample
SELECT	2007, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0


SELECT	[Year],
	NULLIF(CHARINDEX('0',
				CAST(Mon_1 AS CHAR(1))
				+ CAST(Mon_2 AS CHAR(1))
				+ CAST(Mon_3 AS CHAR(1))
				+ CAST(Mon_4 AS CHAR(1))
				+ CAST(Mon_5 AS CHAR(1))
				+ CAST(Mon_6 AS CHAR(1))
				+ CAST(Mon_7 AS CHAR(1))
				+ CAST(Mon_8 AS CHAR(1))
				+ CAST(Mon_9 AS CHAR(1))
				+ CAST(Mon_10 AS CHAR(1))
				+ CAST(Mon_11 AS CHAR(1))
				+ CAST(Mon_12 AS CHAR(1))
			) - 1
		, -1) AS LastMonth
FROM	@Sample

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

issam
Starting Member

30 Posts

Posted - 2007-09-11 : 13:48:53

yes sir, the result is the same as follows:-

2004 0
2005 0
2006 0

the result that am looking for the the last closed month.

if you want me to send the file defin. I will.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 13:49:54

Michael, that's a good solution if closed months are consecutive?
But what if you have 101110010011 ?

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

issam
Starting Member

30 Posts

Posted - 2007-09-11 : 13:56:28

peso

the month is a sequens close operation, this case not allow

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 13:57:29

[code]DECLARE @Sample TABLE
(
[Year] SMALLINT,
Mon_1 BIT,
Mon_2 BIT,
Mon_3 BIT,
Mon_4 BIT,
Mon_5 BIT,
Mon_6 BIT,
Mon_7 BIT,
Mon_8 BIT,
Mon_9 BIT,
Mon_10 BIT,
Mon_11 BIT,
Mon_12 BIT
)

INSERT @Sample
SELECT 2005, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 UNION ALL
SELECT 2007, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0 UNION ALL
SELECT 2006, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 UNION ALL
SELECT 2008, 1, 0, 1, 0, 0, 1, 1, 1, 0, 0, 1, 0

SELECT [Year],
NULLIF(13 - CHARINDEX('1',
CAST(Mon_12 AS CHAR(1))
+ CAST(Mon_11 AS CHAR(1))
+ CAST(Mon_10 AS CHAR(1))
+ CAST(Mon_9 AS CHAR(1))
+ CAST(Mon_8 AS CHAR(1))
+ CAST(Mon_7 AS CHAR(1))
+ CAST(Mon_6 AS CHAR(1))
+ CAST(Mon_5 AS CHAR(1))
+ CAST(Mon_4 AS CHAR(1))
+ CAST(Mon_3 AS CHAR(1))
+ CAST(Mon_2 AS CHAR(1))
+ CAST(Mon_1 AS CHAR(1))
)
, 13) AS LastMonth
FROM @Sample
ORDER BY [Year][/code]

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

issam
Starting Member

30 Posts

Posted - 2007-09-11 : 14:19:24

thank you all

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-11 : 15:37:42

quote:
Originally posted by Peso

Michael, that's a good solution if closed months are consecutive?
But what if you have 101110010011 ?

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

The requirement was "find the last month closed", so I believe my code would do that. In other words, it would return 12, even if month 12 was the only one closed.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources