Sql question

Hi Experts,

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 Expr1
FROM dbo_TimeSheet WHERE (((dbo_TimeSheet.Day) Is Not Null) AND ((dbo_TimeSheet.TimeEntered) Is Not Null) AND ((Format([TimeEntered],"mm/dd/yy"))<>CStr([day])));

Open in new window

query2
SELECT dbo_TimeSheet.Day, dbo_TimeSheet.TimeEntered, Format([TimeEntered],"mm/dd/yy") AS Expr1
FROM dbo_TimeSheet WHERE (((dbo_TimeSheet.Day) Is Not Null) AND ((dbo_TimeSheet.TimeEntered) Is Not Null) AND ((Format([TimeEntered],"mm/dd/yy"))=CStr([day])));[/

Open in new window


P.S. there are only few records with no Day field entered or no TimeEntered, not enough to justify the discrepancy.
LVL 4
bfuchsAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor Commented:
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 UNEQL
FROM dbo_TimeSheet
WHERE (dbo_TimeSheet.Day) IS NOT NULL
      AND (dbo_TimeSheet.TimeEntered) IS NOT NULL)
;

Open in new window


and get the rows like this
SELECT
      dbo_TimeSheet.Day
    , dbo_TimeSheet.TimeEntered
    , Format([TimeEntered], "mm/dd/yy") AS EXPR1
FROM dbo_TimeSheet
WHERE DATEDIFF("d", [day], [TimeEntered]) <> 0
;

Open in new window


SELECT
      dbo_TimeSheet.Day
    , dbo_TimeSheet.TimeEntered
    , Format([TimeEntered], "mm/dd/yy") AS EXPR1
FROM dbo_TimeSheet
WHERE DATEDIFF("d", [day], [TimeEntered]) = 0
;

Open in new window


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.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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...
0
 
bfuchsAuthor Commented:
Hi,
Total quantity of records from both queries together should equal the quantity of records in the table.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define 'quantity', by the column name, and the aggregate you wish to perform (SUM, COUNT, etc.)
0
 
bfuchsAuthor Commented:
I Have a table containing 2 columns

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.
0
 
lalitgadaCommented:
hi,

Kindly check date entered in the table is correct or not.
0
 
Gustav BrockConnect With a Mentor CIOCommented:
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.

/gustav
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
How many records returns each query? And how many records was you expecting to be returned?
Without some sample data it's hard for us to try to guess.
0
 
bfuchsAuthor Commented:
@PortletPaul,
In middle of testing yours..

@Gustav,
See attached.
untitled.bmp
0
 
Gustav BrockCIOCommented:
It is:

   DateValue([TimeEntered]) AS DayOfTimeEntered,

/gustav
0
 
bfuchsAuthor Commented:
Working well, thank you!
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
All Courses

From novice to tech pro — start learning today.