
SQL Dates with a join?
Hello :)<BR><BR>I'm new at this please help, this is a two part question..<BR><BR>1. How do I add column totals in an inner join?<BR><BR><BR>2. In the inner join where statement, how do I tell it to look for the dates specified for each join selected?<BR><BR><BR>Here is what I have as an example (a non working example.. :)<BR><BR><BR>SELECT COUNT(dbo.cat_MSC_Doc.DATESENT) + COUNT(dbo.cat_MSC_Doc.resentdate) + COUNT(dbo.cat_MSC_ResendDate.resentdate) AS Expr1<BR>FROM dbo.cat_MSC_Doc INNER JOIN<BR> dbo.cat_MSC_ResendDate ON dbo.cat_MSC_Doc.cat_MSC_Doc_Key = dbo.cat_MSC_ResendDate.cat_MSC_Doc_Key<BR><BR><BR> ??????WHERE (GETDATE()  10) AND (GETDATE()  9)????????????<BR><BR>I need the getdate to pull from all three collumns in the inner join and I'm not sure how to get it to work?<BR><BR>Please Help!!! :)

RE: SQL Dates with a join?
Are you using SQL Server or Access?<BR><BR>I'm not sure what your trying to add up. Let's look at the DatePart. First of All GetDate() doesn't pull from any column in your query, it returns the current date.<BR><BR>You need to reference the actual column in your where statement:<BR><BR>Where dbo.cat_MSC_Doc.DATESENT between 'aDate' and 'anotherDate'<BR>And dbo.cat_MSC_Doc.resentdate between 'aDate' and 'anotherDate'<BR><BR>You need to figure out the logic here: Do all the dates have to be between something, or just one etc?<BR><BR>If you want to add or subtract days from a date, use <BR>DateAdd(daypart, number, Date)<BR><BR>In your case something like <BR>DateAdd('d', 10, dbo.cat_MSC_Doc.DATESENT)

RE: using Asp and Microsoft SQL
Hi drs1..<BR><BR>Thanks for your help!<BR><BR>Ok.. So I'm using Microsoft SQL connecting to an ASP page.<BR>Yes, all the dates in the 3 joined columns need to be calculated between specific dates, and then added together to get a master total.<BR><BR>Example:<BR>Total for column1 between a specific dates<BR>Total for column2 between a specific dates<BR>Total for column3 between a specific dates<BR><BR>Then a total of all three added up for a final total.<BR><BR><BR><BR>This is what I did have, I can only get it to work with a single statement using "DATESENT" column. I'm not sure how to get the join to total each column up between a specified dates then add those totals together.<BR><BR><BR><BR>WHERE (cat_MSC_Doc.DATESENT > GETDATE()  10) AND (cat_MSC_Doc.DATESENT < GETDATE()  9)<BR><BR><BR>any sugestions??

RE: help help
Hi drs1..<BR><BR>Thanks for your help!<BR><BR>Ok.. So I'm using Microsoft SQL connecting to an ASP page.<BR>Yes, all the dates in the 3 joined columns need to be calculated between specific dates, and then added together to get a master total.<BR><BR>Example:<BR>Total for column1 between a specific dates<BR>Total for column2 between a specific dates<BR>Total for column3 between a specific dates<BR><BR>Then a total of all three added up for a final total.<BR><BR><BR><BR>This is what I did have, I can only get it to work with a single statement using "DATESENT" column. I'm not sure how to get the join to total each column up between a specified dates then add those totals together.<BR><BR><BR><BR>WHERE (cat_MSC_Doc.DATESENT > GETDATE()  10) AND (cat_MSC_Doc.DATESENT < GETDATE()  9)<BR><BR><BR>any sugestions??

Are the dates the same?
Or are the dates for each column different?<BR><BR>If each is different, I'd just get three separate totals and add them with a tiny bit of VBS code.<BR><BR>You *can* do it all in SQL but it's ugly and big (or at least the way I know to do it is).<BR><BR>Your WHERE clause looks suspicious to me.<BR><BR>Are you aware the GETDATE() returns both date *AND* time?<BR><BR>SO you are, essentially, saying "all records with a DATESENT field that is later then 10 days ago *at this time of day*".<BR><BR>Is that what you want? If not, you ought to be using DATEDIFF to subtract and get the whole days of difference.<BR><BR>Or, maybe better, generate the dates in VBS code and use BETWEEN.<BR><BR>

RE: about the dates. help help
Hi Bill :)<BR><BR>ahh.. yes.. about the dates..<BR><BR>They are all time stamped and I'm looking to pull them up from a certin amount of previous days.<BR><BR>Datediff is good with me,(newbie! not totally sure how to do it?) but I need to pull them all in a SQL statement that totals each colomn amount within a date frame and then adds those together. <BR><BR>How would you set up a join statement to do this? If your columns looked something like below..<BR><BR>(Only and example of the columns I need to pull the info from.(not real info))<BR><BR>dbo.a. DATESENT dbo.a. resentdate dbo.b.resentdate<BR>   <BR>08/01/02 10:00pm 08/01/02 8:00pm 7/31/02 7:45pm<BR><BR><BR><BR>The total for today would be 2<BR>The total for today and yesterday would be 3<BR><BR>Total<BR><BR>2<BR><BR>

RE: about the dates. help help
I really think you may need to OUTER JOIN that second table. If there is not always a record in that table for each record in the first table you will miss out on some dates. If you're not familiar with outer joins I'd check out the database faqs to get the general idea.<BR><BR>Also, if the sent date and resent date are both within your range, how do you want to count that for the total? One or two?

RE: about the dates. help help
Hi,<BR><BR>So OUTER JOIN.. I'll try it. <BR>To answer your question I need to count each column separately.<BR>So the final total would be 2 for 8/1/02.<BR>(Adding column 1 and column 2 together). <BR>(If all 3 columns were the same date then the total would be 3 because there are 3 columns)<BR><BR>The main issue is counting them separately.<BR><BR>Do you have an example of how to count via the date?<BR>

RE: about the dates. help help
Since you really just want to total up all these fields I think the easiest way will be to just do this like 3 different selects. No need to join any tables here and you can execute all 3 selects in one statement.<BR><BR>Select <BR>sendCount=(Select count(SendDate) From YourTable Where DateDiff(d,SendDate,GetDate()) < 5),<BR>reSendCount=(Select count(reSendDate) From YourOtherTable Where DateDiff(d,reSendDate,GetDate()) < 5),<BR>... <BR><BR>That would be where the dates were within the past 5 days. I still can't tell what kind of range you're trying to get but let's say you want the range between 5  10 days ago...<BR>change the where clause to something like:<BR><BR>Where DateDiff(d,SendDate,GetDate()) between 5 And 10<BR><BR>To get the grand total I would just add up these 3 values on you ASP page as Bill suggested.

RE: right on, but there is more. help help
Right on!! That sounds great!! But there is more..<BR><BR>I am trying to add the three columns up to use in this asp script that pulls the days of the week from the previouse week. and that is why I need it to all be done in the SQL part.<BR><BR>Can you think of a any way to pull all the totals from one SQL statement?<BR><BR>Check out the script I'm using.. This is where I'm trying fit in the sql statement.<BR><BR><BR><BR><BR><BR>This what I'm using now and need to pull the other column totals into it.. I'm not quite sure how to do it.. <BR><BR><BR><BR>***************************<BR>� 60;table border="1" width="80%" bordercolorlight="#666699" cellspacing="10" bordercolordark="#FFFFFF"><BR> <tr><BR> <BR> <%<BR> 'checking date<BR> datetocheck = Date() <BR> do until datepart("w", datetocheck) =2<BR> DateToCheck = DateAdd("d", 1, datetocheck)<BR> loop<BR> <BR> sqlx = "SELECT case when datepart(dw, convert(varchar(10), DATESENT, 101)) = 1 then 'Sunday' when datepart(dw, convert(varchar(10), DATESENT, 101)) = 2 then 'Monday' when datepart(dw, convert(varchar(10), DATESENT, 101)) = 3 then 'Tuesday' when datepart(dw, convert(varchar(10), DATESENT, 101)) = 4 then 'Wednesday' when datepart(dw, convert(varchar(10), DATESENT, 101)) = 5 then 'Thursday' when datepart(dw, convert(varchar(10), DATESENT, 101)) = 6 then 'Friday' when datepart(dw, convert(varchar(10), DATESENT, 101)) = 7 then 'Saturday' end wd, convert(varchar(10), DATESENT, 101) [date1], COUNT(*) AS 'ct' FROM dbo.cat_MSC_Doc WHERE convert(varchar(10), DATESENT, 112) between convert(varchar(10), convert(datetime, '" & dateadd("d", 7, datetocheck) & "'), 112) and converT(varchar(10), convert(datetime, '"&dateadd("d", 1, datetocheck)&"'), 112) GROUP BY datepart(dw, convert(varchar(10), DATESENT, 101)), convert(varchar(10), DATESENT, 101) order by convert(varchar(10), DATESENT, 101)"<BR> 'Response.Write replace(sqlx, ",", ",<BR>")<BR> Set adox = CreateObject("ADODB.Recordset")<BR> adox.Open sqlx, LOKI_Envelope, 3,3<BR> <BR> <BR> <BR> i = 1<BR> TotalCSCs = 0<BR> do until adox.eof<BR> <BR> ct = adox("ct")<BR> <BR> <BR> %><BR> <BR> <BR> <TD><BR> <TABLE border="0"><BR> <TR><BR> <td width="11%" bgcolor="#CCCCCC" align="center"><font face="Arial" color="#666699"><%=adox("WD")%>&nbs p;<%=adox("Date1")%></font></td><BR> </TR><BR> <TR><BR> <td width="11%" align="center"><%=ct%> </td><BR> </TR><BR> </TABLE><BR> </TD><BR> <BR> <% <BR> if i = 7 then<BR> i = 1<BR> Response.Write "</TR><TR>"<BR> end if<BR> i = i + 1<BR> TotalCSCs = TotalCSCs + ct<BR> adox.MoveNext<BR> loop<BR> 'adox.close%><BR> <BR><BR> <TD><BR> <TABLE border="0"><BR> <TR><BR> <td width="11%" bgcolor="#CCCCCC" align="center"><font face="Arial" color="#666699">Total <BR> Sent</font></td><BR> </TR><BR> <TR><BR> <td width="11%" align="center"><%=TotalCSCs%> </td><BR> </TR><BR> </TABLE><BR> </TD><BR> <%adox.Close%><BR>********************** ***********<BR><BR><BR>
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

