If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with a subquery
This is a subquery that Duane helped me with to calculate hours for
the previous 7 days: HoursLast7: (SELECT SUM(On_Duty_Hours) FROM [Driver Hours] t WHERE t.Hours_ID = [Driver Hours].Hours_ID AND t.Hours_Date BETWEEN [Driver Hours].Hours_Date -6 and [Driver Hours].Hours_Date) What I would like to do is to have the total reset to zero if any two consecutive days in the previous 7 equal zero. Do I need a separate query/subquery or can this be done inside of the current one? Thanks Justin Thomas |
#2
|
|||
|
|||
Help with a subquery
t.Hours_Date BETWEEN [Driver Hours].Hours_Date -6 and [Driver
Hours].Hours_Date) I was examining this to decide on an approach and discovered this will not work as it is requiring that [Driver Hours].Hours_Date be between itself and itself minus 6 days. Maybe you want [Driver Hours].Hours_Date to be between Date() and Date()-6 or maybe between Max([Driver Hours].Hours_Date) and Max([Driver Hours].Hours_Date)-6 or even a date entered at a prompt. -- Build a little, test a little. "Justin" wrote: This is a subquery that Duane helped me with to calculate hours for the previous 7 days: HoursLast7: (SELECT SUM(On_Duty_Hours) FROM [Driver Hours] t WHERE t.Hours_ID = [Driver Hours].Hours_ID AND t.Hours_Date BETWEEN [Driver Hours].Hours_Date -6 and [Driver Hours].Hours_Date) What I would like to do is to have the total reset to zero if any two consecutive days in the previous 7 equal zero. Do I need a separate query/subquery or can this be done inside of the current one? Thanks Justin Thomas . |
#3
|
|||
|
|||
Help with a subquery
On Feb 22, 3:35*pm, KARL DEWEY
wrote: *t.Hours_Date BETWEEN [Driver Hours].Hours_Date -6 and [Driver Hours].Hours_Date) I was examining this to decide on an approach and discovered this will not work as it is requiring that [Driver Hours].Hours_Date be between itself and itself minus 6 days. Maybe you want [Driver Hours].Hours_Date to be between Date() and Date()-6 or maybe between Max([Driver Hours].Hours_Date) and Max([Driver Hours].Hours_Date)-6 or even a date entered at a prompt. -- Build a little, test a little. "Justin" wrote: This is a subquery that Duane helped me with to calculate hours for the previous 7 days: HoursLast7: (SELECT SUM(On_Duty_Hours) FROM [Driver Hours] t WHERE t.Hours_ID = [Driver Hours].Hours_ID AND t.Hours_Date BETWEEN [Driver Hours].Hours_Date -6 and [Driver Hours].Hours_Date) What I would like to do is to have the total reset to zero if any two consecutive days in the previous 7 equal zero. Do I need a separate query/subquery or can this be done inside of the current one? Thanks Justin Thomas . This query works as it is. I just need to have the total to reset zero if any two previous days equal zero. |
#4
|
|||
|
|||
Help with a subquery
If you will post your actual SQL maybe some one can suggest a method for you.
Open query in design view and click on VIEW - SQL View, hightlight all, copy, and paste in a post. -- Build a little, test a little. "Justin" wrote: On Feb 22, 3:35 pm, KARL DEWEY wrote: t.Hours_Date BETWEEN [Driver Hours].Hours_Date -6 and [Driver Hours].Hours_Date) I was examining this to decide on an approach and discovered this will not work as it is requiring that [Driver Hours].Hours_Date be between itself and itself minus 6 days. Maybe you want [Driver Hours].Hours_Date to be between Date() and Date()-6 or maybe between Max([Driver Hours].Hours_Date) and Max([Driver Hours].Hours_Date)-6 or even a date entered at a prompt. -- Build a little, test a little. "Justin" wrote: This is a subquery that Duane helped me with to calculate hours for the previous 7 days: HoursLast7: (SELECT SUM(On_Duty_Hours) FROM [Driver Hours] t WHERE t.Hours_ID = [Driver Hours].Hours_ID AND t.Hours_Date BETWEEN [Driver Hours].Hours_Date -6 and [Driver Hours].Hours_Date) What I would like to do is to have the total reset to zero if any two consecutive days in the previous 7 equal zero. Do I need a separate query/subquery or can this be done inside of the current one? Thanks Justin Thomas . This query works as it is. I just need to have the total to reset zero if any two previous days equal zero. . |
#5
|
|||
|
|||
Help with a subquery
On Feb 23, 1:12*am, KARL DEWEY
wrote: If you will post your actual SQL maybe some one can suggest a method for you. Open query in design view and click on VIEW - SQL View, hightlight all, copy, and paste in a post. -- Build a little, test a little. "Justin" wrote: On Feb 22, 3:35 pm, KARL DEWEY wrote: *t.Hours_Date BETWEEN [Driver Hours].Hours_Date -6 and [Driver Hours].Hours_Date) I was examining this to decide on an approach and discovered this will not work as it is requiring that [Driver Hours].Hours_Date be between itself and itself minus 6 days. Maybe you want [Driver Hours].Hours_Date to be between Date() and Date()-6 or maybe between Max([Driver Hours].Hours_Date) and Max([Driver Hours].Hours_Date)-6 or even a date entered at a prompt. -- Build a little, test a little. "Justin" wrote: This is a subquery that Duane helped me with to calculate hours for the previous 7 days: HoursLast7: (SELECT SUM(On_Duty_Hours) FROM [Driver Hours] t WHERE t.Hours_ID = [Driver Hours].Hours_ID AND t.Hours_Date BETWEEN [Driver Hours].Hours_Date -6 and [Driver Hours].Hours_Date) What I would like to do is to have the total reset to zero if any two consecutive days in the previous 7 equal zero. Do I need a separate query/subquery or can this be done inside of the current one? Thanks Justin Thomas . This query works as it is. I just need to have the total to reset zero if any two previous days equal zero. . Here it is again. SELECT [Driver Hours].ID, [Driver Hours].Hours_ID, [Driver Hours].Hours_Date, [Driver Hours].On_Duty_Hours, (SELECT SUM(On_Duty_Hours) FROM [Driver Hours] t WHERE t.Hours_ID = [Driver Hours].Hours_ID AND t.Hours_Date BETWEEN [Driver Hours].Hours_Date -6 and [Driver Hours].Hours_Date) AS HoursLast7 FROM [Driver Hours] ORDER BY [Driver Hours].Hours_ID, [Driver Hours].Hours_Date; Thanks Justin Thomas |
#6
|
|||
|
|||
Help with a subquery
These two queries will do it except as it is it is pull from all records, not
just the last 7 days. You will need to add to the criteria your starting point in time. Justin_1 -- SELECT [Driver Hours].Hours_ID, [Driver Hours].Hours_Date FROM [Driver Hours] WHERE ((((SELECT [XX].On_Duty_Hours FROM [Driver Hours] AS [XX] WHERE [XX].Hours_ID = [Driver Hours].Hours_ID AND [XX].Hours_Date = DateAdd("d",-1,[Driver Hours].Hours_Date))+[Driver Hours].[On_Duty_Hours])=0)) ORDER BY [Driver Hours].Hours_ID, [Driver Hours].Hours_Date; SELECT [Driver Hours].ID, [Driver Hours].Hours_ID, [Driver Hours].Hours_Date, [Driver Hours].On_Duty_Hours, (SELECT SUM(On_Duty_Hours) FROM [Driver Hours] t WHERE t.Hours_ID = [Driver Hours].Hours_ID AND iif(Justin_1.Hours_Date is null, t.Hours_Date BETWEEN [Driver Hours].Hours_Date -6 and [Driver Hours].Hours_Date, t.Hours_Date BETWEEN [Driver Hours].Hours_Date -6 and [Driver Hours].Hours_Date AND t.Hours_Date Justin_1.Hours_Date )) AS HoursLast7 FROM [Driver Hours] LEFT JOIN Justin_1 ON [Driver Hours].Hours_ID = Justin_1.Hours_ID ORDER BY [Driver Hours].Hours_ID, [Driver Hours].Hours_Date; -- Build a little, test a little. "Justin" wrote: On Feb 23, 1:12 am, KARL DEWEY wrote: If you will post your actual SQL maybe some one can suggest a method for you. Open query in design view and click on VIEW - SQL View, hightlight all, copy, and paste in a post. -- Build a little, test a little. "Justin" wrote: On Feb 22, 3:35 pm, KARL DEWEY wrote: t.Hours_Date BETWEEN [Driver Hours].Hours_Date -6 and [Driver Hours].Hours_Date) I was examining this to decide on an approach and discovered this will not work as it is requiring that [Driver Hours].Hours_Date be between itself and itself minus 6 days. Maybe you want [Driver Hours].Hours_Date to be between Date() and Date()-6 or maybe between Max([Driver Hours].Hours_Date) and Max([Driver Hours].Hours_Date)-6 or even a date entered at a prompt. -- Build a little, test a little. "Justin" wrote: This is a subquery that Duane helped me with to calculate hours for the previous 7 days: HoursLast7: (SELECT SUM(On_Duty_Hours) FROM [Driver Hours] t WHERE t.Hours_ID = [Driver Hours].Hours_ID AND t.Hours_Date BETWEEN [Driver Hours].Hours_Date -6 and [Driver Hours].Hours_Date) What I would like to do is to have the total reset to zero if any two consecutive days in the previous 7 equal zero. Do I need a separate query/subquery or can this be done inside of the current one? Thanks Justin Thomas . This query works as it is. I just need to have the total to reset zero if any two previous days equal zero. . Here it is again. SELECT [Driver Hours].ID, [Driver Hours].Hours_ID, [Driver Hours].Hours_Date, [Driver Hours].On_Duty_Hours, (SELECT SUM(On_Duty_Hours) FROM [Driver Hours] t WHERE t.Hours_ID = [Driver Hours].Hours_ID AND t.Hours_Date BETWEEN [Driver Hours].Hours_Date -6 and [Driver Hours].Hours_Date) AS HoursLast7 FROM [Driver Hours] ORDER BY [Driver Hours].Hours_ID, [Driver Hours].Hours_Date; Thanks Justin Thomas . |
Thread Tools | |
Display Modes | |
|
|