How Do I Select Dates Which Return No Data

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

SQL

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">

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

Posted 5-Jan-15 2:51am

Hassan(Aych Jay)

Updated 5-Jan-15 3:12am

v2

Add a Solution

Comments

nikhil-vartak 6-Jan-15 0:53am

Can you please re-format your question properly? Also provide info on your table structures.

Hassan(Aych Jay) 6-Jan-15 1:02am

I am gathering data from multiple tables so on run time i am creating a table to save all the data.

If i simplify the question then lets suppose i have 3 columns
Date NotAssigned Assigned
05/01/2015 0 1
06/01/2015 2 1
07/01/2015 5 0

and so on for next 15 days

now what happens is that when its 0 0 for a specific date the that date gets skipped. i need to show that date with 0 0
like
07/01/2015 5 0
08/01/2015 0 0
09/01/2015 1 4

Shweta N Mishra 6-Jan-15 3:43am

check your where condition, the issue must me in that.
columns which you are applying in where condition does not reflect in your result mention so its difficult to identify your issue.

Hassan(Aych Jay) 6-Jan-15 5:09am

i have used pivot tables that's why there are no column names. my whole stored procedure is kind f lengthy thats why i gave only share this part where i think the error lies

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Umer Akram · Answer 1 · 2015-01-06T00:42:00

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:

SQL

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.

SQL

;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