how to calculate date time

Question

how to calculate date time

immaduddinahmed

Ten Centuries

Points: 1375
More actions
April 9, 2015 at 12:17 am

#316421

hi,
my data is looks like this,
Date---------------------------------------A
2015-03-01 13:38:07.343----------------1
2015-03-01 14:04:04.460----------------1
2015-03-02 19:33:55.117----------------3
2015-03-02 19:33:55.117----------------4
2015-03-02 19:39:26.580----------------1
i want data looks like this
Date-------------------------------------------A
Day 1------------------------------------------2
Day 2------------------------------------------8
please can any one provide me a query.
please help me out
Thanks for the help
immad

Viewing 8 posts - 1 through 7 (of 7 total)

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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 1

immaduddinahmed (4/9/2015)
hi,
my data is looks like this,
Date---------------------------------------A
2015-03-01 13:38:07.343----------------1
2015-03-01 14:04:04.460----------------1
2015-03-02 19:33:55.117----------------3
2015-03-02 19:33:55.117----------------4
2015-03-02 19:39:26.580----------------1
i want data looks like this
Date-------------------------------------------A
Day 1------------------------------------------2
Day 2------------------------------------------8
please can any one provide me a query.
please help me out
Thanks for the help

Looks like homework to me. What have you tried?

You might want to look at "Beginning of this day" by Lynn Pettis in his Some Common Date Routines[/url] to get you started.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

immaduddinahmed Ten Centuries Points: 1375 More actions · Answer 2

hi,

i try count function + datename function and also datediff function but no use.

i cant merge same dates

please help me out

thanks for the help

immad

Dwain Camps SSC Guru Points: 86948 More actions · Answer 3

immaduddinahmed (4/9/2015)
hi,
i try count function + datename function and also datediff function but no use.
i cant merge same dates
please help me out
thanks for the help

Try converting your DATETIME value using the formula I pointed you to in Lynn's article. That should give you a pretty clear idea on how to proceed with grouping.

And you'll probably want to use SUM instead of COUNT.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

pietlinden SSC Guru Points: 63503 More actions · Answer 4

You need to return just the date portion of the datetime field. After that, it's easy.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

immaduddinahmed (4/9/2015)
hi,
my data is looks like this,
Date---------------------------------------A
2015-03-01 13:38:07.343----------------1
2015-03-01 14:04:04.460----------------1
2015-03-02 19:33:55.117----------------3
2015-03-02 19:33:55.117----------------4
2015-03-02 19:39:26.580----------------1
i want data looks like this
Date-------------------------------------------A
Day 1------------------------------------------2
Day 2------------------------------------------8
please can any one provide me a query.
please help me out
Thanks for the help

What do you want for days that might be missing between the first date and the last date? For example, what if your data looked like this? Then what would you want returned?

Date---------------------------------------A

2015-03-01 13:38:07.343----------------1

2015-03-01 14:04:04.460----------------1

2015-03-04 19:33:55.117----------------3

2015-03-06 19:33:55.117----------------4

2015-03-06 19:39:26.580----------------1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sigerson Hall of Fame Points: 3697 More actions · Answer 6

And what constitutes Day1? Is Day1 always the earliest date in your table?

Sigerson

"No pressure, no diamonds." - Thomas Carlyle

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Try this:

select

cast(YourDateTimeCol as date) CalDate,

sum(A) A

from

dbo.YourTable

group by

cast(YourDateTimeCol);

This will not provide values for missing dates which is the question Jeff Moden was asking you about in his post.