make date to loom at today and next 5 working days 2

[Cpreston]

I have a query currently which converts a datetime field into date. There is also another field called Totalvolume.

I want the query to look at todays date and then the next 5 days ahead (preferably working days) and also do a sum of the TOTALVolume for the day rather than have individual lines listed

thia is the query I have currently

SELECT CONVERT(varchar, CONVERT(varchar(2), DATEPART(day, DateRequired)) + '/' + CONVERT(varchar(2), DATEPART(month, DateRequired)) + '/' + CONVERT(varchar(4),
DATEPART(year, DateRequired))) AS Date, TotalVolume
FROM dbo.[148-vwOrderHeadCP]

Could someone please advise (I will keep trying in the mean time and update if successful)

Thanks in advance

 

[Cpreston]

Hi

Getting abit closer, I now have my query like this

SELECT TOP (100) PERCENT DateRequired, SUM(TotalVolume) AS M3
FROM dbo.[148-vwOrderHeadCP]
GROUP BY DateRequired
HAVING (DateRequired >= CONVERT(VARCHAR(10), GETDATE(), 110))
ORDER BY DateRequired

But the date is still datetime
2015-05-13 00:00:00 (any ides how I can just get it to look like 13/05/2015)

Thanks

 

[djj55]

Try using something like:

SELECT TOP (100) PERCENT DateRequired, SUM(TotalVolume) AS M3
FROM dbo.[148-vwOrderHeadCP]
GROUP BY DateRequired
WHERE DateRequired >= CAST(GETDATE() AS DATE) 
[indent]AND DateRequired <= DATEADD(day, 5, CAST(GETDATE() AS DATE))[/indent]
ORDER BY DateRequired

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Cpreston]

Hi

Thanks for the reply

I am getting an error message saying Error in group By clause, Unable to parse, query text
When I click OK I get an execution error with Error Message Incorrect syntax near the keyword WHERE

The sql is like below

SELECT TOP (100) PERCENT DateRequired, SUM(TotalVolume) AS M3
FROM dbo.[148-vwOrderHeadCP]
GROUP BY DateRequired
WHERE DateRequired >= CAST(GETDATE() AS DATE)
AND DateRequired <= DATEADD(day, 5, CAST(GETDATE() AS DATE))
ORDER BY DateRequired

Any ideas please

 

[Cpreston]

Hi

Changed the code to below and it looks like it works now

SELECT TOP (100) PERCENT DateRequired, SUM(TotalVolume) AS M3
FROM dbo.[148-vwOrderHeadCP]
WHERE DateRequired >= CAST(GETDATE() AS DATE)
AND DateRequired <= DATEADD(day, 5, CAST(GETDATE() AS DATE))
GROUP BY DateRequired
ORDER BY DateRequired

Thanks

 

[Cpreston]

oh Jus noticed it is still giving datetime format and not just date so I am getting for today

2015-05-13 00:00:00

would prefer it to be only

2015-05-13

Any ideas please

Many thanks

 

[djj55]

T-SQL is not the best place to do formatting, however in this case one solution would be to cast the DATETIME value as DATE. If you do this in the select you will also need to do it in the GROUP BY.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Cpreston]

Sorry, don't understand, could you give me an example if possible, I will keep trying in the meantime. Thanks

 

[gmmastros]

try this:

SELECT TOP (100) PERCENT Convert(VarChar(10), DateRequired, 103), SUM(TotalVolume) AS M3
FROM dbo.[148-vwOrderHeadCP]
WHERE DateRequired >= CAST(GETDATE() AS DATE) 
AND DateRequired <= DATEADD(day, 5, CAST(GETDATE() AS DATE))
GROUP BY Convert(VarChar(10), DateRequired, 103)
ORDER BY Convert(VarChar(10), DateRequired, 103)

-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

That worked perfectly, thanks for the great posts and the assistance.

Many thanks