Grouping rows on basis of date

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)

Grouping rows on basis of date

Author

Topic

timezone
Starting Member

3 Posts

Posted - 2011-06-12 : 14:37:37

My table structure is:
Name:installment_premium

installment_1 installment_2 installment_3 installment_4 policy_no
2011/05/02 2011/06/02 2011/07/10 2011/08/13 2167
2011/04/10 2011/08/18 2011/12/10 2011/04/13 2168
2011/06/02 2011/07/02 2011/08/10 2011/09/13 2169
2011/01/02 2011/02/02 2011/03/10 2011/04/13 2170

Output:all the policies which have installment to be paid on june and august
eg:
June,2011
2167 2011/06/02
2169 2011/06/02

So looking forward for help on grouping on dates.
Thanking you in advance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-12 : 14:49:50

June and August of any year, or June and August of the current year? For any year,

select
   *
from
  installment_premium
where
  (
     month(installment_1) = 6 or month(installment_2) = 6 
     or month(installment_3) = 6 or month(installment_4) = 6
  )
  and

  (
     month(installment_1) = 8 or month(installment_2) = 8 
     or month(installment_3) = 8 or month(installment_4) = 8
  )

timezone
Starting Member

3 Posts

Posted - 2011-06-13 : 06:14:42

Thanks for the code but i want for all months.I want output in foll.format:

policy_no due date

June,2011

2167 2011/06/04
2168 2011/06/08

July,2011
3130 2011/07/12
and so on and i am having 12 installment fields .If i do select * then it will give me all installment values but i want only that value which satisfies the month desired.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-13 : 07:10:54

I see what you mean. Probably simpler to use UNPIVOT as in:


WITH cte AS
(
   SELECT
      *
   FROM
     installment_premium
   UNPIVOT
   ( DueDate FOR Installment IN (installment_1,installment_2,installment_3,installment_4)) U
)
SELECT * FROM CTE c WHERE MONTH(c.DueDate) IN (6,8);

timezone
Starting Member

3 Posts

Posted - 2011-06-13 : 15:28:56

Thanks for your help but i got the solution by using union and merging 12 queries .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources