Adding case statement - 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

Adding case statement

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-02-27 : 15:55:09

Right now it checks for over 250 days old

How can I add to check if it's > 249 and < 301 to this statement below? I also want to check >301 and < 351 days old as well.



if @doc = 'F03' 
begin
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
end

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-27 : 17:43:26

What is the column that represents the date?

IF @doc = 'F03'
BEGIN
    SELECT doc,
		   CASE 
			WHEN Age BETWEEN 250 AND 300 THEN '250-300'
			WHEN Age BETWEEN 302 AND 350 THEN '302-340'
			END AS AgeRange,
           COUNT(*) AS cnt
    FROM   (
               SELECT doc, DATEDIFF(DAY,yourDateColumn,GETDATE()) Age
               FROM   pendingdds p
                      JOIN Offices.dbo.OfficeCodes d
                           ON  d.officecode = p.doc
               WHERE  d.typecode = '7'
                      AND d.reportsto = 'F03'
                      AND DATEDIFF(DAY,yourDateColumn,GETDATE()) BETWEEN 250 AND 350
               GROUP BY
                      doc,
                      clms
           ) AS T
	
    GROUP BY
           doc,
           	CASE 
			WHEN Age BETWEEN 250 AND 300 THEN '250-300'
			WHEN Age BETWEEN 302 AND 350 THEN '302-340'
			END
END

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-02-27 : 20:02:01

It's called filedate. I will try it in the morning and get back to you. Thanks so much!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-02-28 : 09:15:29

Thanks this works but I need to add a few more case statements. When it comes in the first time it should show pending cases > 250

The case statements I wish to add/correct:

> 300 < 351

> 350 < 401

> 400

How will I add these to the stored procedure?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-02-28 : 10:16:44

The way this stored procdure is set up it produces this:



doc        cnt

S09	    1
S09	    2
S09	    9
S09	    51
S0B	     1
S0B	     4
S0B	    16
S0B	    57

How do I change it to give me results as this?


Doc       250    300   350    400
S09	   1      2     9       51
S0B	   1      4    16       57



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

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)
      And (Datediff(Day, filedate, Getdate()) < 351)
         Group By 
            doc, clms    ) AS T
GROUP BY 
    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()) > 350)
      And (Datediff(Day, filedate, Getdate()) < 400)
         Group By 
            doc, clms    ) AS T
GROUP BY 
    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()) > 400)
         Group By 
            doc, clms    ) AS T
GROUP BY 
    doc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources