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
This are the tables
Notification
Sales
Notification_User
And the relations goes as follows:
Notification 1:* Notification_User
Notification_User *:1 User
User 1:* Sales
Could you attach your sample data as file(s)? It will save me the time of typing all the data.
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