Member 13867163 Ответов: 1

Разделите данные по неделям в зависимости от даты

Hi,

I have a Table with columns ProductId, DateofPurchase, Quantity. 

ProductId DateofPurchase Quantity
1245       10/03/2018      50
4577       23/03/2018      100

I have a parameter in where clause which is dateofpurchase based on this i get the quanity as below

Select * from table where dateofpurchase='23/03/2018' and the result is 100
But I want the result as follows.
Quantity should be decided in which week it belongs to.

ProductId Week1  Week2  Week3  Week4
4577         -      -    100    -

Here the weeks are decided by 
1-7 is week1,8-15 week2,16-23 week3, 24-30 week4

Please Suggest.

What I have tried:

Based on date parameter the week should be displayed

1 Ответов

Рейтинг:
1

CHill60

Первое, что вам нужно сделать, это выяснить, какой день является частью даты ... для этого вы можете использовать Функция datepart[^]

Например:

SELECT *, datepart(D, DateOfPurchase) AS [day] from [table]
что дает вам 
ProductId	DateOfPurchase	Quantity	day
1245		2018-03-10	50		10
4577		2018-03-23	100		23
Затем вы можете использовать этот [день] в тесте, используя ДЕЛО[^] с МЕЖДУ[^]

Например (неполный)
Select  
	Week1 = case when datepart(D, DateOfPurchase) BETWEEN 1 AND 7 THEN Quantity
		ELSE NULL END,
	Week2 = case when datepart(D, DateOfPurchase) BETWEEN 8 AND 15 THEN Quantity
		ELSE NULL END, ...

Member 13867163

Большое вам спасибо