Forum Discussion
Filtering Sales between Notification Dispatch and Sales Time
Nice.
A couple of things. I suspect that these sample data are simplified, perhaps to much. As long as each customer only has 1 notification, the relationships are 1-to-many. But once you have a customer with more than 1 notification the relationships between the tables will be many-to-many. Many-to-many relationships are much more complex to handle than 1-to-many.
You wrote that you want to analyse on the difference in time between dispatch and sales. How should the difference be calculated when there are more than 1 sale?
You were also looking for the difference between sales to customers who received notification to those that don't. But your sample sales data does not contain any customers who did not reveice notification.
What you could do is to do some calculations on your sample data in Excel to describe you desired output.
If you are having problem sharing the data directly in the forum, you can upload the data to dropbox/onedrive/other, and share the link
Hi sturlaws I really cound't attach. I am Writting an example
| NotificationID | Date_Schedule | DateRead | Date_Dispatch | appid |
| 123 | 2020-01-27 10:45:00 | 2020-02-26 17:20:12 | 2020-01-27 10:59:22 | 6 |
| 173 | 2020-02-10 15:30:00 | 2020-02-26 17:20:12 | 2020-02-10 15:42:31 | 6 |
| 733 | 2020-04-17 18:30:00 | 2020-04-17 18:30:23 | 2020-04-17 18:34:42 | 6 |
| 734 | 2020-04-19 18:30:00 | 2020-04-19 18:30:50 | 2020-04-19 18:31:33 | 6 |
| 735 | 2020-04-19 18:30:00 | 2020-04-19 18:30:53 | 2020-04-19 18:31:50 | 6 |
| NotificationID | UserID | StatusID |
| 123 | 92132 | 1 |
| 123 | 95259 | 1 |
| 123 | 107170 | 1 |
| 173 | 79137 | 1 |
| 173 | 79194 | 1 |
| 173 | 79286 | 1 |
| 733 | 76698 | 1 |
| 733 | 77767 | 1 |
| 733 | 77930 | 1 |
| 733 | 77939 | 1 |
| 733 | 78120 | 1 |
| 735 | 256292 | 1 |
| 735 | 256774 | 1 |
| 735 | 257702 | 1 |
| 123 | 230571 | 2 |
| 123 | 97722 | 2 |
| 173 | 79478 | 2 |
| 733 | 77780 | 0 |
| 733 | 79396 | 0 |
| 734 | 78977 | 0 |
| 734 | 79275 | 0 |
| 734 | 79736 | 0 |
| 734 | 92930 | 0 |
| 734 | 98586 | 0 |
| SaleID | value | Discount | Date | ComandID | UserID | TypeID |
| 1994 | 16 | 27/01/2020 05:31 | 6503 | 107170 | 2 | |
| 1881 | 35 | 27/01/2020 01:23 | 6285 | 79137 | 1 | |
| 1877 | 66 | 27/01/2020 19:47 | 6278 | 79286 | 2 | |
| 1879 | 15 | 28/01/2020 19:58 | 6280 | 79137 | 2 | |
| 1876 | 35 | 27/01/2020 04:25 | 6273 | 79137 | 2 | |
| 1686 | 55,55 | 28/01/2020 06:28 | 5969 | 92132 | 2 | |
| 1631 | 31,58 | 28/01/2020 02:43 | 5881 | 76698 | 3 | |
| 1605 | 29,25 | 28/01/2020 08:40 | 5846 | 79194 | 2 | |
| 1608 | 24,9 | 28/01/2020 23:09 | 5854 | 79194 | 2 | |
| 1661 | 20 | 28/01/2020 00:56 | 5931 | 79286 | 2 | |
| 1840 | 19 | 28/01/2020 16:33 | 6212 | 92132 | 2 | |
| 1596 | 125,4 | 28/01/2020 07:59 | 5834 | 77930 | 2 | |
| 1581 | 100 | 27/01/2020 10:33 | 5799 | 77930 | 1 | |
| 1580 | 124,3 | 28/01/2020 00:22 | 5797 | 79137 | 2 | |
| 1569 | 20 | 27/01/2020 02:55 | 5782 | 79286 | 2 | |
| 1619 | 20 | 27/01/2020 11:57 | 5868 | 107170 | 2 | |
| 1699 | 22,9 | 28/01/2020 21:25 | 5995 | 77767 | 2 | |
| 1853 | 22,9 | 01/01/2020 20:51 | 6230 | 79286 | 2 | |
| 1573 | 48 | 01/01/2020 22:09 | 5787 | 79194 | 3 | |
| 1553 | 52,45 | 01/01/2020 18:17 | 5763 | 79137 | 1 | |
| 1550 | 12 | 10/01/2020 14:43 | 5760 | 79137 | 3 | |
| 1552 | 5,5 | 23/02/2020 12:18 | 5762 | 79194 | 3 | |
| 1896 | 22,9 | 06/02/2020 12:23 | 6317 | 107170 | 3 | |
| 1547 | 41,8 | 20/02/2020 21:32 | 5747 | 76698 | 2 | |
| 1546 | 36,3 | 20/02/2020 14:30 | 5743 | 79194 | 3 | |
| 1545 | 35,2 | 20/02/2020 14:29 | 5743 | 79194 | 3 | |
| 1540 | 61 | 20/02/2020 12:24 | 5736 | 95259 | 2 | |
| 1660 | 16,9 | 07/02/2020 12:05 | 5928 | 79137 | 2 | |
| 1695 | 20,9 | 13/02/2020 12:08 | 5991 | 76698 | 2 | |
| 1851 | 17,9 | 29/02/2020 12:21 | 6229 | 79286 | 2 | |
| 1544 | 26,4 | 20/02/2020 13:32 | 5740 | 95259 | 2 | |
| 1609 | 16,9 | 31/03/2020 13:00 | 5855 | 79194 | 2 | |
| 1672 | 16,9 | 08/04/2020 13:43 | 5944 | 79286 | 2 | |
| 1725 | 17,9 | 17/04/2020 13:25 | 6031 | 107170 | 2 | |
| 1527 | 52,8 | 19/04/2020 13:33 | 5711 | 79286 | 3 | |
| 1541 | 17,9 | 20/05/2020 12:31 | 5737 | 107170 | 1 | |
| 1512 | 29,7 | 19/04/2020 19:11 | 5694 | 77767 | 2 | |
| 1511 | 37,4 | 19/04/2020 19:11 | 5694 | 92132 | 2 | |
| 1502 | 14,9 | 18/04/2020 12:01 | 5681 | 107170 | 3 | |
| 1483 | 26 | 19/04/2020 21:19 | 5654 | 79137 | 3 | |
| 1649 | 26 | 19/04/2020 20:09 | 5912 | 79194 | 3 | |
| 1751 | 26 | 17/04/2020 18:24 | 6070 | 79194 | 3 | |
| 1880 | 26 | 20/04/2020 14:55 | 6284 | 95259 | 3 | |
| 1448 | 20 | 17/04/2020 09:24 | 5578 | 79137 | 2 | |
| 1443 | 18,9 | 18/04/2020 20:08 | 5572 | 77767 | 2 | |
| 1446 | 17,9 | 20/04/2020 19:56 | 5575 | 79286 | 2 | |
| 1473 | 16,9 | 20/04/2020 17:37 | 5628 | 79137 | 2 | |
| 1568 | 20,9 | 20/04/2020 18:44 | 5781 | 107170 | 2 | |
| 1628 | 16,9 | 18/04/2020 08:14 | 5880 | 79286 | 2 | |
| 1494 | 5,5 | 19/04/2020 09:20 | 5673 | 76698 | 2 | |
| 1496 | 17,5 | 19/04/2020 02:19 | 5675 | 79137 | 2 | |
| 1535 | 10,25 | 17/04/2020 13:32 | 5728 | 79137 | 2 | |
| 1537 | 22,9 | 18/04/2020 19:46 | 5730 | 95259 | 2 | |
| 1548 | 8,25 | 18/04/2020 08:13 | 5758 | 95259 | 2 | |
| 1554 | 3 | 20/04/2020 12:57 | 5764 | 77767 | 2 | |
| 1562 | 20 | 17/04/2020 23:07 | 5772 | 107170 | 2 | |
| 1567 | 9 | 20/04/2020 13:17 | 5780 | 79286 | 2 | |
| 1576 | 9 | 20/04/2020 23:13 | 5790 | 79137 | 2 | |
| 1602 | 36,8 | 20/04/2020 10:55 | 5843 | 77767 | 2 | |
| 1603 | 7,5 | 17/04/2020 04:37 | 5844 | 92132 | 2 | |
| 1625 | 6,5 | 17/04/2020 19:24 | 5877 | 92132 | 2 | |
| 1633 | 17,5 | 18/04/2020 16:00 | 5886 | 107170 | 2 | |
| 1634 | 8,5 | 20/04/2020 01:01 | 5887 | 79194 | 2 | |
| 1655 | 11,5 | 19/04/2020 01:33 | 5923 | 92132 | 2 | |
| 1656 | 15,5 | 17/04/2020 09:24 | 5924 | 79286 | 2 | |
| 1664 | 6 | 18/04/2020 04:39 | 5934 | 77767 | 2 | |
| 1682 | 3 | 19/04/2020 05:19 | 5960 | 79137 | 2 | |
| 1702 | 3 | 20/04/2020 14:37 | 5998 | 77939 | 2 | |
| 1708 | 14 | 20/04/2020 15:38 | 6006 | 107170 | 2 | |
| 1711 | 47,8 | 12,5 | 20/04/2020 10:47 | 6013 | 92132 | 2 |
| 1727 | 6 | 17/04/2020 20:11 | 6032 | 256774 | 2 | |
| 1740 | 9,5 | 20/04/2020 14:30 | 6048 | 76698 | 2 | |
| 1762 | 47,8 | 20/04/2020 06:44 | 6094 | 79194 | 2 | |
| 1779 | 12 | 17/04/2020 03:59 | 6113 | 92132 | 2 | |
| 1787 | 4,5 | 17/04/2020 22:19 | 6123 | 107170 | 2 | |
| 1809 | 17,5 | 10 | 17/04/2020 08:26 | 6151 | 79137 | 2 |
| 1817 | 3 | 20/04/2020 02:48 | 6164 | 79194 | 2 | |
| 1828 | 14,5 | 18/04/2020 03:55 | 6195 | 79286 | 2 | |
| 1857 | 16,5 | 17/04/2020 21:41 | 6239 | 79137 | 2 | |
| 1864 | 27 | 3 | 17/04/2020 11:27 | 6248 | 76698 | 2 |
| 1869 | 18,5 | 20/04/2020 10:59 | 6254 | 95259 | 2 | |
| 1889 | 53,8 | 18/04/2020 07:40 | 6300 | 77930 | 2 | |
| 1906 | 3,5 | 19/04/2020 04:16 | 6341 | 79286 | 2 | |
| 1927 | 21,5 | 18/04/2020 21:39 | 6385 | 77939 | 2 | |
| 1934 | 47,8 | 4,78 | 20/04/2020 15:14 | 6390 | 107170 | 2 |
| 1988 | 3 | 17/04/2020 13:26 | 6489 | 79286 | 2 | |
| 1996 | 47,8 | 17/04/2020 15:27 | 6509 | 79137 | 2 | |
| 1542 | 16,9 | 18/04/2020 03:17 | 5738 | 79194 | 2 | |
| 1445 | 34,1 | 19/04/2020 04:27 | 5573 | 92132 | 3 | |
| 1489 | 35,75 | 5,6 | 18/04/2020 11:00 | 5664 | 95259 | 3 |
| 1519 | 31,9 | 18/04/2020 03:58 | 5701 | 79286 | 3 | |
| 1621 | 31,9 | 19/04/2020 16:21 | 5871 | 107170 | 3 | |
| 1397 | 20 | 18/04/2020 00:44 | 5490 | 79286 | 2 | |
| 1412 | 21,9 | 17/04/2020 08:20 | 5524 | 95259 | 2 | |
| 1427 | 8,25 | 18/04/2020 14:31 | 5552 | 79194 | 2 | |
| 1435 | 6,75 | 19/04/2020 09:10 | 5560 | 79137 | 2 | |
| 1464 | 20 | 12 | 17/04/2020 23:08 | 5606 | 79286 | 2 |
| 1505 | 27,25 | 18/04/2020 12:30 | 5687 | 79137 | 2 | |
| 1563 | 20 | 19/04/2020 06:40 | 5773 | 77930 | 2 | |
| 1662 | 20 | 19/04/2020 10:10 | 5932 | 107170 | 2 | |
| 1671 | 20 | 20/04/2020 02:11 | 5943 | 76698 | 2 | |
| 1701 | 27,9 | 20/04/2020 08:39 | 5997 | 79137 | 2 | |
| 1713 | 22,9 | 20/04/2020 07:02 | 6016 | 79194 | 2 | |
| 1756 | 24,9 | 12 | 20/04/2020 07:17 | 6087 | 92132 | 2 |
| 1863 | 26,9 | 20/04/2020 19:18 | 6247 | 95259 | 2 | |
| 1410 | 16,9 | 19/04/2020 00:30 | 5516 | 107170 | 2 | |
| 1526 | 15,9 | 17/04/2020 05:51 | 5713 | 95259 | 2 | |
| 1387 | 20,9 | 18/04/2020 04:14 | 5463 | 95259 | 2 | |
| 1420 | 1 | 18/04/2020 22:29 | 5545 | 95259 | 2 | |
| 1641 | 50 | 19/04/2020 00:29 | 5892 | 76698 | 2 | |
| 1665 | 1 | 20/04/2020 13:53 | 5935 | 107170 | 2 | |
| 1668 | 1 | 20/04/2020 13:10 | 5937 | 77767 | 2 | |
| 1679 | 0,25 | 17/04/2020 09:52 | 5952 | 107170 | 2 | |
| 1680 | 0,25 | 18/04/2020 19:21 | 5953 | 79286 | 2 | |
| 1681 | 0,25 | 17/04/2020 15:22 | 5954 | 79137 | 2 | |
| 1688 | 1 | 20/04/2020 01:51 | 5972 | 77930 | 2 | |
| 1709 | 5,5 | 11/02/2020 20:23 | 6007 | 76698 | 2 | |
| 1715 | 1 | 10/02/2020 03:28 | 6018 | 95259 | 2 | |
| 1719 | 1 | 10/02/2020 22:58 | 6022 | 107170 | 2 | |
| 1755 | 1,5 | 1 | 12/02/2020 02:42 | 6085 | 79137 | 2 |
| 1766 | 1 | 10/02/2020 10:14 | 6101 | 92132 | 2 | |
| 1378 | 24,9 | 11/02/2020 04:57 | 5444 | 77767 | 3 | |
| 1463 | 24,9 | 10/02/2020 02:06 | 5604 | 79286 | 2 | |
| 1669 | 20,9 | 12/02/2020 14:54 | 5940 | 79194 | 2 | |
| 1712 | 26,9 | 10 | 12/02/2020 23:53 | 6015 | 79137 | 2 |
| 1515 | 6,5 | 2 | 12/02/2020 17:13 | 5697 | 107170 | 3 |
| 1577 | 68 | 11/02/2020 17:53 | 5802 | 77767 | 2 |
Nice.
A couple of things. I suspect that these sample data are simplified, perhaps to much. As long as each customer only has 1 notification, the relationships are 1-to-many. But once you have a customer with more than 1 notification the relationships between the tables will be many-to-many. Many-to-many relationships are much more complex to handle than 1-to-many.
You wrote that you want to analyse on the difference in time between dispatch and sales. How should the difference be calculated when there are more than 1 sale?
You were also looking for the difference between sales to customers who received notification to those that don't. But your sample sales data does not contain any customers who did not reveice notification.
What you could do is to do some calculations on your sample data in Excel to describe you desired output.
If you are having problem sharing the data directly in the forum, you can upload the data to dropbox/onedrive/other, and share the link