Click here to Skip to main content
14,983,791 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am facing issues as I get records from SQL. The output of records was not right and numbers returned are much bigger than what I expected it to be.

This is my expected result :

Count   CustomerTaskTypeId   ScheduledDispatchedDateTime
852             7                      2019-08-20


but what I get is this :

Count   CustomerTaskTypeId   ScheduledDispatchedDateTime
4964         7                      2019-08-20


And what are the reasons why I get this large number of result?
Thanks for the help. :)

What I have tried:

Here is my query :

SQL
var query= $@"select CAST(CustomerAssignedTaskExec.ScheduledDispatchedDateTime as DATE) as 'Date',
                        CustomerTaskDetails.CustomerTaskTypeId, COUNT(*) as 'Count'
                        from CustomerAssignedTaskExec
                            inner join CustomerAssignedTask on CustomerAssignedTask.Id = CustomerAssignedTaskExec.CustomerAssignedTaskId
                            inner join CustomerTaskDetails on CustomerTaskDetails.Id = CustomerAssignedTaskExec.CustomerTaskDetailsId
                            inner join CustomerAssignedTaskItemStatus on CustomerAssignedTaskItemStatus.Id = CustomerAssignedTaskExec.AssignedTaskItemStatusId
                            inner join customers on customers.CustomerId = CustomerAssignedTask.CustomerId
                            where Customers.StoreId = @storeId and CustomerAssignedTask.TaskStatusId = @runningTaskStatusId
                            group by CAST(CustomerAssignedTaskExec.ScheduledDispatchedDateTime AS DATE), CustomerTaskTypeId, CustomerAssignedTaskItemStatus.Id";
Posted
Updated 29-Aug-19 17:07pm
v2

1 solution

Without knowing the actual schema for the tables and the data we can only guess. On possibility is that you're joining the table using sibling paths thus resulting to duplicating records.

Perhaps the easiest way to quickly find out what's duplicating is to remove the grouping and including the keys of each table in the result. This way you can have a look at the data from which you get the false results and since you know the data and the structure of the tables it's easy to spot what are extra rows. After finding those, it's more easy to see the problem in the query, joins, missing table, extra table missing condition etc...

So have a try with something like
SQL
select CAST(CustomerAssignedTaskExec.ScheduledDispatchedDateTime as DATE) as 'Date',
   CustomerTaskDetails.CustomerTaskTypeId
   CustomerAssignedTaskExec.*,
   CustomerAssignedTask.*,
   CustomerTaskDetails.*,
   CustomerAssignedTaskItemStatus.*,
   customers.*
from CustomerAssignedTaskExec
   inner join CustomerAssignedTask on CustomerAssignedTask.Id = CustomerAssignedTaskExec.CustomerAssignedTaskId
   inner join CustomerTaskDetails on CustomerTaskDetails.Id = CustomerAssignedTaskExec.CustomerTaskDetailsId
   inner join CustomerAssignedTaskItemStatus on CustomerAssignedTaskItemStatus.Id = CustomerAssignedTaskExec.AssignedTaskItemStatusId
   inner join customers on customers.CustomerId = CustomerAssignedTask.CustomerId
where Customers.StoreId = @storeId 
and CustomerAssignedTask.TaskStatusId = @runningTaskStatusId
   
Comments
Maciej Los 30-Aug-19 2:13am
   
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900