How to display days even with zero sum 2

[Cpreston]

Hi

I have a query which was working well but no the ball park as changed slightly.

The query below is a view that displays data for sales for the next 7 days if there are any on that day. If there are not any it does not display anything, so if I run it now it shows information for the dates for

2015-07-10 00:00:00
2015-07-13 00:00:00
2015-07-14 00:00:00
2015-07-15 00:00:00
2015-07-16 00:00:00

Ideally I would like it to show the 11 and 12th even though there may be no sales for that date

I then use the view to update a graph in a back end system that uses this small query
SELECT DateRequired AS Drillcaption1, SUM([Total Volume]) AS Daily
FROM dbo.[148-vwVolumebydateCP]
GROUP BY DateRequired
ORDER BY Drillcaption1


Could someone advise how I get the main query to show dates as a result even if sales are 0.00 for that date.

Thanks in advance

 

[Cpreston]

Possibly , I am using 2009r2

Thought I had cracked it but I think I need one more tweak.

Currently my query is like this

SELECT [148-CalendarCP].CalendarDate AS Drillcaption1,
DATENAME (WEEKDAY,[148-CalendarCP].CalendarDate) + ', '+ CONVERT(varchar(23),[148-CalendarCP].CalendarDate, 121) AS FULLDATE,
Coalesce(SUM([Total Volume]), 0) AS Daily
FROM [148-CalendarCP]
FULL OUTER JOIN dbo.[148-vwVolumebydateCP]
On [148-CalendarCP].CalendarDate = [148-vwVolumebydateCP].DateRequired
WHERE [148-CalendarCP].CalendarDate >= CAST(GETDATE() - 0 AS DATE)
AND [148-CalendarCP].CalendarDate <= DATEADD(day, 7, CAST(GETDATE() - 0 AS DATE))
GROUP BY [148-CalendarCP].CalendarDate
ORDER BY [148-CalendarCP].CalendarDate

This gives me drillcaption1 which is the date, Fulldate which is day and date added together and Daily which is a total.
I only wanted to see 2 columns so took out the [148-CalendarCP].CalendarDate AS Drillcaption1,

This runs ok, but when I add it to our back end system for a graph it is now showing the time also. Is there away to just show the date and take out time?

Thanks

 

[Cpreston]

Hi

Its ok I figured it out I changed 121 to 103

.CalendarDate, 103)

All working now.

Thanks

 

[Cpreston]

Hi

Sorry to bother everyone again but the above posts were to fix 3 different query's.

I have 2 working but on this one below, it is not showing any figure for Sunday. It is based on the ones that are working and are showing Sundays date this week. Any ideas please. many Thanks

SELECT DATENAME (WEEKDAY,[148-CalendarCP].CalendarDate) + ' '+ CONVERT(varchar(23),[148-CalendarCP].CalendarDate, 103)
AS Date, Coalesce(SUM([TotalVolume]), 0) AS M3
FROM [148-CalendarCP]
FULL OUTER JOIN dbo.[148-vwOrderHeadCP]
On [148-CalendarCP].CalendarDate = [148-vwOrderHeadCP].DateRequired
WHERE [148-CalendarCP].CalendarDate >= CAST(GETDATE() - 0 AS DATE)
AND [148-CalendarCP].CalendarDate <= DATEADD(day, 7, CAST(GETDATE() - 0 AS DATE)) AND
(OrderStatus <> 1) AND (OrderStatus <> 0) AND(OrderStatus <> 11)
GROUP BY [148-CalendarCP].CalendarDate
ORDER BY [148-CalendarCP].CalendarDate

 

[Cpreston]

Hi

After a bit of playing I have found it is these

AND
(OrderStatus <> 1) AND (OrderStatus <> 0) AND(OrderStatus <> 11)

That are preventing Sunday showing. If I take them out Sunday shows with a 0.00 as expected. As soon as I add in any of them it takes out Sunday again.
I need these status in as gives incorrect Sum totals otherwise.

Any ideas please.

Thanks

 

[gmmastros]

The problem is that you are using an outer join, but have where clause conditions on the outer table.

Try moving the where clause condition to the join clause, like this:

SELECT  DATENAME (WEEKDAY,[148-CalendarCP].CalendarDate) + ' '+ CONVERT(varchar(23),[148-CalendarCP].CalendarDate, 103) AS Date, 
        Coalesce(SUM([TotalVolume]), 0) AS M3
FROM    [148-CalendarCP]
        FULL OUTER JOIN dbo.[148-vwOrderHeadCP]
          On [148-CalendarCP].CalendarDate = [148-vwOrderHeadCP].DateRequired
          [!]AND (OrderStatus <> 1) AND (OrderStatus <> 0) AND(OrderStatus <> 11)[/!]
WHERE   [148-CalendarCP].CalendarDate >= CAST(GETDATE() - 0 AS DATE)
        AND [148-CalendarCP].CalendarDate <= DATEADD(day, 7, CAST(GETDATE() - 0 AS DATE)) 
GROUP BY [148-CalendarCP].CalendarDate
ORDER BY [148-CalendarCP].CalendarDate

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

Thanks gmmastros that worked perfect

many thanks for the help everyone

 

[softhemc]

Just an aside, but would

[tt]NOT OrderStatus IN (0, 1, 11)[/tt]

also do the trick and if so be easier to modify if other OrderStatus values needed to be excluded at some time?

NB: Without Management Studio I can't check this, but it's possible that some additional brackets may be needed.

Note to self - Install Management Studio on this machine!!!!!