Count issue/replace between!!!

Question

Count issue/replace between!!!

jcachado

Mr or Mrs. 500

Points: 574
More actions
January 25, 2016 at 4:42 am

#399619

Hi,
I need help for this statement:
SELECT whse_id as wh,count(*) as tot FROM Back_ickpt_prd1 where Format([create_dtim],'hh:nn:ss') between '21-01-2016 00:00:01' and '22-01-2016 00:00:01' group by whse_id
This statement work and show :
wh tot
4151
8137
101
What i need to do to show the count per day instead of by wh.
Thks for your help,
Regards,
JC

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

jcachado (1/25/2016)
Hi,
I need help for this statement:
SELECT whse_id as wh,count(*) as tot FROM Back_ickpt_prd1 where Format([create_dtim],'hh:nn:ss') between '21-01-2016 00:00:01' and '22-01-2016 00:00:01' group by whse_id
This statement work and show :
wh tot
4151
8137
101
What i need to do to show the count per day instead of by wh.
Thks for your help,
Regards,
JC

SELECT

--whse_id AS wh,

CAST(create_dtim AS DATE),

COUNT(*) AS tot

FROM Back_ickpt_prd1

WHERE CAST(create_dtim AS DATE) BETWEEN '20160121' AND '20160122'

GROUP BY

--whse_id,

CAST(create_dtim AS DATE)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jcachado Mr or Mrs. 500 Points: 574 More actions · Answer 2

But if I want per day will have to remove the between right?

Thks,

JC

jcachado Mr or Mrs. 500 Points: 574 More actions · Answer 3

Hi again,

Already work in this way:

SELECT Format([create_dtim],'dd-mm-yyyy'),COUNT(*) AS tot FROM Back_ickpt_prd1 WHERE Format([create_dtim],'dd-mm-yyyy') GROUP BY Format([create_dtim],'dd-mm-yyyy' order by Format([create_dtim],'dd-mm-yyyy'

But when i put ORDER BY dont run.

What is the error?

Thks a lot,

JC

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

jcachado (1/25/2016)
Hi again,
Already work in this way:
SELECT Format([create_dtim],'dd-mm-yyyy'),COUNT(*) AS tot FROM Back_ickpt_prd1 WHERE Format([create_dtim],'dd-mm-yyyy') GROUP BY Format([create_dtim],'dd-mm-yyyy' order by Format([create_dtim],'dd-mm-yyyy'
But when i put ORDER BY dont run.
What is the error?
Thks a lot,
JC

Your WHERE clause is incomplete.

Avoid using FORMAT, it's known for poor performance. If you are simply stripping time from a DATETIME, then use CAST as I did above - it's SARGable, which means it's compatible with index usage.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

You should also alias your columns and use the alias in the ORDER BY.

SELECT CAST(create_dtim AS DATE) AS create_date,

COUNT(*) AS tot

FROM Back_ickpt_prd1

WHERE CAST(create_dtim AS DATE) BETWEEN '20160121' AND '20160122'

GROUP BY CAST(create_dtim AS DATE)

ORDER BY create_date

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jcachado Mr or Mrs. 500 Points: 574 More actions · Answer 6

My mistake , already run but not accept the CAST.

Thks ,

JC

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

jcachado (1/25/2016)
My mistake , already run but not accept the CAST.
Thks ,
JC

Did you get an error message? If so, posting it here would reduce the number of guesses we have to take to figure out what might have gone wrong. Also, post the exact statement you submitted so we can check visually for errors.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jcachado Mr or Mrs. 500 Points: 574 More actions · Answer 8

Ok,

The statment is:

SELECT format([create_dtim],'dd-mm-yyyy'),COUNT(*) AS tot FROM Back_ickpt_prd1 WHERE format([create_dtim],'dd-mm-yyyy') GROUP BY format([create_dtim],'dd-mm-yyyy') order by format([create_dtim],'dd-mm-yyyy') desc

Its run, but i think the DESC dont work because is ordering a day and not through complete date! Strange!

05-01-2016275

04-12-2015298

04-01-2016291

03-12-2015301

03-01-201627

02-12-2015255

02-01-2016122

01-12-2015243

Thks,

JC

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

jcachado (1/25/2016)
Ok,
The statment is:
SELECT format([create_dtim],'dd-mm-yyyy'),COUNT(*) AS tot FROM Back_ickpt_prd1 WHERE format([create_dtim],'dd-mm-yyyy') GROUP BY format([create_dtim],'dd-mm-yyyy') order by format([create_dtim],'dd-mm-yyyy') desc
Its run, but i think the DESC dont work because is ordering a day and not through complete date! Strange!
05-01-2016275
04-12-2015298
04-01-2016291
03-12-2015301
03-01-201627
02-12-2015255
02-01-2016122
01-12-2015243
Thks,
JC

Don't use FORMAT for this. Reasons are already given. Change your code to use CAST instead.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

The order is working fine, because you're ordering strings not dates. That's why 05 comes before 04 and so on.

Take our advice, replace FORMAT with CAST and you'll solve your problem.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jcachado Mr or Mrs. 500 Points: 574 More actions · Answer 11

ChrisM@Work (1/25/2016)
jcachado (1/25/2016)
Ok,
The statment is:
SELECT format([create_dtim],'dd-mm-yyyy'),COUNT(*) AS tot FROM Back_ickpt_prd1 WHERE format([create_dtim],'dd-mm-yyyy') GROUP BY format([create_dtim],'dd-mm-yyyy') order by format([create_dtim],'dd-mm-yyyy') desc
Its run, but i think the DESC dont work because is ordering a day and not through complete date! Strange!
05-01-2016275
04-12-2015298
04-01-2016291
03-12-2015301
03-01-201627
02-12-2015255
02-01-2016122
01-12-2015243
Thks,
JC
Don't use FORMAT for this. Reasons are already given. Change your code to use CAST instead.

Ok, but already told , the cast dont work in statment 🙁

:crying:

Luis Cazares SSC Guru Points: 183706 More actions · Answer 12

We wouldn't insist if it didn't work. Here's an example:

SELECT create_dtim = ABS(CHECKSUM(NEWID())) % 7 + CAST( '20160117' AS datetime)

INTO #Back_ickpt_prd1

FROM sys.all_columns ac1

SELECT CAST(create_dtim AS DATE) AS create_date,

COUNT(*) AS tot

FROM #Back_ickpt_prd1

WHERE CAST(create_dtim AS DATE) BETWEEN '20160118' AND '20160122'

GROUP BY CAST(create_dtim AS DATE)

ORDER BY create_date;

GO

DROP TABLE #Back_ickpt_prd1

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

jcachado (1/25/2016)
ChrisM@Work (1/25/2016)
jcachado (1/25/2016)
Ok,
The statment is:
SELECT format([create_dtim],'dd-mm-yyyy'),COUNT(*) AS tot FROM Back_ickpt_prd1 WHERE format([create_dtim],'dd-mm-yyyy') GROUP BY format([create_dtim],'dd-mm-yyyy') order by format([create_dtim],'dd-mm-yyyy') desc
Its run, but i think the DESC dont work because is ordering a day and not through complete date! Strange!
05-01-2016275
04-12-2015298
04-01-2016291
03-12-2015301
03-01-201627
02-12-2015255
02-01-2016122
01-12-2015243
Thks,
JC
Don't use FORMAT for this. Reasons are already given. Change your code to use CAST instead.
Ok, but already told , the cast dont work in statment 🙁
:crying:

Please post the statement you submitted which caused an error when you attempted to use CAST instead of FORMAT.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183706 More actions · Answer 14

Please, post the statement using CAST that won't work. Also, post DDL and sample data to know the data types and be able to test with data close to reality.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2