Go to Main page..

during aggregation by dates, only NULL are issued (Microsoft SQL Server)





during aggregation by dates, only NULL [new]
 table final1 ( product_id int , shop_code nvarchar ( 20 ), doc_date datetime , diff2 int , diff1 int , BASEITLOG int , base int , TotalSum int ) insert into final1 values ( 11628 , '00664НСК' , '2015-01-01 00:00:00.000' , 0 , 208 , 318 , 318 , 526 ), ( 11628 , '00664НСК' , '2015-02-01 00:00:00.000' , 24 , 290 , 345 , 369 , 659 ), ( 11628 , '00664НСК' , '2015-03-01 00:00:00.000' , 12 , 0 , 503 , 515 , 515 ), ( 11628 , '00664НСК' , '2015-04-01 00:00:00.000' , 0 , 273 , 264 , 264 , 537 ), ( 11628 , '00664НСК' , '2015-05-01 00:00:00.000' , 0 , 195 , 266 , 266 , 461 ), ( 11628 , '00664НСК' , '2015-06-01 00:00:00.000' , 0 , 0 , 265 , 265 , 265 ), ( 11628 , '00664НСК' , '2015-07-01 00:00:00.000' , 482 , 0 , 231 , 713 , 713 ), ( 11628 , '00664НСК' , '2015-08-01 00:00:00.000' , 182 , 0 , 333 , 515 , 515 ), ( 11628 , '00664НСК' , '2015-09-01 00:00:00.000' , 287 , 0 , 376 , 663 , 663 ), ( 11628 , '00664НСК' , '2015-10-01 00:00:00.000' , 180 , 0 , 232 , 412 , 4 Create table final1 ( product_id int , shop_code nvarchar ( 20 ), doc_date datetime , diff2 int , diff1 int , BASEITLOG int , base int , TotalSum int ) insert into final1 values ( 11628 , '00664NSK' , '2015-01-01 00: 00: 00.000 ' , 0 , 208 , 318 , 318 , 526 ), ( 11628 , ' 00664NSK ' , ' 2015-02-01 00: 00: 00.000 ' , 24 , 290 , 345 , 369 , 659 ), ( 11628 , '00664NSK' , '2015-03-01 00: 00: 00.000' , 12 , 0 , 503 , 515 , 515 ), ( 11628 , '00664NSK' , '2015-04-01 00: 00: 00.000' , 0 , 273 , 264 , 264 , 537 ), ( 11628 , '00664NSK' , '2015-05-01 00: 00: 00.000' , 0 , 195 , 266 , 266 , 461 ), ( 11628 , '00664NSK' , '2015- 06-01 00: 00: 00.000 ' , 0 , 0 , 265 , 265 , 265 ), ( 11628 , ' 00664NSK ' , ' 2015-07-01 00: 00: 00.000 ' , 482 , 0 , 231 , 713 , 713 ), ( 11628 , '00664NSK' , '2015-08-01 00: 00: 00.000' , 182 , 0 , 333 , 515 , 515 ), ( 11628 , '00664NSK' , '2015-09-01 00:00: 00.000 ' , 287 , 0 , 376 , 663 , 663 ), ( 11628 , ' 00664NSK ' , ' 2015-10-01 00: 00: 00.000 ' , 180 , 0 , 232 , 412 , 4  ( 11628 , '00664НСК' , '2015-11-01 00:00:00.000' , 0 , 0 , 197 , 197 , 197 ), ( 11628 , '00664НСК' , '2015-12-01 00:00:00.000' , 596 , 0 , 952 , 1548 , 1548 ), ( 11628 , '00664НСК' , '2016-01-01 00:00:00.000' , 0 , 0 , 375 , 375 , 375 ), ( 11628 , '00664НСК' , '2016-02-01 00:00:00.000' , 256 , 164 , 252 , 508 , 672 ), ( 11628 , '00664НСК' , '2016-03-01 00:00:00.000' , 0 , 0 , 267 , 267 , 267 ), ( 11628 , '00664НСК' , '2016-04-01 00:00:00.000' , 0 , 0 , 176 , 176 , 176 ), ( 11628 , '00664НСК' , '2016-05-01 00:00:00.000' , 0 , 0 , 181 , 181 , 181 ), ( 11628 , '00664НСК' , '2016-06-01 00:00:00.000' , 0 , 0 , 236 , 236 , 236 ), ( 11628 , '00664НСК' , '2016-07-01 00:00:00.000' , 16 , 455 , 208 , 224 , 679 ), ( 11628 , '00664НСК' , '2016-08-01 00:00:00.000' , 0 , 0 , 185 , 185 , 185 ), ( 11628 , '00664НСК' , '2016-09-01 00:00:00.000' , 0 , 0 , 196 , 196 , 196 ), ( 11628 , '00664НСК' , '2016-10-01 00:00:00.000' , 0 , 0 , 201 , 201 , 201 ), ( 11628 , ' 12 ), ( 11628 , '00664NSK' , '2015-11-01 00: 00: 00.000' , 0 , 0 , 197 , 197 , 197 ), ( 11628 , '00664NSK' , '2015-12-01 00:00 : 00.000 ' , 596 , 0 , 952 , 1548 , 1548 ), ( 11628 , ' 00664NSK ' , ' 2016-01-01 00: 00: 00.000 ' , 0 , 0 , 375 , 375 , 375 ), ( 11628 , ' 00664NSK ' , ' 2016-02-01 00: 00: 00.000 ' , 256 , 164 , 252 , 508 , 672 ), ( 11628 , ' 00664NSK ' , ' 2016-03-01 00: 00: 00.000 ' , 0 , 0 , 267 , 267 , 267 ), ( 11628 , '00664NSK' , '2016-04-01 00: 00: 00.000' , 0 , 0 , 176 , 176 , 176 ), ( 11628 , '00664NSK' , '2016-05 -01 00: 00: 00.000 ' , 0 , 0 , 181 , 181 , 181 ), ( 11628 , ' 00664NSK ' , ' 2016-06-01 00: 00: 00.000 ' , 0 , 0 , 236 , 236 , 236 ) , ( 11628 , '00664NSK' , '2016-07-01 00: 00: 00.000' , 16 , 455 , 208 , 224 , 679 ), ( 11628 , '00664NSK' , '2016-08-01 00: 00: 00.000 ' , 0 , 0 , 185 , 185 , 185 ), ( 11628 , ' 00664NSK ' , ' 2016-09-01 00: 00: 00.000 ' , 0 , 0 , 196 , 196 , 196 ), ( 11628 , ' 00664NSK ' , '2016-10-01 00: 00: 00.000' , 0 , 0 , 201 , 201 , 201 ), ( 11628 , '  , '2016-11-01 00:00:00.000' , 0 , 454 , 207 , 207 , 661 ), ( 11628 , '00664НСК' , '2016-12-01 00:00:00.000' , 0 , 0 , 275 , 275 , 275 ), ( 11628 , '00664НСК' , '2017-01-01 00:00:00.000' , 0 , 212 , 116 , 116 , 328 ), ( 11628 , '00664НСК' , '2017-02-01 00:00:00.000' , 0 , 170 , 125 , 125 , 295 ), ( 11628 , '00664НСК' , '2017-03-01 00:00:00.000' , 0 , 0 , 242 , 242 , 242 ), ( 11628 , '00664НСК' , '2017-04-01 00:00:00.000' , 0 , 0 , 136 , 136 , 136 ), ( 11628 , '00664НСК' , '2017-05-01 00:00:00.000' , 0 , 0 , 193 , 193 , 193 ), ( 11628 , '00664НСК' , '2017-06-01 00:00:00.000' , 251 , 0 , 219 , 470 , 470 ), ( 11628 , '00664НСК' , '2017-07-01 00:00:00.000' , 0 , 0 , 183 , 183 , 183 ), ( 11628 , '00664НСК' , '2017-08-01 00:00:00.000' , 0 , 11 , 219 , 219 , 230 ), ( 11628 , '00664НСК' , '2017-09-01 00:00:00.000' , 0 , 204 , 194 , 194 , 398 ), ( 11628 , '00664НСК' , '2017-10-01 00:00:00.000' , 0 , 81 , 204 , 204 , 285 ), ( 11628 , '00664НСК' , ' 00664NSK ' , ' 2016-11-01 00: 00: 00.000 ' , 0 , 454 , 207 , 207 , 661 ), ( 11628 , ' 00664NSK ' , ' 2016-12-01 00: 00: 00.000 ' , 0 , 0 , 275 , 275 , 275 ), ( 11628 , '00664NSK' , '2017-01-01 00: 00: 00.000' , 0 , 212 , 116 , 116 , 328 ), ( 11628 , '00664NSK' , '2017-02 -01 00: 00: 00.000 ' , 0 , 170 , 125 , 125 , 295 ), ( 11628 , ' 00664NSK ' , ' 2017-03-01 00: 00: 00.000 ' , 0 , 0 , 242 , 242 , 242 ) , ( 11628 , '00664NSK' , '2017-04-01 00: 00: 00.000' , 0 , 0 , 136 , 136 , 136 ), ( 11628 , '00664NSK' , '2017-05-01 00: 00: 00.000 ' , 0 , 0 , 193 , 193 , 193 ), ( 11628 , ' 00664NSK ' , ' 2017-06-01 00: 00: 00.000 ' , 251 , 0 , 219 , 470 , 470 ), ( 11628 , ' 00664NSK ' , '2017-07-01 00: 00: 00.000' , 0 , 0 , 183 , 183 , 183 ), ( 11628 , '00664NSK' , '2017-08-01 00: 00: 00.000' , 0 , 11 , 219 , 219 , 230 ), ( 11628 , '00664NSK' , '2017-09-01 00: 00: 00.000' , 0 , 204 , 194 , 194 , 398 ), ( 11628 , '00664NSK' , '2017-10-01 00: 00: 00.000 ' , 0 , 81 , 204 , 204 , 285 ), ( 11628 , ' 00664NSK ' , '  , 0 , 60 , 191 , 191 , 251 ), ( 11628 , '00664НСК' , '2017-12-01 00:00:00.000' , 16 , 207 , 251 , 267 , 474 ), ( 11628 , '00664НСК' , '2018-01-01 00:00:00.000' , 0 , 0 , 148 , 148 , 148 ), ( 11628 , '00664НСК' , '2018-02-01 00:00:00.000' , 0 , 0 , 164 , 164 , 164 ), ( 11628 , '00664НСК' , '2018-03-01 00:00:00.000' , 0 , 0 , 277 , 277 , 277 ), ( 11628 , '00664НСК' , '2018-04-01 00:00:00.000' , 0 , 0 , 137 , 137 , 137 ), ( 11628 , '00664НСК' , '2018-05-01 00:00:00.000' , 0 , 274 , 194 , 194 , 468 ), ( 11628 , '00664НСК' , '2018-06-01 00:00:00.000' , 12 , 30 , 184 , 196 , 226 ), ( 11628 , '00664НСК' , '2018-07-01 00:00:00.000' , 0 , 0 , 134 , 134 , 134 ), ( 11628 , '00664НСК' , '2018-08-01 00:00:00.000' , 0 , 32 , 116 , 116 , 148 ), ( 11628 , '00664НСК' , '2018-09-01 00:00:00.000' , 0 , 115 , 117 , 117 , 232 ) ; 2017-11-01 00: 00: 00.000 ' , 0 , 60 , 191 , 191 , 251 ), ( 11628 , ' 00664NSK ' , ' 2017-12-01 00: 00: 00.000 ' , 16 , 207 , 251 , 267 , 474 ), ( 11628 , '00664NSK' , '2018-01-01 00: 00: 00.000' , 0 , 0 , 148 , 148 , 148 ), ( 11628 , '00664NSK' , '2018-02-01 00: 00: 00.000 ' , 0 , 0 , 164 , 164 , 164 ), ( 11628 , ' 00664NSK ' , ' 2018-03-01 00: 00: 00.000 ' , 0 , 0 , 277 , 277 , 277 ), ( 11628 , '00664NSK' , '2018-04-01 00: 00: 00.000' , 0 , 0 , 137 , 137 , 137 ), ( 11628 , '00664NSK' , '2018-05-01 00: 00: 00.000' , 0 , 274 , 194 , 194 , 468 ), ( 11628 , '00664NSK' , '2018-06-01 00: 00: 00.000' , 12 , 30 , 184 , 196 , 226 ), ( 11628 , '00664NSK' , '2018- 07-01 00: 00: 00.000 ' , 0 , 0 , 134 , 134 , 134 ), ( 11628 , ' 00664NSK ' , ' 2018-08-01 00: 00: 00.000 ' , 0 , 32 , 116 , 116 , 148 ), ( 11628 , '00664NSK' , '2018-09-01 00: 00: 00.000' , 0 , 115 , 117 , 117 , 232 ) ;  CTE AS ( SELECT *, YEAR ( doc_date ) AS DocYr , DATENAME ( mm , doc_date ) AS DocMnth FROM final1 ) SELECT variable , product_id , shop_code , DocYr AS [ Year ], MAX ( CASE WHEN DocMnth = 'January' THEN val END ) AS Jan , MAX ( CASE WHEN DocMnth = 'February' THEN val END ) AS Feb , MAX ( CASE WHEN DocMnth = 'March' THEN val END ) AS Mar , MAX ( CASE WHEN DocMnth = 'April' THEN val END ) AS Apr , MAX ( CASE WHEN DocMnth = 'May' THEN val END ) AS May , MAX ( CASE WHEN DocMnth = 'June' THEN val END ) AS Jun , MAX ( CASE WHEN DocMnth = 'July' THEN val END ) AS Jul , MAX ( CASE WHEN DocMnth = 'August' THEN val END ) AS Aug , MAX ( CASE WHEN DocMnth = 'September' THEN val END ) AS Sep , MAX ( CASE WHEN DocMnth = 'October' THEN val END ) AS Oct , MAX ( CASE WHEN DocMnth = 'November' THEN val END ) AS Nov , MAX ( CASE WHEN DocMnth = 'December' THEN val END ) AS [ Dec ] FROM ( SELECT product_id , shop_code , diff2 , diff1 , BASEITLOG , base , TotalSum , DocYr , DocMnth FROM CTE ) c UNPIVOT With CTE AS ( SELECT *, YEAR ( doc_date ) AS DocYr , DATENAME ( mm , doc_date ) AS DocMnth FROM final1 ) SELECT variable , product_id , shop_code , DocYr AS [ Year ], MAX ( CASE WHEN DocMnth = 'January' THEN val END ) AS Jan , MAX ( CASE WHEN DocMnth = 'February' THEN val END ) AS Feb , MAX ( CASE WHEN DocMnth = 'March' THEN val END ) AS Mar , MAX ( CASE WHEN DocMnth = 'April' THEN val END ) AS Apr , MAX ( CASE WHEN DocMnth = 'May' THEN val END ) AS May , MAX ( CASE WHEN DocMnth = 'June' THEN val END ) AS Jun , MAX ( CASE WHEN DocMnth = 'July' THEN val END ) AS Jul , MAX ( CASE WHEN DocMnth = 'August' THEN val END ) AS Aug , MAX ( CASE WHEN DocMnth = 'September' THEN val END ) AS Sep , MAX ( CASE WHEN DocMnth = 'October' THEN val END ) AS Oct , MAX ( CASE WHEN DocMnth = 'November' THEN val END ) AS Nov , MAX ( CASE WHEN DocMnth = 'December' THEN val END ) AS [ Dec ] FROM ( SELECT product_id , shop_code , diff2 , diff1 , BASEITLOG , base , TotalSum , DocYr , DocMnth FROM CTE ) with UNPIVOT  val FOR variable IN ([ diff2 ],[ diff1 ],[ BASEITLOG ],[ base ],[ TotalSum ])) u GROUP BY variable , product_id , shop_code , DocYr ( val FOR variable IN ([ diff2 ], [ diff1 ], [ BASEITLOG ], [ base ], [ TotalSum ])) u GROUP BY variable , product_id , shop_code , DocYr 



I get zeros
variable product_id Shop_code Year Feb Feb Mar Apr May Jul Jul Aug Sep Oct Nov Dec
base 11628 00664NSK 2015 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

But this is a wrong calculation, there should be aggregated values. How to do it right?
Re: at aggregation by dates there are only NULL [new]
You checked that DATENAME (mm, doc_date) returns to you?
Re: at aggregation by dates there are only NULL [new]
Kontox,

SET LANGUAGE ENGLISH
Re: at aggregation by dates there are only NULL [new]
Kontox
How to do it right?
Idavavitsya depending on the language:
 ;  CTE AS ( SELECT *, YEAR ( doc_date ) AS DocYr , month ( doc_date ) AS DocMnth FROM final1 ) SELECT variable , product_id , shop_code , DocYr AS [ Year ], MAX ( CASE WHEN DocMnth = 1 THEN val END ) AS Jan , MAX ( CASE WHEN DocMnth = 2 THEN val END ) AS Feb , MAX ( CASE WHEN DocMnth = 3 THEN val END ) AS Mar , MAX ( CASE WHEN DocMnth = 4 THEN val END ) AS Apr , MAX ( CASE WHEN DocMnth = 5 THEN val END ) AS May , MAX ( CASE WHEN DocMnth = 6 THEN val END ) AS Jun , MAX ( CASE WHEN DocMnth = 7 THEN val END ) AS Jul , MAX ( CASE WHEN DocMnth = 8 THEN val END ) AS Aug , MAX ( CASE WHEN DocMnth = 9 THEN val END ) AS Sep , MAX ( CASE WHEN DocMnth = 10 THEN val END ) AS Oct , MAX ( CASE WHEN DocMnth = 11 THEN val END ) AS Nov , MAX ( CASE WHEN DocMnth = 12 THEN val END ) AS [ Dec ] With CTE AS ( SELECT *, YEAR ( doc_date ) AS DocYr , month ( doc_date ) AS DocMnth FROM final1 ) SELECT variable , product_id , shop_code , DocYr AS [ Year ], MAX ( CASE WHEN DocMnth = 1 THEN val END ) AS Jan , MAX ( CASE WHEN DocMnth = 2 THEN val END ) AS Feb , MAX ( CASE WHEN DocMnth = 3 THEN val END ) AS Mar , MAX ( CASE WHEN DocMnth = 4 THEN val END ) AS Apr , MAX ( CASE WHEN DocMnth = 5 THEN val END ) AS May , MAX ( CASE WHEN DocMnth = 6 THEN val END ) AS Jun , MAX ( CASE WHEN DocMnth = 7 THEN val END ) AS Jul , MAX ( CASE WHEN DocMnth = 8 THEN val END ) AS Aug , MAX ( CASE WHEN DocMnth = 9 THEN val END ) AS Sep , MAX ( CASE WHEN DocMnth = 10 THEN val END ) AS Oct , MAX ( CASE WHEN DocMnth = 11 THEN val END ) AS Nov , MAX ( CASE WHEN DocMnth = 12 THEN val END ) AS [ Dec ] 
Re: at aggregation by dates there are only NULL [new]
If I change either the language or the month
The message 245, level 16, state 1, line 3
Error converting varchar "January" value to int data type.
Re: at aggregation by dates there are only NULL [new]
Kontox
If I change either the language or the month
The message 245, level 16, state 1, line 3
Error converting varchar "January" value to int data type.


and you only see the part of the code that invm allocated?
did not notice that in his example, instead of the names of the months are their numbers?
Re: at aggregation by dates there are only NULL [new]
And I copied all the code. Just January counted, the rest are zeros.
Re: at aggregation by dates there are only NULL [new]
I got the impression that the code works yyyy-dd-mm, together yyyy-mm
Re: at aggregation by dates there are only NULL [new]
Kontox
I got the impression that the code works yyyy-dd-mm, together yyyy-mm



I put SET LANGUAGE ENGLISH before your request without changing a single line and everything worked.
Re: at aggregation by dates there are only NULL [new]
yes now and it worked for me when sql overloaded
Re: at aggregation by dates there are only NULL [new]
Kontox,

determine the number of the month number of religion does not allow, or what? You wrote an example.



Old articles you may read here
Go to Main page






ZZZZZZZZZZZZ