SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 query help		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pnpsql
Posting Yak Master

India
241 Posts
Posted - 02/28/2013 :  23:48:03  Show Profile  Reply with Quote

i have two TABLE AS follows

TBL_ID

id NAME
1 a
2 b
3 c
4 d
5 E

TABLE_TRN

ID AMOUNT DATE
1 10 01-aug-2012
1 11 05-sep-2012
1 12 08-nov-2012
2 22 05-aug-2012
2 11 05-sep-2012
2 12 02-oct-2012
2 22 05-nov-2012
3 11 05-aug-2012
3 12 02-oct-2012

i want TO know the ids that have missing transaction for any month IN TABLE_TRN TABLE
LIKE id 1 , that have one missing TRANSACTION FOR oct MONTH END , becasue TRANSACTION dates have
aug,sep AND novbut NO TRANSACTION have nov Ifor id 1 .

kindly suggest






challenge everything

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 02/28/2013 :  23:56:34  Show Profile  Reply with Quote 

SELECT p.*
FROM
(SELECT i.*,MonthNo
 FROM TBL_ID i
 CROSS JOIN (SELECT DISTINCT MONTH([DATE]) AS MonthNo FROM TABLE_TRN)m
)p
LEFT JOIN TABLE_TRN t
ON t.id = p.id 
AND MONTH(t.[DATE]) = p.MonthNo
WHERE t.id IS NULL


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

pnpsql
Posting Yak Master

India
241 Posts
Posted - 03/01/2013 :  00:56:10  Show Profile  Reply with Quote

i need sequence breaker id , may be WHERE START MONTH is aug and each month after aug is inseq
THEN that id is ok


challenge everything
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 03/01/2013 :  01:11:41  Show Profile  Reply with Quote
what do you mean by sequence breaker id?

whats the issue with suggestion i posted?

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

pnpsql
Posting Yak Master

India
241 Posts
Posted - 03/01/2013 :  01:30:29  Show Profile  Reply with Quote
TBL_ID

ID NAME
1 A
2 B
3 C
4 D
5 E

TABLE_TRN

ID AMOUNT DATE
1 10 01-AUG-2012
1 11 05-SEP-2012
1 12 08-NOV-2012
2 22 05-AUG-2012
2 11 05-SEP-2012
2 12 02-OCT-2012
2 22 05-NOV-2012
3 11 05-AUG-2012
3 12 02-OCT-2012
4 22 05-SEP-2012
4 11 05-OCT-2012
4 12 02-NOV-2012
5 12 02-DEC-2012

IN ur query id 4 will come but there is NO issue IN that. because there is continous months AFTER
sep with NO breaks

challenge everything
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 03/01/2013 :  02:14:43  Show Profile  Reply with Quote
this?

;with CTE
(
 SELECT i.*,StartMonthNo AS MonthNo,EndMonthNo
 FROM TBL_ID i
 INNER JOIN (SELECT ID ,MIN(MONTH([DATE])) AS StartMonthNo,MAX(MONTH([DATE])) AS EndMonthNo 
             FROM TABLE_TRN
             GROUP BY ID)m
 ON m.ID = i.ID

 UNION ALL

 SELECT i.*,MonthNo+ 1,EndMonthNo
 FROM CTE
 WHERE MonthNo + 1 <= EndMonthNo
)

SELECT c.*
FROM CTE c
LEFT JOIN TABLE_TRN t
ON t.id = c.id 
AND MONTH(t.[DATE]) = c.MonthNo
WHERE t.id IS NULL


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000