0

We need to run a query which pulls a count of all the records added by day into a data extension. We cannot use any of the reports as the system add date is the same for every record.

We created a query which should pull the aggregated date + count data. When we compile it says it is fine - however when we execute the query nothing happens.

SELECT CONVERT(date, O.ouraddate) as dateadded, count(*) as count  FROM [Our customer table] O with (nolock)
group by  CONVERT(date, O.ouraddate)

The data extension this query is loading into contains 2 fields: count - Text(100) dateadded - Text(100)

Anyone know how we can get this query to work?

Cheers Bryan

1 Answer 1

3

It looks like you just need to change the usage of CONVERT(). This should work for you:

SELECT CONVERT(VARCHAR(10), O.ouraddate, 101) AS [dateadded], 
       Count(*)                        AS count 
FROM   [Our Customer Table] O 
GROUP  BY CONVERT(VARCHAR(10), O.ouraddate, 101) 

Reference: https://msdn.microsoft.com/en-us/library/ms187928.aspx

1
  • Thanks that worked - used the following SELECT CONVERT(date, O.enewsaddate) as dateadded, count(*) as count FROM [Customer Table] O with (nolock) where Dateadd(day,-92,getdate()) <= O.enewsaddate and enewsoptin in ('N','Y') group by CONVERT(date, O.enewsaddate)
    – BDOB83
    Oct 23, 2015 at 22:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .