Click here to Skip to main content
15,897,891 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
SQL
Select  B.Date
  ,sum(Recurring) as Recurring
From(Select  A.Date
   ,(case when ServiceID = '0102592002' and Status like 'R%'
   then COUNT(idTrueH) else 0 end) as Recurring
    From(SELECT  *
  ,dateadd(dd,6,TimeModify) as timeexprie
  ,DATEDIFF(dd,getdate(),dateadd(dd,6,TimeModify)) as difday
  ,CONVERT(varCHAR(10),TimeModify,110) as Date
FROM Tb_LogTrueH
WHERE
  Status like 'R%' and  convert(varchar(10),dateadd(dd,6,TimeModify),110)  >= '12-16-2012'
   and convert(varchar(10),TimeModify,110) <= '12-16-2012')A
   Group by A.ServiceID,A.Status,a.idTrueH,A.Date)B
   Group by B.Date

VB
result

12-10-2012  >>  3
12-11-2012  >>  2
12-12-2012  >>  0
12-13-2012  >>  5
12-14-2012  >>  1
12-16-2012  >>  5


But I would result is

12-16-2012 >> 16
I would to Date and total
how would I do?
help me please?
Posted
Comments
PIEBALDconsult 21-Jan-13 23:16pm    
"convert(varchar(10),dateadd(dd,6,TimeModify),110) >= '12-16-2012'"


Don't do that; convert the literal to DATE or DATETIME and perform the compare in that datatype, not string -- especially in a bad format.


Also look into BETWEEN.


What exactly are you trying to get? A weekly total?
Zen Bk'ya TT'ya 22-Jan-13 3:59am    
Certainly
Help me please?
RedDk 22-Jan-13 14:44pm    
I kinda agree with Pc on this one; and by the way this is complex this SELECT you've got. I CAN, however see where you're coming from with this "ServiceID". A lover of order, I often smash all timedate info into a singular atomic integer. But that "serviceID" is ill-ordered for any timemachine entrance into the world of maths, as-is. And How about a looksee at table [Tb_LogTrueH] while you're at the refresh?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900