SQL Date Format

Hello Experts,

Can you please help with this,  I need to group the data but my booking date is in  2010-09-03 10:36:00.000 because of which i get multiple rows of data reason amendments done to booking at different during the day.

I have attached the code but do let me know if i can improve the code.

Looking forward for some replies
SELECT     
CASE WHEN [booking product] IN ('corp', 'wtml', 'grp') THEN 'Corporate' ELSE [booking product] END AS BookingProduct, 
[Booking Ref], 
                      
[Booked Adults] + [Booked Children] + [Booked Infants] AS Pax, [Booking Dep Date], 
CASE WHEN dbo.View_EUR_Masterview.[CountryCode] IS NULL THEN [Booking Country Code] ELSE [CountryCode] END AS [Component Country], 
                      

 
CASE WHEN dbo.View_EUR_Masterview.[creationdate] > dbo.View_EUR_Masterview.[booking conf date] 
THEN dbo.View_EUR_Masterview.[Creationdate]  
ELSE dbo.View_EUR_Masterview.[Booking Conf Date] 
END AS [Booking Date], 


suppliercode, name, 
SUM(COALESCE (GBPCost, 0)) AS GbpCost, 
                      
SUM(COALESCE (price, 0)) AS Price, 
SUM(COALESCE (VATonCommission, 0)) AS VAT, SUM(COALESCE (price, 0)) - SUM(COALESCE (GBPCost, 0)) 
                      - SUM(COALESCE (VATonCommission, 0)) AS Commission, CASE WHEN dbo.View_EUR_Masterview.[owner] IS NULL 
                      THEN dbo.View_EUR_Masterview.[booking owner] WHEN dbo.View_EUR_Masterview.[owner] IN ('Amy.jeffery', 'charlie.gordon', 'julie.horner', 
                      'gabrielle.mears', 'tom.cook') THEN [booking owner] ELSE dbo.View_EUR_Masterview.[owner] END AS Consultant, [Booking Counter], 
                      CASE WHEN [booking product] IN ('corp', 'wtml', 'grp') THEN 'Corporate' WHEN [booking product] IN ('leis', 'best', 'bcfc', 'tmg') 
                      THEN 'Lesiure' ELSE 'White Star' END AS SalesArea
FROM         dbo.View_EUR_Masterview

WHERE     
(BookingStatus = 'confirmed') AND 
([Booking Conf Date] >= CONVERT(DATETIME, '2010-06-01 00:00:00', 110)) 
AND (paymenttype <> 'referral') AND 
(suppliercode NOT IN ('1', '01')) AND 
(name IS NOT NULL) AND 
(name NOT IN ('WEXAS Travel Foundation')) 
and [booking ref] ='L003677'

GROUP BY 
[Booking Conf Date], creationdate, 
[Booking Product], [Booking Dep Date], 
[Booking Country Code], CountryCode, suppliercode, name, 
[Booking Ref],  [Booked Adults], [Booked Children], Owner, 
[Booking Owner], [Booked Infants], BookingStatus, 
[Booking Counter]
--,[BOOKING DATE]

Open in new window

wexasAsked:
Who is Participating?
 
Lee SavidgeCommented:
Truncate the time portion.

GROUP BY
[Booking Conf Date], creationdate,
[Booking Product], [Booking Dep Date],
[Booking Country Code], CountryCode, suppliercode, name,
[Booking Ref],  [Booked Adults], [Booked Children], Owner,
[Booking Owner], [Booked Infants], BookingStatus,
[Booking Counter],
cast(convert(nvarchar(10), [BOOKING DATE], 101) as datetime)
0
 
Lee SavidgeCommented:
Or 103 if your your database date format is UK:

cast(convert(nvarchar(10), [BOOKING DATE], 103) as datetime)
0
 
wexasAuthor Commented:
thanks for your prompt reply, is it possible to add convert with this statement. Something like


CASE WHEN dbo.View_EUR_Masterview.[creationdate] > dbo.View_EUR_Masterview.[booking conf date]
THEN dbo.View_EUR_Masterview.[Creationdate]  
ELSE dbo.View_EUR_Masterview.[Booking Conf Date]
END AS [Booking Date],


I have used what you suggested but it is still not grouping the result, attached is the output

Thanks for your assistance
SQL-Output.xls
0
 
SharathData EngineerCommented:
try this.
SELECT CASE 
           WHEN [booking product] IN ('corp','wtml','grp') THEN 'Corporate' 
           ELSE [booking product] 
         END AS BookingProduct, 
         [Booking Ref], 
         [Booked Adults] + [Booked Children] + [Booked Infants]                               AS Pax,
         [Booking Dep Date], 
         CASE 
           WHEN dbo.View_EUR_Masterview.[CountryCode] IS NULL THEN [Booking Country Code] 
           ELSE [CountryCode] 
         END AS [Component Country], 
         CASE 
           WHEN dbo.View_EUR_Masterview.[creationdate] > dbo.View_EUR_Masterview.[booking conf date] THEN CONVERT(VARCHAR,dbo.View_EUR_Masterview.[Creationdate],
                                                                                                                  101)
           ELSE CONVERT(VARCHAR,dbo.View_EUR_Masterview.[Booking Conf Date], 
                        101) 
         END AS [Booking Date], 
         suppliercode, 
         name, 
         SUM(COALESCE(GBPCost,0))                                                             AS GbpCost,
         SUM(COALESCE(price,0))                                                               AS Price,
         SUM(COALESCE(VATonCommission,0))                                                     AS VAT,
         SUM(COALESCE(price,0)) - SUM(COALESCE(GBPCost,0)) - SUM(COALESCE(VATonCommission,0)) AS Commission,
         CASE 
           WHEN dbo.View_EUR_Masterview.[owner] IS NULL THEN dbo.View_EUR_Masterview.[booking owner]
           WHEN dbo.View_EUR_Masterview.[owner] IN ('Amy.jeffery','charlie.gordon','julie.horner','gabrielle.mears',
                                                    'tom.cook') THEN [booking owner] 
           ELSE dbo.View_EUR_Masterview.[owner] 
         END AS Consultant, 
         [Booking Counter], 
         CASE 
           WHEN [booking product] IN ('corp','wtml','grp') THEN 'Corporate' 
           WHEN [booking product] IN ('leis','best','bcfc','tmg') THEN 'Lesiure' 
           ELSE 'White Star' 
         END AS SalesArea 
    FROM dbo.View_EUR_Masterview 
   WHERE (BookingStatus = 'confirmed') 
         AND ([Booking Conf Date] >= CONVERT(DATETIME,'2010-06-01 00:00:00',110)) 
         AND (paymenttype <> 'referral') 
         AND (suppliercode NOT IN ('1','01')) 
         AND (name IS NOT NULL) 
         AND (name NOT IN ('WEXAS Travel Foundation')) 
         AND [booking ref] = 'L003677' 
GROUP BY [Booking Conf Date], 
         creationdate, 
         [Booking Product], 
         [Booking Dep Date], 
         [Booking Country Code], 
         CountryCode, 
         suppliercode, 
         name, 
         [Booking Ref], 
         [Booked Adults], 
         [Booked Children], 
         Owner, 
         [Booking Owner], 
         [Booked Infants], 
         BookingStatus, 
         [Booking Counter], 
         CASE 
           WHEN dbo.View_EUR_Masterview.[creationdate] > dbo.View_EUR_Masterview.[booking conf date] THEN CONVERT(VARCHAR,dbo.View_EUR_Masterview.[Creationdate],
                                                                                                                  101)
           ELSE CONVERT(VARCHAR,dbo.View_EUR_Masterview.[Booking Conf Date], 
                        101) 
         END

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.