SP: Change value in subquery - 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

SQL Server 2008 Forums

Transact-SQL (2008)

SP: Change value in subquery

Author

Topic

WhoCan
Starting Member

1 Post

Posted - 2015-03-24 : 02:48:07

Hi!
I have a stored procedure like below. What I want is to, in the subquerys, change so that the date A.MARK_TIME is equal to A.ACCOUNT_DAY.


ALTER PROCEDURE [dbo].[startstoptime] 
	@employno varchar(5),
	@startdate datetime,
	@enddate datetime,
	@enddate2 datetime
AS
BEGIN
	SET NOCOUNT ON;
	SET @enddate = DATEADD (d, 1, CAST(@startdate AS datetime));
	SET @enddate2 = DATEADD (d, 7, CAST(@startdate AS datetime));
SELECT DISTINCT B.EMPLOYNO, A.ACCOUNT_DAY,
                          (SELECT DISTINCT A.MARK_TIME
                            FROM          SYSADM.MARK AS A INNER JOIN
                                                   SYSADM.PLACING AS C ON A.PLAC_ID = C.PLAC_ID INNER JOIN
                                                   SYSADM.STAFF_RESOURCE AS B ON B.RES_ID = C.RES_ID INNER JOIN
                                                   SYSADM.ABSENCE AS D INNER JOIN
                                                   SYSADM.ABSENCE_CAUSE AS E ON D.CAUSE_ID = E.CAUSE_ID ON C.PLAC_ID = D.PLAC_ID AND B.RES_ID = D.RES_ID
                            WHERE      (A.MARKTYPE = '1') AND (B.EMPLOYNO = @employno) AND (A.ACCOUNT_DAY >= CONVERT(DATETIME, @startdate, 102)) AND 
                                                   (A.ACCOUNT_DAY < CONVERT(DATETIME, @endDate, 102)) AND (E.EXP_DESC IS NULL)) AS start,
                          (SELECT DISTINCT A.MARK_TIME
                            FROM          SYSADM.MARK AS A INNER JOIN
                                                   SYSADM.PLACING AS C ON A.PLAC_ID = C.PLAC_ID INNER JOIN
                                                   SYSADM.STAFF_RESOURCE AS B ON B.RES_ID = C.RES_ID INNER JOIN
                                                   SYSADM.ABSENCE AS D INNER JOIN
                                                   SYSADM.ABSENCE_CAUSE AS E ON D.CAUSE_ID = E.CAUSE_ID ON C.PLAC_ID = D.PLAC_ID AND B.RES_ID = D.RES_ID
                            WHERE      (A.MARKTYPE = '2') AND (B.EMPLOYNO = @employno) AND (A.ACCOUNT_DAY >= CONVERT(DATETIME, @startdate, 102)) AND 
                                                   (A.ACCOUNT_DAY < CONVERT(DATETIME, @endDate, 102)) AND (E.EXP_DESC IS NULL)) AS stop
FROM         SYSADM.MARK AS A INNER JOIN
                      SYSADM.PLACING AS C ON A.PLAC_ID = C.PLAC_ID INNER JOIN
                      SYSADM.STAFF_RESOURCE AS B ON B.RES_ID = C.RES_ID INNER JOIN
                      SYSADM.ABSENCE AS D INNER JOIN
                      SYSADM.ABSENCE_CAUSE AS E ON D.CAUSE_ID = E.CAUSE_ID ON C.PLAC_ID = D.PLAC_ID AND B.RES_ID = D.RES_ID
WHERE     (E.EXP_DESC IS NULL)
GROUP BY B.EMPLOYNO, A.ACCOUNT_DAY, A.MARK_TIME, E.EXP_DESC, A.MARKTYPE
HAVING      (B.EMPLOYNO = @employno) AND (A.ACCOUNT_DAY >= CONVERT(DATETIME, @startdate, 102)) AND (A.ACCOUNT_DAY < CONVERT(DATETIME, 
                      @endDate2, 102))
END

The result now is:

EMPLOYNO	ACCOUNT_DAY	start	stopp
03064	2015-02-05 00:00:00.000	2015-02-05 05:21:00.000	2015-02-05 14:15:00.000
03064	2015-02-09 00:00:00.000	2015-02-05 05:21:00.000	2015-02-05 14:15:00.000
03064	2015-02-11 00:00:00.000	2015-02-05 05:21:00.000	2015-02-05 14:15:00.000

It should be something like:

EMPLOYNO	ACCOUNT_DAY	start	stopp
03064	2015-02-05 00:00:00.000	2015-02-05 05:31:00.000	2015-02-05 16:15:00.000
03064	2015-02-09 00:00:00.000	2015-02-09 06:12:00.000	2015-02-09 12:43:00.000
03064	2015-02-11 00:00:00.000	2015-02-11 05:30:00.000	2015-02-11 14:25:00.000

Since these are the values in the SYSADM.MARK - table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources