October 23, 2014 at 9:22 am
Chris,
This seems to be doing the trick :). I will carry on testing and will either mark this as answered or come back with more questions.
Thank you very much.
October 23, 2014 at 9:28 am
vee_jess (10/23/2014)
Chris,This seems to be doing the trick :). I will carry on testing and will either mark this as answered or come back with more questions.
Thank you very much.
Excellent news, thank you very much for the feedback.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 27, 2014 at 9:31 am
Hi Chris,
After testing I have noticed that this query isn't working as expected. I'm getting 0 parcel count 2014 for Tuesday weekNo 4 in October, then there are parcel count 2014 for Wednesday till Sunday of week 4 October. Today is the 4th Monday of October and there should be a parcel count 2014 for only today in October week 4.
Attached is an excel sheet with 2 tabs - 1 for the results and 2nd is the dataset.
Please have a look and see if you can help
Thanks,
J.
October 27, 2014 at 10:05 am
If you can set up your sample dataset in a readily-consumable format, I'll see what I can do.
Readily-consumable means a script which will run and populate the sample table with data, something like
select 1,2,3 union all
select 5,2,4 union all
select 8,2,6
or select * from (values (1,2,3),(5,2,4))
I'm working in the UK and there's only an hour left of the working day - and I have work to do. I'm sure if you can post up the sample data in the way I've suggested, someone will be able to help.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 27, 2014 at 10:42 am
Sorry, it's a lot of data. I hope that's the correct way to post this here.
SELECT * FROM
(
VALUES
('2012-10-18','DM4',1858),
('2012-10-18','DM2',309 ),
('2012-10-18','DM3',750 ),
('2012-11-12','DM2',717 ),
('2012-11-12','DM3',1820 ),
('2012-11-12','DM4',903 ),
('2012-11-24','DM3',111 ),
('2012-11-24','DM4',129 ),
('2012-11-26','DMEBAY',3 ),
('2012-11-27','DMEBAY',9 ),
('2012-11-28','DMEBAY',14 ),
('2012-11-29','DM3',1455 ),
('2012-11-29','DMEBAY',34 ),
('2012-11-29','DM4',1740 ),
('2012-11-29','DM2',375 ),
('2012-11-30','DMEBAY',33 ),
('2012-12-01','DM4',3654 ),
('2012-12-01','DM3',169 ),
('2012-12-01','DMASOS',45606 ),
('2012-12-02','DMASOS',36555 ),
('2012-12-02','DM3',844 ),
('2012-12-02','DM2',1 ),
('2012-12-02','DM4',7126 ),
('2012-12-03','DM2',590 ),
('2012-12-03','DMASOS',85204 ),
('2012-12-03','DM4',15044 ),
('2012-12-03','DMEBAY',43084 ),
('2012-12-04','DMASOS',82730 ),
('2012-12-04','DM4',17923 ),
('2012-12-04','DM2',418 ),
('2012-12-04','DMEBAY',36803 ),
('2012-12-05','DMEBAY',26500 ),
('2012-12-05','DM2',297 ),
('2012-12-05','DMASOS',87520 ),
('2012-12-05','DM4',14190 ),
('2012-12-06','DM4',18613 ),
('2012-12-06','DM2',612 ),
('2012-12-06','DM3',2116 ),
('2012-12-06','DMASOS',78290 ),
('2012-12-06','DMEBAY',23897 ),
('2012-12-07','DMEBAY',21119 ),
('2012-12-07','DMASOS',70971 ),
('2012-12-07','DM2',339 ),
('2012-12-07','DM4',15377 ),
('2012-12-08','DMASOS',37810 ),
('2012-12-08','DM4',1522 ),
('2012-12-08','DM3',166 ),
('2012-12-09','DM4',8088 ),
('2012-12-09','DMASOS',42469 ),
('2012-12-10','DMASOS',100027 ),
('2012-12-10','DM4',21054 ),
('2012-12-10','DMEBAY',46780 ),
('2012-12-10','DM2',435 ),
('2012-12-11','DMASOS',83697 ),
('2012-12-11','DMEBAY',35242 ),
('2012-12-11','DM4',20578 ),
('2012-12-11','DM3',687 ),
('2012-12-11','DM1',20444 ),
('2012-12-12','DM2',1089 ),
('2012-12-12','DM3',1859 ),
('2012-12-12','DM4',20978 ),
('2012-12-12','DMEBAY',26383 ),
('2012-12-12','DM1',109804 ),
('2012-12-12','DMASOS',97275 ),
('2012-12-13','DM2',602 ),
('2012-12-13','DM3',1822 ),
('2012-12-13','DMEBAY',22737 ),
('2012-12-13','DM4',22492 ),
('2012-12-13','DMASOS',94368 ),
('2012-12-13','DM1',109241 ),
('2012-12-14','DM1',115340 ),
('2012-12-14','DMEBAY',22268 ),
('2012-12-14','DM2',814 ),
('2012-12-14','DM4',19501 ),
('2012-12-14','DM3',1202 ),
('2012-12-14','DMASOS',73509 ),
('2012-12-15','DM3',1190 ),
('2012-12-15','DM4',2282 ),
('2012-12-15','DMASOS',40387 ),
('2012-12-15','DM1',64594 ),
('2012-12-16','DM4',20447 ),
('2012-12-16','DM2',3 ),
('2012-12-16','DMASOS',45724 ),
('2012-12-16','DM1',63323 ),
('2012-12-16','DM3',2690 ),
('2012-12-17','DMEBAY',48470 ),
('2012-12-17','DM2',987 ),
('2012-12-17','DM4',22767 ),
('2012-12-17','DM3',11726 ),
('2012-12-17','DMASOS',108792 ),
('2012-12-17','DM1',151701 ),
('2012-12-18','DM4',24097 ),
('2012-12-18','DM2',661 ),
('2012-12-18','DMASOS',89772 ),
('2012-12-18','DM1',139183 ),
('2012-12-18','DMEBAY',37184 ),
('2012-12-18','DM3',1480 ),
('2012-12-19','DM2',586 ),
('2012-12-19','DM3',1264 ),
('2012-12-19','DM1',134811 ),
('2012-12-19','DM4',20767 ),
('2012-12-19','DMASOS',88382 ),
('2012-12-19','DMEBAY',24321 ),
('2012-12-20','DM2',398 ),
('2012-12-20','DMASOS',81451 ),
('2012-12-20','DM1',129563 ),
('2012-12-20','DM3',2108 ),
('2012-12-20','DM4',18301 ),
('2012-12-20','DMEBAY',18979 ),
('2012-12-21','DMASOS',58071 ),
('2012-12-21','DM1',86337 ),
('2012-12-21','DMEBAY',14252 ),
('2012-12-21','DM2',249 ),
('2012-12-21','DM3',1257 ),
('2012-12-21','DM4',8291 ),
('2012-12-22','DM4',2932 ),
('2012-12-22','DM1',44402 ),
('2012-12-22','DM3',27 ),
('2012-12-22','DMASOS',27764 ),
('2012-12-23','DM4',4355 ),
('2012-12-23','DM1',33656 ),
('2012-12-23','DM3',556 ),
('2012-12-23','DMASOS',31604 ),
('2012-12-24','DM1',24959 ),
('2012-12-24','DMEBAY',16510 ),
('2012-12-24','DMASOS',49596 ),
('2012-12-24','DM4',7490 ),
('2012-12-24','DM3',916 ),
('2012-12-26','DM1',171 ),
('2012-12-26','DMASOS',29591 ),
('2012-12-27','DM1',99668 ),
('2012-12-27','DMASOS',84278 ),
('2012-12-27','DM2',724 ),
('2012-12-27','DM3',1902 ),
('2012-12-27','DMEBAY',5380 ),
('2012-12-27','DM4',13899 ),
('2012-12-28','DM4',11148 ),
('2012-12-28','DMEBAY',13781 ),
('2012-12-28','DM1',25346 ),
('2012-12-28','DMASOS',82153 ),
('2012-12-29','DM4',2280 ),
('2012-12-29','DMASOS',42909 ),
('2012-12-30','DMASOS',45039 ),
('2012-12-30','DM4',963 ),
('2012-12-30','DM1',449 ),
('2012-12-31','DMEBAY',23974 ),
('2012-12-31','DM4',12394 ),
('2012-12-31','DMASOS',62110 ),
('2012-12-31','DM1',7 ),
('2013-10-01','DMEBAY',30525 ),
('2013-10-01','DM1',96835 ),
('2013-10-01','DM3',34396 ),
('2013-10-01','DMASOS',75285 ),
('2013-10-01','DMMS',4682 ),
('2013-10-01','DM4',59561 ),
('2013-10-01','DM2',37731 ),
('2013-10-01','DM5',24015 ),
('2013-10-02','DM4',52304 ),
('2013-10-02','DMEBAY',20807 ),
('2013-10-02','DMMS',7738 ),
('2013-10-02','DM3',34177 ),
('2013-10-02','DM2',32151 ),
('2013-10-02','DM1',91302 ),
('2013-10-02','DM5',24244 ),
('2013-10-02','DMASOS',63152 ),
('2013-10-03','DMEBAY',18753 ),
('2013-10-03','DM4',52256 ),
('2013-10-03','DMASOS',64306 ),
('2013-10-03','DMMS',4892 ),
('2013-10-03','DM1',80866 ),
('2013-10-03','DM5',22270 ),
('2013-10-03','DM2',34567 ),
('2013-10-03','DM3',30022 ),
('2013-10-04','DMEBAY',18543 ),
('2013-10-04','DM5',19576 ),
('2013-10-04','DMMS',6439 ),
('2013-10-04','DMASOS',62509 ),
('2013-10-04','DM4',44709 ),
('2013-10-04','DM2',29151 ),
('2013-10-04','DM1',76347 ),
('2013-10-04','DM3',28814 ),
('2013-10-05','DM1',24046 ),
('2013-10-05','DM4',10966 ),
('2013-10-05','DM2',10704 ),
('2013-10-05','DMASOS',26921 ),
('2013-10-05','DM3',5384 ),
('2013-10-05','DMMS',4075 ),
('2013-10-05','DM5',15251 ),
('2013-10-06','DM2',33533 ),
('2013-10-06','DMASOS',2850 ),
('2013-10-06','DM5',17198 ),
('2013-10-06','DM4',32592 ),
('2013-10-06','DM3',17346 ),
('2013-10-06','DM1',29602 ),
('2013-10-07','DM4',61980 ),
('2013-10-07','DM3',45664 ),
('2013-10-07','DMEBAY',4828 ),
('2013-10-07','DM2',34133 ),
('2013-10-07','DM5',25472 ),
('2013-10-07','DM1',111013 ),
('2013-10-08','DM1',90607 ),
('2013-10-08','DMMS',7675 ),
('2013-10-08','DMEBAY',29017 ),
('2013-10-08','DM4',46567 ),
('2013-10-08','DM3',43586 ),
('2013-10-08','DM2',34733 ),
('2013-10-08','DM5',23809 ),
('2013-10-08','DMASOS',88660 ),
('2013-10-09','DMASOS',81626 ),
('2013-10-09','DMEBAY',20413 ),
('2013-10-09','DM2',32754 ),
('2013-10-09','DM3',37162 ),
('2013-10-09','DM4',45504 ),
('2013-10-09','DM1',82691 ),
('2013-10-09','DMMS',9365 ),
('2013-10-09','DM5',23339 ),
('2013-10-10','DMEBAY',18331 ),
('2013-10-10','DM5',21432 ),
('2013-10-10','DM2',40356 ),
('2013-10-10','DM3',35182 ),
('2013-10-10','DMMS',9471 ),
('2013-10-10','DMASOS',93736 ),
('2013-10-10','DM4',50345 ),
('2013-10-10','DM1',78577 ),
('2013-10-11','DM4',40869 ),
('2013-10-11','DM5',20258 ),
('2013-10-11','DMMS',9201 ),
('2013-10-11','DMASOS',80448 ),
('2013-10-11','DM2',46856 ),
('2013-10-11','DM1',82784 ),
('2013-10-11','DMEBAY',14466 ),
('2013-10-11','DM3',32424 ),
('2013-10-12','DMASOS',44861 ),
('2013-10-12','DM5',17263 ),
('2013-10-12','DMMS',6476 ),
('2013-10-12','DM3',2085 ),
('2013-10-12','DM4',13322 ),
('2013-10-12','DM2',18993 ),
('2013-10-12','DM1',30552 ),
('2013-10-13','DM3',24842 ),
('2013-10-13','DM5',21906 ),
('2013-10-13','DM1',33569 ),
('2013-10-13','DM4',28017 ),
('2013-10-13','DM2',29127 ),
('2013-10-13','DMASOS',46826 ),
('2013-10-13','DMMS',7310 ),
('2013-10-14','DMEBAY',34072 ),
('2013-10-14','DM5',28529 ),
('2013-10-14','DM2',40544 ),
('2013-10-14','DMASOS',87222 ),
('2013-10-14','DM1',114783 ),
('2013-10-14','DM3',55887 ),
('2013-10-14','DMMS',8734 ),
('2013-10-14','DM4',63634 ),
('2013-10-15','DMASOS',81690 ),
('2013-10-15','DM1',94306 ),
('2013-10-15','DMEBAY',29434 ),
('2013-10-15','DM4',54188 ),
('2013-10-15','DM5',24721 ),
('2013-10-15','DM3',37402 ),
('2013-10-15','DMMS',7180 ),
('2013-10-15','DM2',47315 ),
('2013-10-16','DM1',83116 ),
('2013-10-16','DM5',23852 ),
('2013-10-16','DM2',40863 ),
('2013-10-16','DMASOS',79405 ),
('2013-10-16','DMEBAY',19670 ),
('2013-10-16','DMMS',6114 ),
('2013-10-16','DM4',54043 ),
('2013-10-16','DM3',40430 ),
('2013-10-17','DMASOS',75911 ),
('2013-10-17','DM2',34469 ),
('2013-10-17','DMEBAY',18496 ),
('2013-10-17','DM1',57607 ),
('2013-10-17','DMMS',7184 ),
('2013-10-17','DM5',21912 ),
('2013-10-17','DM3',35681 ),
('2013-10-17','DM4',54622 ),
('2013-10-18','DMMS',9584 ),
('2013-10-18','DM1',50120 ),
('2013-10-18','DMASOS',64690 ),
('2013-10-18','DM3',29763 ),
('2013-10-18','DM4',50131 ),
('2013-10-18','DM2',38040 ),
('2013-10-18','DM5',18361 ),
('2013-10-18','DMEBAY',18022 ),
('2013-10-19','DM5',17859 ),
('2013-10-19','DMMS',5166 ),
('2013-10-19','DM1',12941 ),
('2013-10-19','DM4',18067 ),
('2013-10-19','DMASOS',38053 ),
('2013-10-19','DM3',950 ),
('2013-10-19','DM2',22653 ),
('2013-10-20','DM3',22011 ),
('2013-10-20','DM2',32422 ),
('2013-10-20','DMASOS',44703 ),
('2013-10-20','DM5',17998 ),
('2013-10-20','DM4',29983 ),
('2013-10-20','DMMS',7685 ),
('2013-10-20','DM1',11116 ),
('2013-10-21','DM4',77597 ),
('2013-10-21','DMMS',8456 ),
('2013-10-21','DMASOS',76402 ),
('2013-10-21','DM2',47155 ),
('2013-10-21','DMEBAY',41158 ),
('2013-10-21','DM5',26487 ),
('2013-10-21','DM1',86250 ),
('2013-10-21','DM3',61729 ),
('2013-10-22','DM3',39893 ),
('2013-10-22','DM4',54056 ),
('2013-10-22','DM2',45488 ),
('2013-10-22','DM1',66604 ),
('2013-10-22','DMASOS',76725 ),
('2013-10-22','DMMS',5790 ),
('2013-10-22','DMEBAY',35346 ),
('2013-10-22','DM5',25142 ),
('2013-10-23','DMASOS',68892 ),
('2013-10-23','DMEBAY',24238 ),
('2013-10-23','DM5',23971 ),
('2013-10-23','DM2',37257 ),
('2013-10-23','DM3',40804 ),
('2013-10-23','DM4',57145 ),
('2013-10-23','DMMS',5977 ),
('2013-10-23','DM1',55286 ),
('2013-10-24','DM1',52179 ),
('2013-10-24','DM4',52327 ),
('2013-10-24','DM5',19672 ),
('2013-10-24','DMASOS',62605 ),
('2013-10-24','DM3',34478 ),
('2013-10-24','DMMS',5945 ),
('2013-10-24','DMEBAY',20779 ),
('2013-10-24','DM2',1 ),
('2013-10-24','DM2',34836 ),
('2013-10-25','DM4',19 ),
('2013-10-25','DMMS',5990 ),
('2013-10-25','DM5',19699 ),
('2013-10-25','DM1',51745 ),
('2013-10-25','DM4',45831 ),
('2013-10-25','DMASOS',62621 ),
('2013-10-25','DM3',32511 ),
('2013-10-25','DM2',33787 ),
('2013-10-25','DMEBAY',18721 ),
('2013-10-26','DM1',11955 ),
('2013-10-26','DMASOS',46623 ),
('2013-10-26','DM3',2814 ),
('2013-10-26','DM4',16550 ),
('2013-10-26','DM2',13882 ),
('2013-10-26','DM5',17914 ),
('2013-10-26','DMMS',5266 ),
('2013-10-27','DM5',21038 ),
('2013-10-27','DM2',29549 ),
('2013-10-27','DMMS',6308 ),
('2013-10-27','DMASOS',47844 ),
('2013-10-27','DM3',25546 ),
('2013-10-27','DM4',26037 ),
('2013-10-27','DM1',10787 ),
('2013-10-28','DM5',22196 ),
('2013-10-28','DM2',51321 ),
('2013-10-28','DMMS',6117 ),
('2013-10-28','DMASOS',80819 ),
('2013-10-28','DM4',70865 ),
('2013-10-28','DM1',87876 ),
('2013-10-28','DM4',1395 ),
('2013-10-28','DMEBAY',41080 ),
('2013-10-28','DM3',56040 ),
('2013-10-29','DMEBAY',34395 ),
('2013-10-29','DM4',64927 ),
('2013-10-29','DMMS',4874 ),
('2013-10-29','DM2',44298 ),
('2013-10-29','DM4',558 ),
('2013-10-29','DM1',83213 ),
('2013-10-29','DM5',24541 ),
('2013-10-29','DMASOS',88010 ),
('2013-10-29','DM3',45106 ),
('2013-10-30','DM4',1272 ),
('2013-10-30','DMMS',6259 ),
('2013-10-30','DM4',58099 ),
('2013-10-30','DM2',43654 ),
('2013-10-30','DMASOS',69235 ),
('2013-10-30','DM5',23546 ),
('2013-10-30','DM1',65871 ),
('2013-10-30','DMEBAY',22651 ),
('2013-10-30','DM3',47704 ),
('2013-10-31','DMEBAY',20261 ),
('2013-10-31','DM5',21788 ),
('2013-10-31','DM2',38085 ),
('2013-10-31','DMMS',6613 ),
('2013-10-31','DM1',59500 ),
('2013-10-31','DM3',49094 ),
('2013-10-31','DMASOS',72522 ),
('2013-10-31','DM4',1547 ),
('2013-10-31','DM4',45300 ),
('2013-11-01','DM2',36572 ),
('2013-11-01','DMEBAY',18106 ),
('2013-11-01','DM4',1119 ),
('2013-11-01','DM4',45859 ),
('2013-11-01','DM3',40598 ),
('2013-11-01','DM1',56451 ),
('2013-11-01','DM5',19672 ),
('2013-11-01','DMASOS',50110 ),
('2013-11-01','DMMS',5808 ),
('2013-11-02','DMMS',6170 ),
('2013-11-02','DM2',22971 ),
('2013-11-02','DMASOS',34859 ),
('2013-11-02','DM1',12522 ),
('2013-11-02','DM3',4371 ),
('2013-11-02','DM4',88 ),
('2013-11-02','DM5',17574 ),
('2013-11-02','DM4',15204 ),
('2013-11-03','DM5',22926 ),
('2013-11-03','DM4',29199 ),
('2013-11-03','DMASOS',39587 ),
('2013-11-03','DMMS',4409 ),
('2013-11-03','DM2',27524 ),
('2013-11-03','DM3',31807 ),
('2013-11-03','DM4',428 ),
('2013-11-03','DM1',14156 ),
('2013-11-04','DMEBAY',45233 ),
('2013-11-04','DM1',99247 ),
('2013-11-04','DMMS',5759 ),
('2013-11-04','DMASOS',88554 ),
('2013-11-04','DM4',885 ),
('2013-11-04','DM5',27393 ),
('2013-11-04','DM3',67860 ),
('2013-11-04','DM2',64507 ),
('2013-11-04','DM4',81529 ),
('2013-11-05','DMEBAY',37366 ),
('2013-11-05','DM2',45337 ),
('2013-11-05','DM5',28139 ),
('2013-11-05','DM3',49963 ),
('2013-11-05','DMMS',7382 ),
('2013-11-05','DMASOS',81585 ),
('2013-11-05','DM4',61178 ),
('2013-11-05','DM1',73496 ),
('2013-11-05','DM4',992 ),
('2013-11-06','DMMS',10148 ),
('2013-11-06','DM1',68080 ),
('2013-11-06','DM5',28112 ),
('2013-11-06','DM4',385 ),
('2013-11-06','DMASOS',76302 ),
('2013-11-06','DM4',53735 ),
('2013-11-06','DM3',48859 ),
('2013-11-06','DMEBAY',24142 ),
('2013-11-06','DM2',47067 ),
('2013-11-07','DM5',23947 ),
('2013-11-07','DMASOS',64306 ),
('2013-11-07','DM2',48092 ),
('2013-11-07','DMEBAY',22854 ),
('2013-11-07','DM4',53482 ),
('2013-11-07','DM3',43583 ),
('2013-11-07','DM1',67228 ),
('2013-11-07','DMMS',14484 ),
('2013-11-08','DM4',40191 ),
('2013-11-08','DMEBAY',21730 ),
('2013-11-08','DM2',45344 ),
('2013-11-08','DMASOS',67556 ),
('2013-11-08','DM3',36970 ),
('2013-11-08','DM1',58436 ),
('2013-11-08','DM5',20681 ),
('2013-11-08','DMMS',18828 ),
('2013-11-09','DMASOS',36388 ),
('2013-11-09','DM3',1739 ),
('2013-11-09','DM1',16276 ),
('2013-11-09','DM2',24679 ),
('2013-11-09','DMMS',12064 ),
('2013-11-09','DM4',18162 ),
('2013-11-09','DM5',19200 ),
('2013-11-10','DM1',15775 ),
('2013-11-10','DM5',24501 ),
('2013-11-10','DMASOS',45599 ),
('2013-11-10','DM3',29030 ),
('2013-11-10','DM4',29732 ),
('2013-11-10','DM2',47793 ),
('2013-11-10','DMMS',8412 ),
('2013-11-11','DM2',103944 ),
('2013-11-11','DM1',100793 ),
('2013-11-11','DM3',64715 ),
('2013-11-11','DMEBAY',45704 ),
('2013-11-11','DM4',75957 ),
('2013-11-11','DM5',24903 ),
('2013-11-11','DMASOS',95172 ),
('2013-11-11','DMMS',11174 ),
('2013-11-12','DM5',29695 ),
('2013-11-12','DMEBAY',38831 ),
('2013-11-12','DM2',101735 ),
('2013-11-12','DM3',54590 ),
('2013-11-12','DMASOS',87329 ),
('2013-11-12','DM4',69862 ),
('2013-11-12','DM1',74109 ),
('2013-11-12','DMMS',14550 ),
('2013-11-13','DM5',25990 ),
('2013-11-13','DMMS',19160 ),
('2013-11-13','DM3',53451 ),
('2013-11-13','DMASOS',81026 ),
('2013-11-13','DMEBAY',25738 ),
('2013-11-13','DM4',67861 ),
('2013-11-13','DM2',117515 ),
('2013-11-13','DM1',73290 ),
('2013-11-14','DMMS',9317 ),
('2013-11-14','DM1',66104 ),
('2013-11-14','DMEBAY',22016 ),
('2013-11-14','DM4',61652 ),
('2013-11-14','DM3',39321 ),
('2013-11-14','DM5',22406 ),
('2013-11-14','DM2',97311 ),
('2013-11-14','DMASOS',72841 ),
('2013-11-15','DM2',85349 ),
('2013-11-15','DMASOS',56738 ),
('2013-11-15','DMEBAY',20420 ),
('2013-11-15','DMMS',10723 ),
('2013-11-15','DM1',62114 ),
('2013-11-15','DM3',43478 ),
('2013-11-15','DM5',22360 ),
('2013-11-15','DM4',54254 ),
('2013-11-16','DMASOS',37343 ),
('2013-11-16','DM2',72006 ),
('2013-11-16','DM4',18327 ),
('2013-11-16','DM3',3505 ),
('2013-11-16','DM1',16916 ),
('2013-11-16','DM5',19886 ),
('2013-11-16','DMMS',992 ),
('2013-11-17','DM2',89591 ),
('2013-11-17','DM1',17392 ),
('2013-11-17','DMMS',4297 ),
('2013-11-17','DM4',34622 ),
('2013-11-17','DM5',25792 ),
('2013-11-17','DMASOS',43582 ),
('2013-11-17','DM3',31433 ),
('2013-11-18','DM5',29767 ),
('2013-11-18','DMASOS',89005 ),
('2013-11-18','DM4',184 ),
('2013-11-18','DM4',88152 ),
('2013-11-18','DM2',119430 ),
('2013-11-18','DMEBAY',53644 ),
('2013-11-18','DMMS',14248 ),
('2013-11-18','DM3',74336 ),
('2013-11-18','DM1',110297 ),
('2013-11-19','DMASOS',91181 ),
('2013-11-19','DMEBAY',37166 ),
('2013-11-19','DM2',81040 ),
('2013-11-19','DM1',83314 ),
('2013-11-19','DM4',74183 ),
('2013-11-19','DM3',58901 ),
('2013-11-19','DMMS',14370 ),
('2013-11-19','DM5',29289 ),
('2013-11-20','DM4',80515 ),
('2013-11-20','DM2',54896 ),
('2013-11-20','DM1',72075 ),
('2013-11-20','DMEBAY',28486 ),
('2013-11-20','DMMS',12569 ),
('2013-11-20','DM5',28098 ),
('2013-11-20','DM3',54225 ),
('2013-11-20','DMASOS',79175 ),
('2013-11-21','DMMS',41806 ),
('2013-11-21','DM4',72460 ),
('2013-11-21','DM5',26412 ),
('2013-11-21','DMASOS',86385 ),
('2013-11-21','DMEBAY',24918 ),
('2013-11-21','DM1',73280 ),
('2013-11-21','DM2',48391 ),
('2013-11-21','DM3',56029 ),
('2013-11-22','DMEBAY',24134 ),
('2013-11-22','DM4',69290 ),
('2013-11-22','DM3',51094 ),
('2013-11-22','DMASOS',76889 ),
('2013-11-22','DM1',75159 ),
('2013-11-22','DMMS',15771 ),
('2013-11-22','DM5',22761 ),
('2013-11-22','DM2',57568 ),
('2013-11-23','DM2',40608 ),
('2013-11-23','DMMS',15347 ),
('2013-11-23','DM4',25688 ),
('2013-11-23','DM5',20648 ),
('2013-11-23','DM1',20657 ),
('2013-11-23','DMASOS',40059 ),
('2013-11-23','DM3',4359 ),
('2013-11-24','DM4',1 ),
('2013-11-24','DMMS',16388 ),
('2013-11-24','DM2',54573 ),
('2013-11-24','DM3',33000 ),
('2013-11-24','DMASOS',47143 ),
('2013-11-24','DM1',15896 ),
('2013-11-24','DM4',44582 ),
('2013-11-24','DM5',25913 ),
('2013-11-25','DMEBAY',50853 ),
('2013-11-25','DMASOS',100298 ),
('2013-11-25','DM4',117923 ),
('2013-11-25','DM1',119134 ),
('2013-11-25','DMMS',18296 ),
('2013-11-25','DM5',29493 ),
('2013-11-25','DM2',1 ),
('2013-11-25','DM3',85624 ),
('2013-11-25','DM2',83918 ),
('2013-11-25','DM4',1 ),
('2013-11-26','DM4',86537 ),
('2013-11-26','DM3',65234 ),
('2013-11-26','DMMS',15957 ),
('2013-11-26','DM1',92307 ),
('2013-11-26','DM2',128511 ),
('2013-11-26','DMEBAY',41258 ),
('2013-11-26','DMASOS',78269 ),
('2013-11-26','DM5',31968 ),
('2013-11-27','DMMS',18951 ),
('2013-11-27','DM1',83469 ),
('2013-11-27','DM4',86881 ),
('2013-11-27','DMASOS',81564 ),
('2013-11-27','DM2',120295 ),
('2013-11-27','DM5',30831 ),
('2013-11-27','DMEBAY',28962 ),
('2013-11-27','DM3',64531 ),
('2013-11-28','DM1',87335 ),
('2013-11-28','DMMS',19181 ),
('2013-11-28','DMASOS',95474 ),
('2013-11-28','DM5',35630 ),
('2013-11-28','DMEBAY',26538 ),
('2013-11-28','DM4',88515 ),
('2013-11-28','DM3',65418 ),
('2013-11-28','DM2',104875 ),
('2013-11-29','DM3',64485 ),
('2013-11-29','DMEBAY',24733 ),
('2013-11-29','DM1',86078 ),
('2013-11-29','DM2',100125 ),
('2013-11-29','DM5',33020 ),
('2013-11-29','DMASOS',83495 ),
('2013-11-29','DMMS',21265 ),
('2013-11-29','DM4',78566 ),
('2013-11-30','DM3',2533 ),
('2013-11-30','DMASOS',67115 ),
('2013-11-30','DM5',29880 ),
('2013-11-30','DM2',86789 ),
('2013-11-30','DM4',26136 ),
('2013-11-30','DM1',33836 ),
('2013-11-30','DMMS',15963 ),
('2013-12-01','DMMS',21569 ),
('2013-12-01','DM4',46889 ),
('2013-12-01','DM3',61042 ),
('2013-12-01','DM5',35054 ),
('2013-12-01','DMASOS',53088 ),
('2013-12-01','DM2',97290 ),
('2013-12-01','DM1',19900 ),
('2013-12-02','DMMS',22598 ),
('2013-12-02','DM4',153281 ),
('2013-12-02','DMEBAY',60120 ),
('2013-12-02','DMASOS',118932 ),
('2013-12-02','DM2',127970 ),
('2013-12-02','DM3',123240 ),
('2013-12-02','DM5',37122 ),
('2013-12-02','DM1',154490 ),
('2013-12-03','DM3',97468 ),
('2013-12-03','DMASOS',109399 ),
('2013-12-03','DM4',117000 ),
('2013-12-03','DMEBAY',50511 ),
('2013-12-03','DM5',43661 ),
('2013-12-03','DMMS',23572 ),
('2013-12-03','DM1',122184 ),
('2013-12-03','DM2',139513 ),
('2013-12-04','DM3',74315 ),
('2013-12-04','DM2',89246 ),
('2013-12-04','DMMS',25984 ),
('2013-12-04','DMEBAY',34365 ),
('2013-12-04','DMASOS',124979 ),
('2013-12-04','DM5',40632 ),
('2013-12-04','DM4',112395 ),
('2013-12-04','DM4',1 ),
('2013-12-04','DM1',108487 ),
('2013-12-05','DM3',88496 ),
('2013-12-05','DMASOS',107823 ),
('2013-12-05','DM2',72085 ),
('2013-12-05','DM4',1 ),
('2013-12-05','DM4',106396 ),
('2013-12-05','DM5',35970 ),
('2013-12-05','DM1',112970 ),
('2013-12-05','DMEBAY',31032 ),
('2013-12-05','DMMS',21714 ),
('2013-12-06','DM4',102087 ),
('2013-12-06','DM3',69659 ),
('2013-12-06','DM1',97242 ),
('2013-12-06','DM5',34680 ),
('2013-12-06','DMASOS',101761 ),
('2013-12-06','DMMS',24633 ),
('2013-12-06','DM4',1 ),
('2013-12-06','DM2',76968 ),
('2013-12-06','DMEBAY',17273 ),
('2013-12-07','DMMS',19346 ),
('2013-12-07','DM1',35727 ),
('2013-12-07','DMASOS',83832 ),
('2013-12-07','DM3',8388 ),
('2013-12-07','DM4',36526 ),
('2013-12-07','DM5',27755 ),
('2013-12-07','DM2',44808 ),
('2013-12-08','DMASOS',58567 ),
('2013-12-08','DM4',77151 ),
('2013-12-08','DM1',20630 ),
('2013-12-08','DMMS',17978 ),
('2013-12-08','DM3',51030 ),
('2013-12-08','DM5',30876 ),
('2013-12-08','DM2',67005 ),
('2013-12-09','DM2',92951 ),
('2013-12-09','DMASOS',137372 ),
('2013-12-09','DMEBAY',65482 ),
('2013-12-09','DM1',160613 ),
('2013-12-09','DM5',46123 ),
('2013-12-09','DMMS',22791 ),
('2013-12-09','DM4',134660 ),
('2013-12-09','DM3',105718 ),
('2013-12-10','DM1',116528 ),
('2013-12-10','DM5',41213 ),
('2013-12-10','DM4',114175 ),
('2013-12-10','DM2',88563 ),
('2013-12-10','DMASOS',112608 ),
('2013-12-10','DMEBAY',48814 ),
('2013-12-10','DM3',86247 ),
('2013-12-10','DMMS',21099 ),
('2013-12-11','DM2',89583 ),
('2013-12-11','DM4',118238 ),
('2013-12-11','DM3',86711 ),
('2013-12-11','DMMS',21225 ),
('2013-12-11','DM1',107617 ),
('2013-12-11','DMASOS',109869 ),
('2013-12-11','DM5',40309 ),
('2013-12-11','DMEBAY',33743 ),
('2013-12-12','DM5',36965 ),
('2013-12-12','DM4',125378 ),
('2013-12-12','DMASOS',136228 ),
('2013-12-12','DM1',110473 ),
('2013-12-12','DMMS',21970 ),
('2013-12-12','DMEBAY',28933 ),
('2013-12-12','DM3',83683 ),
('2013-12-12','DM2',95082 ),
('2013-12-13','DM3',70687 ),
('2013-12-13','DM1',108441 ),
('2013-12-13','DMASOS',114821 ),
('2013-12-13','DM4',95719 ),
('2013-12-13','DM2',78355 ),
('2013-12-13','DM5',36923 ),
('2013-12-13','DMEBAY',27830 ),
('2013-12-13','DMMS',21549 ),
('2013-12-14','DM5',33803 ),
('2013-12-14','DMASOS',71692 ),
('2013-12-14','DM2',48545 ),
('2013-12-14','DMMS',23564 ),
('2013-12-14','DM3',7549 ),
('2013-12-14','DM1',25629 ),
('2013-12-14','DM4',25937 ),
('2013-12-15','DM1',37159 ),
('2013-12-15','DMASOS',63805 ),
('2013-12-15','DMMS',19946 ),
('2013-12-15','DM4',63382 ),
('2013-12-15','DM5',29212 ),
('2013-12-15','DM3',58379 ),
('2013-12-15','DM2',69021 ),
('2013-12-16','DM2',108786 ),
('2013-12-16','DM5',40541 ),
('2013-12-16','DMMS',22848 ),
('2013-12-16','DM3',105012 ),
('2013-12-16','DM1',156956 ),
('2013-12-16','DMEBAY',62519 ),
('2013-12-16','DM4',162081 ),
('2013-12-16','DMASOS',140885 ),
('2013-12-17','DM5',40155 ),
('2013-12-17','DMASOS',113435 ),
('2013-12-17','DM2',73683 ),
('2013-12-17','DMMS',20770 ),
('2013-12-17','DM3',79101 ),
('2013-12-17','DMEBAY',43787 ),
('2013-12-17','DM4',117900 ),
('2013-12-17','DM1',132772 ),
('2013-12-18','DM1',153948 ),
('2013-12-18','DM2',63436 ),
('2013-12-18','DM5',31967 ),
('2013-12-18','DMMS',72032 ),
('2013-12-18','DMASOS',126024 ),
('2013-12-18','DMEBAY',30525 ),
('2013-12-18','DM4',119157 ),
('2013-12-18','DM3',68518 ),
('2013-12-19','DM5',29300 ),
('2013-12-19','DM1',117472 ),
('2013-12-19','DMASOS',108108 ),
('2013-12-19','DMEBAY',24816 ),
('2013-12-19','DMMS',17589 ),
('2013-12-19','DM4',99066 ),
('2013-12-19','DM2',47427 ),
('2013-12-19','DM3',55718 ),
('2013-12-20','DMASOS',107003 ),
('2013-12-20','DM1',105006 ),
('2013-12-20','DM5',21686 ),
('2013-12-20','DM4',75144 ),
('2013-12-20','DMMS',17134 ),
('2013-12-20','DMEBAY',19796 ),
('2013-12-20','DM2',36494 ),
('2013-12-20','DM3',43097 ),
('2013-12-21','DMASOS',53964 ),
('2013-12-21','DM1',59214 ),
('2013-12-21','DM4',29958 ),
('2013-12-21','DM2',19063 ),
('2013-12-21','DM5',14003 ),
('2013-12-21','DM3',5491 ),
('2013-12-21','DMMS',19757 ),
('2013-12-22','DM1',26288 ),
('2013-12-22','DM2',22520 ),
('2013-12-22','DM5',14083 ),
('2013-12-22','DMASOS',52512 ),
('2013-12-22','DMMS',26200 ),
('2013-12-22','DM4',51510 ),
('2013-12-22','DM3',31987 ),
('2013-12-23','DM2',30268 ),
('2013-12-23','DMASOS',97467 ),
('2013-12-23','DM3',43201 ),
('2013-12-23','DMEBAY',28910 ),
('2013-12-23','DM1',86002 ),
('2013-12-23','DM5',12543 ),
('2013-12-23','DMMS',19023 ),
('2013-12-23','DM4',87538 ),
('2013-12-24','DM3',11988 ),
('2013-12-24','DM2',18515 ),
('2013-12-24','DMEBAY',15529 ),
('2013-12-24','DMASOS',42192 ),
('2013-12-24','DM4',31433 ),
('2013-12-24','DM5',7297 ),
('2013-12-24','DMMS',5127 ),
('2013-12-24','DM1',20860 ),
('2013-12-25','DMASOS',16868 ),
('2013-12-25','DM4',7418 ),
('2013-12-26','DM5',27969 ),
('2013-12-26','DM2',14883 ),
('2013-12-26','DM3',7678 ),
('2013-12-26','DM4',14546 ),
('2013-12-26','DMMS',25322 ),
('2013-12-26','DM1',16421 ),
('2013-12-26','DMASOS',60347 ),
('2013-12-27','DMASOS',93735 ),
('2013-12-27','DM4',100165 ),
('2013-12-27','DMMS',17150 ),
('2013-12-27','DMEBAY',12129 ),
('2013-12-27','DM2',117052 ),
('2013-12-27','DM5',27608 ),
('2013-12-27','DM1',83327 ),
('2013-12-27','DM3',77185 ),
('2013-12-28','DMMS',31353 ),
('2013-12-28','DM4',40251 ),
('2013-12-28','DM3',9677 ),
('2013-12-28','DM2',51236 ),
('2013-12-28','DM1',21471 ),
('2013-12-28','DMASOS',79491 ),
('2013-12-28','DM5',24176 ),
('2013-12-29','DM2',58236 ),
('2013-12-29','DM4',39642 ),
('2013-12-29','DMMS',28980 ),
('2013-12-29','DMASOS',65639 ),
('2013-12-29','DM5',21888 ),
('2013-12-29','DM3',55282 ),
('2013-12-29','DM1',19561 ),
('2013-12-30','DMEBAY',36897 ),
('2013-12-30','DM2',80355 ),
('2013-12-30','DM1',111367 ),
('2013-12-30','DM4',103076 ),
('2013-12-30','DMMS',26488 ),
('2013-12-30','DMASOS',87367 ),
('2013-12-30','DM5',28542 ),
('2013-12-30','DM3',72688 ),
('2013-12-31','DM5',22852 ),
('2013-12-31','DM1',43795 ),
('2013-12-31','DM4',60716 ),
('2013-12-31','DMASOS',83922 ),
('2013-12-31','DM2',38083 ),
('2013-12-31','DMMS',13858 ),
('2013-12-31','DM3',49935 ),
('2013-12-31','DMEBAY',22385 ),
('2014-10-01','DM4',74334 ),
('2014-10-01','DMMS',14766 ),
('2014-10-01','DM5',36904 ),
('2014-10-01','DMASOS',46762 ),
('2014-10-01','DM1',78053 ),
('2014-10-01','DM3',34314 ),
('2014-10-01','DMEBAY',35192 ),
('2014-10-01','DM6',108 ),
('2014-10-01','DM2',27312 ),
('2014-10-02','DM4',72205 ),
('2014-10-02','DMEBAY',30829 ),
('2014-10-02','DMMS',14031 ),
('2014-10-02','DM1',74427 ),
('2014-10-02','DM2',38531 ),
('2014-10-02','DM5',35382 ),
('2014-10-02','DM3',34372 ),
('2014-10-02','DMASOS',66173 ),
('2014-10-02','DM6',236 ),
('2014-10-03','DMMS',17766 ),
('2014-10-03','DM2',31655 ),
('2014-10-03','DMASOS',57346 ),
('2014-10-03','DMEBAY',27518 ),
('2014-10-03','DM1',62312 ),
('2014-10-03','DM6',295 ),
('2014-10-03','DM4',75793 ),
('2014-10-03','DM5',33745 ),
('2014-10-03','DM3',30949 ),
('2014-10-04','DMASOS',46310 ),
('2014-10-04','DM6',357 ),
('2014-10-04','DMMS',8632 ),
('2014-10-04','DM1',8222 ),
('2014-10-04','DM5',22586 ),
('2014-10-04','DMEBAY',1 ),
('2014-10-04','DM3',8958 ),
('2014-10-04','DM4',47492 ),
('2014-10-04','DM2',17504 ),
('2014-10-05','DMASOS',50088 ),
('2014-10-05','DM2',29214 ),
('2014-10-05','DM1',19557 ),
('2014-10-05','DM5',29814 ),
('2014-10-05','DM6',285 ),
('2014-10-05','DMMS',19896 ),
('2014-10-05','DM4',32036 ),
('2014-10-05','DMEBAY',2 ),
('2014-10-05','DM3',24898 ),
('2014-10-06','DM4',115526 ),
('2014-10-06','DMASOS',75508 ),
('2014-10-06','DM1',86831 ),
('2014-10-06','DM5',50335 ),
('2014-10-06','DM2',37679 ),
('2014-10-06','DM6',284 ),
('2014-10-06','DM3',42238 ),
('2014-10-06','DMEBAY',64164 ),
('2014-10-06','DMMS',16465 ),
('2014-10-07','DM5',40591 ),
('2014-10-07','DM6',511 ),
('2014-10-07','DM1',72142 ),
('2014-10-07','DM3',37501 ),
('2014-10-07','DMEBAY',49024 ),
('2014-10-07','DMMS',17644 ),
('2014-10-07','DM2',33649 ),
('2014-10-07','DM4',95458 ),
('2014-10-07','DMASOS',70168 ),
('2014-10-08','DM1',63496 ),
('2014-10-08','DM5',40729 ),
('2014-10-08','DM6',502 ),
('2014-10-08','DM2',42105 ),
('2014-10-08','DMMS',13990 ),
('2014-10-08','DMEBAY',32999 ),
('2014-10-08','DMASOS',66030 ),
('2014-10-08','DM3',40177 ),
('2014-10-08','DM4',96463 ),
('2014-10-09','DM3',37272 ),
('2014-10-09','DMEBAY',32499 ),
('2014-10-09','DMASOS',80396 ),
('2014-10-09','DM4',92268 ),
('2014-10-09','DM2',43637 ),
('2014-10-09','DM6',2058 ),
('2014-10-09','DM1',64784 ),
('2014-10-09','DM5',37078 ),
('2014-10-09','DMMS',19951 ),
('2014-10-10','DM2',50117 ),
('2014-10-10','DMMS',19886 ),
('2014-10-10','DM1',55570 ),
('2014-10-10','DMASOS',74755 ),
('2014-10-10','DMEBAY',31574 ),
('2014-10-10','DM5',35775 ),
('2014-10-10','DM4',80931 ),
('2014-10-10','DM3',33580 ),
('2014-10-10','DM6',2505 ),
('2014-10-11','DM5',21501 ),
('2014-10-11','DMASOS',44775 ),
('2014-10-11','DM2',27250 ),
('2014-10-11','DM6',1918 ),
('2014-10-11','DM4',38255 ),
('2014-10-11','DMMS',11822 ),
('2014-10-11','DMEBAY',10 ),
('2014-10-11','DM1',6159 ),
('2014-10-11','DM3',9699 ),
('2014-10-12','DMASOS',61189 ),
('2014-10-12','DMMS',18621 ),
('2014-10-12','DM3',23701 ),
('2014-10-12','DMEBAY',6 ),
('2014-10-12','DM2',30722 ),
('2014-10-12','DM5',29155 ),
('2014-10-12','DM6',2269 ),
('2014-10-12','DM4',25973 ),
('2014-10-12','DM1',17718 ),
('2014-10-13','DM6',2711 ),
('2014-10-13','DMEBAY',58569 ),
('2014-10-13','DM3',48460 ),
('2014-10-13','DMASOS',75514 ),
('2014-10-13','DM1',99323 ),
('2014-10-13','DM5',46020 ),
('2014-10-13','DMMS',20417 ),
('2014-10-13','DM4',122914 ),
('2014-10-13','DM2',47867 ),
('2014-10-14','DM6',2574 ),
('2014-10-14','DMASOS',70071 ),
('2014-10-14','DMEBAY',49667 ),
('2014-10-14','DM4',99334 ),
('2014-10-14','DMMS',17985 ),
('2014-10-14','DM3',38008 ),
('2014-10-14','DM1',70731 ),
('2014-10-14','DM2',43755 ),
('2014-10-14','DM5',41753 ),
('2014-10-15','DM4',95738 ),
('2014-10-15','DM3',32570 ),
('2014-10-15','DMEBAY',37180 ),
('2014-10-15','DM1',66625 ),
('2014-10-15','DM6',2220 ),
('2014-10-15','DMMS',12642 ),
('2014-10-15','DMASOS',56636 ),
('2014-10-15','DM2',43446 ),
('2014-10-15','DM5',37115 ),
('2014-10-16','DMASOS',95135 ),
('2014-10-16','DM5',36054 ),
('2014-10-16','DM3',40617 ),
('2014-10-16','DM1',57373 ),
('2014-10-16','DM4',94090 ),
('2014-10-16','DMMS',16957 ),
('2014-10-16','DM2',44724 ),
('2014-10-16','DM6',2092 ),
('2014-10-16','DMEBAY',34510 ),
('2014-10-17','DMMS',4298 ),
('2014-10-17','DM4',82698 ),
('2014-10-17','DMEBAY',31561 ),
('2014-10-17','DM2',40921 ),
('2014-10-17','DMASOS',82859 ),
('2014-10-17','DM3',35449 ),
('2014-10-17','DM5',33549 ),
('2014-10-17','DM6',2032 ),
('2014-10-17','DM1',55203 ),
('2014-10-18','DM4',45768 ),
('2014-10-18','DMEBAY',9 ),
('2014-10-18','DM6',1575 ),
('2014-10-18','DM5',20132 ),
('2014-10-18','DM2',24740 ),
('2014-10-18','DM1',7561 ),
('2014-10-18','DMASOS',39463 ),
('2014-10-18','DMMS',7221 ),
('2014-10-18','DM3',9399 ),
('2014-10-19','DMMS',3818 ),
('2014-10-19','DM5',28169 ),
('2014-10-19','DM3',25313 ),
('2014-10-19','DM6',1639 ),
('2014-10-19','DM4',34956 ),
('2014-10-19','DMEBAY',4 ),
('2014-10-19','DM1',16467 ),
('2014-10-19','DMASOS',50111 ),
('2014-10-19','DM2',29277 ),
('2014-10-20','DM4',119107 ),
('2014-10-20','DM6',1090 ),
('2014-10-20','DMMS',12751 ),
('2014-10-20','DMEBAY',68121 ),
('2014-10-20','DM1',93804 ),
('2014-10-20','DM3',45566 ),
('2014-10-20','DMASOS',107355 ),
('2014-10-20','DM5',46657 ),
('2014-10-20','DM2',51164 ),
('2014-10-21','DM3',36455 ),
('2014-10-21','DM4',96240 ),
('2014-10-21','DMMS',29032 ),
('2014-10-21','DM6',2804 ),
('2014-10-21','DMEBAY',47481 ),
('2014-10-21','DM2',42933 ),
('2014-10-21','DMASOS',76710 ),
('2014-10-21','DM5',41612 ),
('2014-10-21','DM1',70571 ),
('2014-10-22','DMASOS',74759 ),
('2014-10-22','DM4',85913 ),
('2014-10-22','DM6',2320 ),
('2014-10-22','DMEBAY',42115 ),
('2014-10-22','DM1',63043 ),
('2014-10-22','DM2',41530 ),
('2014-10-22','DMMS',18637 ),
('2014-10-22','DM3',38896 ),
('2014-10-22','DM5',37629 ),
('2014-10-23','DM3',33721 ),
('2014-10-23','DM4',81131 ),
('2014-10-23','DMMS',28921 ),
('2014-10-23','DM5',31773 ),
('2014-10-23','DM2',38801 ),
('2014-10-23','DM1',57416 ),
('2014-10-23','DM6',695 ),
('2014-10-23','DMEBAY',35658 ),
('2014-10-23','DMASOS',84127 ),
('2014-10-24','DMASOS',80045 ),
('2014-10-24','DM3',28937 ),
('2014-10-24','DMMS',21959 ),
('2014-10-24','DM2',37798 ),
('2014-10-24','DM5',30580 ),
('2014-10-24','DMEBAY',35238 ),
('2014-10-24','DM4',82696 ),
('2014-10-24','DM1',54490 ),
('2014-10-24','DM6',192 ),
('2014-10-25','DMMS',25757 ),
('2014-10-25','DM1',8264 ),
('2014-10-25','DM2',27492 ),
('2014-10-25','DM3',10630 ),
('2014-10-25','DM5',20371 ),
('2014-10-25','DM4',37175 ),
('2014-10-25','DMASOS',40268 ),
('2014-10-25','DMEBAY',19 ),
('2014-10-26','DMEBAY',1 ),
('2014-10-26','DM4',37030 ),
('2014-10-26','DM2',35304 ),
('2014-10-26','DM3',23850 ),
('2014-10-26','DMASOS',56699 ),
('2014-10-26','DM5',25572 ),
('2014-10-26','DM1',13933 ),
('2014-10-26','DMMS',21723 ),
('2014-10-27','DM4',47997 ),
('2014-10-27','DMASOS',67310 ),
('2014-10-27','DM5',9855 ),
('2014-10-27','DMEBAY',86280 ),
('2014-10-27','DM2',20107 ),
('2014-10-27','DMMS',8534 ),
('2014-10-27','DM1',22940 ),
('2014-10-27','DM3',20492 )
) d ([Date], Stack, ParcelCount)
October 28, 2014 at 2:59 am
vee_jess (10/27/2014)
Hi Chris,After testing I have noticed that this query isn't working as expected. I'm getting 0 parcel count 2014 for Tuesday weekNo 4 in October, then there are parcel count 2014 for Wednesday till Sunday of week 4 October. Today is the 4th Monday of October and there should be a parcel count 2014 for only today in October week 4.
Attached is an excel sheet with 2 tabs - 1 for the results and 2nd is the dataset.
Please have a look and see if you can help
Thanks,
J.
The last query I posted had a filter for November and December:
WHERE MONTH(c.TheDate) IN (11,12)
Did you change it to include October?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 28, 2014 at 3:11 am
Yep, I changed it to WHERE MONTH(c.TheDate) IN (10,11,12). Is that causing the issue?
October 28, 2014 at 3:42 am
vee_jess (10/28/2014)
Yep, I changed it to WHERE MONTH(c.TheDate) IN (10,11,12). Is that causing the issue?
I don't see an issue, the query outputs data for October, week 4 - can you be a little more specific?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 28, 2014 at 4:11 am
Hi Chris,
My apologies, I was confused when I didn't see data for Tuesday week 4 2014 and then there was data for Wednesday week 4 2014. The reason for this is because 4 Tuesdays and 5 Wednesdays in October 2014. Make sense. Thank you so much for your help.
It may be useful to have the full date as a column but then that would explode the result set will it?
October 28, 2014 at 5:27 am
vee_jess (10/28/2014)
Hi Chris,My apologies, I was confused when I didn't see data for Tuesday week 4 2014 and then there was data for Wednesday week 4 2014. The reason for this is because 4 Tuesdays and 5 Wednesdays in October 2014. Make sense. Thank you so much for your help.
It may be useful to have the full date as a column but then that would explode the result set will it?
I think it will be fine because you are aggregating to day level. Include it in your SELECT list and see. You may have to add it to your GROUP BY.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 28, 2014 at 8:19 am
Chris,
Your date function is very cleverly written. Is there a way to get a bigger dataset out of this function? I want a date to start from 01/01/2012 till 31/01/2015.
What I need to do now is to forecast the 2014 parcel count starting from tomorrow all the way till end of January 2015 by looking on how things have changed between the count for 2012 and 2013 for the same period. Any idea on how I can achieve this using your query as a base?
October 29, 2014 at 2:59 am
vee_jess (10/28/2014)
Chris,Your date function is very cleverly written. Is there a way to get a bigger dataset out of this function? I want a date to start from 01/01/2012 till 31/01/2015.
What I need to do now is to forecast the 2014 parcel count starting from tomorrow all the way till end of January 2015 by looking on how things have changed between the count for 2012 and 2013 for the same period. Any idea on how I can achieve this using your query as a base?
-- The function takes parameter @Enddate and uses it like this:
DECLARE @Enddate DATE
SET @Enddate = GETDATE() -- or whatever you wish to pass in
SELECT
Startdate = DATEADD(YEAR,-2,DATEADD(MONTH,DATEDIFF(MONTH,0,@Enddate),0)),
Enddate = CAST(@Enddate AS DATE)
-- Find the first day of the month of @Enddate, then subtract two years from it.
SELECT FirstDayOfMonth = DATEADD(MONTH,DATEDIFF(MONTH,0,@Enddate),0)
SELECT Startdate = DATEADD(YEAR,-2,
DATEADD(MONTH,DATEDIFF(MONTH,0,@Enddate),0)
)
There are two ways to make this function output the date ranges you want: either adjust the expression for startdate, or add @Startdate as a parameter. You could do both: use a value passed in as @startdate, or calculate startdate when @startdate is NULL.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply