Help with stored procedure

[nc297]

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.dbfficeCodes 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.dbfficeCodes 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.dbfficeCodes 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.dbfficeCodes 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

 

[djj55]

Are your columns the same all the time or do they change?
(200, 250, 300, 350 ...)

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[nc297]

They are the same all of the time. I have radio buttons on the page so when I click on 300 I want the gridview to fill with the pending items that are between 300 - 350.

 

[MarkSweetland]

Something like this maybe?

select doc, [250]=SUM([250]), [300]=SUM([300]), [350]=SUM([350]), [400]=SUM([400])
from 
(    
    SELECT	
		doc, 
	    [250] = case when [Days] between 250 and 299 then cnt else 0 end,
        [300] = case when [days] between 300 and 349 then cnt else 0 end,
        [350] = case when [days] between 350 and 399 then cnt else 0 end,
        [400] = case when [days] >= 400 then cnt else 0 end
    from  (
        SELECT	
            doc, cnt=COUNT(*), 
		    [days]=Datediff(Day, filedate, Getdate())
        FROM pendingdds p
             join Offices.dbo.OfficeCodes d on d.officecode = p.doc 
        WHERE d.typecode='7' and d.reportsto='F03' 
		group by doc, Datediff(Day, filedate, Getdate())
    ) as a
)as b
group by doc

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[nc297]

Thanks Mark this works well!

Now how would I grab all the 300 cases? Would I need a parameter? I have buttons called 300, 350 and 400 so when a user clicks on them I want them to change to display the info.

 

[wbodger]

Isn't this a great case for pivot tables?

 

[nc297]

No because I need one set of result at a time. So if you click on the 300 button I only want those cases that are over 300 days old and younder than 350 days old.

 

[MarkSweetland]

Yes, but in a different procedure:

declare @MIN_DAYS int
	set @MIN_DAYS = 300
declare @MAX_DAYS int
	set @MAX_DAYS = 349

SELECT [fieldNames_here]
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()) between @MIN_DAYS and @MAX_DAYS

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[nc297]

Thanks Mark I will try this in the morning and get back to you.

 

[nc297]

Mark, this one worked better for me. Thanks so much.

Can you please explain what it's doing:
select doc, [250]=SUM([250]), [300]=SUM([300]), [350]=SUM([350]), [400]=SUM([400])
from
(
SELECT
doc,
[250] = case when [Days] between 250 and 299 then cnt else 0 end,
[300] = case when [days] between 300 and 349 then cnt else 0 end,
[350] = case when [days] between 350 and 399 then cnt else 0 end,
[400] = case when [days] >= 400 then cnt else 0 end
from (
SELECT
doc, cnt=COUNT(*),
[days]=Datediff(Day, filedate, Getdate())
FROM pendingdds p
join Offices.dbfficeCodes d on d.officecode = p.doc
WHERE d.typecode='7' and d.reportsto='F03'
group by doc, Datediff(Day, filedate, Getdate())
) as a
)as b
group by doc

 

[MarkSweetland]

If you run each select indivdually, you'll see the output at each stage.

SELECT doc, cnt=COUNT(*), [days]=Datediff(Day, filedate, Getdate())
FROM pendingdds p
        join Offices.dbo.OfficeCodes d on d.officecode = p.doc 
WHERE d.typecode='7' and d.reportsto='F03' 
GROUP BY doc, Datediff(Day, filedate, Getdate())

then

SELECT    
        doc, 
        [250] = case when [Days] between 250 and 299 then cnt else 0 end,
        [300] = case when [days] between 300 and 349 then cnt else 0 end,
        [350] = case when [days] between 350 and 399 then cnt else 0 end,
        [400] = case when [days] >= 400 then cnt else 0 end
    from  (
        SELECT    
            doc, cnt=COUNT(*), 
            [days]=Datediff(Day, filedate, Getdate())
        FROM pendingdds p
             join Offices.dbo.OfficeCodes d on d.officecode = p.doc 
        WHERE d.typecode='7' and d.reportsto='F03' 
        group by doc, Datediff(Day, filedate, Getdate())
    ) as a

then the whole thing.



Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[nc297]

Okay yes I see the difference. I will keep this just in case they come back and want this veiw. Thanks again!

Have another problem...

When you select a botton how do I show the claims for the botton they selected. This query shows 250. Do I add a parameter to show 300 cases, 350 cases etc... I need to show the bold section one at a time.

if @doc in ('V01')
Begin
Select DOC,
Reg,
FO,
CLMS,


Age = Datediff(day,max(filedate), getdate()),
FileDate = max(FileDate),
o.mailingaddressstate as DDS,
o.ddsofficecode as DDSCode,
Min(ddsrcpt) AS DDSReceipt,
DDSAge = Datediff(day,min(ddsrcpt), getdate()),
Title = case when min(p.Title) <> max(p.Title) then 'Concurr' else min(p.Title) end
From
(select *, row_number() over (Partition by ddsrcpt order by ddsrcpt) as rownum
from pendingdds) p
join offices.dbo.doorsinfo o
on o.officecode = p.fo
where doc in ('V01', 'S67', 'S41', 'S66')

AND ( Datediff(DAY, filedate, Getdate()) > 250 )
AND ( Datediff(DAY, filedate, Getdate()) > 300 )
AND ( Datediff(DAY, filedate, Getdate()) > 350 )
AND ( Datediff(DAY, filedate, Getdate()) > 400 )

Group By FO, Reg, DOC, CLMS, o.mailingaddressstate, o.ddsofficecode
order by p.fo, age , DDSReceipt desc
End