根据日期每周拆分数据 [英] Split the data week wise based on date

问题描述

 
我有一个包含ProductId，DateofPurchase，Quantity的列表。 
 
 ProductId DateofPurchase数量
 1245 10/03/2018 50 
 4577 23/03/2018 100 
 
我在where子句中有一个参数是dateofpurchase基于此我得到的数量如下
 
从表中选择* dateofpurchase = '23 / 03/2018'，结果是100 
但是我想要的结果如下。 
数量应该在它属于哪个星期来决定。 
 
 ProductId Week1 Week2 Week3 Week4 
 4577  -   -  100  -  
 
这里的周数由
决定1-7是周1,8-15周2 ，16-23周3,24-30周4 
 
请建议。 
 
 我尝试过： 
 
基于日期参数，应显示一周

解决方案

你需要做的第一件事是弄清楚日期的日期是什么......因为你可以使用 DATEPART [ ^ ]



例如：

  SELECT  *，datepart（D，DateOfPurchase） AS  [day] 来自 [ table ] 

给你

 ProductId DateOfPurchase数量日
 1245 2018-03-10 50 10 
 4577 2018-03-23 100 23 

你可以使用 CASE [ ^ ] BETWEEN [ ^ ]



例如（不完整）

 选择 
 Week1 =  case    datepart（D，DateOfPurchase） BETWEEN   1   AND   7  那么数量
  ELSE   NULL   END ，
 Week2 =  case  当 datepart（D，DateOfPurchase） BETWEEN   8   AND   15  那么数量
  ELSE   NULL   END ，... 

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

解决方案

The first thing you need to do is work out what the day bit of the date is ... for that you can use DATEPART[^]

For example:

SELECT *, datepart(D, DateOfPurchase) AS [day] from [table]

which gives you

ProductId	DateOfPurchase	Quantity	day
1245		2018-03-10	50		10
4577		2018-03-23	100		23

You can then use that [day] in a test using CASE[^] with BETWEEN[^]

For example (incomplete)

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, ...

这篇关于根据日期每周拆分数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！