如何选择不返回数据的日期 [英] How Do I Select Dates Which Return No Data

问题描述

SELECT   ISNULL(co.CustomerOrder_DeliveryDate,@dateArgu) AS CustomerOrder_DeliveryDaten ,
                                    CustomerOrderDetails_Code ,
                                    CASE WHEN cod.IHOrderDetailStatus_Code = 230
                                         THEN 30
                                         WHEN ( cod.IHOrderDetailStatus_Code = 220
                                                OR cod.IHOrderDetailStatus_Code = 240
                                              ) THEN 40
                                         WHEN ( cod.IHOrderDetailStatus_Code = 210
                                                OR cod.IHOrderDetailStatus_Code = 250
                                                OR cod.IHOrderDetailStatus_Code = 260
                                              ) THEN 50
                                         ELSE cod.IHOrderDetailStatus_Code
                                    END AS IHOrderDetailStatus_Code
                           FROM     CustomerOrder co
                                    LEFT JOIN CustomerOrderDetails cod ON cod.CustomerOrder_Code = co.CustomerOrder_Code
                           WHERE    OrderStatus_Code = 30
                                    AND co.Is_Active = 1
                                    AND CustomerOrder_DeliveryDate >= @dateArgu+ '00:00:00.000'
                                    AND CustomerOrder_DeliveryDate <=@dateArgu+ '23:59:59.000'
                                    AND [cod].[IHOrderDetailStatus_Code] IN (
                                    20, 30, 40, 70, 80, 90, 230, 240, 220, 260,
                                    250, 210 )
                                    AND co.CustomerOrder_ExternalCustomerOrder_ID IS NOT NULL<pre lang="SQL">

例如我的结果是：

日期详情开始未分配已分配Inhouse等待审核InhouseApprovedAwatingClientRev iew Approved InhouseRevisionRequested ClientRevisionRequested ReAssigned Canceled DetailsReview

05/10/2014 0 0 0 0 1 0 0 0 0 0 0

05/01/2015 0 0 0 1 0 0 1 0 0 0 0

06/01/2015 0 0 2 0 0 0 0 0 0 0 0

07/01/2015 0 0 4 2 0 0 0 0 0 0 0

09/01/2015 0 0 1 1 0 0 0 0 0 0 0

10/01/2015 0 0 1 1 0 0 0 0 0 0 0

11/01/2015 0 2 2 0 0 0 0 0 0 0 0

12/01/2015 0 0 0 1 0 0 0 0 0 0 0

14/01/2015 0 3 1 2 0 0 1 0 0 0 0

16/01/2015 0 0 1 0 0 0 0 0 0 0 0

18/01/2015 0 1 0 2 0 0 0 0 0 0 0





这是滑行日期哪个没有结果，我想显示这些日期，即使他们的所有字段都是0 inm

for example my results are :
Date DetailsPeneding NotAssigned Assigned InhouseAwaitingReview InhouseApprovedAwatingClientReview Approved InhouseRevisionRequested ClientRevisionRequested ReAssigned Cancelled DetailsReview
05/10/2014 0 0 0 0 1 0 0 0 0 0 0
05/01/2015 0 0 0 1 0 0 1 0 0 0 0
06/01/2015 0 0 2 0 0 0 0 0 0 0 0
07/01/2015 0 0 4 2 0 0 0 0 0 0 0
09/01/2015 0 0 1 1 0 0 0 0 0 0 0
10/01/2015 0 0 1 1 0 0 0 0 0 0 0
11/01/2015 0 2 2 0 0 0 0 0 0 0 0
12/01/2015 0 0 0 1 0 0 0 0 0 0 0
14/01/2015 0 3 1 2 0 0 1 0 0 0 0
16/01/2015 0 0 1 0 0 0 0 0 0 0 0
18/01/2015 0 1 0 2 0 0 0 0 0 0 0


Here it is skiping date which have no results , i want to show these dates even though they have all fields with 0 in themm

推荐答案

你的问题是你使用的是CustomerOrder_DeliveryDate将缺少日期值。你需要的是一个连续的日期表，称为日历表。



这样的东西：

you problem is that you are using the "CustomerOrder_DeliveryDate" which will have missing date values. what you need is a sequential table of dates, which is called a calendar table.

Something like this:

Declare @CalendarTable table
	(
	[DateKey] [int] NOT NULL,
	[FullDateAlternateKey] [date] NOT NULL,
	[DayNumberOfWeek] [tinyint] NOT NULL,
	[EnglishDayNameOfWeek] [nvarchar](10) NULL,
	[DayNumberOfMonth] [tinyint] NOT NULL,
	[DayNumberOfYear] [smallint] NOT NULL,
	[WeekNumberOfYear] [tinyint] NULL,
	[EnglishMonthName] [nvarchar](10) NULL,
	[MonthNumberOfYear] [tinyint] NOT NULL,
	[CalendarQuarter] [tinyint] NOT NULL,
	[CalendarYear] [smallint] NOT NULL,
	[CalendarSemester] [tinyint] NULL
	)

Declare @StartDate datetime 
Declare @EndDate datetime 
 
Select @StartDate = '20150101',
		@EndDate = '20151231'

;WITH cTally
AS
(
select top 1100 ROW_NUMBER() Over (order by (select null)) - 1 as num
from syscolumns c
cross join syscolumns cc
) 


INSERT INTO @CalendarTable(DateKey, FullDateAlternateKey, DayNumberOfWeek,
	        EnglishDayNameOfWeek, DayNumberOfMonth, DayNumberOfYear,
	        WeekNumberOfYear, EnglishMonthName, MonthNumberOfYear,
	        CalendarQuarter, CalendarYear)
SELECT 	  
	  CONVERT(INT, CONVERT(nvarchar(12), DATEADD(dd,num,@StartDate), 112)),
	  DATEADD(dd,num,@StartDate),
	  DATEPART(dw, DATEADD(dd,num,@StartDate)),
	  DATENAME(dw, DATEADD(dd,num,@StartDate)),
	  DATEPART(dd, DATEADD(dd,num,@StartDate)),
	  DATEPART(dayofyear, DATEADD(dd,num,@StartDate)),
	  DATEPART(week, DATEADD(dd,num,@StartDate)),
	  DATENAME(MONTH, DATEADD(dd,num,@StartDate)),
	  DATEPART(mm, DATEADD(dd,num,@StartDate)),
	  DATEPART(qq, DATEADD(dd,num,@StartDate)),
	  DATEPART(yyyy,  DATEADD(dd,num,@StartDate))
from cTally
Where  DATEADD(dd,num,@StartDate) <= @EndDate

现在只需简单地将这个表与你运行时创建的表联系起来就可以获得正确的结果。



这样的东西。

now just simply LEFT JOIN this table with you runtime created table and you will get the proper result.

something like this.

;with OrderDetail
AS
(
select '20151005' AS orderdate, 1	 as orderqty	union all
select '20150105' AS orderdate, 2	 as orderqty	union all
select '20150106' AS orderdate, 3	 as orderqty	union all
select '20150107' AS orderdate, 4	 as orderqty	union all
select '20150109' AS orderdate, 5	 as orderqty	union all
select '20150110' AS orderdate, 6	 as orderqty	union all
select '20150111' AS orderdate, 7	 as orderqty	union all
select '20150112' AS orderdate, 8	 as orderqty	union all
select '20150114' AS orderdate, 9	 as orderqty	union all
select '20150116' AS orderdate, 10 as orderqty	union all
select '20150118' AS orderdate, 11 as orderqty
)

select  c.FullDateAlternateKey, sum(orderqty) as orderqty 
from @CalendarTable c
left join OrderDetail o on c.FullDateAlternateKey = o.orderdate
group by c.FullDateAlternateKey
order by c.FullDateAlternateKey

希望有帮助

hope it helps

这篇关于如何选择不返回数据的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！