I have the following two queries that would like to total the quantity of the records in the table, and for some reason I am missing some records.
Can someone explain where is the hole?
query1
SELECT dbo_TimeSheet.Day, dbo_TimeSheet.TimeEntered, Format([TimeEntered],"mm/dd/yy") AS Expr1FROM dbo_TimeSheet WHERE (((dbo_TimeSheet.Day) Is Not Null) AND ((dbo_TimeSheet.TimeEntered) Is Not Null) AND ((Format([TimeEntered],"mm/dd/yy"))<>CStr([day])));
SELECT dbo_TimeSheet.Day, dbo_TimeSheet.TimeEntered, Format([TimeEntered],"mm/dd/yy") AS Expr1FROM dbo_TimeSheet WHERE (((dbo_TimeSheet.Day) Is Not Null) AND ((dbo_TimeSheet.TimeEntered) Is Not Null) AND ((Format([TimeEntered],"mm/dd/yy"))=CStr([day])));[/
>total the quantity of the records in the table
Total ... what exactly? Sum of days, sum of time entered, ...
Both queries together, each one separately...
Define 'quantity', by the column name, and the aggregate you wish to perform (SUM, COUNT, etc.)
0
Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.
Day where date is entered
TimeEntered where date+time is entered
would like to see all those records that day column has a different date than the date portion in TimeEntered field.
This is the reason I am running those queries.
In order to ensure I am getting the correct results, I want to sum all those records that dates are the same with those that they are not the same, and get the total qty of records equal to what I have in the table.
I would suggest using DATEDIFF(interval, date1, date2) that returns an integer of the chosen interval between two dates instead of relying on character representations of dates. Here the interval of day ("d") can be used as you are only concerned with the date and not time.
You could check the numbers like this:
SELECT COUNT(*) AS TOT , SUM(IIF(DATEDIFF("d", [day], [TimeEntered]) = 0,1,0)) AS EQL , SUM(IIF(DATEDIFF("d", [day], [TimeEntered]) <> 0,1,0)) AS UNEQLFROM dbo_TimeSheetWHERE (dbo_TimeSheet.Day) IS NOT NULL AND (dbo_TimeSheet.TimeEntered) IS NOT NULL);
Note: if either [day] or [TimeEntered] are NULL then datediff() cannot return a value so only those rows with values in both fields can meet the <> 0 or = 0 criteria.
As you want to check the total count of records, you would have to count those with Nulls as well.
You could have one single query:
Select
dbo_TimeSheet.Day,
dbo_TimeSheet.TimeEntered,
DateValue([TimeEntered] AS DayOfTimeEntered,
dbo_TimeSheet.Day Is Null Or dbo_TimeSheet.TimeEntered Is Null As NullDate,
DateDiff("d", Nz(dbo_TimeSheet.Day, #00:00#), Nz(dbo_TimeSheet.TimeEntered, #00:00#)) = 0 As DateMatch
From
dbo_TimeSheet
Now you can use this directly and filter as you wish, or you can use it as the source for other queries that filter the output.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other.
As you build tables in Microsoft Ac…
Wrapper-1-Query. Use an Excel function to calculate a column for an Access query. Part 1. Shows a query in Access that has a calculated column with the results of an Excel worksheet function.
See how to call a wrapper function from a query, and …