December 20, 2015 at 1:27 am
Hi,
I need a help with getting dates from day to day in month.
Example:
I have daily sales fact. Customer wants to compute monthly sales from 2nd in month to 1st in next month and set is as month from start date.
January: 2.1.2015 - 1.2.2015
February: 2.2.2015 - 1.3.2015
etc..
Now I can do next:
SELECT * FROM FactSales WHERE DateID > datefromparts(year(DateID), month(DateID), 1) AND DateID <= DATEADD(DAY, 1, EOMONTH(DateID,0))
but the output alway without 1st in month as i assume my first condition filters it out.
Can I do this with group by?
select * from factsales
group by
datefromparts(year(DateID), month(DateID), 2) , DATEADD(DAY, 1, EOMONTH(DateID,0))
but if I want to get dates, I have to group by dateid also and it is not OK, as I am getting wrong dates.
Thank you for your help
Borut
December 20, 2015 at 3:06 am
Quick suggestion, add a [SALES_MONTH] column to the a date dimension with the offset of 1 day for the month.
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @START_DATE DATE = '2014-01-01';
DECLARE @END_DATE DATE = '2017-01-01';
DECLARE @SAMPLE_SIZE INT = DATEDIFF(DAY,@START_DATE,@END_DATE)
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
, NUMS(N) AS (SELECT 0 UNION ALL SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
,DATE_LIST AS
(
SELECT
NM.N
,DATEADD(DAY,NM.N,@START_DATE) AS DT_VAL
FROM NUMS NM
)
SELECT
DL.N AS [N]
,DL.DT_VAL AS [DATE]
,YEAR(DL.DT_VAL) AS [YEAR]
,MONTH(DL.DT_VAL) AS [MONTH]
,LAG(MONTH(DL.DT_VAL),1,12) OVER
(
ORDER BY DL.N ASC
) AS [SALES_MONTH]
,DATEPART(WEEK, DL.DT_VAL) AS [WEEK]
,DATEPART(DD, DL.DT_VAL) AS [DOM]
,DATEPART(DAYOFYEAR, DL.DT_VAL) AS [DOM]
FROM DATE_LIST DL;
First 60 days of the output
N DATE YEAR MONTH SALES_MONTH WEEK DOM DOM
-------------------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
0 2014-01-01 2014 1 12 1 1 1
1 2014-01-02 2014 1 1 1 2 2
2 2014-01-03 2014 1 1 1 3 3
3 2014-01-04 2014 1 1 1 4 4
4 2014-01-05 2014 1 1 2 5 5
5 2014-01-06 2014 1 1 2 6 6
6 2014-01-07 2014 1 1 2 7 7
7 2014-01-08 2014 1 1 2 8 8
8 2014-01-09 2014 1 1 2 9 9
9 2014-01-10 2014 1 1 2 10 10
10 2014-01-11 2014 1 1 2 11 11
11 2014-01-12 2014 1 1 3 12 12
12 2014-01-13 2014 1 1 3 13 13
13 2014-01-14 2014 1 1 3 14 14
14 2014-01-15 2014 1 1 3 15 15
15 2014-01-16 2014 1 1 3 16 16
16 2014-01-17 2014 1 1 3 17 17
17 2014-01-18 2014 1 1 3 18 18
18 2014-01-19 2014 1 1 4 19 19
19 2014-01-20 2014 1 1 4 20 20
20 2014-01-21 2014 1 1 4 21 21
21 2014-01-22 2014 1 1 4 22 22
22 2014-01-23 2014 1 1 4 23 23
23 2014-01-24 2014 1 1 4 24 24
24 2014-01-25 2014 1 1 4 25 25
25 2014-01-26 2014 1 1 5 26 26
26 2014-01-27 2014 1 1 5 27 27
27 2014-01-28 2014 1 1 5 28 28
28 2014-01-29 2014 1 1 5 29 29
29 2014-01-30 2014 1 1 5 30 30
30 2014-01-31 2014 1 1 5 31 31
31 2014-02-01 2014 2 1 5 1 32
32 2014-02-02 2014 2 2 6 2 33
33 2014-02-03 2014 2 2 6 3 34
34 2014-02-04 2014 2 2 6 4 35
35 2014-02-05 2014 2 2 6 5 36
36 2014-02-06 2014 2 2 6 6 37
37 2014-02-07 2014 2 2 6 7 38
38 2014-02-08 2014 2 2 6 8 39
39 2014-02-09 2014 2 2 7 9 40
40 2014-02-10 2014 2 2 7 10 41
41 2014-02-11 2014 2 2 7 11 42
42 2014-02-12 2014 2 2 7 12 43
43 2014-02-13 2014 2 2 7 13 44
44 2014-02-14 2014 2 2 7 14 45
45 2014-02-15 2014 2 2 7 15 46
46 2014-02-16 2014 2 2 8 16 47
47 2014-02-17 2014 2 2 8 17 48
48 2014-02-18 2014 2 2 8 18 49
49 2014-02-19 2014 2 2 8 19 50
50 2014-02-20 2014 2 2 8 20 51
51 2014-02-21 2014 2 2 8 21 52
52 2014-02-22 2014 2 2 8 22 53
53 2014-02-23 2014 2 2 9 23 54
54 2014-02-24 2014 2 2 9 24 55
55 2014-02-25 2014 2 2 9 25 56
56 2014-02-26 2014 2 2 9 26 57
57 2014-02-27 2014 2 2 9 27 58
58 2014-02-28 2014 2 2 9 28 59
59 2014-03-01 2014 3 2 9 1 60
60 2014-03-02 2014 3 3 10 2 61
December 20, 2015 at 4:02 am
hm, ok.
But what if I have dynamic day from - day to and I am doing dynamic filter on my factsales. Because of that once I will have day from: 2, once I have a day from: 5, etc... and I can have dynamic day to...
I don't think that with that solution I will resolve my problem ...
December 20, 2015 at 4:22 am
Borut Olenik (12/20/2015)
hm, ok.But what if I have dynamic day from - day to and I am doing dynamic filter on my factsales. Because of that once I will have day from: 2, once I have a day from: 5, etc... and I can have dynamic day to...
I don't think that with that solution I will resolve my problem ...
You can change the offset parameter of the LAG function and use the output of that function as a grouping parameter.
😎
Quick example
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @START_DATE DATE = '2014-01-01';
DECLARE @END_DATE DATE = '2017-01-01';
DECLARE @SAMPLE_SIZE INT = DATEDIFF(DAY,@START_DATE,@END_DATE);
DECLARE @DATE_OFFSET INT = 15;
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
, NUMS(N) AS (SELECT 0 UNION ALL SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
,DATE_LIST AS
(
SELECT
NM.N
,DATEADD(DAY,NM.N,@START_DATE) AS DT_VAL
FROM NUMS NM
)
,BASE_DATA AS
(
SELECT
DL.N AS [N]
,DL.DT_VAL AS [DATE]
,LAG(YEAR(DL.DT_VAL),@DATE_OFFSET,2013) OVER
(
ORDER BY DL.N ASC
) AS [SALES_YEAR]
,LAG(MONTH(DL.DT_VAL),@DATE_OFFSET,12) OVER
(
ORDER BY DL.N ASC
) AS [SALES_MONTH]
FROM DATE_LIST DL
)
SELECT
BD.SALES_YEAR
,BD.SALES_MONTH
,SUM(BD.N) AS SUM_N
,COUNT(BD.DATE) AS COUNT_DATE
FROM BASE_DATA BD
GROUP BY BD.SALES_YEAR
,BD.SALES_MONTH
ORDER BY BD.SALES_YEAR
,BD.SALES_MONTH
;
Results
SALES_YEAR SALES_MONTH SUM_N COUNT_DATE
----------- ----------- -------------------- -----------
2013 12 105 15
2014 1 930 31
2014 2 1666 28
2014 3 2759 31
2014 4 3585 30
2014 5 4650 31
2014 6 5415 30
2014 7 6541 31
2014 8 7502 31
2014 9 8175 30
2014 10 9393 31
2014 11 10005 30
2014 12 11284 31
2015 1 12245 31
2015 2 11886 28
2015 3 14074 31
2015 4 14535 30
2015 5 15965 31
2015 6 16365 30
2015 7 17856 31
2015 8 18817 31
2015 9 19125 30
2015 10 20708 31
2015 11 20955 30
2015 12 22599 31
2016 1 23560 31
2016 2 22910 29
2016 3 25420 31
2016 4 25515 30
2016 5 27311 31
2016 6 27345 30
2016 7 29202 31
2016 8 30163 31
2016 9 30105 30
2016 10 32054 31
2016 11 31935 30
2016 12 18496 17
December 20, 2015 at 6:21 am
Thank you. I will try to get out what I need.
I have tried your solution just to see how it is working, now I have to implement this in my solution :).
I will let you know.
December 20, 2015 at 8:47 am
I guess I'd have to have a clarification meeting with the customer. I just can't imagine why anyone would want to add a day offset to each month. For example, what happens when the range is 15 months? Start on the 16th for a month? Doesn't seem right to me.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply