Problem with Query - 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

Problem with Query

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-01 : 07:36:27

These two queries are the same but gives me different counts. How can I get this query



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

To match the counts in this query:

if @doc ='F03'
begin

select doc, [250]=SUM([250]), [300]=SUM([300]), [350]=SUM([350]), [400]=SUM([400])
from
(
SELECT
doc,
[250] = case when [Days] > 250 then cnt else 0 end,
[300] = case when [days] > 300 then cnt else 0 end,
[350] = case when [days] > 350 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
end

What am I doing wrong in the counts department?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-01 : 08:38:25

Here's some table information that you can try:

This query:


SELECT 
    doc, 
    COUNT(*) AS cnt
FROM
    (
        Select  
            doc
     FROM testpend p

         Group By 
            doc, clms    ) AS T
GROUP BY 
    doc

Produces this:


doc     cnt
S0L	3
S11	2
S23	2

This query:


select doc, [250]=SUM([250]), [300]=SUM([300]), [350]=SUM([350]), [400]=SUM([400])
from 
(    
    SELECT    
        doc,  
        [250] = case when [Days] > 250 then cnt else 0 end,
        [300] = case when [days] > 300 then cnt else 0 end,
        [350] = case when [days] > 350 then cnt else 0 end,
        [400] = case when [days] > 400 then cnt else 0 end
    from  (
        SELECT    
            doc, count(*) as cnt,
          [days]=Datediff(Day, filedate, Getdate())
        FROM testpend p
        group by doc, Datediff(Day, filedate, Getdate())
    ) as a
)as b
group by doc

Produces this output:


doc    250     300     350     400
S0L	4	0	0	0
S11	2	2	2	2
S23	3	0	0	0

Since there are duplicate clms in the table but the title's are different then they should be counted as one not two.

Ex, this should come out as one count but it's coming out as two.

select '016', 'C', 'S0L', '589642', '589642', '05/17/2011', '08/18/2011', 'T16'  union all
select '016', 'C', 'S0L', '589642', '589642', '05/17/2011', '08/18/2011', 'T2'  union all


CREATE TABLE [dbo].[TestPend](
	[fo] [varchar](3) NOT NULL,
	[reg] [varchar](3) NULL,
	[doc] [varchar](4) NOT NULL,
	[clms] [char](9) NOT NULL,
	[cos] [char](9) NOT NULL,
	[FileDate] [datetime] NULL,
	[DDSRcpt] [datetime] NULL,
	[Title] [varchar](3) NOT NULL,
 CONSTRAINT [PK_TestPend] PRIMARY KEY CLUSTERED 
(
	[fo] ASC,
	[clms] ASC,
	[cos] ASC,
	[Title] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


insert into TestPend
select '016', 'C', 'S0L', '589642', '589642', '05/17/2011', '08/18/2011', 'T16'  union all
select '016', 'C', 'S0L', '589642', '589642', '05/17/2011', '08/18/2011', 'T2'  union all
select '019', 'C', 'S0L', '549875', '549875', '06/17/2011', '12/22/2011', 'T2' union all
select '019', 'C', 'S23', '587456', '587456', '06/03/2011', '01/13/2012', 'T16' union all
select '019', 'C', 'S0L', '236542', '236542', '05/20/2011', '07/13/2011', 'T2' union all
select '196', 'C', 'S11', '145697', '145697', '11/24/2010', '09/16/2011', 'T16' union all
select '196', 'C', 'S11', '147756', '147756', '11/24/2010', '09/16/2011', 'T2' union all
select '196', 'C', 'S23',  '456987', '456987', '06/08/2011', '06/08/2011', 'T16' union all
select '196', 'C', 'S23', '456987', '456987', '06/08/2011', '06/08/2011', 'T2'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-01 : 08:48:26

So what is the expected output for the sample data?

-- SwePeso
SELECT		doc, 
		SUM(Items) AS cnt
FROM		(
			SELECT		doc,
					COUNT(*) AS Items
			FROM		testpend
			GROUP BY	doc,
					clms
		) AS t
GROUP BY	doc
ORDER BY	doc

N 56°04'39.26"
E 12°55'05.63"

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-01 : 09:04:30

I want to get this:


doc     cnt
S0L	3
S11	2
S23	2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources