January 13, 2015 at 1:21 pm
I've got some records like this:
ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0
where each month field has a 0 or 1, depending on if the person was enrolled that month.
I'm being asked to generate a table like this:
ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014
is there some slam dunk way to do this without a bunch of If/Then statements?
The editor compressed all my space fields, so the column headers are off in some places.
January 13, 2015 at 1:42 pm
1) it really helps us help you if you provide create table script with sample data and expected output.
2) you can do this with CASE pretty straight-forwardly, and it would be single-pass-set-based. This works because CASE short-circuits (mostly). Pseudo code:
SELECT
case when jan = 1 then '01/01/2015'
when feb = 1 then '02/01/2015'
...
end as start,
case when dec = 1 then '12/31/2015'
when nov = 1 then '11/01/2015'
...
end as end,
3) I suspect you could also pivot the columns and do min/max, but I think the case will be more efficient and certainly easier to understand! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 13, 2015 at 4:04 pm
thank you.
I'll try to remember to include the scripts. Sorry, didn't think of it.
January 13, 2015 at 7:54 pm
How do you know the year?
January 16, 2015 at 10:08 am
It is only in one year blocks. Jan-Dec. Whatever year that particular report is for.
January 16, 2015 at 10:13 am
DSNOSPAM (1/16/2015)
It is only in one year blocks. Jan-Dec. Whatever year that particular report is for.
And how do we know what year the report is being Generated?
January 16, 2015 at 2:41 pm
for my purposes, it's part of the imported table name.
January 16, 2015 at 5:14 pm
DSNOSPAM (1/16/2015)
for my purposes, it's part of the imported table name.
And this gets passed to a SQL procedure somehow?
January 18, 2015 at 5:34 pm
About the only thing this doesn't do is reformat the date into precisely the string you want (I used CONVERT with the 106 argument to demonstrate).
DECLARE @Year DATE = '2014';
WITH SampleData (ID, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec) AS
(
SELECT '0000030257', 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 0
)
SELECT ID
,CONVERT(VARCHAR(11), MIN(DATEADD(month, MonthNo-1, @Year)), 106)
,CONVERT(VARCHAR(11), DATEADD(day, -1, MAX(DATEADD(month, MonthNo, @Year))), 106)
FROM SampleData a
CROSS APPLY
(
VALUES(Jan * 1),(Feb * 2),(Mar * 3),(Apr * 4),(May * 5),(Jun * 6)
,(Jul * 7),(Aug * 8),(Sep * 9),(Oct * 10),(Nov * 11),(Dec * 12)
) b (MonthNo)
WHERE MonthNo <> 0
GROUP BY ID;
Let me know if this helps.
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply