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

 

[gmmastros]

In situations like this, I usually use a calendar or numbers table that I have in my database.

The calendar table is usually very simple, having just a single column with an incrementing day. With this calendar table, you could use it as the basis of the query, and the left join it to your existing query.

SELECT Calendar.CalendarDate AS Drillcaption1, Coalesce(SUM([Total Volume]), 0) AS Daily
FROM   dbo.Calendar
       Left Join dbo.[148-vwVolumebydateCP]
         On Calendar.CalendarDate = [148-vwVolumebydateCP].DateRequired
GROUP BY Calendar.CalendarDate
ORDER BY Calendar.CalendarDate

This is not a complete solution, but it should help you get started. Let me know if you have any questions.

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

Hi

Sorry I don't understand you about the calendar table, obviously I do not have this in my databse.

I have played with the ISNUll and Coalesce but have not managed it to bring in, for example 11 and 12th dates
I have treid in my main query and the summary query but with no luck.

SELECT DateRequired AS Drillcaption1, ISNULL(SUM([Total Volume]),0) AS Daily
FROM dbo.[148-vwVolumebydateCP]
GROUP BY DateRequired
ORDER BY Drillcaption1

Thanks

 

[SkipVought]

Well if you don't have a calendar, the maybe you ought to add one to your deficient database!

You cannot display NOTHING, as NOTHING in NOT ANYTHING!

So you need a SOMETHING date, in order to show that you have NOTHING on the SOMETHING date.

It's really very simple to understand.

 

[Cpreston]

Yes SkipVought, for people with your skills it is very simple, for a novice it isn't.

 

[gmmastros]

You can create a calendar table with the following code.

Create Table Calendar(CalendarDate DateTime, Primary Key(CalendarDate))

; With Numbers As
(
  Select 0 As Num
  Union All
  Select 1
  Union All
  Select 2
  Union All
  Select 3
  Union All
  Select 4
  Union All
  Select 5
  Union All
  Select 6
  Union All
  Select 7
  Union All
  Select 8
  Union All
  Select 9
)
Insert 
Into    Calendar(CalendarDate)
Select	DateAdd(Day, A.Num * 10000 + B.Num * 1000 + C.Num * 100 + D.Num * 10 + E.Num, '19700101') As X
From    Numbers As A
        Cross Join Numbers As B
        Cross Join Numbers As C
        Cross Join Numbers As D
        Cross Join Numbers As E
Order By X

Select * From Calendar

This table will take approximately 2 megabytes of storage in your database, but will be very useful, not just with this query, but also with future queries.

Once you have the query created, the code I showed above should get you close to what you want.

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

Hi

Ok I have added the table in and if I do a select I am get replys

I have changed the code to this

SELECT 148-CalendarCP.CalendarDate AS Drillcaption1, Coalesce(SUM([Total Volume]), 0) AS Daily
FROM "148-CalendarCP"
Left Join dbo.[148-vwVolumebydateCP]
On 148-CalendarCP.CalendarDate = [148-vwVolumebydateCP].DateRequired
GROUP BY 148-CalendarCP.CalendarDate
ORDER BY 148-CalendarCP.CalendarDate

I ma then getting this

Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "CalendarCP.CalendarDate" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "CalendarCP.CalendarDate" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "CalendarCP.CalendarDate" could not be bound.
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "CalendarCP.CalendarDate" could not be bound.


Any ideas please

Thanks

 

[Cpreston]

Hi

Ok worked with it a little bit and I now get a result for every date in the calendar table from

1970-01-01 00:00:00.000 0.00000
2243-10-16 00:00:00.000 0.00000

The query looks like this

SELECT [148-CalendarCP].CalendarDate AS Drillcaption1, Coalesce(SUM([Total Volume]), 0) AS Daily
FROM [148-CalendarCP]
FULL OUTER JOIN dbo.[148-vwVolumebydateCP]
On [148-CalendarCP].CalendarDate = [148-vwVolumebydateCP].DateRequired
GROUP BY [148-CalendarCP].CalendarDate
ORDER BY [148-CalendarCP].CalendarDate


This in theory should just bring back todays date and the next 7 days as my view dictates this. So below is my SQl for the view

SELECT TOP (100) PERCENT dbrderHeader.DateRequired, ProductGroup_1.Name AS [Level 1], dbo.ProductGroup.Name AS [Level 2],
dbrderLine.TotalVolume AS [Total Volume]
FROM dbrderHeader INNER JOIN
dbrderLine ON dbrderHeader.OrderID = dbrderLine.OrderID INNER JOIN
dbo.Product ON dbrderLine.ProductID = dbo.Product.ProductID INNER JOIN
dbo.ProductGroup ON dbo.Product.ProductGroupID = dbo.ProductGroup.ProductGroupID INNER JOIN
dbo.ProductGroup AS ProductGroup_1 ON dbo.ProductGroup.ParentID = ProductGroup_1.ProductGroupID
WHERE (dbrderHeader.StockIssued IN (0, 1)) AND (ProductGroup_1.Name = 'Arborflor') AND (dbrderHeader.DateRequired >= GETDATE() - 1) AND
(dbrderHeader.StockIssued = 1) AND (dbrderHeader.OrderType <> 2) OR
(dbrderHeader.DateRequired >= GETDATE() - 1) AND (dbo.ProductGroup.Name IN ('mouldings', 'loose stock', 'Spindles', 'Newels', 'Handrail Kits', 'DeckAncillaries',
'DeckPosts', 'DeckRails', 'MDF Loose', 'Length stocks', 'MDF architraves loose', 'MDF skirtings loose', 'MDF windowboard loose')) AND
(dbrderHeader.DateRequired >= CAST(GETDATE() - 1 AS DATE)) AND (dbrderHeader.DateRequired <= DATEADD(day, 7, CAST(GETDATE() - 1 AS DATE)))
ORDER BY dbrderHeader.DateRequired


What do I need to do to make it show for example as below, using your calendar method? Many Thanks

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

 

[gmmastros]

try this:

SELECT CalendarCP.CalendarDate AS Drillcaption1, Coalesce(SUM([Total Volume]), 0) AS Daily
FROM   [148-CalendarCP] As CalendarCP
       Left Join dbo.[148-vwVolumebydateCP]
         On CalendarCP.CalendarDate = [148-vwVolumebydateCP].DateRequired
GROUP BY CalendarCP.CalendarDate
ORDER BY CalendarCP.CalendarDate

Since your table name has a number dash text format, I think sql was trying to subtract the table name from the value 148. by using square brackets, that problem can be overcome. So I don't have to repeat square brackets everywhere, I create an alias for the table named CalendarCP and used that alias throughout the query.

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

Hi

we must have crossed reply's, I managed to get it like your script (believe me I ma trying to work it out for myself)
So please look at my last thread if you have time and advise if you can

Many Thanks

 

[SkipVought]

This is not about a technical skill.

This is merely LOGIC and thinking carefully about what NOTHING is. NOTHING is NOT ANYTHING.

So if you have NOT ANYTHING, how can you show SOMETHING? It is a non sequitur!

 

[gmmastros]

Apply a filter on the outer query like this:

SELECT [148-CalendarCP].CalendarDate AS Drillcaption1, 
       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() - 1 AS DATE)
       AND [148-CalendarCP].CalendarDate <= DATEADD(day, 7, CAST(GETDATE() - 1 AS DATE))
GROUP BY [148-CalendarCP].CalendarDate
ORDER BY [148-CalendarCP].CalendarDate

You NEED to have an outer join (left join would be sufficient) so that you can return missing rows. Unfortunately, this means you will get all the data from the calendar table unless you limit the rows like I show in the query above.

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

Hi

Ok I ran this in SQl and it appears to be what am after, I will try and apply it to my views and dashboards that are suing it and see how it goes.

Many thanks for your help and patience on this. I will update the post with the result.

Thanks

 

[Cpreston]

Hi

Thanks , it worked perfect and all data looks good.

One cheeky add on question, is it possible to have the date and the day showing in the result, currently just have the date.

Many thanks

 

[Cpreston]

Hi again

I now have my query like this


SELECT [148-CalendarCP].CalendarDate AS Drillcaption1,
DATENAME (WEEKDAY,[CalendarDate])AS Today,
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

Date Day Daily
2015-07-13 00:00:00.000 Monday 30.246


I am now trying to joing the date and day together, concat is not recognised function and I have also tied cast with + but keep running into syntax errors, any ideas how I can join them please. I will keep at it but if anyone can suggest something that would be great

Thanks

 

[softhemc]

I don't have Management Studio on this machine - but I think the following is either correct or very close. The varchar length of 23 was counted out on my fingers! so it may be out by one or two characters - adjust as necessary.

This should produce : [tt]Monday, 2015-07-13 00:00:00.000[/tt]

[tt]CONCAT(DATENAME (WEEKDAY,[148-CalendarCP].CalendarDate), ', ', CONVERT(varchar(23),[148-CalendarCP].CalendarDate, 121))[/tt]

 

[Cpreston]

Thanks for the reply

Unfortunately I am getting

'CONCAT' is not a recognized built-in function name.

So cannot use concat.

Thanks

 

[softhemc]

The Plus sign + should also work:

[tt]DATENAME (WEEKDAY,[148-CalendarCP].CalendarDate) + ', ' + CONVERT(varchar(23),[148-CalendarCP].CalendarDate, 121)[/tt]

 

[Cpreston]

Ok great , that worked.

Thanks for the reply and help

 

[softhemc]

I think CONCAT was introduced in TransactSQL in SQL Server 2012