获取从一个表中的所有日期，从另一个表

Question

我创建了一个存储过程将接受startdate，enddate，timezone，user和host

，并会显示每个所有记录计数得到每个日期的总记录在给定的日期范围内的一天。

我已经创建了一个存储过程对于此使用LEFT OUTER JOIN以下是我的代码：

USE [database] 
GO 
/****** Object: StoredProcedure [dbo].[sp_getPageViewCountDayWise]   Script Date: 24/02/2015 10:25:11 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
ALTER PROCEDURE [dbo].[sp_getPageViewCountDayWise] 

    -- Add the parameters for the stored procedure here 
    --<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
    --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0> 

    @startdate DATETIME, 
    @enddate DATETIME, 
    @timezone VARCHAR(6), 
    @user varchar(500), 
    @host VARCHAR(200) 

AS 
BEGIN 

    SET NOCOUNT ON; 

    IF NULLIF(@user, '') IS NULL 
    begin 
     set @user = @host+'/%' 
    end 
    else 
    begin 
     set @user = @host+'/'[email protected]+'/%' 
    end 

    select * from [dbo].[ExplodeDates](@startdate,@enddate) 

    select CAST(a.DateTime AS DATE) AS Date, count(*) as count 
    from [dbo].[DateRange](@startdate,@enddate) AS b 
    LEFT OUTER JOIN UserLog AS a 
    ON CAST(b.thedate AS DATE) = CAST(dbo.[ConvertDateToFromTimeZone] (a.DateTime, @timezone) AS DATE) 
    and a.Url like @user 
    group by CAST(a.DateTime AS date) 

    END 

[dbo].[DateRange]是一个用户定义的函数将返回具有在特定日期范围内的所有日期的表如下：

GO 
/****** Object: UserDefinedFunction [dbo].[ExplodeDates] Script Date:  24/02/2015 11:12:38 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
ALTER FUNCTION [dbo].[ExplodeDates](@startdate datetime, @enddate datetime) 
returns table as 
return (
with 
N0 as (SELECT 1 as n UNION ALL SELECT 1) 
,N1 as (SELECT 1 as n FROM N0 t1, N0 t2) 
,N2 as (SELECT 1 as n FROM N1 t1, N1 t2) 
,N3 as (SELECT 1 as n FROM N2 t1, N2 t2) 
,N4 as (SELECT 1 as n FROM N3 t1, N3 t2) 
,N5 as (SELECT 1 as n FROM N4 t1, N4 t2) 
,N6 as (SELECT 1 as n FROM N5 t1, N5 t2) 
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6) 
SELECT DATEADD(day,num-1,@startdate) as thedate 
FROM nums 
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1 
); 

和

dbo.[ConvertDateToFromTimeZone]是其中每个日期转换成第一个功能E中给出时区

GO 
/****** Object: UserDefinedFunction [dbo].[ConvertDateToFromTimeZone]  Script Date: 24/02/2015 11:14:03 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
ALTER FUNCTION [dbo].[ConvertDateToFromTimeZone] 
(
    -- Add the parameters for the function here 
    @date DATETIMEOFFSET, 
    @offset VARCHAR(6) 
) 
RETURNS DATETIME 
AS 
BEGIN 
    -- Declare the return variable here 
    DECLARE @Result DATETIME 

    -- Add the T-SQL statements to compute the return value here 
    SELECT @Result= SWITCHOFFSET (@date , @offset) 
    -- Return the result of the function 
    RETURN @Result 

END 

假设我希望看到的2015年2月2日之间的记录数以每天2015年2月5日的结果应该是

Date  Count 
02/02/2015 10 
03/02/2015  2 
04/02/2015  0 
05/02/2015  3 

但我的过程是这样的

返回的东西

Date   Count 
NULL   2 
02/02/2015  10 
03/02/2015  2 
05/02/2015  3 

我做错了什么？

Answer 1

a.DateTime是左连接的右侧，因此可以返回空值。看来你想要连接的左侧，这是b.thedate。因此，您应该在查询中选择b.thedate而不是a.DateTime（并且最好也可以使用此组）。