ms sql weekday

Often in sales reports and so on you need to compare this day to the same day last month, but based on the same "weekday", not "day of month".

So for example, if i select 11/02/2015(MON), and  try to find last month record which is supposed to be 10/01/2015 (THUR)
How can this be done in T-SQL?asdfas
LVL 1
ITsolutionWizardAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
Business days are also affected by national holidays, company days off etc.
The best you can do here is to create your own calendar table where you can assign business day sequence No. to every day in the month (non-business days should have NULL value).

You can even use date data type for the business day column and thus utilize SQL date functions in calculations. Of course, you have to solve cases where the appropriate business day does not exist etc.

So to compare business days is not good method in all the cases because to compare Fridays or Mondays to Wednesdays could raise unexpected differences... To compare the daily averages for the whole month should give more consistent results.
ITsolutionWizardAuthor Commented:
our query will exclude all major holidays. so do not worry about it.
Can you show me some ms sql codes how to do it?
Jim HornMicrosoft SQL Server Data DudeCommented:
Check out my series of articles starting with SQL Server Calendar Table on how to build your own table that stores columns such as day of week, day of month, for use in querying.
Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

pcelbaCommented:
"Can you show me some ms sql codes how to do it?"

The calendar table creation is described in the Jim's article.
Do you want the code which takes the N-th business day of the previous month?
Brian CroweDatabase AdministratorCommented:
Maybe this will help.

WITH cteDateRaw AS
(
	SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AS [Date]
	UNION ALL
	SELECT DATEADD(DAY, 1, [Date])
	FROM cteDateRaw
	WHERE DATEADD(DAY, 1, [Date]) < GETDATE()
),
cteDate AS
(
	SELECT [Date],
		MONTH([Date]) AS [Month],
		DATEPART(WEEKDAY, [Date]) AS [DayOfWeek],
		CASE
			WHEN DATEPART(WEEKDAY, [Date]) IN (1, 7) THEN NULL
			ELSE ROW_NUMBER() OVER(PARTITION BY MONTH([Date]) ORDER BY [Date])
		END AS DayNumber
	FROM cteDateRaw
	WHERE DATEPART(WEEKDAY, [Date]) NOT IN (1, 7)
)
SELECT *
FROM cteDate AS PrevMonth
LEFT OUTER JOIN cteDate AS CurrMonth
	ON PrevMonth.[Month] = MONTH(DATEADD(MONTH, -1, GETDATE()))
	AND CurrMonth.[Month] = MONTH(GETDATE())
	AND PrevMonth.DayNumber = CurrMonth.DayNumber
WHERE PrevMonth.DayNumber IS NOT NULL
ORDER BY PrevMonth.Date

Open in new window


Date	Month	DayOfWeek	DayNumber	Date	Month	DayOfWeek	DayNumber
2015-10-01 00:00:00.000	10	5	1	2015-11-02 00:00:00.000	11	2	1
2015-10-02 00:00:00.000	10	6	2	2015-11-03 00:00:00.000	11	3	2
2015-10-05 00:00:00.000	10	2	3	2015-11-04 00:00:00.000	11	4	3
2015-10-06 00:00:00.000	10	3	4	2015-11-05 00:00:00.000	11	5	4
2015-10-07 00:00:00.000	10	4	5	2015-11-06 00:00:00.000	11	6	5
2015-10-08 00:00:00.000	10	5	6	2015-11-09 00:00:00.000	11	2	6
2015-10-09 00:00:00.000	10	6	7	2015-11-10 00:00:00.000	11	3	7
2015-10-12 00:00:00.000	10	2	8	2015-11-11 00:00:00.000	11	4	8
2015-10-13 00:00:00.000	10	3	9	2015-11-12 00:00:00.000	11	5	9
2015-10-14 00:00:00.000	10	4	10	2015-11-13 00:00:00.000	11	6	10
2015-10-15 00:00:00.000	10	5	11	NULL	NULL	NULL	NULL
2015-10-16 00:00:00.000	10	6	12	NULL	NULL	NULL	NULL
2015-10-19 00:00:00.000	10	2	13	NULL	NULL	NULL	NULL
2015-10-20 00:00:00.000	10	3	14	NULL	NULL	NULL	NULL
2015-10-21 00:00:00.000	10	4	15	NULL	NULL	NULL	NULL
2015-10-22 00:00:00.000	10	5	16	NULL	NULL	NULL	NULL
2015-10-23 00:00:00.000	10	6	17	NULL	NULL	NULL	NULL
2015-10-26 00:00:00.000	10	2	18	NULL	NULL	NULL	NULL
2015-10-27 00:00:00.000	10	3	19	NULL	NULL	NULL	NULL
2015-10-28 00:00:00.000	10	4	20	NULL	NULL	NULL	NULL
2015-10-29 00:00:00.000	10	5	21	NULL	NULL	NULL	NULL
2015-10-30 00:00:00.000	10	6	22	NULL	NULL	NULL	NULL
2015-11-02 00:00:00.000	11	2	1	NULL	NULL	NULL	NULL
2015-11-03 00:00:00.000	11	3	2	NULL	NULL	NULL	NULL
2015-11-04 00:00:00.000	11	4	3	NULL	NULL	NULL	NULL
2015-11-05 00:00:00.000	11	5	4	NULL	NULL	NULL	NULL
2015-11-06 00:00:00.000	11	6	5	NULL	NULL	NULL	NULL
2015-11-09 00:00:00.000	11	2	6	NULL	NULL	NULL	NULL
2015-11-10 00:00:00.000	11	3	7	NULL	NULL	NULL	NULL
2015-11-11 00:00:00.000	11	4	8	NULL	NULL	NULL	NULL
2015-11-12 00:00:00.000	11	5	9	NULL	NULL	NULL	NULL
2015-11-13 00:00:00.000	11	6	10	NULL	NULL	NULL	NULL

Open in new window

ITsolutionWizardAuthor Commented:
HiBrian Crowe, thank ,but I just need to see one date not the whole list.

So for example, if i select 11/02/2015(MON), and  try to find last month record which is supposed to be 10/01/2015 (THUR)
Brian CroweDatabase AdministratorCommented:
You could incorporate the logic I posted into a UDF to return the DayOfMonth Equivalent from the Previous month fairly easily.

CREATE FUNCTION dbo.PreviousMonthBusinessDay
(
	@SourceDate	DATE = NULL
)
RETURNS DATE
AS
BEGIN
	DECLARE @ReturnDate	DATE;

	SELECT @SourceDate = ISNULL(@SourceDate, GETDATE());

	WITH cteDateRaw AS
	(
		SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @SourceDate) - 1, 0) AS [Date]
		UNION ALL
		SELECT DATEADD(DAY, 1, [Date])
		FROM cteDateRaw
		WHERE DATEADD(DAY, 1, [Date]) <= @SourceDate
	),
	cteDate AS
	(
		SELECT [Date],
			MONTH([Date]) AS [Month],
			DATEPART(WEEKDAY, [Date]) AS [DayOfWeek],
			CASE
				WHEN DATEPART(WEEKDAY, [Date]) IN (1, 7) THEN NULL
				ELSE ROW_NUMBER() OVER(PARTITION BY MONTH([Date]) ORDER BY [Date])
			END AS DayNumber
		FROM cteDateRaw
		WHERE DATEPART(WEEKDAY, [Date]) NOT IN (1, 7)
	)
	SELECT @ReturnDate = PrevMonth.[Date]
	FROM cteDate AS PrevMonth
	INNER JOIN cteDate AS CurrMonth
		ON PrevMonth.[Month] = MONTH(DATEADD(MONTH, -1, @SourceDate))
		AND CurrMonth.[Month] = MONTH(@SourceDate)
		AND PrevMonth.DayNumber = CurrMonth.DayNumber
	WHERE CurrMonth.[Date] = @SourceDate;

	RETURN @ReturnDate;
END

Open in new window

Brian CroweDatabase AdministratorCommented:
Caveat:  This isn't going to be nearly as efficient as using a date table of some kind especially if you try to apply it to a large dataset but it will work fine on small datasets.
ITsolutionWizardAuthor Commented:
from the query list. Can you help me to add two columns as attached?
it is called revertcount1 and revertcount2

I just do not know how to loop increment to 1.....thanks
Book1.xls
Brian CroweDatabase AdministratorCommented:
Can you post the query that generates that output?
ITsolutionWizardAuthor Commented:
WITH cteDateRaw AS
(
      SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AS [Date]
      UNION ALL
      SELECT DATEADD(DAY, 1, [Date])
      FROM cteDateRaw
      WHERE DATEADD(DAY, 1, [Date]) < GETDATE()
),
cteDate AS
(
      SELECT [Date],
            MONTH([Date]) AS [Month],
            DATEPART(WEEKDAY, [Date]) AS [DayOfWeek],
            CASE
                  WHEN DATEPART(WEEKDAY, [Date]) IN (1, 7) THEN NULL
                  ELSE ROW_NUMBER() OVER(PARTITION BY MONTH([Date]) ORDER BY [Date])
            END AS DayNumber
      FROM cteDateRaw
      WHERE DATEPART(WEEKDAY, [Date]) NOT IN (1, 7)
)
insert table(using your query header + revertcount1 + revertcount2)
SELECT *
FROM cteDate AS PrevMonth
LEFT OUTER JOIN cteDate AS CurrMonth
      ON PrevMonth.[Month] = MONTH(DATEADD(MONTH, -1, GETDATE()))
      AND CurrMonth.[Month] = MONTH(GETDATE())
      AND PrevMonth.DayNumber = CurrMonth.DayNumber
WHERE PrevMonth.DayNumber IS NOT NULL
ORDER BY PrevMonth.Date
Brian CroweDatabase AdministratorCommented:
Where are the revertcount1 and revertcount2 coming from?  You should be able to just join them into the query using the date columns within that table.  You might need to join the table twice, once to CurrMonth and again to PrevMonth to be able to compare sales values or whatever side-by-side.
PortletPaulEE Topic AdvisorCommented:
I am unsure what you intend to do with this because the "pivoted" column arrangement may be quite inefficient for the overall report logic.

However, this MIGHT be of some assistance. Here I create 32 rows using some CTEs, then I join the working dates, Monday-Friday (ignoring any holidays!) , to those rows to arrive at a side-by-side list of working dates like this:
| rn | LastMonthDate | LastMonthWday | ThisMonthDate | ThisMonthWday |
|----|---------------|---------------|---------------|---------------|
|  1 |    2015-10-01 |             1 |    2015-11-02 |             1 |
|  2 |    2015-10-02 |             2 |    2015-11-03 |             2 |
|  3 |    2015-10-05 |             3 |    2015-11-04 |             3 |
|  4 |    2015-10-06 |             4 |    2015-11-05 |             4 |
|  5 |    2015-10-07 |             5 |    2015-11-06 |             5 |
|  6 |    2015-10-08 |             6 |    2015-11-09 |             6 |
|  7 |    2015-10-09 |             7 |    2015-11-10 |             7 |
|  8 |    2015-10-12 |             8 |    2015-11-11 |             8 |
|  9 |    2015-10-13 |             9 |    2015-11-12 |             9 |
| 10 |    2015-10-14 |            10 |    2015-11-13 |            10 |
| 11 |    2015-10-15 |            11 |    2015-11-16 |            11 |
| 12 |    2015-10-16 |            12 |        (null) |        (null) |
| 13 |    2015-10-19 |            13 |        (null) |        (null) |
| 14 |    2015-10-20 |            14 |        (null) |        (null) |
| 15 |    2015-10-21 |            15 |        (null) |        (null) |
| 16 |    2015-10-22 |            16 |        (null) |        (null) |
| 17 |    2015-10-23 |            17 |        (null) |        (null) |
| 18 |    2015-10-26 |            18 |        (null) |        (null) |
| 19 |    2015-10-27 |            19 |        (null) |        (null) |
| 20 |    2015-10-28 |            20 |        (null) |        (null) |
| 21 |    2015-10-29 |            21 |        (null) |        (null) |
| 22 |    2015-10-30 |            22 |        (null) |        (null) |
        

Open in new window

The query used for that is:
;WITH
  R2 AS (SELECT 0 AS N UNION ALL SELECT 1)
, R4 AS  (SELECT R2.N FROM R2 CROSS JOIN R2 AS N2)
, R16 AS (SELECT R4.N FROM R4 CROSS JOIN R4 AS N4)
, R32 AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RN
           FROM (
                 SELECT R2.N FROM R16 CROSS JOIN R2
                ) D
          )
, cteDateRaw AS (
              SELECT
                     DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AS [aDate]
                   , DATEDIFF(DAY,0,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)) % 7 AS DOW
              UNION ALL
              SELECT
                     DATEADD(DAY, 1, [aDate])
                   , DATEDIFF(DAY,0,DATEADD(DAY, 1, [aDate])) % 7
              FROM cteDateRaw
              WHERE DATEADD(DAY, 1, [aDate]) < GETDATE()
            )
, cteDates AS (
            SELECT
                  aDate
                , ROW_NUMBER() OVER(PARTITION BY MONTH(aDate) ORDER BY aDate) AS WDayNumber
            FROM cteDateRaw
            WHERE DOW < 5
            )  
            
SELECT
      r32.rn
    , format( lm.adate , 'yyyy-MM-dd') LastMonthDate
    , lm.WDayNumber LastMonthWday
    , format( tm.adate , 'yyyy-MM-dd') ThisMonthDate
    , tm.WDayNumber ThisMonthWday
FROM R32
LEFT JOIN cteDates as lm on R32.rn = lm.WDayNumber
                        and MONTH(lm.aDate) = MONTH( DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) )
LEFT JOIN cteDates as tm on R32.rn = tm.WDayNumber
                        and MONTH(tm.aDate) = MONTH( DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) , 0) )
WHERE ( lm.aDate IS NOT NULL or tm.aDate IS NOT NULL )
ORDER BY r32.rn

Open in new window

Personally I think it's a bit weird trying to align days this way.
ITsolutionWizardAuthor Commented:
hi brian, below is my logic. Basically, I use your query, and insert into a table.(TestingTmp3)
and I hope I can add revertCount1 and revertCount2 to achieve my goal that I described last week.
Can you show me how to do that?


WITH cteDateRaw AS
(
      SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AS [Date]
      UNION ALL
      SELECT DATEADD(DAY, 1, [Date])
      FROM cteDateRaw
      WHERE DATEADD(DAY, 1, [Date]) < GETDATE()
),
cteDate AS
(
      SELECT [Date],
            MONTH([Date]) AS [Month],
            DATEPART(WEEKDAY, [Date]) AS [DayOfWeek],
            CASE
                  WHEN DATEPART(WEEKDAY, [Date]) IN (1, 7) THEN NULL
                  ELSE ROW_NUMBER() OVER(PARTITION BY MONTH([Date]) ORDER BY [Date])
            END AS DayNumber
      FROM cteDateRaw
      WHERE DATEPART(WEEKDAY, [Date]) NOT IN (1, 7)
)
Insert into dbo.TestingTmp3
--(Date1,Month1,DayOfWeek1,DayNumber1,Date2,Month2,DayOfWeek2,DayNumber2)


SELECT  * FROM cteDate AS PrevMonth
LEFT OUTER JOIN cteDate AS CurrMonth
      ON PrevMonth.[Month] = MONTH(DATEADD(MONTH, -1, GETDATE()))
      AND CurrMonth.[Month] = MONTH(GETDATE())
      AND PrevMonth.DayNumber = CurrMonth.DayNumber
WHERE PrevMonth.DayNumber IS NOT NULL
ORDER BY PrevMonth.Date


Design of TestingTmp3

Date2

Date1      datetime      Checked
Month1      varchar(50)      Checked
DayofWeek1      varchar(50)      Checked
DayNumber1      varchar(50)      Checked
Date2      datetime      Checked
Month2      varchar(50)      Checked
DayofWeek2      varchar(50)      Checked
DayNumber2      varchar(50)      Checked
            Unchecked
Brian CroweDatabase AdministratorCommented:
Below is a way I envision the code being used against a table of dated values.

DECLARE @CountTable TABLE
(
	SomeValue		FLOAT,
	SomeDate		DATE
);

INSERT INTO @CountTable (SomeValue, SomeDate)
VALUES (1001, '20151001'),
	(1002, '20151002'),
	(1003, '20151003'),
	(1004, '20151004'),
	(1005, '20151005'),
	(1006, '20151006'),
	(1101, '20151101'),
	(1102, '20151102'),
	(1103, '20151103'),
	(1104, '20151104'),
	(1105, '20151105'),
	(1106, '20151106');

WITH cteDateRaw AS
(
	SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AS [Date]
	UNION ALL
	SELECT DATEADD(DAY, 1, [Date])
	FROM cteDateRaw
	WHERE DATEADD(DAY, 1, [Date]) < GETDATE()
),
cteDate AS
(
	SELECT [Date],
		MONTH([Date]) AS [Month],
		DATEPART(WEEKDAY, [Date]) AS [DayOfWeek],
		CASE
			WHEN DATEPART(WEEKDAY, [Date]) IN (1, 7) THEN NULL
			ELSE ROW_NUMBER() OVER(PARTITION BY MONTH([Date]) ORDER BY [Date])
		END AS DayNumber
	FROM cteDateRaw
	WHERE DATEPART(WEEKDAY, [Date]) NOT IN (1, 7)
)
SELECT SrcCurr.*, SrcPrev.*
FROM @CountTable AS SrcCurr
INNER JOIN cteDate AS CurrMonth
	ON SrcCurr.SomeDate = CurrMonth.[Date]
INNER JOIN cteDate AS PrevMonth
	ON PrevMonth.[Month] = MONTH(DATEADD(MONTH, -1, GETDATE()))
	AND CurrMonth.[Month] = MONTH(GETDATE())
	AND PrevMonth.DayNumber = CurrMonth.DayNumber
INNER JOIN @CountTable AS SrcPrev
	ON PrevMonth.[Date] = SrcPrev.SomeDate
WHERE PrevMonth.DayNumber IS NOT NULL
ORDER BY PrevMonth.Date

Open in new window

SomeValue	SomeDate	SomeValue	SomeDate
1102	2015-11-02	1001	2015-10-01
1103	2015-11-03	1002	2015-10-02
1104	2015-11-04	1005	2015-10-05
1105	2015-11-05	1006	2015-10-06

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ITsolutionWizardAuthor Commented:
actually, i just need to add two columns revertcount1 and revertcount2
just like this attached file....not the one you posted recently.

Thanks
Book1.xlsx
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.