search day before Last day of the Previous Month

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)

search day before Last day of the Previous Month

Author

Topic

midan1
Starting Member

39 Posts

Posted - 2007-12-22 : 18:22:06

how can i do this
search between 2 rows
day before Last day of the Previous Month + day Last day of the Previous Month"



SELECT     empid, basedate, unit_date, shift, na
FROM         dbo.empbase
WHERE     (basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND  (shift = 5)

 AND

 (basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1)

tnx

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-22 : 18:43:18

I'm not sure I understand your question - but that won't stop me from answering


SELECT     empid, basedate, unit_date, shift, na
FROM         dbo.empbase
WHERE   (  
	(basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND  (shift = 5)
	)

OR
	(
	 (basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1)
	)

or perhaps this:


SELECT     empid, basedate, unit_date, shift, na
FROM         dbo.empbase
WHERE     (basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND  (shift = 5)

union all

SELECT     empid, basedate, unit_date, shift, na
FROM         dbo.empbase
WHERE     (basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1)

Be One with the Optimizer
TG

midan1
Starting Member

39 Posts

Posted - 2007-12-22 : 19:04:13

tnx men but it must be condition
only if
((basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 5)
and
(basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1))

search between 2 rows
for example
only if
the employee in 29/11/2007 and shfit=1
AND
30/11/2007 and shfit=5
than i get this employee
this what i ask for



SELECT 

 [new_shift] =   
 CASE 
 WHEN     (empid IN
                          (SELECT    empid
                            FROM          empbase
                            WHERE     (basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 5) and
 (basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1)))

THEN  1

WHEN     
(empid IN
                          (SELECT    empid
                            FROM          empbase
                            WHERE     (basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 5) and
 (basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 5)))
THEN 2

ELSE 99
END ,


   SilokE.empid, SilokE.Fname, SilokE.basedate, mhlkot.mhlka
FROM         SilokE INNER JOIN
                      mhlkot ON SilokE.mhlka = mhlkot.mhlka_id

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-22 : 19:21:26

This wouldn't be your complete solution but will this accomplish simply finding the employee that meets both conditions?


select	empid
from	(
	SELECT     empid
	FROM         dbo.empbase
	WHERE     (basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND  (shift = 5)
	group by empid
	
	union all
	
	SELECT     empid
	FROM         dbo.empbase
	WHERE     (basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1)
	group by empid
	) derived
group by empid
having count(1) > 1

If you want more assistance please post DDL and DML as well as a desired result set based on the sample data.
ie: CREATE table statements and INSERT INTO statements (sample data)

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-23 : 01:50:09

SELECT

[new_shift] =
CASE
WHEN empid IN
(SELECT empid
FROM empbase
WHERE (basedate =
DATEADD(d,-2,CONVERT (datetime,'01/ + CASE
WHEN MONTH(GETDATE())<10 THEN '0' +MONTH(GETDATE())
ELSE MONTH(GETDATE())
END + YEAR(GETDATE())))
AND shift = 5) AND
(basedate = DATEADD(d,-1,CONVERT (datetime,'01/ + CASE
WHEN MONTH(GETDATE())<10 THEN '0' +MONTH(GETDATE())
ELSE MONTH(GETDATE())
END + YEAR(GETDATE()))) AND (shift = 1)))

THEN 1

WHEN
empid IN
(SELECT empid
FROM empbase
WHERE (basedate = DATEADD(d,-2,CONVERT (datetime,'01/ + CASE
WHEN MONTH(GETDATE())<10 THEN '0' +MONTH(GETDATE())
ELSE MONTH(GETDATE())
END + YEAR(GETDATE()))) AND shift = 5) and
(basedate = DATEADD(d,-1,CONVERT (datetime,'01/ + CASE
WHEN MONTH(GETDATE())<10 THEN '0' +MONTH(GETDATE())
ELSE MONTH(GETDATE())
END + YEAR(GETDATE()))) AND (shift = 5)))
THEN 2

ELSE 99
END ,

SilokE.empid, SilokE.Fname, SilokE.basedate, mhlkot.mhlka
FROM SilokE INNER JOIN
mhlkot ON SilokE.mhlka = mhlkot.mhlka_id

ilan
Starting Member

21 Posts

Posted - 2007-12-23 : 02:50:54

quote:
Originally posted by visakh16

SELECT

[new_shift] =
CASE
WHEN empid IN
(SELECT empid
FROM empbase
WHERE (basedate =
DATEADD(d,-2,CONVERT (datetime,'01/ + CASE
WHEN MONTH(GETDATE())<10 THEN '0' +MONTH(GETDATE())
ELSE MONTH(GETDATE())
END + YEAR(GETDATE())))
AND shift = 5) AND
(basedate = DATEADD(d,-1,CONVERT (datetime,'01/ + CASE
WHEN MONTH(GETDATE())<10 THEN '0' +MONTH(GETDATE())
ELSE MONTH(GETDATE())
END + YEAR(GETDATE()))) AND (shift = 1)))

THEN 1

WHEN
empid IN
(SELECT empid
FROM empbase
WHERE (basedate = DATEADD(d,-2,CONVERT (datetime,'01/ + CASE
WHEN MONTH(GETDATE())<10 THEN '0' +MONTH(GETDATE())
ELSE MONTH(GETDATE())
END + YEAR(GETDATE()))) AND shift = 5) and
(basedate = DATEADD(d,-1,CONVERT (datetime,'01/ + CASE
WHEN MONTH(GETDATE())<10 THEN '0' +MONTH(GETDATE())
ELSE MONTH(GETDATE())
END + YEAR(GETDATE()))) AND (shift = 5)))
THEN 2

ELSE 99
END ,

SilokE.empid, SilokE.Fname, SilokE.basedate, mhlkot.mhlka
FROM SilokE INNER JOIN
mhlkot ON SilokE.mhlka = mhlkot.mhlka_id

tnx men
but i get this error

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '0'.
Msg 102, Level 15, State 1, Line 26
Incorrect syntax near '0'.

ilan
Starting Member

21 Posts

Posted - 2007-12-23 : 03:00:40

quote:
Originally posted by TG

This wouldn't be your complete solution but will this accomplish simply finding the employee that meets both conditions?

select empid
from (
SELECT empid
FROM dbo.empbase
WHERE (basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 5)
group by empid

union all

SELECT empid
FROM dbo.empbase
WHERE (basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1)
group by empid
) derived
group by empid
having count(1) > 1

If you want more assistance please post DDL and DML as well as a desired result set based on the sample data.
ie: CREATE table statements and INSERT INTO statements (sample data)

Be One with the Optimizer
TG

YES YES it working TNX
but how to put it here
instead of the code in the

CASE
WHEN



SELECT 

 [new_shift] =   
 CASE 
 WHEN     (empid IN
                          (SELECT    empid
                            FROM          empbase
                            WHERE     (basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 5) and
 (basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1)))

THEN  1

WHEN     
(empid IN
                          (SELECT    empid
                            FROM          empbase
                            WHERE     (basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 5) and
 (basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 5)))
THEN 2

ELSE 99
END ,


   SilokE.empid, SilokE.Fname, SilokE.basedate, mhlkot.mhlka
FROM         SilokE INNER JOIN
                      mhlkot ON SilokE.mhlka = mhlkot.mhlka_id

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-23 : 09:36:22

quote:
Originally posted by TG

If you want more assistance please post DDL and DML as well as a desired result set based on the sample data.

I think I have a more efficient solution for you but I can't test it without "setting it up" locally. I could do it but I have no idea what your table structure is. Plus, you're the one asking for help so you should at least go to effort of making it easy for us to help, right?

Be One with the Optimizer
TG

ilan
Starting Member

21 Posts

Posted - 2007-12-23 : 09:56:44

tnx
ok
the table code
TABLE [SilokE]=all the employee

TABLE [empbase]= the reference (last month table)

TABLE [mhlkot]= department



/****** Object:  Table [dbo].[empbase]    Script Date: 12/23/2007 16:44:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[empbase](
	[empid] [int] NULL,
	[basedate] [datetime] NULL,
	[unit_date] [datetime] NOT NULL CONSTRAINT [DF_empbase_unit_date]  DEFAULT (((12)/(1))/(2007)),
	[Shift] [int] NULL,
	[na] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]



/****** Object:  Table [dbo].[SilokE]    Script Date: 12/23/2007 16:45:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SilokE](
	[empid] [int] NOT NULL,
	[Fname] [nvarchar](30) NOT NULL,
	[mhlka] [int] NOT NULL,
	[sd] [int] NOT NULL,
	[sd3] [int] NOT NULL,
	[basedate] [datetime] NOT NULL CONSTRAINT [DF_SilokE_sw]  DEFAULT ('01/01/2007 00:00:00'),
 CONSTRAINT [PK_SilokE] PRIMARY KEY CLUSTERED 
(
	[empid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[mhlkot](
	[mhlka_id] [int] NOT NULL,
	[mhlka] [nvarchar](50) NULL,
	[sidor] [int] NOT NULL
) ON [PRIMARY]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-23 : 10:08:03

Well you didn't inlcude any sample data so I couldn't test but...

This ASSUMES that only one row can satisfy any one [bw] condition:


select	[new_shift] = case 
		when bw & 3 = 3 then 1
		when bw & 12 = 12 then 2
		else 99
		end
	,d.empid
	,d.Fname
	,d.basedate
	,d.mhlka
from	(
	select	sum(case 
			when (SilokE.basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 5) then 1
			when (SilokE.basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1) then 2
			when (SilokE.basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 5) then 4
			when (SilokE.basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 5) then 8
			else 0
			end) as bw
		,SilokE.empid
		,SilokE.Fname
		,SilokE.basedate
		,mhlkot.mhlka
	FROM    SilokE 
	INNER JOIN mhlkot ON SilokE.mhlka = mhlkot.mhlka_id
	INNER JOIN empbase on empbase.empid = SilokE.empid and empbase.basedate = SilokE.basedate
	group by SilokE.empid
		,SilokE.Fname
		,SilokE.basedate
		,mhlkot.mhlka
	) d

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources