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
 query help

Author  Topic 

pnpsql
Posting Yak Master

246 Posts

Posted - 2013-02-28 : 23:48:03

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

52326 Posts

Posted - 2013-02-28 : 23:56:34
[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

pnpsql
Posting Yak Master

246 Posts

Posted - 2013-03-01 : 00:56:10

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

52326 Posts

Posted - 2013-03-01 : 01:11:41
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

246 Posts

Posted - 2013-03-01 : 01:30:29
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

52326 Posts

Posted - 2013-03-01 : 02:14:43
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
   

- Advertisement -