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

0
votes

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.stack.imgur.com/5aECo.png https://i.stack.imgur.com/Cgu7A.png

2
sqlsql-servertsqlsql-server-2008ssms
Please edit your question and insert the SQL query as text instead of a screenshot. Thanks! - Corion
Welcome to SO. The function you are looking for is DATEPART(). - dfundako
Hi and welcome to SO. Here is a great place to start. - Sean Lange
Welcome to Stack Overflow. Firstly, please don't tag irrelevant RDBMS. Oracle and SQL Server are completely different products; the picture is clearly of SSMS, so why are you tagging Oracle? When Posting Data and code, don't post it as an image. Post it as text. - Larnu

2 Answers

2
votes

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

1
votes

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)