在SQL中生成考勤报告，其中给出了用户ID [英] Generating attendance report in SQL where user id is given

问题描述

我在sql生成一个考勤报告.i这里有这个查询，它允许选择开始日期和结束日期，并生成我桌子上所有用户ID的报告。但现在我希望它只显示它特定的用户ID。那么如何将用户ID作为我存储过程中的参数传递？



我尝试了什么：

I am generating an attendance report in sql .i have this query here which allows to select the start date and the end date and generate report of all the User Ids in my table .But now i want it to display it just for a specific User Id. so how do i pass the User Id as the parameter in my stored procedure??

What I have tried:

<pre>USE [first_db]
GO

/****** Object:  StoredProcedure [dbo].[GET_ATTENDANCE]    Script Date: 25-02-2018 14:30:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GET_ATTENDANCE]
@STARTDATE  DATETIME,
@ENDDATE    DATETIME
AS BEGIN

--Now generate dates between two dates by Common table expression . and store that values in one temporary table (#TMP_DATES) .
;WITH DATERANGE  AS
(
  SELECT DT =DATEADD(DD,0, @STARTDATE)
  WHERE DATEADD(DD, 1, @STARTDATE) <= @ENDDATE
  UNION ALL
  SELECT DATEADD(DD, 1, DT)
  FROM DATERANGE
  WHERE DATEADD(DD, 1, DT) <= @ENDDATE
)
SELECT * INTO #TMP_DATES
FROM DATERANGE


--As the Report columns (Dates) are dynamic ,  hence Columns (Dates) are concatenated   one by one  from Temporary table (#TMP_DATES) and store the value in a local variable . 
DECLARE @COLUMN VARCHAR(MAX)
SELECT @COLUMN=ISNULL(@COLUMN+',','')+ '['+ CAST(CONVERT(DATE , T.DT) AS VARCHAR) + ']'  FROM #TMP_DATES T

--After Pivot , some columns may be Null as data (here PRESENT_STATUS) not exists in pivot section    .Now  replace the Null values by 'N/A'  .

DECLARE @Columns2 VARCHAR(MAX)
SET @Columns2 = SUBSTRING((SELECT DISTINCT ',ISNULL(['+ CAST(CONVERT(DATE , DT) as varchar )+'],''A'') AS ['+CAST(CONVERT(DATE , DT) as varchar )+']' FROM #TMP_DATES GROUP BY dt FOR XML PATH('')),2,8000)

--Now declare one local variable to write the dynamic sql query .
DECLARE @QUERY VARCHAR(MAX)

--Here Right outer join is done to show the all dates from the temporary table

SET @QUERY = 'SELECT User_Id, ' + @Columns2  +' FROM
            (
                  SELECT  A.User_Id , B.DT AS DATE, A.STATUS FROM MarkA A RIGHT OUTER  JOIN #TMP_DATES B ON A.DATE=B.DT              
           ) X
            PIVOT
            (
                 MIN([STATUS])
                 FOR [DATE] IN (' + @COLUMN + ')
            ) P
            WHERE ISNULL(User_Id,'''')<>''''
            '

 EXEC (@QUERY)

 --Drop the  temporary table
      DROP TABLE #TMP_DATES
     
 END

GO

推荐答案

将用户id参数添加到CREATE PROCEDURE之间的现有两个（或者更可能是你我需要一个ALTER PROCEDURE语句和AS BEGIN行：

Add the user id parameter to the existing two between the CREATE PROCEDURE (or more likely you will need an ALTER PROCEDURE statement instead) and the AS BEGIN lines:

CREATE PROCEDURE [dbo].[GET_ATTENDANCE]
@STARTDATE  DATETIME,
@ENDDATE    DATETIME,
@USERID     INT
AS BEGIN

（使用适合您的用户ID的数据类型，我不知道它是什么类型）



然后可能更改您的表格代码只生成所需的用户信息：

(Use the appropriate datatype for your user ID, I don't know what type it is)

Then probably change your table code to only generate the required user info:

SELECT * INTO #TMP_DATES
FROM DATERANGE
WHERE UserID = @USERID

但说实话，如果你在编写像这样复杂的SP之后就无法自己解决这个问题，那么你需要了解SQL基础知识，因为只是从互联网上复制代码而根本不理解它并不是一个好主意！并把它作为你自己的工作交给你...这是抄袭，你的导师可能很擅长发现......

But to be honest, if you can't work that out for yourself after writing a complicated SP like that, then you need to brush up on SQL basics because just copying code from the internet without understanding it at all is not a good idea! And handing it in as your own work ... that's plagiarism, and your tutor is probably pretty good at spotting that ...

这篇关于在SQL中生成考勤报告，其中给出了用户ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！