February 18, 2014 at 1:31 pm
Hey guys, I have the following scenario and here's the test data:
Create Table Test
(
DateColumn Date,
Indicator Varchar(1)
)
Insert into Test
Values
('12-26-2013', Null),
('01-08-2014', Null),
('01-13-2014', Null),
('01-20-2014', Null),
('01-27-2014', Null),
('02-03-2014', Null),
('02-10-2014', Null),
('02-17-2014', Null)
Problem Statement:
The Indicator column for the maximum date in a month should be set to 'Y' and the remaining dates should be 'N'. This is how the output should be like:
12-26-2013 Y
01-08-2014 N
01-13-2014 N
01-20-2014 N
01-27-2014 Y
02-03-2014 N
02-10-2014 N
02-17-2014 Y
My code works if the current month (Febuary) has data but if there is no Feb data, I'm not able to get it working. Please help.
Here's my code:
Create Proc usSetIndicator
As
Begin
Declare @date Date
Declare @Month Varchar(max)
Declare @Month2 Varchar(max)
Select @Date = max(DateColumn) From Test
Select @Month = Datepart(Month, @date)
Select @Month2 = DatePart(month,max(DateColumn)) From Test
If @Month = Datepart(m,GetDate())
Begin
If Max(@Date) <= GetDate()
Update Test
Set Indicator = 'Y' Where DateColumn = @Date
Update Test
Set Indicator = 'N' Where Daptepart(month,Datecolumn) = @month2 and Datecolumn <> @Date
End
February 18, 2014 at 1:38 pm
It's due to the logic break.
You get the last date from the table for @Month, and then compare that to the month for GETDATE() (which, btw, will be REALLY screwy if you cross years).
You've basically built it return nothing when the table isn't up to date for this month.
So, since your table has data for february and I see no reason to rewrite things, what would you EXPECT to see in March with this data?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 18, 2014 at 1:45 pm
I found 2 options that might help. Test them to find out if they work fine with your real data.
--Using CTE and ROW_NUMBER()
WITH CTE AS(
SELECT *, ROW_NUMBER() OVER( PARTITION BY DATEADD( MONTH, DATEDIFF(MONTH, 0, DateColumn), 0) ORDER BY DateColumn DESC) rn
FROM Test
)
UPDATE CTE
SET Indicator = CASE WHEN rn = 1 THEN 'Y' ELSE 'N' END
--Using CROSS APPLY
UPDATE t
SET Indicator = CASE WHEN t.DateColumn = MaxDate THEN 'Y' ELSE 'N' END
FROM Test t
CROSS APPLY ( SELECT MAX(DateColumn) MaxDate
FROM Test x
WHERE DATEADD( MONTH, DATEDIFF(MONTH, 0, x.DateColumn), 0) = DATEADD( MONTH, DATEDIFF(MONTH, 0, t.DateColumn), 0)) y
February 18, 2014 at 2:11 pm
LameAss2DataBadAss (2/18/2014)
@CraigIf March doesnt have data and in case this procedure runs for the first ever run in March, nothing happens with my code.
Right, so what's the problem? Am I just being dense? Either doing nothing is what you want, in which case I don't understand, or I need to know what you'd expect in March with this data.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 18, 2014 at 2:53 pm
Just to be sure. Do you understand how it works? Which option did you take?
February 18, 2014 at 3:43 pm
I'm barely decent with Sql and I never understood how CTEs work. So I used the Cross Apply route :-p
P.S: How do you paste your code in separate box in your message?
February 18, 2014 at 3:51 pm
You could think of CTEs as subqueries that are in a different position or as one-use views.
To post formatted code, you just have to put it between the code tags (removing the space between "[" and "code") [ code="sql"][/code]. You can use the IFCode Shortcuts at the left of the area where you write your post. To see an example, click the Quote button on my post.
February 18, 2014 at 4:01 pm
Umhm ok. I will play with it. thank you Luis
February 18, 2014 at 5:40 pm
DataEnthusiast (2/18/2014)
I'm barely decent with Sql and I never understood how CTEs work. So I used the Cross Apply route :-pP.S: How do you paste your code in separate box in your message?
My suggestion is that you should learn a little about CTEs and use Luis's first (brilliant!) solution. It will probably be faster than the other one.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply