doubt in date function - 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

doubt in date function

Author

Topic

kodumudisadha
Starting Member

33 Posts

Posted - 2008-11-27 : 07:23:33

hi i have 2 columns updatelocation, updatedate
updatelocation, updatedate

--------------------------------------------------------------------------------
aaa 27-nov-08
aaa 26-nov-08
aaa 25-nov-08
aaa 23-nov-08
........... ..................
........... ..................
i want to chech in last 30 days which date is missing .like above 24 date is missing.
but i cant give where updatedate is null.
how to check last one month.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-27 : 08:00:33

[code]DECLARE @Sample TABLE
(
updateLocation VARCHAR(20),
updateDate DATETIME
)

INSERT @Sample
(
updateLocation,
updateDate
)
SELECT 'aaa', '27-nov-08' UNION ALL
SELECT 'aaa', '26-nov-08' UNION ALL
SELECT 'aaa', '25-nov-08' UNION ALL
SELECT 'aaa', '23-nov-08'

DECLARE @fromDate DATETIME,
@toDate DATETIME

SELECT @fromDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),
@toDate = DATEADD(MONTH, 1, @fromDate)

SELECT DATEADD(DAY, v.number, @fromDate) AS missingDate
FROM master..spt_values AS v
LEFT JOIN @Sample AS s ON s.updateDate = DATEADD(DAY, v.number, @fromDate)
WHERE v.Type = 'P'
AND v.number < DATEDIFF(DAY, @fromDate, @toDate)
AND s.updateDate IS NULL[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 08:01:46

[code]DECLARE @startdate datetime
SET @startdate=dateadd(mm,datediff(mm,0,getdate()),0)
SELECT
FROM
(SELECT DATEADD(dd,number,@StartDate) AS DateVal
FROM master..spt_values
WHERE type='p'
AND DATEADD(dd,number,@StartDate)<
DATEADD(mm,1,@Startdate)
)d
LEFT JOIN YourTable t
ON t.updatedate=d.DateVal
WHERE t.updatedate IS NULL[/code]

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-27 : 08:23:14

quote:

Originally posted by visakh16

DECLARE @startdate datetime
SET @startdate=dateadd(mm,datediff(mm,0,getdate()),0)
SELECT DateVal
FROM 
(SELECT DATEADD(dd,number,@StartDate) AS DateVal
            FROM master..spt_values
            WHERE type='p'
           AND DATEADD(dd,number,@StartDate)<
 DATEADD(mm,1,@Startdate)
)d
LEFT JOIN YourTable t
ON t.updatedate=d.DateVal
WHERE t.updatedate IS NULL

====================================================
you realize you've made a mistake, take immediate steps to correct it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 08:34:15

Another method (SQL 2005)

DECLARE @startdate datetime
SET @startdate=dateadd(mm,datediff(mm,0,getdate()),0)

;With CTE(Date) AS
(
SELECT @StartDate
UNION ALL
SELECT DATEADD(dd,1,Date)
FROM CTE
WHERE DATEADD(dd,1,Date)<DATEADD(mm,1,@Startdate)
)
SELECT c.Date
FROM CTE c
LEFT JOIN YourTable t
ON t.updatedate=d.DateVal
WHERE t.updatedate IS NULL

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-27 : 08:42:05

quote:

Originally posted by visakh16

Another method (SQL 2005)

DECLARE @startdate datetime
SET @startdate=dateadd(mm,datediff(mm,0,getdate()),0)

;With CTE(Date) AS
(
SELECT @StartDate
UNION ALL
SELECT DATEADD(dd,1,Date)
FROM CTE
WHERE DATEADD(dd,1,Date)<DATEADD(mm,1,@Startdate)
)
SELECT c.Date
FROM CTE c
LEFT JOIN YourTable t
ON t.updatedate=c.Date
WHERE t.updatedate IS NULL

If you dont mine.

====================================================
you realize you've made a mistake, take immediate steps to correct it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 08:59:55

No problem

Nice catch

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 09:01:43

I think its time for me to have a coffee

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-27 : 09:12:21

Oh......You have a "TIME" to take a Coffee?

====================================================
you realize you've made a mistake, take immediate steps to correct it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 09:14:19

quote:
Originally posted by karthickbabu

Oh......You have a "TIME" to take a Coffee?

====================================================
you realize you've made a mistake, take immediate steps to correct it.

yup...when things like this happens

lapse of concentration

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-27 : 09:17:12

quote:
Originally posted by karthickbabu

Oh......You have a "TIME" to take a Coffee?

You don't know he has i-phone to answer the post while he is on the way to coffee. Just kidding Visakh!!

====================================================
you realize you've made a mistake, take immediate steps to correct it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 11:37:20

quote:
Originally posted by sodeep

quote:
Originally posted by karthickbabu

Oh......You have a "TIME" to take a Coffee?

You don't know he has i-phone to answer the post while he is on the way to coffee. Just kidding Visakh!!

====================================================
you realize you've made a mistake, take immediate steps to correct it.

kodumudisadha
Starting Member

33 Posts

Posted - 2008-11-27 : 23:09:01

hi thanks for all,its working.is it possible to write in single sql.
like using LEAD arithmatic function in oracle.
because i want to use directly in jasper report.
procedural block is not supporting.
please........guide me

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-28 : 03:48:18

Just copy and save that Query then save as FileName.SQL.

You mean this one.

====================================================
you realize you've made a mistake, take immediate steps to correct it.

kodumudisadha
Starting Member

33 Posts

Posted - 2008-11-28 : 05:13:08

hi, how to sho missing date with storeid
like below
aaa 27-nov-08
aaa 26-nov-08
aaa 25-nov-08
aaa 23-nov-08
........... ..................
........... ..................
i want like below query
SELECT DateVal,t.storeid
FROM
(SELECT DATEADD(dd,number,dateadd(mm,datediff(mm,0,getdate()),0)) AS DateVal,
( select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all ) storeid
FROM master..spt_values
WHERE type='p'
AND DATEADD(dd,number,dateadd(mm,datediff(mm,0,getdate()),0))<
DATEADD(mm,1,dateadd(mm,datediff(mm,0,getdate()),0))
)d
ON t.cm_date=d.Dateval,t.store_id=d.store_id
WHERE t.cm_date IS NULL

please help me......

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-28 : 05:31:16

[code]DECLARE @Sample TABLE
(
updateLocation VARCHAR(20),
updateDate DATETIME
)

INSERT @Sample
(
updateLocation,
updateDate
)
SELECT 'aaa', '27-nov-08' UNION ALL
SELECT 'aaa', '26-nov-08' UNION ALL
SELECT 'bbb', '25-nov-08' UNION ALL
SELECT 'bbb', '23-nov-08'

DECLARE @fromDate DATETIME,
@toDate DATETIME

SELECT @fromDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),
@toDate = DATEADD(MONTH, 1, @fromDate)

SELECT ul.updateLocation,
v.missingDate
FROM (
SELECT updateLocation
FROM @Sample
GROUP BY updateLocation
) AS ul
CROSS JOIN (
SELECT DATEADD(DAY, number, @fromDate) AS missingDate
FROM master..spt_values
WHERE Type = 'P'
AND number < DATEDIFF(DAY, @fromDate, @toDate)
) AS v
LEFT JOIN @Sample AS s ON s.updateDate = v.missingDate
AND s.updateLocation = ul.updateLocation
WHERE s.updateDate IS NULL[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-28 : 05:33:22

If you want to, you can add these two WHERE clauses to last row.

		AND v.missingDate >= (SELECT MIN(updateDate) FROM @Sample)
		AND v.missingDate <= (SELECT MAX(updateDate) FROM @Sample)]

E 12°55'05.63"
N 56°04'39.26"

kodumudisadha
Starting Member

33 Posts

Posted - 2008-11-28 : 05:58:47

Thank u very much its working now

kodumudisadha
Starting Member

33 Posts

Posted - 2008-11-29 : 02:22:00

hi friends,
i have one more doubt in below query i am taking 1 year missing date
SELECT ul.StoreId,
v.missingDate
FROM (
SELECT StoreId
FROM POSSALESTRNHEADER
GROUP BY StoreId
) AS ul
CROSS JOIN (
SELECT DATEADD(DAY, number, DATEADD(YEAR, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS missingDate
FROM master..spt_values
WHERE Type = 'P'
AND number < DATEDIFF(DAY,DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), DATEADD(YEAR, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)))
) AS v
LEFT JOIN POSSALESTRNHEADER AS s ON s.CM_Date = v.missingDate
AND s.StoreId = ul.StoreId
WHERE s.CM_Date IS NULL and v.missingDate < GETDATE()-1
order by ul.StoreId

here i want to change the from date based on storeid.because store 'aaa' date is starting from 1 year back ,store 'bbb' date is starting from 6 months back only.so for 'bbb' 1st six month date also showing missing date.
like the below logic
SELECT DATEADD(DAY, number, DATEADD(YEAR, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS missingDate
FROM master..spt_values
WHERE Type = 'P'
AND
case storeid when 'aaa' then
number < last 6 months only
when 'bbb' then
number < last 1 year

please help this logic .....

kodumudisadha
Starting Member

33 Posts

Posted - 2008-11-29 : 03:13:07

hi
itried below query
SELECT ul.StoreId,
v.missingDate
FROM (
SELECT StoreId
FROM POSSALESTRNHEADER
GROUP BY StoreId
) AS ul
CROSS JOIN (
SELECT DATEADD(DAY, number, DATEADD(YEAR, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS missingDate
FROM master..spt_values
WHERE Type = 'P'
AND
(case s.storeid when 25 then
number <= DATEDIFF(DAY,DATEADD(month, DATEDIFF(YEAR, 0, GETDATE()), 0), DATEADD(month, 3, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)))
else
number <= DATEDIFF(DAY,DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), DATEADD(YEAR, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))) )
) AS v
LEFT JOIN POSSALESTRNHEADER AS s ON s.CM_Date = v.missingDate
AND s.StoreId = ul.StoreId
WHERE s.CM_Date IS NULL and v.missingDate < GETDATE()-1
order by ul.StoreId

but its showing error

Line 14: Incorrect syntax near '<'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-29 : 08:21:31

quote:

Originally posted by kodumudisadha

hi
itried below query


SELECT		ul.StoreId,
                v.missingDate
FROM		(
			SELECT		StoreId
			FROM		POSSALESTRNHEADER
			GROUP BY	StoreId
		) AS ul
CROSS JOIN	(
			SELECT	DATEADD(DAY, number, DATEADD(YEAR, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS missingDate
			FROM	master..spt_values
			WHERE	Type = 'P'
				AND 
number <=(case s.storeid when 25 then 
 DATEDIFF(DAY,DATEADD(month, DATEDIFF(YEAR, 0, GETDATE()), 0), DATEADD(month, 3, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)))
else 
 DATEDIFF(DAY,DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), DATEADD(YEAR, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)))	)
	) AS v 
LEFT JOIN	POSSALESTRNHEADER AS s ON s.CM_Date = v.missingDate
			AND s.StoreId = ul.StoreId
WHERE		s.CM_Date IS NULL and v.missingDate < GETDATE()-1
order by ul.StoreId

but its showing error

Line 14: Incorrect syntax near '<'.

try like above

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources