need lillte bit help in my query

• isa-369599

  SSCommitted

  Points: 1602

  More actions

  November 15, 2006 at 11:59 pm

  #90756

  hello everyone :

  here is my query:

  Declare @Date datetime

  select top 1

  (select count(*) as [Re]  from Buyers where RegistrationDate= @Date) as '7',

  (select count(*) as [ree]  from Buyers where RegistrationDate = DATEADD(DAY, -1, DATEDIFF(DAY, 0, @Date))) as '6',

  (select count(*)  from Buyers where RegistrationDate = DATEADD(DAY, -2, DATEDIFF(DAY, 0, @Date))) as '5',

  (select count(*)  from Buyers where RegistrationDate = DATEADD(DAY, -3, DATEDIFF(DAY, 0, @Date))) as '4',

  from Buyers

  as if u see i used the alias  7,6,5,4 (hardcoded) now i used here as it gets the actaul day of the week of the date it retrived , for example in sqlserver 2000 we used datepart(dw,@Date) function for getting day of week , iused this as heading of my columns , plz tell me how can i get this ,

  plz tell me abt this.

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  November 16, 2006 at 11:40 am

  #672390

  I'm not clear about your question.  This gives me the same results without "hardcoding" the numbers in text as the field values...  (I also stripped the extra DATEDIFF with zero [0] as the input). 

   

  DECLARE @Buyers TABLE( RegistrationDate datetime)

  INSERT INTO @Buyers

  SELECT '12/31/2000'

  UNION

  SELECT '12/30/2000'

  UNION

  SELECT '12/29/2000'

  UNION

  SELECT '12/28/2000'

  UNION

  SELECT '12/27/2000'

  UNION

  SELECT '12/26/2000'

  UNION

  SELECT '12/31/2000'

  DECLARE @Date datetime

  SET @Date = '12/31/2000'

  SELECT TOP 1

       (SELECT COUNT(*) FROM @Buyers WHERE RegistrationDate = @Date) AS '7',

       (SELECT COUNT(*) FROM @Buyers WHERE RegistrationDate = DATEADD( DAY, -1, DATEDIFF( DAY, 0, @Date))) AS '6',

       (SELECT COUNT(*) FROM @Buyers WHERE RegistrationDate = DATEADD( DAY, -2, DATEDIFF( DAY, 0, @Date))) AS '5',

       (SELECT COUNT(*) FROM @Buyers WHERE RegistrationDate = DATEADD( DAY, -3, DATEDIFF( DAY, 0, @Date))) AS '4'

  FROM @Buyers

  SELECT TOP 1

       (SELECT COUNT(*) FROM @Buyers WHERE RegistrationDate = @Date) AS [@Date],

       (SELECT COUNT(*) FROM @Buyers WHERE RegistrationDate = DATEADD( DAY, -1, @Date)) AS [@Date - 1],

       (SELECT COUNT(*) FROM @Buyers WHERE RegistrationDate = DATEADD( DAY, -2, @Date)) AS [@Date - 2],

       (SELECT COUNT(*) FROM @Buyers WHERE RegistrationDate = DATEADD( DAY, -3, @Date)) AS [@Date - 3]

  FROM @Buyers

  I wasn't born stupid - I had to study.