February 11, 2012 at 1:13 pm
Ok the data..
IF OBJECT_ID('tempdb..#tempData') IS NOT NULL DROP TABLE #tempData;
CREATE TABLE #tempData(
Code INT,
Subs_End DATETIME
)
INSERT INTO #tempData VALUES (102,'2012-02-12 00:00:00.000')
INSERT INTO #tempData VALUES (102,'2012-02-12 00:00:00.000')
INSERT INTO #tempData VALUES (5,'2012-02-12 00:00:00.000')
INSERT INTO #tempData VALUES (802,'2012-02-13 00:00:00.000')
INSERT INTO #tempData VALUES (802,'2012-02-13 00:00:00.000')
INSERT INTO #tempData VALUES (5,'2012-02-14 00:00:00.000')
INSERT INTO #tempData VALUES (5,'2012-02-14 00:00:00.000')
INSERT INTO #tempData VALUES (5,'2012-02-15 00:00:00.000')
INSERT INTO #tempData VALUES (104,'2012-02-16 00:00:00.000')
INSERT INTO #tempData VALUES (102,'2012-02-20 00:00:00.000')
INSERT INTO #tempData VALUES (802,'2012-02-20 00:00:00.000')
INSERT INTO #tempData VALUES (102,'2012-02-23 00:00:00.000')
INSERT INTO #tempData VALUES (102,'2012-02-29 00:00:00.000')
INSERT INTO #tempData VALUES (102,'2012-03-07 00:00:00.000')
INSERT INTO #tempData VALUES (104,'2012-03-09 00:00:00.000')
INSERT INTO #tempData VALUES (102,'2012-03-12 00:00:00.000')
INSERT INTO #tempData VALUES (102,'2012-03-13 00:00:00.000')
INSERT INTO #tempData VALUES (102,'2012-03-16 00:00:00.000')
SELECT * FROM #tempData
IF OBJECT_ID('tempdb..#tempData') IS NOT NULL DROP TABLE #tempData;
I am trying to get this output, Current Month year is 201202, for 12 months to 201301
Code 201202 201203 201204 201205 201206 201207 201208 .. 201301
5 6 1
102 4 4
A horizontal spread by YYYYMM of the dates counted (total dates per each YYYYMM), with the spread always being a rolling 12 months out
Any ideas:-)
February 11, 2012 at 3:13 pm
To get all the month in the time range you need a calendar table (either a permanent or on-the-fly).
For the Pivot I'd use the DynamicCrossTab approach as described in the related link in my signature.
For a better understanding on how to use I'd recommend to start with the CrossTab article.
Give it a try and post back if you have any further questions.
February 11, 2012 at 4:36 pm
Thanks, but that stuff hurts my head...:w00t:
Any one else wishing help out much appreciated...
February 11, 2012 at 4:49 pm
Digs (2/11/2012)
Thanks, but that stuff hurts my head...:w00t:Any one else wishing help out much appreciated...
Hmmm....
Let's assume you'll get a coded version. How will you actually use it?
Would you just put it into production? How would you explain to others how it works?
February 11, 2012 at 7:47 pm
Sir, I am an old fart working on a private project...just needed a little help !
Thanks 🙂
February 11, 2012 at 9:22 pm
Digs (2/11/2012)
Sir, I am an old fart working on a private project...just needed a little help !Thanks 🙂
Jeez, Digs. The 2nd Cross Tab article that Lutz pointed you to does what you need to do. Give it a try and stop complaining about how learning something new is hurting that tender ol' head of yours. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 12, 2012 at 2:29 am
Digs (2/11/2012)
Sir, I am an old fart working on a private project...just needed a little help !Thanks 🙂
"A little help" in the meaning of "Give me some fish" or "I want to learn how to fish"?
February 12, 2012 at 2:36 am
Whatever you do, just don’t mess-up with shark... :Whistling:
February 12, 2012 at 7:29 am
LutzM (2/12/2012)
Digs (2/11/2012)
Sir, I am an old fart working on a private project...just needed a little help !Thanks 🙂
"A little help" in the meaning of "Give me some fish" or "I want to learn how to fish"?
Brings back somthing I heard while in the Bahamas.
Give a man a fish and you feed him for a day. Teach him how to fish and you feed him for the remainder of his life.
Spelling errors corrected (I hope)
February 12, 2012 at 7:32 am
bitbucket-25253 (2/12/2012)
LutzM (2/12/2012)
Digs (2/11/2012)
Sir, I am an old fart working on a private project...just needed a little help !Thanks 🙂
"A little help" in the meaning of "Give me some fish" or "I want to learn how to fish"?
Bromgs bacl a somting I heard while in the Bahamas.
Give a man a fish and you feed him for a day. Teach him how to fish and you feed him for the remainder of his life.
cant remember where I heard this .........
"catch a man a fish and you can sell it to him"
"teach a man to fish and you have lost a business opportunity"
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 12, 2012 at 7:44 am
J Livingston SQL (2/12/2012)
..."catch a man a fish and you can sell it to him"
"teach a man to fish and you have lost a business opportunity"
ROFL!!
That's just great!
Just another example of the effect of a different perspective.
February 12, 2012 at 1:22 pm
my go..
DECLARE @CurrentDate DATETIME
SET @CurrentDate = GETUTCDATE()
IF OBJECT_ID('tempdb..#tempData') IS NOT NULL DROP TABLE #tempData;
CREATE TABLE #tempData(
Code INT,
Subs_End DATETIME
)
INSERT INTO #tempData VALUES (102,'2012-02-12 00:00:00.000')
INSERT INTO #tempData VALUES (102,'2012-02-12 00:00:00.000')
INSERT INTO #tempData VALUES (5,'2012-02-12 00:00:00.000')
INSERT INTO #tempData VALUES (802,'2012-02-13 00:00:00.000')
INSERT INTO #tempData VALUES (802,'2012-02-13 00:00:00.000')
INSERT INTO #tempData VALUES (5,'2012-02-14 00:00:00.000')
INSERT INTO #tempData VALUES (5,'2012-02-14 00:00:00.000')
INSERT INTO #tempData VALUES (5,'2012-02-15 00:00:00.000')
INSERT INTO #tempData VALUES (104,'2012-02-16 00:00:00.000')
INSERT INTO #tempData VALUES (102,'2012-02-20 00:00:00.000')
INSERT INTO #tempData VALUES (802,'2012-02-20 00:00:00.000')
INSERT INTO #tempData VALUES (102,'2012-02-23 00:00:00.000')
INSERT INTO #tempData VALUES (102,'2012-02-29 00:00:00.000')
INSERT INTO #tempData VALUES (102,'2012-03-07 00:00:00.000')
INSERT INTO #tempData VALUES (104,'2012-03-09 00:00:00.000')
INSERT INTO #tempData VALUES (102,'2012-03-12 00:00:00.000')
INSERT INTO #tempData VALUES (102,'2012-03-13 00:00:00.000')
INSERT INTO #tempData VALUES (102,'2012-03-16 00:00:00.000')
SELECT Code,
COUNT(CASE WHEN YEAR(@CurrentDate)*100 + MONTH(@CurrentDate) = YEAR(Subs_End)*100 + MONTH(Subs_End) THEN 1 ELSE 0 END) AS '0'
,COUNT(CASE WHEN YEAR(DATEADD(month,1,@CurrentDate))*100 + DATEADD(month,1,@CurrentDate) = YEAR(Subs_End)*100 + MONTH(Subs_End) THEN 1 ELSE 0 END) AS '1'
FROM #tempData
GROUP BY Code
IF OBJECT_ID('tempdb..#tempData') IS NOT NULL DROP TABLE #tempData;
Code01
544
10299
10422
80233
The column 1 is incorrect, why did it not pick up March 2012 dates ?
Any ideas??:-)
PS: Fish for breakfast, lunch and dinner it seams !
February 12, 2012 at 1:46 pm
Here's a solution using a on-the-fly calendar table and the CrossTab method.
If you need this solution as a "sliding window" I still recommend to use the CrossTab article referenced in my signature (to match the concept I used and the sample Jeff used in his article) and then continue and read the DynamicCrossTab article.
WITH cte_calendar AS
(
SELECT TOP 12
DATEADD(mm,number,'2012-01-01') AS month_from,
DATEADD(mm,number+1,'2012-01-01') AS month_to
FROM master..spt_values
WHERE type='P'
ORDER BY number
),
cte_subresult as
(
SELECT
month_from,
MONTH(month_from) AS mnth,
code,
COUNT(*) AS cnt
FROM cte_calendar
LEFT OUTER JOIN #tempData ON month_from<=Subs_end AND month_to>Subs_end
GROUP BY month_from, code
)
SELECT
code,
SUM(CASE WHEN mnth=1 THEN cnt ELSE 0 END ) AS [1],
SUM(CASE WHEN mnth=2 THEN cnt ELSE 0 END ) AS [2],
SUM(CASE WHEN mnth=3 THEN cnt ELSE 0 END ) AS [3],
SUM(CASE WHEN mnth=4 THEN cnt ELSE 0 END ) AS [4],
SUM(CASE WHEN mnth=5 THEN cnt ELSE 0 END ) AS [5],
SUM(CASE WHEN mnth=6 THEN cnt ELSE 0 END ) AS [6],
SUM(CASE WHEN mnth=7 THEN cnt ELSE 0 END ) AS [7]
FROM cte_subresult
WHERE code IS NOT NULL
GROUP BY code
February 12, 2012 at 1:47 pm
DECLARE @CurrentDate DATETIME
SET @CurrentDate = GETUTCDATE()
select @currentdate
select YEAR(@CurrentDate) * 100 + MONTH(@CurrentDate)
select YEAR(Dateadd(MONTH, 1, @CurrentDate)) * 100 + Dateadd(MONTH, 1, @CurrentDate)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 12, 2012 at 3:39 pm
Thanks Lutz, will play with that !
Livingston SQL ; Dont understand your last post!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply