Sub Queries - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Sub Queries

Author

Topic

sql_monkey
Starting Member

19 Posts

Posted - 2012-03-22 : 11:37:15

Hello,

I'm trying to run a query on an insurance database which returns the number of claims and number of notifications recived last month. Sounds simple but heres where I've got to:


SELECT Count(c.ClaimDate) as 'Claim Date',  Count(c.NotificationDate) as 'Notification Date'
FROM dbo.tblClaim As c
WHERE c.ClaimDate IN (SELECT (c.ClaimDate) FROM dbo.tblClaim As c WHERE c.ClaimDate
between dateadd(mm, datediff(mm, 0, dateadd(MM, -1, getdate())), 0)
and dateadd(ms, -3, dateadd(mm, datediff(mm, 0, dateadd(MM, -1, getdate())) + 1, 0)))
and
c.NotificationDate IN (SELECT c.NotificationDate FROM dbo.tblClaim As c WHERE c.NotificationDate
between dateadd(mm, datediff(mm, 0, dateadd(MM, -1, getdate())), 0)
and dateadd(ms, -3, dateadd(mm, datediff(mm, 0, dateadd(MM, -1, getdate())) + 1, 0)))

This dosen't work because the second sub query is run on the first sub query.

Although both of these fields are on the same table, i do not want to use the UNION clause as I'm going to add further fields that are not on the same table so i will need a solution that works for these as well.

Any ideas how I get the two sub queries to run idenpendently on the two different columns?

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-22 : 11:55:25

try this


SELECT 	Count(case when c.ClaimDate 		between	dateadd(month, datediff(month, 0, getdate()) - 1, 0)
						and	dateadd(month, datediff(month, 0, getdate()), -1)
		   then c.ClaimDate
		   end) 	as 'Claim Date',  
	Count(case when c.NotificationDate	between	dateadd(month, datediff(month, 0, getdate()) - 1, 0)
						and	dateadd(month, datediff(month, 0, getdate()), -1)
		   then c.NotificationDate
		   end) 	as 'Notification Date'
FROM 	dbo.tblClaim As c
WHERE 	c.ClaimDate		between	dateadd(month, datediff(month, 0, getdate()) - 1, 0)
				and	dateadd(month, datediff(month, 0, getdate()), -1)
or	c.NotificationDate	between	dateadd(month, datediff(month, 0, getdate()) - 1, 0)
				and	dateadd(month, datediff(month, 0, getdate()), -1)

KH
[spoiler]Time is always against us[/spoiler]

sql_monkey
Starting Member

19 Posts

Posted - 2012-03-22 : 12:13:07

That works perfectly thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources