Problem with pivot SQL server 2008 r2

Question

Problem with pivot SQL server 2008 r2

Navigation

#1 by (0 votes)

1

Good people,

I have not touched much the issue of sql, at least to do what a client has asked me, you need to create a view where you see a single line with the days of a month that you enter a user of your application and show certain data.

I have this query that works very well

    SELECT *
FROM (
    SELECT 
       left(datename(day,DATEFIELD),3)as [day],
        FIELD1 AS FIELD1
    FROM dbo.TABLEFROMDDBB
    WHERE FIELD2 = 0 
    AND FIELD1 = 0
    AND DATEFIELD between '01/06/2017 00:00:00' AND '30/06/2017 23:59:00'
    AND FIELD3 = 'xxx'
    AND FIELD4 = 123456789
    AND FIELD5 = 'xxxx'
) as s
PIVOT
(
    COUNT(FIELD1)
    FOR [day] IN ([1], [2], [3], [4],[5],[6],[7],[8],[9],[10],
[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
[21],[22],[23],[24],[25],[26],[27],[28],[29],[30])
)AS pvt

What I need is to put this other query right in the "FOR [day] IN" of the pivot so that it shows me the days of the specific month (with year included) in the resulting table, this is the rest of the code that I have created:

    DECLARE @month TINYINT        
SET @month = MONTH('01/12/2017 00:00:00');
WITH
CTE_Days AS
(
SELECT DATEADD(month, @month , DATEADD(month, -MONTH(GETDATE()), DATEADD(day, -DAY(GETDATE())
+ 1, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS datetime)))) D
UNION ALL
SELECT DATEADD(day, 1, D)
FROM CTE_Days
WHERE D < DATEADD(day, -1, DATEADD(month, 1, DATEADD(month, @month, DATEADD(month, -MONTH(GETDATE()),
DATEADD(day, -DAY(GETDATE()) + 1, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))))))
)
SELECT DAY(d) AS Days_Of_Week
FROM CTE_Days

Who says put this query says that solutions are accepted to see if I can get this forward, I appreciate any help you can lend me.

Thank you very much forearm:)

EDIT:

I have tried to create a dynamic sql:

DECLARE @cols AS VARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @date1 AS NVARCHAR(MAX),
    @date2 AS NVARCHAR(MAX)

    set @date1 = '01/06/2017'
    set @date2 = '30/06/2017'

;WITH
CTE_Days AS
(
SELECT DATEADD(month, 06, DATEADD(month, -MONTH(GETDATE()), DATEADD(day, -DAY(GETDATE())
+ 1, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS datetime)))) D
UNION ALL
SELECT DATEADD(day, 1, D)
FROM CTE_Days
WHERE D < DATEADD(day, -1, DATEADD(month, 1, DATEADD(month, 06, DATEADD(month, -MONTH(GETDATE()),
DATEADD(day, -DAY(GETDATE()) + 1, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)))))))



SELECT @cols = 'SELECT DAY(d) AS Days_Of_Week FROM CTE_Days'


SET @query = 'SELECT *
FROM (
    SELECT 
       left(datename(day,DATEFIELD),3)as [day],
        FIELD1 AS FIELD1
    FROM dbo.TABLEFROMDDBB
    WHERE FIELD2 = 0 
    AND FIELD1 = 0
    AND DATEFIELD between ' + @date1 + ' AND ' + @date2 + ' 
     AND FIELD3 = ''xxx''
    AND FIELD4 = 123456789
    AND FIELD5 = ''xxxx''
) as s
PIVOT
(
    COUNT(xrealizado_sn)
    FOR [day] IN (' + @cols + ')
)AS pvt'

execute(@query)

but it throws the following error to me, that for more than I have looked for in Don Google I have not cleared nothing:

Common table expression defined but not used.

I appreciate any help you can give me, compañeros.

Thank you very much forearm:)

sql sql-server pivot

asked by Tony Waters 14.11.2017 в 17:37

source

1 answer

Extract profile picture API login facebook with JavaScript Leverage Browser Caching

score 0 · Answer 1

In the end, after giving it more laps than a Noria I have taken out a query that, at least works for me:

    DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @date1 AS NVARCHAR(MAX),
    @date2 AS NVARCHAR(MAX),
    @space NVARCHAR (10),
    @x NVARCHAR (10) 

    set @date1 = '''01/06/2013'''
    set @date2 = '''30/06/2013'''
    set @space = ''''''
    set @x = '''X'''

select @cols = STUFF((SELECT ',' + QUOTENAME(DAY(a.FECHA))
                    from dbo.CALENDARIO AS a
                    WHERE a.FECHA between @date1 AND @date2 
                    group by a.FECHA
                    order by a.FECHA
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

SET @query = 'SELECT pvt.*
FROM (
    SELECT 
       left(datename(day,DATEFIELD),3)as [day],
        FIELD1 AS FIELD1
    FROM dbo.TABLEFROMDDBB
    WHERE FIELD2 = 0 
    AND FIELD1 = 0
    AND DATEFIELD between ' + @date1 + ' AND ' + @date2 + ' 
    AND FIELD3 = 'xxx'
    AND FIELD4 = 123456789
    AND FIELD5 = 'xxxx'
) as s
PIVOT
(
    COUNT(FIELD1)
    FOR [day] IN (' + @cols + ')
)AS pvt' 

execute(@query);

I created a Calendar table where I inserted ALL the dates from 2000 to 3000 (DD / MM / YYYY) from where I take the data of the days of the chosen month and then I do the pivot, I only managed it with one sql dynamic, but now I have other problems for which I will open another thread to see if I can get help or advice, which are always welcome.

I hope that if someone has this problem, it will help.