Order by problem
-
When I run this my Discontinued and sunset field dates become incorrect on some rows. If I take out the ORDER BY then it works fine.
Anything missing or?
SELECT CATEGORY, TYPE, VENDOR, VENDORID, PRODUCT, DISCONTINUEDDATE, Sunset, RELEASEDATE, Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8,
DATEPART(YEAR, DISCONTINUEDDATE) AS 'Quarter Year', DATEPART(quarter, DISCONTINUEDDATE) AS Quarter
FROM EOL
WHERE (DISCONTINUEDDATE > DATEADD(M, - 24, GETDATE()))
ORDER BY DISCONTINUEDDATE
-
Can you post the table definition, sample data and the incorrect results sets VS the correct on so we can figure it out?
-
Interesting problem, I plan to stick around until someone answers it. My gut response would have been to say, impossible, that can't happen. On second thought, however, I'm wondering if the DISCONTINUEDDATE column is defined as something other than a DATETIME -- e.g., a VARCHAR that's just expected to adhere to a format that can be easily converted implicitly into a DATETIME. In such a scenario, the data might look fine, but of course it will collate in an order other than you expect -- e.g., 01/01/05 would come before 02/01/04. Then things just won't appear where you expect them.
If this wild-a** guess is anywhere close to the mark, then change the ORDER BY to ORDER BY CONVERT (DATETIME, DISCONTINUEDDATE), and see if it works.
-
ORDER BY CONVERT (DATETIME, DISCONTINUEDDATE) still mixs up the dates
all of the date fields are datetime 8
I rechecked all of the fields and I do not have anything other than the following
1 ID int 4 0
0 TYPE nvarchar 100 1
0 CATEGORY nvarchar 255 1
0 PRODUCTID varchar 53 1
0 PRODUCT nvarchar 100 1
0 VENDORID varchar 10 1
0 VENDOR nvarchar 255 1
0 VERSIONID varchar 53 1
0 VERSION nvarchar 255 1
0 DESCRIPTION nvarchar 255 1
0 PLATFORM nvarchar 255 1
0 GLOBALRECOMMENDATION nvarchar 255 1
0 LOCALRECOMMENDATION nvarchar 255 1
0 SUPPORTED varchar 53 1
0 SUPPORTGROUP nvarchar 255 1
0 ESTIMATEDCOST float 8 1
0 LICENSESCHEME nvarchar 255 1
0 OBTAINCHANNEL nvarchar 53 1
0 BUDGETCODE nvarchar 255 1
0 PRODUCTPUBLISH varchar 10 1
0 VERSIONPUBLISH varchar 10 1
0 DISCONTINUEDDATE datetime 8 1
0 Sunset datetime 8 1
0 GTPM nvarchar 100 1
0 COMPOUND varchar 10 1
0 CREATEDDATE datetime 8 1
0 REQUESTSTATUS nvarchar 53 1
0 PRODUCTLASTUPDATEDATE datetime 8 1
0 VERSIONLASTUPDATEDATE datetime 8 1
0 PROJECT_COUNT varchar 10 1
0 APP_COUNT varchar 10 1
0 Q1 char 10 1
0 Q2 char 10 1
0 Q3 char 10 1
0 Q4 char 10 1
0 Q5 char 10 1
0 Q6 char 10 1
0 Q7 char 10 1
0 Q8 char 10 1
0 RELEASEDATE datetime 8 1
0 Details nchar 50 1
Field
1 Software AIX Infrastructure Software 9473 IBM AIX 5L for POWER V5.1 2 IBM CORP 11738 5.1.0.0 Version 5.1 AIX-Unix RESTRICTED Global Standard 0 Global Standard 1000.0 CPU Purchase (unspecified) 1 1 2005-12-31 00:00:00.000 2005-06-30 00:00:00.000 Evangelinos, Kostas 0 2004-03-23 08:22:39.000 Approve 2004-03-30 18:09:36.000 2004-03-30 18:09:36.000 0 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2005
with order
834 Software Mainframe Software - CICS 8046 CICS ABEND-AID/FX 180 COMPUWARE 9441 4.4 On SSMF 0402 and 0401 z/OS STANDARD Global Standard 0 Global Standard 100000.0 CPU Infrastructure T5740 1 1 2005-11-01 00:00:00.000 2000-08-26 00:00:00.000 Halupa, John P 0 NULL NULL 2005-09-06 10:47:36.000 2005-09-06 10:47:36.000 0 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2005
-
-
CREATE TABLE [EOL] (
[ID] [int] NOT NULL ,
[TYPE] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CATEGORY] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PRODUCTID] [varchar] (53) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PRODUCT] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VENDORID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VENDOR] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VERSIONID] [varchar] (53) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VERSION] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DESCRIPTION] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PLATFORM] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GLOBALRECOMMENDATION] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOCALRECOMMENDATION] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SUPPORTED] [varchar] (53) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SUPPORTGROUP] [nvarchar] (53) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ESTIMATEDCOST] [float] NULL ,
[LICENSESCHEME] [nvarchar] (53) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OBTAINCHANNEL] [nvarchar] (53) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUDGETCODE] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PRODUCTPUBLISH] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VERSIONPUBLISH] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DISCONTINUEDDATE] [datetime] NULL ,
[Sunset] [datetime] NULL ,
[GTPM] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMPOUND] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATEDDATE] [datetime] NULL ,
[REQUESTSTATUS] [nvarchar] (53) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PRODUCTLASTUPDATEDATE] [datetime] NULL ,
[VERSIONLASTUPDATEDATE] [datetime] NULL ,
[PROJECT_COUNT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[APP_COUNT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Q1] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Q2] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Q3] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Q4] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Q5] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Q6] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Q7] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Q8] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RELEASEDATE] [datetime] NULL ,
[Details] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
-
Can't do squat without the inserts statements. I'm not gonna do your job for you unless you help yourself >>
Generate insert statements
-
one at a time<G>
-
INSERT INTO [youdatabase].[dbo].[EOL]([ID], [TYPE], [CATEGORY], [PRODUCTID], [PRODUCT], [VENDORID], [VENDOR], [VERSIONID], [VERSION], [DESCRIPTION], [PLATFORM], [GLOBALRECOMMENDATION], [LOCALRECOMMENDATION], [SUPPORTED], [SUPPORTGROUP], [ESTIMATEDCOST], [LICENSESCHEME], [OBTAINCHANNEL], [BUDGETCODE], [PRODUCTPUBLISH], [VERSIONPUBLISH], [DISCONTINUEDDATE], [Sunset], [GTPM], [COMPOUND], [CREATEDDATE], [REQUESTSTATUS], [PRODUCTLASTUPDATEDATE], [VERSIONLASTUPDATEDATE], [PROJECT_COUNT], [APP_COUNT], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6], [Q7], [Q8], [RELEASEDATE], [Details])
VALUES(<ID,int,>, <TYPE,nvarchar(100),>, <CATEGORY,nvarchar(60),>, <PRODUCTID,varchar(53),>, <PRODUCT,nvarchar(100),>, <VENDORID,varchar(10),>, <VENDOR,nvarchar(255),>, <VERSIONID,varchar(53),>, <VERSION,nvarchar(100),>, <DESCRIPTION,nvarchar(255),>, <PLATFORM,nvarchar(255),>, <GLOBALRECOMMENDATION,nvarchar(30),>, <LOCALRECOMMENDATION,nvarchar(30),>, <SUPPORTED,varchar(53),>, <SUPPORTGROUP,nvarchar(53),>, <ESTIMATEDCOST,float,>, <LICENSESCHEME,nvarchar(53),>, <OBTAINCHANNEL,nvarchar(53),>, <BUDGETCODE,nvarchar(25),>, <PRODUCTPUBLISH,varchar(1),>, <VERSIONPUBLISH,varchar(1),>, <DISCONTINUEDDATE,datetime,>, <Sunset,datetime,>, <GTPM,nvarchar(100),>, <COMPOUND,varchar(10),>, <CREATEDDATE,datetime,>, <REQUESTSTATUS,nvarchar(53),>, <PRODUCTLASTUPDATEDATE,datetime,>, <VERSIONLASTUPDATEDATE,datetime,>, <PROJECT_COUNT,varchar(10),>, <APP_COUNT,varchar(10),>, <Q1,char(1),>, <Q2,char(1),>, <Q3,char(1),>, <Q4,char(1),>, <Q5,char(1),>, <Q6,char(1),>, <Q7,char(1),>, <Q8,char(1),>, <RELEASEDATE,datetime,>, <Details,nchar(50),>
-
The point of providing the insert statement is that I don't have to type the data. It also gives me a chance to work with the exact same data and schema than you so we don't work for nothing. Now that this seems way too hard for you to do I'll let you solve this problem since I already anticipate 5-10 more requests for what I actually need to help you.
Good luck.
-
Since in my particular job I cannot "JUST" send the data, I have to make sure there is nothing referenced to the company I work for. Big no no. So I have to "CLEAN" the data "BEFORE" sending it. Takes time. Hard? No. But cleaning takes time. But I guess you would just send companies data to whomever without a care. Me I like my job
thanks
-
Since this is going nowhere, let's go back to the original problem statement:
>>When I run this my Discontinued and sunset field dates become incorrect on some rows.
"Incorrect" and "some" are pretty vague terms. What *exactly* are you *expecting* to see, and what *do* you see instead ?
-
Seriously :
Select left(col1, 3) as col1,... into temp_table
copy the script from em, use the sp I sent you to make the insert statements. I care about my job too and I'll go back to it. Good luck figuring out the casting errors you made.
-
Your are correct. I should be more specific and I appreciate you pointing that out. Here is what I did to possibly fix it.
NEW
SELECT CATEGORY, TYPE, VENDOR, VENDORID, PRODUCT, DISCONTINUEDDATE, RELEASEDATE, Q1, Q2, Q3, Q4, DATEADD(m, - 6, DISCONTINUEDDATE)
AS sunset, DATEPART(YEAR, DISCONTINUEDDATE) AS 'Quarter Year', DATEPART(quarter, DISCONTINUEDDATE) AS Quarter
FROM EOL
WHERE (DISCONTINUEDDATE > DATEADD(M, - 0, GETDATE()))
ORDER BY DISCONTINUEDDATE
OLD
SELECT CATEGORY, TYPE, VENDOR, VENDORID, PRODUCT, DISCONTINUEDDATE, Sunset, RELEASEDATE, Q1, Q2, Q3, Q4, DATEPART(YEAR, DISCONTINUEDDATE) AS 'Quarter Year', DATEPART(quarter, DISCONTINUEDDATE) AS Quarter
FROM EOL
WHERE (DISCONTINUEDDATE > DATEADD(M, - 0, GETDATE()))
ORDER BY DISCONTINUEDDATE
I deleted the sunset field and the created it based on the discontinueddate. Before it was already in there. The order by now works and the dates are good. This is an error on my part since I should have never "Belived" the data was good in the Sunset filed which must always be 6 months prior to the Discontinueddate field. The discontinueddate field is a good date but it may also change I found out.
I do appreciate everyones help on this and other issues I have posted here.
thanks
Mike
-
May I ask what the dateadd is supposed to do in this context?? (DATEADD(M, - 0, GETDATE()))
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply