Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2009
    Posts
    1

    Question Unanswered: Counting rows with dates older than 6 months

    Hi

    Hope someone can help me here.
    I have a table called Yearly_stats_cape_town with the following relevant columns:

    Case_No, BWC, Custody_date

    I need to count the number of unique Case_no where BWC =C and the custody_date is 6 months before the current date.

    My current query looks like this :
    SELECT COUNT(*) AS [CASE NO1]
    FROM (SELECT DISTINCT [CASE NO] AS [CASE NO 2]
    FROM [YEARLY STATS CAPE TOWN])

    This happily returns the total number of unique case numbers in the table.

    I do not know how to proceed though to filter BWC to C and custody_date to 6 months and older

    After 2 days of struggling with datediff, getdate and dateadd I seriously feel like crying.


  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Not sure if this fulfils the requirement. I had to make a few assumptions along the way:
    Code:
    declare @YEARLY_STATS_CAPE_TOWN table
    (case_no varchar(10),
     bwc char(1),
     custody_date datetime)
    
    insert into @YEARLY_STATS_CAPE_TOWN
    select 'case1', 'c', getdate() - 190 union all
    select 'case1', 'a', getdate() - 190 union all
    select 'case1', 'b', getdate() - 190 union all
    select 'case2', 'c', getdate() - 160 union all
    select 'case2', 'b', getdate() - 180 union all
    select 'case2', 'a', getdate() - 190 union all
    select 'case3', 'c', getdate() - 190 union all
    select 'case3', 'c', getdate() - 190 
    
    select count(*), case_no
    from @YEARLY_STATS_CAPE_TOWN
    where bwc = 'c'
      and custody_date < getdate() - 180
    group by case_no

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •