Todays date required in list from query 2

[Cpreston]

Hi

I thought I had sorted this from an earlier post but looks like I have not. I have this query which up until 2 hours ago was working fine.

it was giving the daterequired starting from todays date, now it is giving tomorrow date?

Could someone advise why this is happening, it should display 2015-06-16 but it is starting at 2015-06-17

SELECT TOP (100) PERCENT dbrderHeader.DateRequired, ProductGroup_1.Name AS [Level 1], dbo.ProductGroup.Name AS [Level 2], SUM(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 = 0) AND (ProductGroup_1.Name = 'Arborflor') AND (dbrderHeader.DateRequired >= GETDATE() - 1) OR
(dbrderHeader.StockIssued = 0) AND (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)))
GROUP BY dbrderHeader.DateRequired, dbo.ProductGroup.Name, ProductGroup_1.Name
ORDER BY dbrderHeader.DateRequired



Thanks

 

[SkipVought]

Does your table contain rows with the date of interest?

 

[Cpreston]

Hi

Yes I have rows for that date for 2015-06-16

Thanks

 

[Cpreston]

Hi

After further investigation I have found if I take this out of the query

AND (ProductGroup_1.Name = 'Arborflor')

I get all other groups for 2015-06-16 so obviously no results match for Arboflor.

Therefore my next question would be if there is nothing to result back is there away to display a 0 total, and if so how do I out that in the query.

Thanks

 

[SkipVought]

There is NOTHING for Arborflor for Today, so how could ZERO which is SOMETHING be a value for NOTHING?

What You'ld need to do is join to your query, a list of all the dates you want SOMETHING for.

This is almost like Sam Harris defining NOTHING as a quantum vacuum, which is SOMETHING!!!

 

[gmmastros]

In your where clause, there is an OR condition which is almost certainly causing your problem.

-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

 

[gmmastros]

Try changing your where clause to this:

WHERE [!]([/!](dbo.OrderHeader.StockIssued = 0) AND (ProductGroup_1.Name = 'Arborflor') AND (dbo.OrderHeader.DateRequired >= GETDATE() - 1) [!])[/!] OR [!]([/!]
(dbo.OrderHeader.StockIssued = 0) AND (dbo.OrderHeader.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 (dbo.OrderHeader.DateRequired >= CAST(GETDATE() - 1 AS DATE)) AND (dbo.OrderHeader.DateRequired <= DATEADD(day, 7, 
CAST(GETDATE() - 1 AS DATE))) [!])[/!]

-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

 

[SkipVought]

@George,

Vital observation!