asked on

ORDER BY Date not working in Views

Hello,

I am having a situation with the View treating the SQL statement below, when I run this statement, it orders all the records by date (oldest--newest), however when I make the View
with the same statement and run it, its ordering it rather by Product type name.
I am unable to figure this out.

I want it to show the results order by date.

Please can someone advise?

Thanks for any help.

Regards
M

meinIT

ASKER

please have the SQL and the view attached..

thanks
m

SQL Statement
=============
 
SELECT     TOP (100) PERCENT GCT.GiftCertificateTypeName, DATEPART(day, GCH.GiftHistDateAdded) AS day, DATEPART(month, GCH.GiftHistDateAdded) 
                      AS month, DATEPART(year, GCH.GiftHistDateAdded) AS year, COUNT(GCH.GiftCodeID) AS NumCertsSent, COALESCE (PT.PonyName, DN.DonkeyName, 
                      MN.MonkeyName, BC.BigCatName, TN.TurtleName, PN.PenguinName) AS SideName
FROM         dbo.GiftCertificateHistory AS GCH INNER JOIN
                      dbo.GiftCertificate AS GC ON GCH.GiftCodeID = GC.GiftCodeID INNER JOIN
                      dbo.GiftCertificateType AS GCT ON GC.GiftCertificateTypeID = GCT.GiftCertificateTypeID LEFT OUTER JOIN
                      dbo.AdoptAPony AS AP ON GC.GiftCodeID = AP.GiftCodeID LEFT OUTER JOIN
                      dbo.PonyType AS PT ON PT.PonyID = AP.PonyID LEFT OUTER JOIN
                      dbo.AdoptADonkey AS AD ON GC.GiftCodeID = AD.GiftCodeID LEFT OUTER JOIN
                      dbo.DonkeyName AS DN ON DN.DonkeyID = AD.DonkeyID LEFT OUTER JOIN
                      dbo.AdoptAMonkey AS AM ON GC.GiftCodeID = AM.GiftCodeID LEFT OUTER JOIN
                      dbo.MonkeyName AS MN ON MN.MonkeyID = AM.MonkeyID LEFT OUTER JOIN
                      dbo.AdoptABigCat AS ABC ON GC.GiftCodeID = ABC.GiftCodeID LEFT OUTER JOIN
                      dbo.BigCatName AS BC ON BC.BigCatID = ABC.BigCatID LEFT OUTER JOIN
                      dbo.AdoptATurtle AS TA ON GC.GiftCodeID = TA.GiftCodeID LEFT OUTER JOIN
                      dbo.TurtleName AS TN ON TN.TurtleID = TA.TurtleID LEFT OUTER JOIN
                      dbo.LearnItTaken AS LT ON GC.GiftCodeID = LT.GiftCodeID LEFT OUTER JOIN
                      dbo.LearnItName AS LN ON LN.LearnItID = LT.LearnItID LEFT OUTER JOIN
                      dbo.AdoptAPenguin AS PA ON GC.GiftCodeID = PA.GiftCodeID LEFT OUTER JOIN
                      dbo.PenguinName AS PN ON PN.PenguinID = PA.PenguinID
WHERE     (GCH.StatusID = 'REGISTERED') AND (GCH.GiftHistDateAdded >= DATEADD(dd, - 7, CAST(CONVERT(VARCHAR(10), GETDATE(), 120) AS DATETIME))) AND
                       (GCH.GiftHistDateAdded < GETDATE() - 1)
GROUP BY GCT.GiftCertificateTypeName, COALESCE (PT.PonyName, DN.DonkeyName, MN.MonkeyName, BC.BigCatName, TN.TurtleName, PN.PenguinName), 
                      DATEPART(day, GCH.GiftHistDateAdded), DATEPART(month, GCH.GiftHistDateAdded), DATEPART(year, GCH.GiftHistDateAdded)
ORDER BY DATEPART(year, GCH.GiftHistDateAdded) ,DATEPART(month, GCH.GiftHistDateAdded)  ,DATEPART(day, GCH.GiftHistDateAdded)  
 
 
View
====
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE VIEW [dbo].[viewWeeklyRegistrationsReport]
AS
SELECT     TOP (100) PERCENT GCT.GiftCertificateTypeName, DATEPART(day, GCH.GiftHistDateAdded) AS day, DATEPART(month, GCH.GiftHistDateAdded) 
                      AS month, DATEPART(year, GCH.GiftHistDateAdded) AS year, COUNT(GCH.GiftCodeID) AS NumCertsSent, COALESCE (PT.PonyName, DN.DonkeyName, 
                      MN.MonkeyName, BC.BigCatName, TN.TurtleName, PN.PenguinName) AS SideName
FROM         dbo.GiftCertificateHistory AS GCH INNER JOIN
                      dbo.GiftCertificate AS GC ON GCH.GiftCodeID = GC.GiftCodeID INNER JOIN
                      dbo.GiftCertificateType AS GCT ON GC.GiftCertificateTypeID = GCT.GiftCertificateTypeID LEFT OUTER JOIN
                      dbo.AdoptAPony AS AP ON GC.GiftCodeID = AP.GiftCodeID LEFT OUTER JOIN
                      dbo.PonyType AS PT ON PT.PonyID = AP.PonyID LEFT OUTER JOIN
                      dbo.AdoptADonkey AS AD ON GC.GiftCodeID = AD.GiftCodeID LEFT OUTER JOIN
                      dbo.DonkeyName AS DN ON DN.DonkeyID = AD.DonkeyID LEFT OUTER JOIN
                      dbo.AdoptAMonkey AS AM ON GC.GiftCodeID = AM.GiftCodeID LEFT OUTER JOIN
                      dbo.MonkeyName AS MN ON MN.MonkeyID = AM.MonkeyID LEFT OUTER JOIN
                      dbo.AdoptABigCat AS ABC ON GC.GiftCodeID = ABC.GiftCodeID LEFT OUTER JOIN
                      dbo.BigCatName AS BC ON BC.BigCatID = ABC.BigCatID LEFT OUTER JOIN
                      dbo.AdoptATurtle AS TA ON GC.GiftCodeID = TA.GiftCodeID LEFT OUTER JOIN
                      dbo.TurtleName AS TN ON TN.TurtleID = TA.TurtleID LEFT OUTER JOIN
                      dbo.LearnItTaken AS LT ON GC.GiftCodeID = LT.GiftCodeID LEFT OUTER JOIN
                      dbo.LearnItName AS LN ON LN.LearnItID = LT.LearnItID LEFT OUTER JOIN
                      dbo.AdoptAPenguin AS PA ON GC.GiftCodeID = PA.GiftCodeID LEFT OUTER JOIN
                      dbo.PenguinName AS PN ON PN.PenguinID = PA.PenguinID
WHERE     (GCH.StatusID = 'REGISTERED') AND (GCH.GiftHistDateAdded >= DATEADD(dd, - 7, CAST(CONVERT(VARCHAR(10), GETDATE(), 120) AS DATETIME))) AND
                       (GCH.GiftHistDateAdded < GETDATE() - 1)
GROUP BY GCT.GiftCertificateTypeName, COALESCE (PT.PonyName, DN.DonkeyName, MN.MonkeyName, BC.BigCatName, TN.TurtleName, PN.PenguinName), 
                      DATEPART(day, GCH.GiftHistDateAdded), DATEPART(month, GCH.GiftHistDateAdded), DATEPART(year, GCH.GiftHistDateAdded)
ORDER BY DATEPART(year, GCH.GiftHistDateAdded) ,DATEPART(month, GCH.GiftHistDateAdded)  ,DATEPART(day, GCH.GiftHistDateAdded)  
 
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "GCH"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 121
               Right = 224
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "GC"
            Begin Extent = 
               Top = 6
               Left = 262
               Bottom = 121
               Right = 461
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "GCT"
            Begin Extent = 
               Top = 6
               Left = 499
               Bottom = 121
               Right = 744
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AP"
            Begin Extent = 
               Top = 6
               Left = 782
               Bottom = 91
               Right = 950
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "PT"
            Begin Extent = 
               Top = 6
               Left = 988
               Bottom = 91
               Right = 1156
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AD"
            Begin Extent = 
               Top = 96
               Left = 782
               Bottom = 181
               Right = 950
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "DN"
            Begin Extent = 
               Top = 96
               Left = 988
               Bottom = 181
               Right = 1156
            End
            DisplayFlags = 280
            TopColumn = 0
         En' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'viewWeeklyRegistrationsReport'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'd
         Begin Table = "AM"
            Begin Extent = 
               Top = 126
               Left = 38
               Bottom = 211
               Right = 206
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "MN"
            Begin Extent = 
               Top = 126
               Left = 244
               Bottom = 211
               Right = 412
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "ABC"
            Begin Extent = 
               Top = 126
               Left = 450
               Bottom = 211
               Right = 618
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "BC"
            Begin Extent = 
               Top = 186
               Left = 656
               Bottom = 271
               Right = 824
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "TA"
            Begin Extent = 
               Top = 186
               Left = 862
               Bottom = 271
               Right = 1030
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "TN"
            Begin Extent = 
               Top = 186
               Left = 1068
               Bottom = 271
               Right = 1236
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "LT"
            Begin Extent = 
               Top = 216
               Left = 38
               Bottom = 301
               Right = 206
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "LN"
            Begin Extent = 
               Top = 216
               Left = 244
               Bottom = 301
               Right = 412
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "PA"
            Begin Extent = 
               Top = 216
               Left = 450
               Bottom = 316
               Right = 618
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "PN"
            Begin Extent = 
               Top = 276
               Left = 656
               Bottom = 391
               Right = 861
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 12
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'viewWeeklyRegistrationsReport'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'viewWeeklyRegistrationsReport'

Open in new window

udaya kumar laligondla

Post the select satatment

ASKER CERTIFIED SOLUTION

hari_tailor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

vinurajr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

meinIT

ASKER

thanks all.
i have a slight problem, the order is still seems not to be correct...
have used the following statement

SELECT * FROM viewWeeklyWebOrdersReport ORDER BY DAY DESC,MONTH DESC,YEAR DESC

attached is what i have got

31	3	2009
30	3	2009
30	3	2009
30	3	2009
30	3	2009
28	3	2009
28	3	2009
27	3	2009
26	3	2009
26	3	2009
26	3	2009
26	3	2009
26	3	2009
26	3	2009
26	3	2009
1	4	2009
 
this should rather would have been starting from 26 ..and ending to 1st april
 
please can you still advise?

Open in new window

hari_tailor

your new select statement will first desc day than month then year, so return result as you show
I think your statement like

Year, Month than Day

SELECT * FROM viewWeeklyWebOrdersReport ORDER BY YEAR DESC, MONTH DESC, DAY DESC