Query

Question

Query

sonu_mhl

Old Hand

Points: 333
More actions
May 11, 2015 at 4:27 am

#297962

Hi
I have table in which month & year are stored, Like this
Month Year
10 2014
11 2014
12 2014
1 2015
2 2015
3 2015
4 2015
I wanted a query in which it should return the value in a status field which has latest year & month.
Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

So for the example you specified, what should be the output?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sonu_mhl Old Hand Points: 333 More actions · Answer 2

Hi

I have 1 more field Status. It should return its value

Thanks

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

What value? Please be specific. For each row in the example you gave, what should the status column be?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sonu_mhl Old Hand Points: 333 More actions · Answer 4

Hi

I have 3 fields month,year,status. Status will have value 0 or 1

Thanks

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

For each row in the example data, please could you explicitly state what the value of status should be for that row?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sonu_mhl Old Hand Points: 333 More actions · Answer 6

Hi

Just pls guide me i want to display the latest year maximum month value.

Thanks

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

I'm asking so that I can be sure that I have your requirements right. I don't feel like writing queries multiple times

Status 1 for maximum month (which will be 12) and status 1 for latest year (2015)? Or status 1 for the latest month of each year? Or?

Please, can you list for the dates you put in the first post, which rows should get 0 and which should get 1, as your request can be interpreted multiple ways?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 8

sonu_mhl (5/11/2015)
Hi
I have 3 fields month,year,status. Status will have value 0 or 1
Thanks

You have to understand that we don't know anything even about the simple data you posted. Is it stored as VARCHAR, INT, what??? And, yeah, it's going to make a difference here.

Also, most people on this forum are extremely conscientious and want to make sure that any suggestions or code they may write are actually correct but don't have the time to create real test data in a table to test their code with. Instead of nickel-diming information one requirement at a time, please see the article at the first link in my signature line below under "Helpful Links" for this and future posts. Most folks will jump through flaming hoops to help you when you do something like what's in that article.

Help us help you. What you think is simple frequently is missing a couple of pieces without readily consumable data to explain it all.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sonu_mhl Old Hand Points: 333 More actions · Answer 9

Hi

I have below data & i want record with value 6 & 2013 should be returned. Both the fields are numeric

U_MonthU_Year

122012

12013

22013

32013

42013

52013

62013

Thanks

Alan Burstein SSC Guru Points: 61152 More actions · Answer 10

First, Month and Year are terrible field names. Second, Splitting date attributes into separate columns is a bad idea.

Using your original sample data you could do this:

DECLARE @yourData AS TABLE (Month int, Year int);

INSERT INTO @yourData

SELECT 10, 2014 UNION ALL

SELECT 11, 2014 UNION ALL

SELECT 12, 2014 UNION ALL

SELECT 1, 2015 UNION ALL

SELECT 2, 2015 UNION ALL

SELECT 3, 2015 UNION ALL

SELECT 4, 2015;

WITH formatAsDate AS

(

SELECT Month, Year, CAST('1-'+CAST(month AS varchar(2))+'-'+CAST(year AS char(4)) AS date) AS dt

FROM @yourData

)

SELECT TOP 1 Month, Year

FROM formatAsDate

ORDER BY dt DESC

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 11

sonu_mhl (5/11/2015)
Hi
I have below data & i want record with value 6 & 2013 should be returned. Both the fields are numeric
U_MonthU_Year
122012
12013
22013
32013
42013
52013
62013
Thanks

does this work?

SELECT TOP 1

U_Year

, U_month

FROM <yourtable>

ORDER BY

U_Year DESC , U_month DESC;

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

sonu_mhl Old Hand Points: 333 More actions · Answer 12

Hi Alan

I have written the query like this . It is working is it o.k

SELECT top 1 u_Month, u_Year, CAST('1-'+CAST(u_month AS varchar(2))+'-'+CAST(u_year AS char(4)) AS date) AS dt

FROM [TECHAIDS].[dbo].[@M_PERIODS] order by dt desc

Thanks

sonu_mhl Old Hand Points: 333 More actions · Answer 13

sonu_mhl

Old Hand

Points: 333

May 11, 2015 at 9:29 am

#1796987

Hi Livingston

Thanks

Alan Burstein SSC Guru Points: 61152 More actions · Answer 14

Glad that worked. Happy to help.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001