Pivot tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pivot tables

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-02 : 10:46:43

This gives me a count of 250 pending:

How can I add this:

and (Datediff(DAY, filedate, Getdate()) > 300 )

and pivot the table to come out as


doc   250   300
s23    5     25


SELECT 
    doc, 
    COUNT(*) AS cnt
FROM
    (
        Select  
            doc
     FROM pendingdds p
join Offices.dbo.OfficeCodes d
on d.officecode = p.doc
where d.typecode='7' and d.reportsto='F03' 

         Group By 
            doc, clms    ) AS T
GROUP BY 
    doc

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-02 : 11:14:34

Okay I added this but the results are coming out not like how I want them. Here's the query


SELECT 
    doc, 
    COUNT(*) AS cnt
FROM
    (
        Select  
            doc
     FROM pendingdds p
join Offices.dbo.OfficeCodes d
on d.officecode = p.doc
where d.typecode='7' and d.reportsto='F03' 
and (Datediff(DAY, filedate, Getdate()) > 250 )
         Group By 
            doc, clms   ) AS T

group by t.doc

union


SELECT 
    doc, 
    COUNT(*) AS cnt
FROM
    (
        Select  
            doc
     FROM pendingdds p
join Offices.dbo.OfficeCodes d
on d.officecode = p.doc
where d.typecode='7' and d.reportsto='F03' 
and (Datediff(DAY, filedate, Getdate()) > 300 )
         Group By 
            doc, clms    ) AS T
GROUP BY 
    doc

These results are coming out as:


doc   cnt
s23    6
s23    2

How can I get it to come out this way:


doc   250   300
s23    6     2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-02 : 12:17:48

sounds like this


        Select  doc,
           COUNT(CASE WHEN Datediff(DAY, filedate, Getdate()) > 300 THEN clms END) AS [300],
           COUNT(CASE WHEN Datediff(DAY, filedate, Getdate()) > 250 THEN clms END) AS [250]
     FROM pendingdds p
join Offices.dbo.OfficeCodes d
on d.officecode = p.doc
where d.typecode='7' and d.reportsto='F03' 
Group By doc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-02 : 13:17:46

THANKS visakh16 that was what I was looking for!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-02 : 15:11:01

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources