January 12, 2009 at 1:48 pm
I'm using this to pull data into a table that is used for an Excel Chart. Problem is that the data in the source is in a random order and the Date field is text so ORDER BY will not work. I'm trying to figure out the easiest way to order this by date (Date being month only) so it shows up in the charts in the right order.
It seems like rather simple thing to do but the only solutions I can think of seem rather complex and would require a lot of maintenance.
Use Reports
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'MonthTierClose')
DROP TABLE MonthTierClose
-- Create New Table
CREATE TABLE MonthTierClose (Month char(12), Index_Date int, L1_Tickets int, L2_Tickets int, L3_Tickets int, Month_Total int)
DECLARE @Month as varchar(20)
DECLARE @MonthIndex as int
DECLARE @MonthL1 as int
DECLARE @MonthL2 as int
DECLARE @MonthL3 as int
DECLARE @MonthTotal as int
DECLARE MonthlyCursor cursor for
SELECT DISTINCT CreateDate_Month
FROM dbo.ViperReport
FOR read only
SET @MonthIndex = 0
OPEN MonthlyCursor
FETCH NEXT FROM MonthlyCursor INTO @Month
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- Calculate Tier Totals
SET @MonthL1 = (SELECT COUNT(*) FROM dbo.ViperReport WHERE CreateDate_Month LIKE @Month and ResolvedTier_Value LIKE '1')
PRINT @Month + ' L1 Tickets ' + cast(@MonthL1 as Char)
SET @MonthL2 = (SELECT COUNT(*) FROM dbo.ViperReport WHERE CreateDate_Month LIKE @Month and ResolvedTier_Value LIKE '2')
PRINT @Month + ' L2 Tickets ' + cast(@MonthL2 as Char)
SET @MonthL3 = (SELECT COUNT(*) FROM dbo.ViperReport WHERE CreateDate_Month LIKE @Month and ResolvedTier_Value LIKE '3')
PRINT @Month + ' L3 Tickets ' + cast(@MonthL3 as Char)
-- Calculate Month Totals
SET @MonthTotal = (SELECT COUNT(*) FROM dbo.ViperReport WHERE CreateDate_Month = @Month)
PRINT @Month + ' Total Tickets ' + cast(@MonthTotal as char)
-- Create Index
SET @MonthIndex = @MonthIndex + 1
-- Populate Table
INSERT into dbo.MonthTierClose VALUES (@Month, @MonthIndex, @MonthL1, @MonthL2, @MonthL3, @MonthTotal)
FETCH NEXT FROM MonthlyCursor INTO @Month
END
DEALLOCATE MonthlyCursor
SELECT * FROM dbo.MonthTierClose ORDER BY Index_Date
January 13, 2009 at 8:55 am
Not knowing too much
What are date formats like?
Possible to CAST them to datetime, and use MONTH(date_column) function?
I thought if the datetime are consistent and still valid datetime format, you can sort by it as text too...
January 13, 2009 at 9:06 am
I guess that would be a good detail to know. The dates in the source Data are in a format like 'Jan-08' or 'MMM-YY' and the field is an nvarchar field. Which makes doing a CAST a bit problematic.
I had considered doing a long IF ELSE IF type statement and assigning an index to each month that could them be sorted correctly but that would requiring updating the script every month to add the new index. Not a big issue but not ideal.
January 13, 2009 at 9:45 am
SELECT CONVERT(datetime, '01 ' + REPLACE(SourceDate, '-', ' '), 106)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply