SQL Server - Data not in sequence query - Asked By farrukh on 19-Dec-11 08:40 AM

Dear All,

Help me out the below query works fine about it did not get the data in a sequence month wise i used order by 1 or
 

ORDER BY CAST (DATEPART (YYYY,Otr.START_DATETIME) AS varchar(30))


+ CAST (DATEPART (mm,Otr.START_DATETIME) AS varchar(30)) ASC or DESC both


But it did not work for me. The query and results are below


SELECT

CAST (DATEPART (YYYY,Otr.START_DATETIME) AS varchar(30))

+ CAST (DATEPART (mm,Otr.START_DATETIME) AS varchar(30)),

ISNULL(SUM(Otr.[Column_1]),0),

( SELECT ISNULL(SUM(Inr.[Column_1]),0)

FROM Table1 Inr

WHERE Inr.START_DATETIME <= max(Otr.START_DATETIME)

) AS PREV_Test_1_Value

FROM table2 Otr

WHERE Otr.START_DATETIME <= '11/30/2011'

GROUP BY CAST (DATEPART (YYYY,Otr.START_DATETIME) AS varchar(30))

+ CAST (DATEPART (mm,Otr.START_DATETIME) AS varchar(30))

ORDER

Month Test_1 PREV_Test_1_Value
201110 1811.748 6039.16
201111 1237.832 7276.992
20118 1811.748 1811.748
20119 2415.664 4227.412

BY 1




Thanks
hammeed
kalpana aparnathi replied to farrukh on 19-Dec-11 02:06 PM

select
cur.*
from orders cur
left join orders prev
   
on cur.webordernumber = prev.webordernumber + 1
   
and cur.webstoreid = prev.webstoreid
where cur.webordernumber <> 1
and prev.webordernumer is null
Web Star replied to farrukh on 19-Dec-11 11:01 PM
Yes when you are doing ORDER by on Datetime after converting it to varchar than it sort based on left to right character and 0 to 9 digit based on varchar datatype where you need based on interger datatype
 eg  you get result as 201110
201111
20119

So when you want to sort as date time than you need to change you query as follows

SELECT CAST (DATEPART (YYYY,Otr.START_DATETIME) AS varchar(30))
+ CAST (DATEPART (mm,Otr.START_DATETIME) AS varchar(30)),
ISNULL(SUM(Otr.[Column_1]),0),
( SELECT ISNULL(SUM(Inr.[Column_1]),0) 
FROM Table1 Inr
WHERE Inr.START_DATETIME <= max(Otr.START_DATETIME)
) AS PREV_Test_1_Value
FROM table2 Otr WHERE Otr.START_DATETIME <= '11/30/2011'
GROUP BY CAST (DATEPART (YYYY,Otr.START_DATETIME) AS varchar(30))
+ CAST (DATEPART (mm,Otr.START_DATETIME) AS varchar(30)) 
ORDER BY DATEPART (YYYY,Otr.START_DATETIME)  ASC,  DATEPART (mm,Otr.START_DATETIME)  ASC

you need order by as follows then first it sort on yyyy and after that sort on mm based on interger because I am not converting it into varchar in roderby clause

farrukh replied to Web Star on 19-Dec-11 11:25 PM

Hello Web Star,


I am getting an error,

Msg 8127, Level 16, State 1, Line is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.



Thanks
Hammeed

Web Star replied to farrukh on 20-Dec-11 11:29 AM
This error is not due to Order by, this is due to Group by which need aggrigate function.
In fact, you need just remove group by only use order by because no need group by in your requirment if run below query with order by it self sort on first yyyy and then sort by mm within that year what you want.


SELECT CAST (DATEPART (YYYY,Otr.START_DATETIME) AS varchar(30))
+ CAST (DATEPART (mm,Otr.START_DATETIME) AS varchar(30)),
ISNULL(SUM(Otr.[Column_1]),0),
( SELECT ISNULL(SUM(Inr.[Column_1]),0) 
FROM Table1 Inr
WHERE Inr.START_DATETIME <= max(Otr.START_DATETIME)
) AS PREV_Test_1_Value
FROM table2 Otr WHERE Otr.START_DATETIME <= '11/30/2011'
BY DATEPART (YYYY,Otr.START_DATETIME)  ASC,  DATEPART (mm,Otr.START_DATETIME)  ASC
farrukh replied to Web Star on 07-Jan-12 02:53 AM
Thanks you so much for your help and kindness.


Regards,
hammeed