Adding MIN Date to Query 1

[Swi]

I have the following query in a ASP page:

        dbfilter=request.form("filter")
        dbbegindate=request.form("begindate")
        dbenddate=request.form("enddate")
        if trim(dbbegindate) <> "" And trim(dbenddate) <> "" then
            infosearchbuffer= "SELECT A.code AS Campaign, A.UniqueHits AS [Unique Hits], B.TotalHits AS [Total Hits] " & _
            "FROM (SELECT code, Count(*) AS UniqueHits FROM (SELECT DISTINCT code, logip FROM log WHERE " & _
            "logtimestamp >= #" & dbbegindate & " 12:00:00 AM# AND logtimestamp < #" & dbenddate & " 11:59:59 PM#) " & _
            "AS T GROUP BY code) AS A INNER JOIN (SELECT code, Count(*) AS TotalHits FROM log WHERE " & _
            "logtimestamp >= #" & dbbegindate & " 12:00:00 AM# AND logtimestamp < #" & dbenddate & " 11:59:59 PM# " & _
            "GROUP BY code) AS B ON A.code = B.code;"
        end if
	  rsinfo.Open infosearchbuffer,conQR,3,3

I would like to add another field (the first date of the entry) to the output of the recordset named logtimestamp in the database. I tried doing the following but I just keep getting join errors. I must be missing something easy but I can't see the forest through the trees. Been a long day.

        dbfilter=request.form("filter")
        dbbegindate=request.form("begindate")
        dbenddate=request.form("enddate")
        if trim(dbbegindate) <> "" And trim(dbenddate) <> "" then
            infosearchbuffer= "SELECT A.logtimestamp As BegDate, A.code AS Campaign, A.UniqueHits AS [Unique Hits], B.TotalHits AS [Total Hits] " & _
            "FROM (SELECT MIN(logtimestamp), code, Count(*) AS UniqueHits FROM (SELECT DISTINCT MIN(logtimestamp), code, logip FROM log WHERE " & _
            "logtimestamp >= #" & dbbegindate & " 12:00:00 AM# AND logtimestamp < #" & dbenddate & " 11:59:59 PM#) " & _
            "AS T GROUP BY code) AS A INNER JOIN (SELECT MIN(logtimestamp), code, Count(*) AS TotalHits FROM log WHERE " & _
            "logtimestamp >= #" & dbbegindate & " 12:00:00 AM# AND logtimestamp < #" & dbenddate & " 11:59:59 PM# " & _
            "GROUP BY code) AS B ON A.code = B.code;"
        end if
	  rsinfo.Open infosearchbuffer,conQR,3,3

Any help would be greatly appreciated.

Thanks.

Swi

 

[SkipVought]

hi,

Help yourself out! Structure your code (vba and sql) so in such a way that it's easier to understand what's going on.

I spent a few minutes just looking at your last SQL as is RAN using this code...

    Dim infosearchbuffer, dbbegindate, dbenddate
    dbbegindate = #9/1/2013#
    dbenddate = #9/30/2013#
    
    
    infosearchbuffer = "SELECT A.logtimestamp As BegDate, A.code AS Campaign, A.UniqueHits AS [Unique Hits], B.TotalHits AS [Total Hits] " & _
    "FROM (SELECT MIN(logtimestamp), code, Count(*) AS UniqueHits FROM (SELECT DISTINCT MIN(logtimestamp), code, logip FROM log WHERE " & _
    "logtimestamp >= #" & dbbegindate & " 12:00:00 AM# AND logtimestamp < #" & dbenddate & " 11:59:59 PM#) " & _
    "AS T GROUP BY code) AS A INNER JOIN (SELECT MIN(logtimestamp), code, Count(*) AS TotalHits FROM log WHERE " & _
    "logtimestamp >= #" & dbbegindate & " 12:00:00 AM# AND logtimestamp < #" & dbenddate & " 11:59:59 PM# " & _
    "GROUP BY code) AS B ON A.code = B.code;"

    Debug.Print infosearchbuffer

Thsn looking at the sql string...
[pre]
SELECT
A.logtimestamp As BegDate
, A.code AS Campaign
, A.UniqueHits AS [Unique Hits]
, B.TotalHits AS [Total Hits]

FROM
(
SELECT MIN(logtimestamp)
, code
, Count(*) AS UniqueHits
FROM
(
SELECT DISTINCT MIN(logtimestamp)
, code
, logip
FROM log
WHERE logtimestamp >= #9/1/2013 12:00:00 AM#
AND logtimestamp < #9/30/2013 11:59:59 PM#
) AS T GROUP BY code
) AS A INNER JOIN (
SELECT MIN(logtimestamp)
, code
, Count(*) AS TotalHits
FROM log
WHERE logtimestamp >= #9/1/2013 12:00:00 AM#
AND logtimestamp < #9/30/2013 11:59:59 PM#
GROUP BY code
) AS B ON A.code = B.code;
[/pre]
Can you see the problem? It JUMPS OFF THE PAGE when you order your code!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oops, I see that I missed the parenthesis after the IINER JOIN.

Regardless, it was much easier to find that if the code is orders than not.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

I'd try this instead:

infosearchbuffer= "SELECT B.BegDate, A.code AS Campaign, A.UniqueHits AS [Unique Hits], B.TotalHits AS [Total Hits] " & _
"FROM (SELECT code, Count(*) AS UniqueHits FROM (SELECT DISTINCT code, logip FROM log WHERE " & _
"logtimestamp >= #" & dbbegindate & " 12:00:00 AM# AND logtimestamp < #" & dbenddate & " 11:59:59 PM#) " & _
"AS T) AS A INNER JOIN (SELECT MIN(logtimestamp) As BegDate, code, Count(*) AS TotalHits FROM log WHERE " & _
"logtimestamp >= #" & dbbegindate & " 12:00:00 AM# AND logtimestamp < #" & dbenddate & " 11:59:59 PM# " & _
"GROUP BY code) AS B ON A.code = B.code;"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Swi]

PHV,

Thank you for the code. I had to add a GROUP BY code after the "AS T but other than that worked great. Thanks!

Skip,

Point well taken.

Swi