Date comparison weekly

Question

Post reply

Date comparison weekly

Roy Rogers

SSCertifiable

Points: 7712
More actions
August 19, 2005 at 10:22 am

#165722

I need this for my monthly report. Any help is greatly appreciated.
/* Schema */
DECLARE @MyCalls TABLE
(
CallDate DATETIME,
Calls INT
)
DECLARE @MyCars TABLE
(
DataDate DATETIME,
Qty INT
)
/* Data */
INSERT @MyCalls
SELECT '06/01/2006', 1 UNION ALL
SELECT '06/02/2006', 12 UNION ALL
SELECT '06/03/2006', 5 UNION ALL
SELECT '06/04/2006', 4 UNION ALL
SELECT '06/05/2006', 2 UNION ALL
SELECT '06/06/2006', 124 UNION ALL
SELECT '06/07/2006', 4 UNION ALL
SELECT '06/08/2006', 12 UNION ALL
SELECT '06/09/2006', 57 UNION ALL
SELECT '06/10/2006', 15 UNION ALL
SELECT '06/11/2006', 85 UNION ALL
SELECT '06/12/2006', 54 UNION ALL
SELECT '06/13/2006', 85 UNION ALL
SELECT '06/14/2006', 8 UNION ALL
SELECT '06/15/2006', 89 UNION ALL
SELECT '06/16/2006', 45 UNION ALL
SELECT '06/17/2006', 45 UNION ALL
SELECT '06/18/2006', 56 UNION ALL
SELECT '06/19/2006', 23 UNION ALL
SELECT '06/20/2006', 45 UNION ALL
SELECT '06/21/2006', 89 UNION ALL
SELECT '06/22/2006', 89 UNION ALL
SELECT '06/23/2006', 46 UNION ALL
SELECT '06/24/2006', 87 UNION ALL
SELECT '06/25/2006', 12 UNION ALL
SELECT '06/26/2006', 47 UNION ALL
SELECT '06/27/2006', 12 UNION ALL
SELECT '06/28/2006', 89 UNION ALL
SELECT '06/29/2006', 35 UNION ALL
SELECT '06/30/2006', 74
INSERT @MyCars
SELECT '06/04/2006', 1256 UNION ALL
SELECT '06/11/2006', 1267 UNION ALL
SELECT '06/18/2006', 1282 UNION ALL
SELECT '06/25/2006', 1298
/* Requirement */
/*
Calls are recorded daily and cars are counted weekly
I have to create a report how many calls were received per car
daily.
If the parameter is passed as 'PAST'
SELECT A.CallDate, A.Calls / B.Qty (Qty as of previous reorded date from call date)
FROM
@MyCalls A
JOIN
@MyCars B
If the parameter is passed as 'NEXT'
SELECT A.CallDate, A.Calls / B.Qty (Qty as of next reorded date from call date)
FROM
@MyCalls A
JOIN
@MyCars B
If the parameter is passed as 'CURRENT'
SELECT A.CallDate, A.Calls / B.Qty (Qty as of reorded date +- 3 days from call date)
FROM
@MyCalls A
JOIN
@MyCars B

*/
Regards,
gova

Viewing 11 posts - 1 through 10 (of 10 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Can you send the sample results for any of the 3 possible values of the parameter? I'm not sure of what you need.

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 2

/* I gave the results for firat 11 days it will continue like this */

/*

Calls are recorded daily and cars are counted weekly

I have to create a report how many calls were received per car

daily. It is calls / qty. But qty is recorded per week. Which value to take.

They want past week, next week and nearest week's totals for calculation.

*/

/* Results PAST */

SELECT '06/01/2006', 1./ 1256 UNION ALL

SELECT '06/02/2006', 12./ 1256 UNION ALL

SELECT '06/03/2006', 5./ 1256 UNION ALL

SELECT '06/04/2006', 4./ 1256 UNION ALL

SELECT '06/05/2006', 2./ 1267 UNION ALL

SELECT '06/06/2006', 124./ 1267 UNION ALL

SELECT '06/07/2006', 4./ 1267 UNION ALL

SELECT '06/08/2006', 12./ 1267 UNION ALL

SELECT '06/09/2006', 57./ 1267 UNION ALL

SELECT '06/10/2006', 15./ 1267 UNION ALL

SELECT '06/11/2006', 85./ 1267

/* Results NEXT */

SELECT '06/01/2006', 1./ 1267 UNION ALL

SELECT '06/02/2006', 12./ 1267 UNION ALL

SELECT '06/03/2006', 5./ 1267 UNION ALL

SELECT '06/04/2006', 4./ 1267 UNION ALL

SELECT '06/05/2006', 2./ 1282 UNION ALL

SELECT '06/06/2006', 124./ 1282 UNION ALL

SELECT '06/07/2006', 4./ 1282 UNION ALL

SELECT '06/08/2006', 12./ 1282 UNION ALL

SELECT '06/09/2006', 57./ 1282 UNION ALL

SELECT '06/10/2006', 15./ 1282 UNION ALL

SELECT '06/11/2006', 85./ 1282

/* Results CURRENT */

SELECT '06/01/2006', 1./ 1256 UNION ALL

SELECT '06/02/2006', 12./ 1256 UNION ALL

SELECT '06/03/2006', 5./ 1256 UNION ALL

SELECT '06/04/2006', 4./ 1256 UNION ALL

SELECT '06/05/2006', 2./ 1256 UNION ALL

SELECT '06/06/2006', 124./ 1256 UNION ALL

SELECT '06/07/2006', 4./ 1256 UNION ALL

SELECT '06/08/2006', 12./ 1267 UNION ALL

SELECT '06/09/2006', 57./ 1267 UNION ALL

SELECT '06/10/2006', 15./ 1267 UNION ALL

SELECT '06/11/2006', 85./ 1267

Regards,
gova

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 3

I was able to make it. Thanks for your help??

Let me know if there are any easier ways.

SELECT CallDate, DataDate,

1. * Calls / Qty CallsPerCar

FROM

@MyCalls A

LEFT OUTER JOIN

@MyCars B

ON

DATEDIFF(DAY, DataDate, CallDate) BETWEEN

CASE WHEN @pCountFrom = 'PAST' THEN 0 WHEN @pCountFrom = 'NEXT' THEN -6 ELSE -3 END AND

CASE WHEN @pCountFrom = 'PAST' THEN 6 WHEN @pCountFrom = 'NEXT' THEN 0 ELSE 3 END

Regards,
gova

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Hmm.. was wondering what the heck I was missing. I couldn't believe you'd miss a simple exotic join like this.

Glad you figured it out.

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 5

I could not believe myself.

As I closed my query analyzer window I could not give the queries I was trying. was so stupid. But why am I not getting answers from here. Didn't I explain the scenario well or I made it too complicated.

I believe I gave the scema, data to work with and expected answer.

Regards,
gova

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

I wasn't seing th eobvious either... well I guess it's one of those days.

So all is fine now?

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 7

Yes. I see how I will be completing my Stored Procedure Now.

Regards,
gova

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

I think the first step should

exec dbo.GotoWeekEnd

followed by

exec dbo.ResumeNightSleep

with

exec dbo.TakeAbeer

somewhere in between

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 9

exec govinn.GotoWeekEnd

followed by

exec govinn.ResumeNightSleep

with

exec govinn.TakeALotofbeer

Sounds like melody

Regards,
gova

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Ninja's_RGR'us

SSC Guru

Points: 294069

August 19, 2005 at 1:41 pm

#583698

only 5800 seconds left 'till weekend .