June 22, 2012 at 8:17 am
I have a column which has months (1 --> 12).
In my result set I want to show all the data for the next three months. If we were in june, I want to show June,July, August
June 22, 2012 at 8:25 am
Sachin 80451 (6/22/2012)
I have a column which has months (1 --> 12).In my result set I want to show all the data for the next three months. If we were in june, I want to show June,July, August
Easily done, but hard to say how you should do it based on what little information you have given. If you could post the DDL (CREATE TABLE statement) for the table, some sample data the reflects your problem domain, and the expected results I am sure we can provide you with answer pretty quickly.
June 22, 2012 at 8:26 am
Then you will need to become familiar with date routines.
Lynn has some great ones here. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]
You can use that to figure out your starting date. Then you just need dateadd to get 3 months out.
If you want detailed help you will need to provide details. See the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 22, 2012 at 8:52 am
CREATE TABLE #mytable
( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Product Char(50),
Sum INT,
YearValue INT,
MonthValue INT
)
INSERT INTO #mytable
(ID, Product, Sum, YearValue, Monthvalue)
SELECT '4','Green','1000','2012','2' UNION ALL
SELECT '37','Yellow','9000','2012','8' UNION ALL
SELECT '44','Red','5558','2012','5' UNION ALL
SELECT '54','Green','8756','2012','8' UNION ALL
SELECT '55','Blue','894','2012','7' UNION ALL
SELECT '81','Black','8654','2012','9' UNION ALL
From this data if we are in June (6) I want to show months for next three months. So I dont want to show date for the 1st and 3rd rows.
June 22, 2012 at 8:57 am
Sachin 80451 (6/22/2012)
CREATE TABLE #mytable( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Product Char(50),
Sum INT,
YearValue INT,
MonthValue INT
)
INSERT INTO #mytable
(ID, Product, Sum, YearValue, Monthvalue)
SELECT '4','Green','1000','2012','2' UNION ALL
SELECT '37','Yellow','9000','2012','8' UNION ALL
SELECT '44','Red','5558','2012','5' UNION ALL
SELECT '54','Green','8756','2012','8' UNION ALL
SELECT '55','Blue','894','2012','7' UNION ALL
SELECT '81','Black','8654','2012','9' UNION ALL
From this data if we are in June (6) I want to show months for next three months. So I dont want to show date for the 1st and 3rd rows.
Thanks for the ddl. The fact that you don't have datetime datatypes would be totally lost without this. This of course begs the question, why don't you have datetime instead of ints like this?
Even though the datatypes are not appropriate we can make this work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 22, 2012 at 9:04 am
Couple of minor issues with your code. I suggest you test your code in a sandbox database before posting.
Here is one solution:
CREATE TABLE #mytable
( ID INT PRIMARY KEY CLUSTERED,
Product Char(50),
[Sum] INT,
YearValue INT,
MonthValue INT
);
GO
INSERT INTO #mytable
(ID, Product, [Sum], YearValue, MonthValue)
SELECT '4','Green','1000','2012','2' UNION ALL
SELECT '37','Yellow','9000','2012','8' UNION ALL
SELECT '44','Red','5558','2012','5' UNION ALL
SELECT '54','Green','8756','2012','8' UNION ALL
SELECT '55','Blue','894','2012','7' UNION ALL
SELECT '81','Black','8654','2012','9';
GO
WITH basedata AS (
SELECT
ID,
Product,
[Sum],
YearValue,
MonthValue,
DATEADD(mm, MonthValue - 1, DATEADD(yy, YearValue - 1900, 0)) AS RecDate
FROM
#mytable
)
SELECT
ID,
Product,
[Sum],
YearValue,
MonthValue
FROM
basedata
WHERE
RecDate >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AND
RecDate < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 3, 0)
ORDER BY
RecDate;
GO
DROP TABLE #mytable;
GO
June 22, 2012 at 9:04 am
OK I modified your ddl a little bit so the sample data will work.
CREATE TABLE #mytable
( ID INT ,
Product Char(50),
Sum INT,
YearValue INT,
MonthValue INT
)
INSERT INTO #mytable
(ID, Product, Sum, YearValue, Monthvalue)
SELECT '4','Green','1000','2012','2' UNION ALL
SELECT '37','Yellow','9000','2012','8' UNION ALL
SELECT '44','Red','5558','2012','5' UNION ALL
SELECT '54','Green','8756','2012','8' UNION ALL
SELECT '55','Blue','894','2012','7' UNION ALL
SELECT '81','Black','8654','2012','9'
select *
, cast(CAST(YearValue as varchar(4)) + '-' + cast(MonthValue as varchar(2)) + '-1' as datetime)
from #mytable
where cast(CAST(YearValue as varchar(4)) + '-' + cast(MonthValue as varchar(2)) + '-1' as datetime) between GETDATE() and
DATEADD(m, 3, cast(CAST(YearValue as varchar(4)) + '-' + cast(MonthValue as varchar(2)) + '-1' as datetime))
drop table #mytable
This works with your sample data.
This is going to be horrible performance wise if you have more than about 1,000 rows because of the incorrect datatypes. You have to cast the month and year to a datetime more than once. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 22, 2012 at 9:08 am
Also, my solution is based on your original post, If we were in june, I want to show June,July, August . This means the last record won't be returned as well as the 1st and 3rd.
Sean, why all the concatenation?
June 22, 2012 at 9:12 am
Lynn Pettis (6/22/2012)
Sean, why all the concatenation?
That my friend is a might fine question. 😛 Sometimes the keyboard starts before the brain kicks in.
Not only is your solution cleaner it also return the correct info. Mine is poor and incorrect...I shall walk away quietly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 22, 2012 at 9:15 am
Sean Lange (6/22/2012)
Lynn Pettis (6/22/2012)
Sean, why all the concatenation?
That my friend is a might fine question. 😛 Sometimes the keyboard starts before the brain kicks in.
Not only is your solution cleaner it also return the correct info. Mine is poor and incorrect...I shall walk away quietly.
Should we call this the Seandrome? 😉
June 22, 2012 at 9:17 am
Lynn Pettis (6/22/2012)
Sean Lange (6/22/2012)
Lynn Pettis (6/22/2012)
Sean, why all the concatenation?
That my friend is a might fine question. 😛 Sometimes the keyboard starts before the brain kicks in.
Not only is your solution cleaner it also return the correct info. Mine is poor and incorrect...I shall walk away quietly.
Should we call this the Seandrome? 😉
ROFL doesn't have quite the same ring to it but probably still true.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply