Another question

[Altamarus]

It's similar to my other post...

I have a table like this

//STARTING_DATE//DAY1//DAY2//DAY3//DAY4...//DAY7
////////////////////////////////////////////////
//2005-01-02 // 0 // 16 // ...
//2005-01-09 // 0 // 18 // ...
//2005-01-16 // 0 // 18 // ...
//2005-01-23 // 0 // 18 // ...
//2005-01-30 // 0 // 16 // ...
/////////////////////////////////////////////////
Where day 1 to day 7 are the days worked (DAY>0) or not(0) worked in the week for the month. I just cannot figure out how I could count the number of days worked in the month depending on the date entered by the user.


Thank you =D

 

[Juice05]

Run this in Query Analyzer instead of Enterprise Manager.

 

[Altamarus]

I tried your last in query analyser and it gives me

Dates///////Days
2006-12-31//15

Since 01/01/2007 is a non-worked day, the result should be 0


Sorry for the delay I was working on another project

 

[Altamarus]

I dont now if this can help, but my date format is yyyy-mm-dd

 

[Juice05]

Since 01/01/2007 is a non-worked day, the result should be 0

Unless you have entries for the week of 12/31/2006 Through 1/6/2007.

Try replacing 1/1/2007 with a different value. The query will take the date entered and figure out all hours worked within the work week of the date specified.

 

[Altamarus]

Let's say I enter 12-01-2006 (january 12th)

In my calendar table the entry is

2006-01-08(january 8th) 0 // 16 // 16 // 16 // 16 // 16 // 0

Where the week starts with Sunday and ends with saturday.

The resulting table is

Dates // Column name
2006-11-26 00:00:00.000 // 25

I think months and days are reversed in your code.

 

[Juice05]

What datatype is your date field?

 

[Altamarus]

Datatype is datetime
Size is 8

 

[Juice05]

I forgot to switch this back after I was testing. Change your final Select Statement from

Select  Dates, Count(*) From #Temp Where Days > 0 Group By Dates

To

Select  Dates, Sum(Days) From #Temp Where Days > 0 Group By Dates