stored prrocedure to count days tricky 2

[angela4eva]

i have atble with three fields
date1 amount of datetime and money
10/2/2006 11:00 am $4
10/3/2006 12:01 pm $5
10/2/2006 5:02 pm $6
10/5/2006 12:20 am $7
10/6/2006 12:01 pm $7
10/3/2006 9:00 am $3
10/2/2006 6:00 pm $9


now i want to count the number of days that have a transaction..
one day is actually a timerange between 12m to 12m
so one day is 10/2/2006 12:00pm to 10/3/2006 12pm
doen't matter how many per day but if there is even one tramsaction between the time range.
I wrote a stored proc that takes in date date range
datebegin=10/2/2006 dateend=10/6/2006
then it should look for days to between 10/2/2006 12:00pm to 10/6/2006 12:00pm then
we
10/2/2006 12:00pm --10/3/2006 12:00pm --1 days
10/3/2006 12:00pm --10/4/2006 12:00pm --1 days
10/4/2006 12:00pm --10/5/2006 12:00pm --1 days
10/5/2006 12:00pm --10/6/2006 12:00pm --1 days
so there are total four days but
____________________________________________________
10/2/2006 12:00pm --10/3/2006 12:00pm this will be counted as one day
------------------------------------------------------
10/2/2006 5:02 pm $6
10/2/2006 6:00 pm $9
10/3/2006 9:00 am $3
__________________________________________________


_______________________________________________
10/3/2006 12:00pm --10/4/2006 12:00pm this will be counted as one day
_----------------------------------------------

10/3/2006 12:01 pm $5
___________________________________________________


10/4/2006 12:00pm --10/5/2006 12:00pm no trransaction ---------so this will be counted 0 days
_______________________________________________


_____________________________________________________
10/5/2006 12:00pm --10/6/2006 12:00pm --no transactions ------------so 0 so this be counted a zero days
------------------------------------------------------

_____________________________________________________

so the sql query should returrn number 2 instead of number 4

 

[bborissov]

I am realy confused. What result you want from this data:

10/2/2006 11:00 am    $4
10/3/2006 12:01 pm    $5
10/2/2006 5:02 pm     $6
10/5/2006 12:20 am    $7
10/6/2006 12:01 pm    $7
10/3/2006 9:00 am     $3
10/2/2006 6:00 pm     $9

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[djj55]

Would something like:

SELECT COUNT(Date1) AS CNT FROM 
SELECT DISTINCT convert(varchar(10), SaleDate, 101) as Date1 FROM TB1

be what you are looking for?

You might think about subtracting one minute from a date/time value (in code not talbe) to get the required date thus all times would have the same date.
djj

 

[Catadmin]

Okay, your main problem is that you're looking for transactions between noon of one day and noon of the next day, so you're going to have to do a lot of date part things to find your transactions.

I did write the below, but found it counted each individual "transaction" associated with the date while only printing the date once. I'm posting it in case you're interested in seeing it, but it won't count each day "once". (i.e., for 10/2/06, it printed the below results set).

Select Distinct Count(Convert(varchar(10),SaleDate,101)) Count, Convert(varchar(10),SaleDate,101) as SaleDate
from MyTable
where BeginDate >= @BeginDate
and EndDate <= @EndDate
Group By SaleDate

Count SaleDate
3 10/02/2006

If you are just wanting to count between BeginDate and EndDate (not including those two dates), remove the = signs from my code.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gmmastros]

Alright! Here we go.

Initially, you are probably thinking that the 'day' changing at noon will be your biggest problem. Luckily, it isn't. DateTime fields are stored internally as a number with the whole number part representing the number of days that have elapsed since 1/1/1900 and the fraction part representing the time (so 0.5 is noon). We can use this to our advantage because grouping data by date is simple, we can subtract 0.5 from the date field and group on that.

For illustration purposes, I am creating a table variable to store your test data.

Declare @Temp Table(TransactionDate DateTime, Amount Int)

Insert Into @Temp Values('10/2/2006 11:00 am',      $4)
Insert Into @Temp Values('10/3/2006 12:01 pm',      $5)
Insert Into @Temp Values('10/2/2006 5:02 pm',     $6)
Insert Into @Temp Values('10/5/2006 12:20 am',     $7)
Insert Into @Temp Values('10/6/2006 12:01 pm',    $7)
Insert Into @Temp Values('10/3/2006 9:00 am',     $3)
Insert Into @Temp Values('10/2/2006 6:00 pm',     $9)

In the same query window, add the following code. Notice that we select the TransactionDate and TransactionDate - 0.5. It's this second value that we will eventually use to group our data so that it is attributed to the proper day.

Select TransactionDate,
       DateAdd(Day, DateDiff(Day, 0, TransactionDate - 0.5), 0) As NewTransactionDate,
       Amount
From   @Temp
Order By TransactionDate

Notice the results:
[tt][blue]
TransactionDate NewTransactionDate Amount
----------------------- ----------------------- ------
[!]2006-10-02 11:00:00.000 2006-10-01 00:00:00.000 4[/!]
2006-10-02 17:02:00.000 2006-10-02 00:00:00.000 6
2006-10-02 18:00:00.000 2006-10-02 00:00:00.000 9
2006-10-03 09:00:00.000 2006-10-02 00:00:00.000 3
2006-10-03 12:01:00.000 2006-10-03 00:00:00.000 5
2006-10-05 00:20:00.000 2006-10-04 00:00:00.000 7
2006-10-06 12:01:00.000 2006-10-06 00:00:00.000 7
[/blue][/tt]

Notice that first record. Oct 2nd @ 11:00 AM. This record should be attributed to Oct 1st because is occurs before noon on the second. If we use NewTransactionDate to group our records, we will get an accurate count. Like this...

Select DateAdd(Day, DateDiff(Day, 0, TransactionDate - 0.5), 0) As NewTransactionDate,
       Count(*) As TransactionCount
From   @Temp
Group By DateAdd(Day, DateDiff(Day, 0, TransactionDate - 0.5), 0)

At this point, we should have the data attributed to the proper date, but if you'd like to see the Date and time ranges included in the query, we can make this previous query be a derived table and then do a little more date math to produce your final results. Like this...

Select NewTransactionDate + 0.5,
       NewTransactionDate + 1.5,
       TransactionCount
From   (
       Select DateAdd(Day, DateDiff(Day, 0, TransactionDate - 0.5), 0) As NewTransactionDate,
              Count(*) As TransactionCount
       From   @Temp
       Group By DateAdd(Day, DateDiff(Day, 0, TransactionDate - 0.5), 0)
       ) as A

When you run this query, you will see that your sample data produces these results.

[tt][blue]
StartDateTime EndDateTime TransactionCount
----------------------- ----------------------- ----------------
2006-10-01 12:00:00.000 2006-10-02 12:00:00.000 1
2006-10-02 12:00:00.000 2006-10-03 12:00:00.000 3
2006-10-03 12:00:00.000 2006-10-04 12:00:00.000 1
2006-10-04 12:00:00.000 2006-10-05 12:00:00.000 1
2006-10-06 12:00:00.000 2006-10-07 12:00:00.000 1
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

Aha! I didn't even think of using a .5 in the date calc. Thanks for that info, George.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[djj55]

Sorry missed the noon thing. My suggestion of subtract 1 minute would be 12 hours so using something like:

SELECT Date1, COUNT(Date1) AS CNT FROM 
(SELECT DISTINCT convert(varchar(10), dateadd(hour, -12, SaleDate), 101) as Date1 FROM TB1) AS TB1a 
GROUP BY Date1

Good luck
djj

Note code is not tested.

 

[angela4eva]

george,you are awesome,is there a way to count the number of days with tranactions..if i give a range from 10/1/2006 to 10/6/2006 and say there are no total 5 days but say there are no transactions between 10/2/2006 12:00pm and 10/3/2006 12m then it should return 4.I am counting only the days with tranaction.The output should only be number in above case 4.I appreciate all your help

 

[Catadmin]

Try changing George's third section of code as follows and it should give you the results you need. HOWEVER, if your remove the Date from it, it won't work.

Select DateAdd(Day, DateDiff(Day, 0, TransactionDate - 0.5), 0) As NewTransactionDateinto #TempDateCount
From   @Temp
Group By DateAdd(Day, DateDiff(Day, 0, TransactionDate - 0.5), 0)
--This is changed from George's third section of code
--Below is all new

Select Count(NewTransactionDate) as CountDate, NewTransactionDate
from #TEmpDateCount
Group By NewTransactionDate

Drop Table #TempDateCount
[code]



Catadmin - MCDBA, MCSA
"No, no.  Yes.  No, I tried that. Yes, both ways.  No, I don't know.  No again.  Are there any more questions?"
   -- Xena, "Been There, Done That"

 

[gmmastros]

Angla,

I would suggest you change the code like so...

Declare @Temp Table(TransactionDate DateTime, Amount Int)

Insert Into @Temp Values('10/2/2006 11:00 am',      $4)
Insert Into @Temp Values('10/3/2006 12:01 pm',      $5)
Insert Into @Temp Values('10/2/2006 5:02 pm',     $6)
Insert Into @Temp Values('10/5/2006 12:20 am',     $7)
Insert Into @Temp Values('10/6/2006 12:01 pm',    $7)
Insert Into @Temp Values('10/3/2006 9:00 am',     $3)
Insert Into @Temp Values('10/2/2006 6:00 pm',     $9)

Select Count(*) as TransactionDayCount
From   (
       Select DateAdd(Day, DateDiff(Day, 0, TransactionDate - 0.5), 0) As TransactionDate,
              Count(*) As TransactionCount
       From   @Temp
       Group By DateAdd(Day, DateDiff(Day, 0, TransactionDate - 0.5), 0)
       Having  DateAdd(Day, DateDiff(Day, 0, TransactionDate - 0.5), 0) Between '[!]20061001[/!]' and '[!]20061003[/!]'
       ) As A

Notice the part in red. That is the iso unseperated date format which is esentially YYYYMMDD with no seperators between the parts. This will save you problems with internation formats. However, if you are writing a stored procedure where the 2 values are passed in as date time, then you won't need to worry about that.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Oh...

This is even simpler which should have better performance.

Declare @Temp Table(TransactionDate DateTime, Amount Int)

Insert Into @Temp Values('10/2/2006 11:00 am',      $4)
Insert Into @Temp Values('10/3/2006 12:01 pm',      $5)
Insert Into @Temp Values('10/2/2006 5:02 pm',     $6)
Insert Into @Temp Values('10/5/2006 12:20 am',     $7)
Insert Into @Temp Values('10/6/2006 12:01 pm',    $7)
Insert Into @Temp Values('10/3/2006 9:00 am',     $3)
Insert Into @Temp Values('10/2/2006 6:00 pm',     $9)


Select Count(Distinct DateAdd(Day, DateDiff(Day, 0, TransactionDate - 0.5), 0))
From   @Temp
Where  DateAdd(Day, DateDiff(Day, 0, TransactionDate - 0.5), 0) Between  '20061003' and '20061003'

Simply count the distinct days (after 1/2 a day has been subtracted and the time component is stripped).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[angela4eva]

thanks to all and goerge and cata thanks a lot

 

[Catadmin]

Glad you found your answer. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"