9e8db04f   
   From: hugo@perFact.REMOVETHIS.info.INVALID   
      
   On Tue, 8 Dec 2009 12:18:30 -0800 (PST), hayko98 wrote:   
      
   >Hello everybody.   
   >I need help with my stats report.I have following query .   
   >   
   >DECLARE @DateFrom DATETIME;   
   >DECLARE @DateTo DATETIME;   
   >DECLARE @Provider INT;   
   >SET @DateFrom='2009-12-01 00:00:00.0';   
   >SET @DateTo='2009-12-01 23:55:00.0';   
   >SET @Provider=6;   
   >   
   >SELECT   
   >   
   >SUM(case when AUDIT_CHANGES LIKE '%A%'   then 1 else 0 end) AS [A],   
   >SUM(case when AUDIT_CHANGES LIKE '%B1%'  then 1 else 0 end) AS [B1],   
   >SUM(case when AUDIT_CHANGES LIKE '%B2%'  then 1 else 0 end) AS [B2],   
   >SUM(case when AUDIT_CHANGES LIKE '%B3%'  then 1 else 0 end) AS [B3],   
   >SUM(case when AUDIT_CHANGES LIKE '%B4%'  then 1 else 0 end) AS [B4],   
   >SUM(case when AUDIT_CHANGES LIKE '%B5%'  then 1 else 0 end) AS [B5],   
   >   
   >FROM  AUDIT_TRAIL WHERE  PROVIDER_ID = @Provider   
   >AND   
   >                            TIME_STAMP BETWEEN @DateFrom AND @DateTo   
   >AND   
   >     AUDIT_CHANGES IS NOT NULL   
   >   
   >   
   >Provider 	A	B1	B2	B3	B4	B5   
   >XXX	0	23	7	10	8	8   
   >   
   >   
   >This report gives me stats for one day(12.01.09).I need to get results   
   >for from 11.01.09 thru 11.30.09 for each weekday.   
   >   
   >I have SQL Server 2005 .   
   >   
   >Thank you   
      
   Hi Hayko,   
      
   Something like this, I guess:   
      
   SELECT   DATEADD(day,   
                    DATEDIFF(day, '20000101', TIME_STAMP),   
                   '20000101') AS Day,   
            PROVIDER_ID AS Provider,   
            SUM(case when AUDIT_CHANGES LIKE '%A%'   
                     then 1 else 0 end) AS [A],   
            SUM(case when AUDIT_CHANGES LIKE '%B1%'   
                     then 1 else 0 end) AS [B1],   
            SUM(case when AUDIT_CHANGES LIKE '%B2%'   
                     then 1 else 0 end) AS [B2],   
            SUM(case when AUDIT_CHANGES LIKE '%B3%'   
                     then 1 else 0 end) AS [B3],   
            SUM(case when AUDIT_CHANGES LIKE '%B4%'   
                     then 1 else 0 end) AS [B4],   
            SUM(case when AUDIT_CHANGES LIKE '%B5%'   
                     then 1 else 0 end) AS [B5]   
   FROM     AUDIT_TRAIL   
   WHERE    TIME_STAMP >= '20091101'   
   AND      TIME_STAMP <  '20091201'   
   AND      AUDIT_CHANGES IS NOT NULL   
   GROUP BY DATEDIFF(day, '20000101', TIME_STAMP), PROVIDER_ID;   
      
   --   
   Hugo Kornelis, SQL Server MVP   
   My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)