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

 

[JarlH]

When a week includes days from two different months (or years), this calculation will be quite complicated.

Maybe you should consider another table design... Perhaps something like CALENDAR(year, month, day, week).

 

[Altamarus]

I guess I'll have to find some kind of way to do it. I cannot change the database =(

Thank you for your advise.

 

[Juice05]

What Database Management System are you using?

 

[Altamarus]

SQL Server Enterprise Manager

I don't have full access.

 

[Juice05]

I'm not real proud of this but given your table structure this should work.

CREATE TABLE #Table (Dates DateTime,Days Int)
Declare @Date DateTime
Set @Date = 'Whatever Date you are looking for'
INSERT Into #Table (Dates,Days) Select Starting_Date, Day1 From YourTable Where Starting_Date = @Date
INSERT Into #Table (Dates,Days) Select Starting_Date, Day2 From YourTable Where Starting_Date = @Date
INSERT Into #Table (Dates,Days) Select Starting_Date, Day3 From YourTable Where Starting_Date = @Date
INSERT Into #Table (Dates,Days) Select Starting_Date, Day4 From YourTable Where Starting_Date = @Date
INSERT Into #Table (Dates,Days) Select Starting_Date, Day5 From YourTable Where Starting_Date = @Date
INSERT Into #Table (Dates,Days) Select Starting_Date, Day6 From YourTable Where Starting_Date = @Date
INSERT Into #Table (Dates,Days) Select Starting_Date, Day7 From YourTable Where Starting_Date = @Date

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

Drop Table #Table

 

[Altamarus]

And what if he enters a date between two Starting Dates ?

 

[Juice05]

I am assuming that he will always be putting in the starting date for the week. If he wanted a range he could use the DateDiff function.

 

[Juice05]

Do you need me to show you what I mean?

 

[Altamarus]

If you have some time to explain it, it would be appreciated.

Thank you.

 

[Juice05]

How are people going to be accessing this? Through an application?

 

[Altamarus]

I am doing a report with Infomaker. The user will enter a date(I choose the retreival criteria), and will have various information that depends on the number of days since the beginning of the week, the beginning of the month, and the beginning of the year. (sales for the day, the week, the month, the year)
A year starts the 1st february and ends january the 30th.

 

[Altamarus]

If I had the possibility to create a GOOD table that act as a calendar where you can enter a worked day or a non-worked day, how should I do it?? What structure would be easier for retrieval considering my previous post? Note that I'm a beginner with SQL and that I'm trying to gather as much information as possible. If I have a structure for a new table, I could make the request to create it.

 

[Juice05]

Here is code that will handle you table structure as it is right now. This code will do what your original post asked.

SET DATEFIRST 7

Declare @Date datetime --The date entered by the employee

Set @Date = '1/1/2007'

IF DATEPART(dw, @Date) <> 1
    SET @Date = CONVERT(varchar(8), @Date - DATEPART(dw, @Date) + 1, 112)

CREATE TABLE #Table (Dates DateTime,Days Int)

INSERT Into #Table (Dates,Days) Select Starting_Date, Day1 From YourTable Where Starting_Date = @Date
INSERT Into #Table (Dates,Days) Select Starting_Date, Day2 From YourTable Where Starting_Date = @Date
INSERT Into #Table (Dates,Days) Select Starting_Date, Day3 From YourTable Where Starting_Date = @Date
INSERT Into #Table (Dates,Days) Select Starting_Date, Day4 From YourTable Where Starting_Date = @Date
INSERT Into #Table (Dates,Days) Select Starting_Date, Day5 From YourTable Where Starting_Date = @Date
INSERT Into #Table (Dates,Days) Select Starting_Date, Day6 From YourTable Where Starting_Date = @Date
INSERT Into #Table (Dates,Days) Select Starting_Date, Day7 From YourTable Where Starting_Date = @Date

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

Drop Table #Table

 

[Altamarus]

It gives me an error prompt when I try the query.
'1 row affected by last query'

 

[Juice05]

Did you change the "YourTable" references? Post your code.

 

[Altamarus]

SET DATEFIRST 7

Declare @Dateent datetime --The date entered by the employee

Set @Dateent = '1/1/2007'

IF DATEPART(dw, @Dateent) <> 1
SET @Dateent = CONVERT(varchar(8), @Dateent - DATEPART(dw, @Dateent) + 1, 112)

CREATE TABLE BUDGET.dbo.CALENDRIER_FONCTIONNEL (Dates DateTime,Days Int)

INSERT Into BUDGET.dbo.CALENDRIER_FONCTIONNEL (Dates,Days) Select Starting_Date, Day1 From TRANCHES.dbo.PR_RESOURCE_CALENDAR Where Starting_Date = @Dateent
INSERT Into BUDGET.dbo.CALENDRIER_FONCTIONNEL (Dates,Days) Select Starting_Date, Day2 From TRANCHES.dbo.PR_RESOURCE_CALENDAR Where Starting_Date = @Dateent
INSERT Into BUDGET.dbo.CALENDRIER_FONCTIONNEL (Dates,Days) Select Starting_Date, Day3 From TRANCHES.dbo.PR_RESOURCE_CALENDAR Where Starting_Date = @Dateent
INSERT Into BUDGET.dbo.CALENDRIER_FONCTIONNEL (Dates,Days) Select Starting_Date, Day4 From TRANCHES.dbo.PR_RESOURCE_CALENDAR Where Starting_Date = @Dateent
INSERT Into BUDGET.dbo.CALENDRIER_FONCTIONNEL (Dates,Days) Select Starting_Date, Day5 From TRANCHES.dbo.PR_RESOURCE_CALENDAR Where Starting_Date = @Dateent
INSERT Into BUDGET.dbo.CALENDRIER_FONCTIONNEL (Dates,Days) Select Starting_Date, Day6 From TRANCHES.dbo.PR_RESOURCE_CALENDAR Where Starting_Date = @Dateent
INSERT Into BUDGET.dbo.CALENDRIER_FONCTIONNEL (Dates,Days) Select Starting_Date, Day7 From TRANCHES.dbo.PR_RESOURCE_CALENDAR Where Starting_Date = @Dateent

Select Dates, Count(*) From BUDGET.dbo.CALENDRIER_FONCTIONNEL Where Days > 0 Group By Dates

Drop Table BUDGET.dbo.CALENDRIER_FONCTIONNEL

 

[Juice05]

Give me the Tablename and fields of all tables you are referencing here.

 

[Juice05]

Don't edit any of this code.

SET DATEFIRST 7

Declare @Dateent datetime --The date entered by the employee

Set @Dateent = '1/1/2007'

IF DATEPART(dw, @Dateent) <> 1
    SET @Dateent = CONVERT(varchar(8), @Dateent - DATEPART(dw, @Dateent) + 1, 112)

CREATE TABLE #Temp (Dates DateTime,Days Int)

INSERT Into #Temp (Dates,Days) Select Starting_Date, Day1 From TRANCHES.dbo.PR_RESOURCE_CALENDAR Where Starting_Date = @Dateent
INSERT Into #Temp (Dates,Days) Select Starting_Date, Day2 From TRANCHES.dbo.PR_RESOURCE_CALENDAR Where Starting_Date = @Dateent
INSERT Into #Temp (Dates,Days) Select Starting_Date, Day3 From TRANCHES.dbo.PR_RESOURCE_CALENDAR Where Starting_Date = @Dateent
INSERT Into #Temp (Dates,Days) Select Starting_Date, Day4 From TRANCHES.dbo.PR_RESOURCE_CALENDAR Where Starting_Date = @Dateent
INSERT Into #Temp (Dates,Days) Select Starting_Date, Day5 From TRANCHES.dbo.PR_RESOURCE_CALENDAR Where Starting_Date = @Dateent
INSERT Into #Temp (Dates,Days) Select Starting_Date, Day6 From TRANCHES.dbo.PR_RESOURCE_CALENDAR Where Starting_Date = @Dateent
INSERT Into #Temp (Dates,Days) Select Starting_Date, Day7 From TRANCHES.dbo.PR_RESOURCE_CALENDAR Where Starting_Date = @Dateent

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

Drop Table #Temp

 

[Altamarus]

I entered the NEW code you gave me in the SQL panel of SQL enterprise manager, then I click the Run button, but it tells me that 1 row is affected by the last query again. Maybe I should specify where to save the new table?

BUDGET.dbo.CALENDRIER_FONCTIONNEL was the new table created in the BUDGET database where I have the rights to create and modify tables.