Converting 'Long Date' to 'Short Date' and using the 'Short Date' in the 'Group By' 1

[barnettjacob]

Hi, I've got the following query which I'm trying to streamline to bring through summarized data. The problem is that in our database all the sales are recorded as a 'long' date with the time which means I am struggling to summarize by day. I've got the code to reformat the date which works well but I can't get that to drive the 'group by' which kind of defeats the object!

Any help would be appreciated.
Jacob

select

p.Analysis1Code,
p.Supplier1,
s.branchCode,
(SELECT CONVERT(VARCHAR(10), Saledate, 103)) as date,
sum(quantity),
sum(fcextendednetamount-fcextendedtaxamount) as 'FCNetSales',
sum(grossprofitalternategp) as 'AltGP',

from eee..vwsaleline_gp s
join eee..product p on p.productcode = s.productcode

where saledate >= '07/01/10' 

group by (SELECT CONVERT(VARCHAR(10), Saledate, 103)), p.Analysis1Code, p.Supplier1, s.branchCode

order by s.BranchCode, s.saledate, p.Analysis1Code,p.Supplier1

 

[r937]

SELECT s.branchCode
     , CONVERT(VARCHAR(10),Saledate,103) AS the_date
     , p.Analysis1Code
     , p.Supplier1
     , SUM(quantity)
     , SUM(fcextendednetamount-fcextendedtaxamount) AS 'FCNetSales'
     , SUM(grossprofitalternategp) AS 'AltGP'
  FROM eee..vwsaleline_gp AS s
INNER  
  JOIN eee..product AS p 
    ON p.productcode = s.productcode
 WHERE s.saledate >= '07/01/10' 
GROUP 
    BY s.BranchCode
     , the_date
     , p.Analysis1Code
     , p.Supplier1 
ORDER 
    BY s.BranchCode
     , the_date
     , p.Analysis1Code
     , p.Supplier1

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[barnettjacob]

Thanks Rudy, really appreciate it!
Jacob