CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008ssms
Akshay Suryavanshi
Akshay Suryavanshi

Reputation: 1

How to Find Mondays of month in given date column as row in sql

These all dates are of Monday of October i want it to do Dynamically. It would be better if can do the same for Mon, Tue , Wed only, and last column should be sum[As that is showing in excel sheet

select ClientName,JobName, JobTime, [2018-09-03], [2018-09-10], [2018-09-17], [2018-09-24] from (select ClientName,JobName, JobTime, convert(date,reporttime,101) as [ReportDate] from StaleFileReport where convert(varchar,ReportTime,102) in ('2018.09.03','2018.09.10','2018.09.17','2018.09.24') group by ClientName, JobName,JobTime, convert(date,reporttime,101))x PIVOT ( Count([ReportDate]) FOR [ReportDate] in ( [2018-09-03], [2018-09-10], [2018-09-17], [2018-09-24]) ) AS P

https://i.sstatic.net/5aECo.png https://i.sstatic.net/Cgu7A.png

Upvotes: 0

Views: 1038

Answers (2)

Cato
Cato

Reputation: 3701

Set it up using a WITH command, you can add this to your SQLquery, then in your query you can use the date table

DECLARE @AdayOfMonth as date = '2018.10.01';   --any date in the month you want
WITH DAYS AS (SELECT 0 AS D UNION ALL SELECT D + 1 FROM DAYS WHERE D < 30),
    PD AS (
          SELECT DATEADD(day,
                         DAYS.D,
                         dateadd(day,1- DAY(@AdayOfMonth),@AdayOfMonth)) t FROM DAYS
          ),
    AD AS (
           SELECT * FROM PD where MONTH(PD.t) = 
                                         MONTH(@AdayOfMonth) 
                                                   AND DATENAME(dw,PD.t) = 'monday'
          )
    SELECT * FROM AD;   --your query in place of this simple select

your query will then include

...IN(SELECT T FROM AD)

Upvotes: 1

Eray Balkanli
Eray Balkanli

Reputation: 7960

You can use a script like below:

DECLARE @PassedInDate AS DATE;

SET @PassedInDate = GETDATE();

WITH    CTE ( N )
          AS (
               SELECT   1
               FROM     ( VALUES ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1) ) a ( N )
               ),
        CTE2 ( N )
          AS (
               SELECT   0 -- 0 based
               UNION ALL
               SELECT TOP 30 -- limit it up front, never more than 31 days in a month!
                        ROW_NUMBER() OVER ( ORDER BY (SELECT   NULL) )
               FROM     CTE a
               CROSS JOIN CTE b
             ),
        CTE3 ( N )
          AS (
               SELECT   DATEADD(DAY, N, DATEADD(MONTH, DATEDIFF(MONTH, 1, @PassedInDate), 0))
               FROM     CTE2
               WHERE    DATEADD(DAY, N, DATEADD(MONTH, DATEDIFF(MONTH, 1, @PassedInDate), 0)) < 
                        DATEADD(MONTH, DATEDIFF(MONTH, 1, @PassedInDate) + 1, 0)
             )
SELECT  N AS [DATE]
FROM    CTE3
WHERE DATENAME(WEEKDAY, N) = 'Monday';

Ref: https://www.sqlservercentral.com/Forums/Topic1654075-391-1.aspx

Upvotes: 2

Related Questions