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
 Help with query

Author  Topic 

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-03-08 : 10:10:48

When I run this query the below sample data comes out as concurr.


Select 
		p.Reg, 
		p.FO,
	 p.CLMS,
	
	
Age = Datediff(day,max(p.filedate), getdate()),
	FileDate = max(FileDate),
Min(ddsrcpt) AS DDSReceipt,
	DDSAge = Datediff(day,min(p.ddsrcpt), getdate()),
	Title = case when min(p.Title) <> max(p.Title) then 'Concurr' else min(p.Title) end

From testdata p
where p.doc ='s09'
AND ( Datediff(DAY, filedate, Getdate()) > 300 ) 
Group By  p.fo, p.Reg, p.DOC, p.CLMS
order by clms


This one comes out concurr.


clms     file date    DDS   DDSCode  DDSReceipt      Title
215689  03/24/2011     DE      S09     03/24/2011      T2
215689  03/24/2011     DE      S09     03/25/2011      T16




This one doesn't come out as concurr. How can I change the code to make it come out concurr since they are duplicates but the titles are different. Only thing on this one is the DDSReceipt dates are the same.



clms     file date    DDS   DDSCode  DDSReceipt      Title
254698  05/05/2011     DE      S09     06/10/2011     T2
254698  05/06/2011     DE      S09     06/10/2011     T16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-08 : 10:13:32
order by clms,[file date]





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-03-08 : 10:30:29
Thanks but that still didn't work. They both are still coming up.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-08 : 10:32:39
can you explain what should be your output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-03-08 : 10:45:17
I have duplicate clms in the database but the title could be T2 or T16. If there are duplicate clms but their titles are different then the query checks it at this part.

Title = case when min(p.Title) <> max(p.Title) then 'Concurr' else min(p.Title) end

so for this record it comes out concurr based of the above case statement:


clms     file date    DDS   DDSCode  DDSReceipt      Title
211568  04/15/2011     DE      S09     03/02/2012     T2
254698  04/15/2011     DE      S09     08/22/2011     T16




Now this record doesn't come out concurr but it should:

clms     file date    DDS   DDSCode  DDSReceipt      Title
215689  03/24/2011     DE      S09     03/24/2011      T2
215689  03/24/2011     DE      S09     03/25/2011      T16


So I need another case statement if clms, filedate and ddsreceipt date are the same then make them concurr (only one record to come out and call it concurr)

This one doesn't come out as concurr either:



clms     file date    DDS   DDSCode  DDSReceipt      Title
254698  05/05/2011     DE      S09     06/10/2011     T2
254698  05/06/2011     DE      S09     06/10/2011     T16



So I would need another case statement to check if clms, and DDSReceipt date is the same and filedate isn't then make that record concurr. I hope this make sense.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-08 : 10:53:47
how have you applied GROUP by? can you show your group by part also?

As per your current case statement i would have expected it to work fine unless you've included dates also in group by
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-03-08 : 10:57:41
Here's group by in bold:




Select 
		p.Reg, 
		p.FO,
	 p.CLMS,
	
	
Age = Datediff(day,max(p.filedate), getdate()),
	FileDate = max(FileDate),
Min(ddsrcpt) AS DDSReceipt,
	DDSAge = Datediff(day,min(p.ddsrcpt), getdate()),
	Title = case when min(p.Title) <> max(p.Title) then 'Concurr' else min(p.Title) end

From testdata p
where p.doc ='s09'--@doc
AND ( Datediff(DAY, filedate, Getdate()) > 300 ) 
Group By  p.fo, p.Reg, p.DOC, p.CLMS
order by clms, filedate
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-03-08 : 11:00:15
When I added p.filedate and p.ddsrcpt none of them came out as concurr
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-08 : 11:01:14
hmm..i see lots of other fields here (p.Reg, p.FO etc). unless you show there values also we wont be able to make out why its behaving so

why not post all relevant info in single post itself so that we can avoid some of these iterations

see how to post a question clearly with required data

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-08 : 11:02:35
quote:
Originally posted by JJ297

When I added p.filedate and p.ddsrcpt none of them came out as concurr

thats obvious as value of filedate and ddsrcpt were different for the ones which didnt work. it will get split up to two groups and the condition will not hold good any more in case expression

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-03-08 : 11:08:55
Sorry I got rid of the joins. Here's the entire query:

Select p.DOC,
		p.Reg, 
n.RegionAcronym,
		p.FO,
	  p.CLMS,
	
	
Age = Datediff(day,max(p.filedate), getdate()),
	FileDate = max(FileDate),
o.mailingaddressstate as DDS,
o.ddsofficecode as DDSCode,
Min(ddsrcpt) AS DDSReceipt,
	DDSAge = Datediff(day,min(p.ddsrcpt), getdate()),
	Title = case when min(p.Title) <> max(p.Title) then 'Concurr' else min(p.Title) end
From pendingdds p
 join Offices.dbo.OfficeCodes d 
on d.officecode = p.doc
join natdocfile n
on n.doc = p.fo
join offices.dbo.doorsinfo o
on o.officecode = p.fo
where p.doc ='s09'
  AND ( Datediff(DAY, filedate, Getdate()) > 300 ) 
Group By p.fo, p.Reg, n.regionacronym,p.DOC, p.CLMS, o.ddsofficecode,
o.mailingaddressstate
order by clms,  age desc
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-08 : 11:44:08
so...how do you expect us to guess the values for other fields?
See link posted and give data in required format if you really want someone to help you out!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-03-08 : 12:01:59
Okay I have to make all the tables that join together and put data into them. Stay tuned I will do that. That would help huh.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-08 : 12:33:53
ok..that will make it clear for us

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-03-08 : 14:03:52
Here's the join tables information:


CREATE TABLE [dbo].[DoorsInfo](
	
	[DDSOfficeCode] [varchar](255) NULL,
	
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


insert into DoorsInfo
select 'S09' union all
select 'S08'


CREATE TABLE [dbo].[natdocfileb](
	
	[RegionAcronym] [char](3) NULL,
	
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

insert into natdocfileb
select 'PHI' union all
select 'BOS'


CREATE TABLE [dbo].[OfficeCodes](
	[OfficeCode] [char](3) NOT NULL,
	
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

insert into officecodes
select '029' union all
select '268' union all
select 'B68' union all
select 'C80' union all
select '001' union all
select '002' union all
select '003' union all
select '009' 


CREATE TABLE [dbo].[TestData](
	[fo] [varchar](3) NOT NULL,
	[reg] [varchar](3) NULL,
	[doc] [varchar](4) NOT NULL,
	[clms] [char](6) NOT NULL,
	[cos] [char](6) NOT NULL,
	[FileDate] [datetime] NULL,
	[DDSRcpt] [datetime] NULL,
	[Title] [varchar](3) NOT NULL,
 CONSTRAINT [PK_testdata] 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 testdata
select '029', 'c', 's09', '129698', '129698', '03/24/2011', '03/24/2011', 'T2' union all
select 'C80', 'c', 's09', '129698', '129698', '03/24/2011', '03/25/2011', 'T16' union all
select '268', 'c', 's09', '005747', '005747', '06/20/2011', '06/20/2011', 'T16' union all
select '268', 'c', 's09', '005747', '005747', '06/20/2011', '06/20/2011', 'T2' union all
select 'C80', 'c', 's09', '698745', '698745', '05/11/2011', '05/12/2011', 'T16' union all
select 'B68', 'c', 's09', '978456', '978456', '06/03/2011', '01/18/2012', 'T16' union all
select '029', 'c', 's09', '125987', '125987', '05/05/2011', '06/10/2011', 'T2' union all
select '268', 'c', 's09', '125987', '125987', '05/06/2011', '06/10/2011', 'T16'






Here's the query:


Select p.DOC,
		p.Reg, 
n.RegionAcronym,
		p.FO,
	  p.CLMS,
	
--getting age of case when it was filed	
Age = Datediff(day,max(p.filedate), getdate()),
	FileDate = max(FileDate),
o.mailingaddressstate as DDS,
o.ddsofficecode as DDSCode,
Min(ddsrcpt) AS DDSReceipt,
--getting ddsage of case when it was receipted into the dds
	DDSAge = Datediff(day,min(p.ddsrcpt), getdate()),
--if 
	Title = case when min(p.Title) <> max(p.Title) then 'Concurr' else min(p.Title) end 


From testdata p
 join Offices.dbo.OfficeCodes d 
on d.officecode = p.doc
join natdocfile n
on n.doc = p.fo
join offices.dbo.doorsinfo o
on o.officecode = p.fo
where p.doc ='s09'--@doc
  AND ( Datediff(DAY, filedate, Getdate()) > 300 ) 
Group By p.fo, p.Reg, n.regionacronym,p.DOC, p.CLMS, o.ddsofficecode,
o.mailingaddressstate
order by clms,  age desc
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site