MissingRecord - 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

General SQL Server Forums

New to SQL Server Programming

MissingRecord

Author

Topic

velliraj
Yak Posting Veteran

59 Posts

Posted - 2008-04-13 : 07:57:08

Hi

I need to find the missing records from a table based on the from and to date in a table.
The dates are not arranged in proper manner.
please help me to find these records

Rajesh

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-13 : 08:19:14

you really need to provide more detail than this. script out something we can test on our systems, create table, insert sample data, and show us what you want the result to be (in this case, what would constitue a 'missing' record?) along with any query that you've already tried

Em

velliraj
Yak Posting Veteran

59 Posts

Posted - 2008-04-13 : 08:26:31

I have table with coloumns like....
id, fromdate, todate
they used to add records manually
eg: monthly salary, weekly salary and biweekly salary are created for employee for one year or six months.
sometimes they miss out to enter data for some weeks or months in a particular period.
i need to find the missing periods.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-13 : 08:33:02

see this for a clearer guide on what to post...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-13 : 08:47:38

You really need to provide more detail to get help on this one. Please try to provide some sample data and your expected o/p too

Your might have to look at function called F_TABLE_DATE inside this forum to get a start on this.

velliraj
Yak Posting Veteran

59 Posts

Posted - 2008-04-14 : 02:56:44

This is the sample data.

id periodfrom periodto
1 1-1-2008 1-30-2008
2 4-1-2008 4-30-2008

Hence i need to find the missing periods of
2-1-2008 to 2-29-2008
3-1-2008 to 3-31-2008

please help me on this to find

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 03:57:10

Are (if present) PeriodFrom and PeriodTo always complete month?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 04:32:34

[code]DECLARE @Sample TABLE (ID INT, PeriodFrom DATETIME, PeriodTo DATETIME)

SET DATEFORMAT MDY

INSERT @Sample
SELECT 1, '1-1-2008', '1-30-2008' UNION ALL
SELECT 2, '4-1-2008', '4-30-2008'

DECLARE @MinMonth INT,
@MaxMonth INT

SELECT @MinMonth = DATEDIFF(MONTH, '19000101', MIN(PeriodFrom)),
@MaxMonth = DATEDIFF(MONTH, '19000101', MAX(PeriodTo))
FROM @Sample

SELECT DATEADD(MONTH, v.Number + @MinMonth, '19000101') AS PeriodFrom,
DATEADD(MONTH, v.Number + @MinMonth + 1, '18991231') AS PeriodTo
FROM master..spt_values AS v
LEFT JOIN (
SELECT DATEDIFF(MONTH, '19000101', PeriodFrom) - @MinMonth AS theMonth
FROM @Sample
) AS s ON s.theMonth = v.Number
WHERE v.Type = 'p'
AND v.Number <= @MaxMonth - @MinMonth
AND s.theMonth IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 04:35:00

[code]DECLARE @Sample TABLE (ID INT, PeriodFrom DATETIME, PeriodTo DATETIME)

SET DATEFORMAT MDY

INSERT @Sample
SELECT 1, '1-1-2008', '1-30-2008' UNION ALL
SELECT 2, '4-1-2008', '4-30-2008'

DECLARE @MinMonth DATETIME,
@MaxMonth DATETIME

SELECT @MinMonth = MIN(PeriodFrom),
@MaxMonth = MAX(PeriodTo)
FROM @Sample

SELECT DISTINCT td.START_OF_MONTH_DATE,
td.END_OF_MONTH_DATE
FROM F_TABLE_DATE(@MinMonth, @MaxMonth) AS td
LEFT JOIN @Sample AS s ON s.PeriodFrom = td.START_OF_MONTH_DATE
WHERE s.PeriodFrom IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources