Create First Day Of Month Column In Calendar Table

Nov 12, 2007



Hello,

I'm trying to get my head around this, any help would be appreciated. I have a calendar table I've created for use with a billing report. It would be great to have a column that had the first day of the month for every row entry (so for every row representing a day in february, that row would have February 1st, in datetime format). I thought it would be easy to create and populate this column at first, but I'm finding it much more difficult. Does anyone know how to populate this column?

Thanks in advance,

Andy

View 4 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Need To Create (or Insert) A Row For Every Calendar Month

Sep 24, 2014

I have a Table with Financial Data in it and for Certain Accounts (the Key Field is actindx Column)there is no data for Certain Months.

I need a query to fill in the data for each Calendar_Month. This should look at the actindx column and Calendar_Month column if there is no data for a specific Month for that actindx I want it to copy all columns for that actindx and insert into a new row, but just puts Zero Dollars for ActivityDebit, ActivityCredit, and NetAmount.

I have created a CalendarMonth_Lookup Table. I assume there is a way to outer Join the two and insert rows or use an "IF" ,"THEN" type of Statement and just manually add the Calendar Month data.

I am including all the code to make the tables and Insert Data into them.

CODE TO CREATE GPBalances Table
CREATE TABLE [dbo].[GPBalances](
[GP_Balance_ID] [int] IDENTITY(1,1) NOT NULL,
[FISCALYEAR] [smallint] NULL,
[ACCTPERIOD] [varchar](255) NULL,
[YEAR_MONTH_TEXT] [varchar](255) NULL,

[Code] ......

View 3 Replies View Related

Generating New Month In Calendar

Jan 21, 2014

i have inherited a sql database to work on and cannot seen to fix this error. its an availability calendar and at the end of each month a new month is generated, it by default makes all dates unavailable but i need them available. is this done in the database as a default??if a member has logged in recently then the avails populate fine but if they have not logged in for a while then they all show unavailable? i

View 3 Replies View Related

Job To Be Run Ever First For The Previous Calendar Month

Jan 18, 2007

Hi!I have a query that has to return bunch of data based on the calendarmonth. I have to make sure that it will return data to me for 28 daysif it is February and for 31 if it is August(for example). I need tobe able to execute it every first of every month for the past 30, 31 or28 days based on the calendar month. Is there a function or a storedprocedure that I can use to do that?Thank you,T.

View 5 Replies View Related

Transact SQL :: Create Columns For Year / Month And Day From Epoch Column

Jul 21, 2015

I'm wanting to create reports in SSDT 2012 which is connected to a 2008R2 database.  I want to have parameters on for my reports where a user is able to select a year such as 2014.  Unfortunate my table containing the data has two columns with a date value.  the first is of the int type and contains an epoch formatted date.  The second is a varchar type and shows the date as 2015-07-01 08:00:00.  I would like to be able to write a query to return the year, monthnumber and daynumber from either of these columns. 

View 5 Replies View Related

Create A Table That Shows Month / Day And Year?

Jan 30, 2015

How could I create a table that shows the month, each day in that month and the year and give me the option to set a start date & and end date? This is sql server 2005. I have used this before, but it doesn't allow me to see the days in the month. Essentially I want my output to be

Day Month Year
01-01-2012 January 2012
.......
01-02-2015 January 2015
.....
03-01-2015 March 2015
....
08-02-2020 August 2020

Code:

CREATE TABLE #yourTempTable([MonthName] VARCHAR(9), [Year] INT);
WITH CTE(N) AS (SELECT 1 FROM (SELECT 1 UNION ALL SELECT 1)a(N)),
CTE2(N) AS (SELECT 1 FROM CTE x CROSS JOIN CTE y),
CTE3(N) AS (SELECT 1 FROM CTE2 x CROSS JOIN CTE2 y),
TALLY(N) AS (SELECT 0 UNION ALL
SELECT TOP 11 ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM CTE3),
DATETALLY(N) AS (SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())+N, 0)
FROM TALLY)
INSERT INTO #yourTempTable
SELECT DATENAME(month,N), YEAR(N)
FROM DATETALLY;
SELECT *
FROM #yourTempTable;

View 4 Replies View Related

Create A Graghicapl Calendar Using VFP 9.0

Mar 8, 2007

I'm working on a project where a user wants to show there scheduled events on a calendar.

The way they want to show it is, displaying a bar across the days that the event takes place.

They want this on a web page and in Word. I'm using VFP 9.0 and I know it has web tools, but I've never used them. Could someone please point me in the right direction of how to do all of this?

Thank!

View 1 Replies View Related

How Can I Create A New Table With Its Column Named From Another Table's One Column Value By Using A Select Sentence?

Sep 27, 2006

For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".

can you tell me how can I complete this with the SQL?

View 2 Replies View Related

Transact SQL :: Create Custom Work Day Calendar

Nov 17, 2015

I am wanting to create a custom workday calendar to show Monday - Friday as a workday, then go back and update a few holidays as non-workdays.  This is the syntax I have to start with, but it is presenting with an error:

Msg 241, Level 16, State 1, Line 21 Conversion failed when converting date and/or time from character string.

What should I alter?

CREATE TABLE dbo.CreateCustomCalendar
(
wk_Date INT IDENTITY NOT NULL,
FullDate DATETIME NOT NULL,
WeekDayName VARCHAR(9) NOT NULL,
IsWorkday varchar(20) NOT NULL
)

[Code]....

View 7 Replies View Related

Power Pivot :: Create Measure Within Date Dimension Table To Sum Single Entry Per Month Eliminating Duplicates

Jul 27, 2015

We are trying to do some utilization calculations that need to factor in a given number of holiday hours per month.

I have a date dimension table (dimdate).  Has a row for every day of every year (2006-2015)

I have a work entry fact table (timedetail).  Has a row for every work entry.  Each row has a worked date, and this column has a relationship to dimdate.

Our holidays fluctuate, and we offer floating holidays that our staff get to pick.  So we cannot hard code which individual dates in dimdate as holidays.  So what we have done is added a column to our dimdate table called HolidayHoursPerMonth. 

This column will list the number of holiday hours available in the given month that the individual date happens to fall within, thus there are a lot of duplicates.  Below is a brief example of dimdate.  In the example below, there are 0 holiday hours for the month of June, and their are 8 holiday hours for the month of July.

DateKey MonthNumber HolidayHoursPerMonth
6/29/2015 6 0
6/30/2015 6 0
7/1/2015 7 8
7/2/2015 7 8

I have a pivot table create based of the fact table.  I then have various date slicers from the dimension table (i.e. year, month).  If I simply drag this column into the pivot table and summarize by MAX it works when you are sliced on a single month, but breaks if anything but a single month is sliced on.  

I am trying to create a measure that calculates the amount of holiday hours based on the what's sliced, but only using a single value for each month.  For example July should just be 8, not  8 x #of days in the month. 

Listed below is how many hours per month.  So if you were to slice on an entire year, the measure should equal 64.  If you sliced on Jan, Feb and March, the measure should equal 12.  If you were to slice nothing, thus including all 15 years in our dimdate table, the measure should equal 640 (10 years x 64 hours per year).

MonthNumberOfYear HolidayHoursPerMonth
1 8
2 4
3 0
4 0
5 8
6 0
7 8
8 0
9 8
10 4
11 16
12 8

View 3 Replies View Related

SQL Server 2012 :: Create Weeks Calendar From Date

Sep 30, 2015

I need to create a week calendar from date in SQL 2012. Week date starts with Sunday regardless if first Sunday or last Sunday overlaps with previous or next month. For example, the first week in Sep 2015 starts on Sunday 8/30/2015 and ends in 9/5/2015. Too, the last week of Sep 2015 starts on 9/27/2015 and ends on 10/3/2015. Here is the final format:

WeekStartdate WeekEndDate WeekName
8/30/2015 9/5/2015 Sep_Week1
9/6/2015 9/12/2015 Sep_Week2
.....
9/27/2015 10/3/2015 Sep_Week5

View 9 Replies View Related

Analysis :: Create A Calculated Member For Cumulative Sum Using Fiscal Calendar?

Jun 2, 2015

I am trying to create a calculated member for Cumulative sum using Fiscal Calendar using the below code:

WITH MEMBER [Measures].[No of Accounts Cumulative] 
AS 'Sum(PeriodsToDate([DimFinancialDate].[FiscalMonth].[(all)],[DimFinancialDate].[FiscalMonth].CURRENTMEMBER), [Measures].[No of Accounts])'
SELECT
   {[Measures].[No of Accounts],[Measures].[No of Accounts Cumulative]} ON COLUMNS, 
   [DimFinancialDate].[FiscalMonth].Members ON ROWS
FROM [Acquisition]
where [DimFinancialDate].[Year -  Quarter -  Month -  Date].[FiscalYear].&[2014-04-01T00:00:00];

Getting below OUTPUT:

No of Accounts No of Accounts Cumulative
All 1763 1763
Apr 14 116 353
May 14 30 383
Jun 14 284 667
Jul 14 112 779
Aug 14 38 817

[code]....

Basically, It is taking all the members of the Fiscal date dimension to compute the output, But what i am expecting is to compute only for Fiscal calendar that is from April 2014 - March 2015.

View 2 Replies View Related

SQL Server 2008 :: Create Table / Set Default Column Value To Value Of Another Column?

Mar 11, 2015

when creating a new table. How can I set the default value of the column to equal the value of another column in the same table?

View 5 Replies View Related

How To Create Key Time Column And Key Column For A Case Table And A Nested Table For Time Series Algorithm?

Jun 18, 2007

Hi, all experts here,



Thanks for your kind attention.



I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?

Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,



View 6 Replies View Related

Transact SQL :: Show (0) Amount For A Month If No Data Exists In The Table For That Month?

Nov 9, 2015

I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.

I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount

My Desired output

My current query

SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]

[Code] ....

View 6 Replies View Related

Transact SQL :: Joining A Calendar Table And Employee Table?

Apr 20, 2015

I run into a problem when asking to show a query of employee vacation days.

table 1:
column1  is dates
e.g.
2015-01-01
2015-01-02
2015-01-03 
.
.
.
2015-12-31

table2:
employeeID
vacation_date
Tom
2015-01-03
Tom
2015-01-04
David
2015-01-04
John
2015-01-08
Mary
2015-01-012

My query output need to be:

2015-01-01
2015-01-02
2015-1-03
Tom
2015-01-04
Tom
2015-01-04
David
2015-01-05
2015-01-06
2015-01-07
2015-01-08
John
2015-01-09
2015-01-10
2015-01-11
2015-11-12
Mary

... etc... all the way to 2015-12-31

when i use left outer join, i only record one employee per date.

View 4 Replies View Related

How To Create A Table Column

Apr 28, 2008

Hi
I have a table (sql server express),and i need to create a column. How can i do that?
Thank you

View 6 Replies View Related

Populate Calendar Table

Aug 3, 2007

Hello all,
I would like to populate a calendar table similar to the one used in the foodmart2005 sample database. In this table the day of the week, month, and year are recorded as well as additional data which I do not require.
If possible I would like to populate the table with 10 years of data starting with 2007.

Secondly would 10 years of data make the table to large?

Thank you for any thoughts you may have.

View 1 Replies View Related

Create Table And Column Dynamically

Jun 2, 2008

Hi,
There is a table exists in a database, I have to write a stored procedure to create the same table in different database, with the same column name and field. This should be done in runtime. Is it possible. The table will be passed as a parameter to the stored procedure.

View 3 Replies View Related

SQL 2012 :: Sync Table With Calendar?

Nov 11, 2014

sync values from table to calender. I got SQL Server 2012 and Outlook 2013.

I want to know is there any way all appointments booked in table should sync with exchange server automatically.

View 5 Replies View Related

Transact SQL :: Create Table With Timestamp Column

Jul 2, 2015

Im trying to insert the values from this query into a table, so I can later check the history of memory usage:

SELECT
[total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
,[system_memory_state_desc] AS [System_Memory_State_Desc]
FROM [master].[sys].[dm_os_sys_memory]

What I'm missing is a way to insert the current timestamp every time I insert to the table.My plan is to use the insert into command.

View 3 Replies View Related

SQL Table Data, Smalldatetime And Calendar Control

Jul 13, 2007

Inside my SQL 2005 database I have a SmallDateTime with the layout "mm/dd/yyyy hh:mm:ss AM/PM" and then on a selected date of a calendar control it returns the date with 12:00:00 AM which obviously doesn't match with my database's time.

So, I'm looking for a way to drop the time off completely (for the query purpose) the database date when running a select statement. I don't want to touch SQL table data by modifying it, I just want to be able to query sql table using calendar control. Does anyone now how to accomplish this? Thank you in advance. - Nietzky

View 5 Replies View Related

Not Able To Create An Identity Column For An Existing Database Table

Feb 1, 2008

I am working with a table in SQL server. I have a column that I want to designateas an identity column. I am not able to do this, because the field for "Identity Specification" is not editiable.
What I did was I went to sql server, right clicked and selected "Modify".The column properties dialog box/edit grid is then displayed with attributesthat I can modify.
There are two major nodes in this dialog box. One is named "General" and the otheris named "Table Designer". I expand the "Table Designer" node and then go to the node labeled "Identity Specification" It is here where I would like to edit thevalues.
The values that are listed for edit are listed below. BUT, the problem is thatI can place my cursor in those fields, but I am not able to change/edit them.Can anyone tell me what the problem is here? and how I can fix it?
 +Identity Specification   (Is Identity)   Identity Increment   Identity Seed

View 3 Replies View Related

Create Table: Want A Column With Money Data Type

Dec 14, 2007

Hi all!

I want to create a table. One of the columns should be in the data type MONEY with two digits on the right side of the decimal point.
How is that possible?

Thanks a lot and greetings from vienna

landau

View 2 Replies View Related

Create A Formula In A Column For Return 2 Fields From A Other Table

Oct 17, 2007

Hello,
I would like create in a table (A) a column with a formula's data.
In this formula I would like implement 2 fields from a Table (B)

So the formula can be :
[TABLE_B].[FIELD1] + [TABLE_B].[FIELD2]

Is it possible?
We can call 2 fields from a other table?

Thank you

View 1 Replies View Related

SQL 2012 :: Create A Check Constraint Where Another Table Column Value Is Also Involved

Mar 12, 2015

Table ATable B
AIDBID
Col 1BCol1
Col 2
Col3

I want to put a check constraint on A.col3 that depends on the values of A.Col1, A.Col2 and B.BCol1

How can I achieve this without using function.

View 8 Replies View Related

Function To Create Comma Separated List From Any Given Column/table.

Jul 20, 2005

Hi,I'm sure this is a common problem.. to create a single field from awhole column, where each row would be separated by a comma.I can do this for a specified table, and column.. and I've created afunction using VBA to achieve a more dynamic (and very slow) solution..so I would like to implement it using a user defined function in sql server.The problems I'm facing are, that I can't use dynamic sql in afunction.. and I also can't use temporary tables which could build up a'standard' table from parameters given to then perform the function on.So, with these limitations, what other options do I have?Cheers,Chris

View 1 Replies View Related

SQL Server 2012 :: Add Business Days To A Date Using Calendar Table

May 12, 2015

I have a date that I need to add 'n' number of business days to. I have a calendar table that has a 'IsBusinessDay' flag, so it would be good if I was able to use this to get what I need. I've tried to use the 'LEAD' function in the following way;

SELECT A. Date, B.DatePlus3BusinessDays
FROM TableA A

LEFT JOIN (Select DateKey, LEAD(DateKey,3) OVER (ORDER BY datekey) AS DatePlus3BusinessDays FROM Calendar WHERE IsBusinessDay = 1) B ON A.DateKey = B.DateKey

Problem with this is that because I am filtering the Calendar for business days only, when there is a date that is not a business day in TableA, a NULL is being returned.

Is there any way to do a conditional LEAD, so it skips rows that are not business days? Or do I have do go with a completely different approach?

View 9 Replies View Related

Transact SQL :: Calendar Table - Get The Date Post 3 Business Days

Sep 10, 2015

I have a calendar table against entire year 2015 with each day with 2 flag,

1. WK_DT_IN == except Satarday and Sunday, value is "Y", for Sat/Sun, value is "N"
2. HOL_DT_IN == value will only be "Y" only for holiday, example for '2015-01-01' date, it's value is "Y"

DECLARE @CAL TABLE (CAL_DT DATE, WK_DT_IN CHAR(1), HOL_DT_IN CHAR(1))
INSERT INTO @CAL VALUES ('2015-01-01', 'Y', 'Y'), ('2015-01-02', 'Y', 'N'),('2015-01-03', 'N', 'N'),
('2015-01-04', 'N', 'N'), ('2015-01-05', 'Y', 'N'), ('2015-01-06', 'Y', 'N'), ('2015-01-07', 'Y', 'N')

We have a given date, example '2015-01-01', I need to find out a date after 2 business days? I can think of loop, but will it work.

I need to exclude date which having HOL_DT_IN = "Y" and WK_DT_IN = "N".

View 3 Replies View Related

SQL Server Admin 2014 :: Create Temporary Table With Dynamics Column

Jun 10, 2014

I have created a stored procedure for retrieving column name, as shown below

CM_id, CM_Name, [Transaction_Month], [Transaction_Year], [Invoice raised date],[Payment Received date],[Payout date],[Payroll lock date]

Now I am trying to create a temporary table using the above generated coluimns from Stored Procedure with datatype.

View 3 Replies View Related

SQL Server 2008 :: Calculated Time Difference Column In Create Table

Jul 14, 2015

How to include a time difference column using 2 other date columns during creation of a table ?

The requirement is to create a table and include the following columns:

1.Downtime start date & time
2.Downtime end date & time
3.Downtime Duration in hh:mm (calculated date difference between column 1 and 2)

View 3 Replies View Related

Using Foreach Loop To Parse An SQL Table - Lookup And Create Derived Column

Oct 25, 2007

I am looking for the best way in SSIS to do the following. I have an SQL table that for each row in the table I want to take an element from the table do a lookup in a Teredata Table, return information from the teredata source. Use that returned data to do some calculations and create a derived column from my calculations and place the data into the same SQL table that I am parsing through.

Ideas?

View 1 Replies View Related

Power Pivot :: Calendar Look Up Table - Custom 24 Hour Start Time

Jul 9, 2015

I know how to do this in Excel, but not sure about Power Pivot. Our fire department works on a 24 hour shift that starts at 0700 each morning. I want to look up what shift is working depending on the date and time of dispatch. In Excel, I would do a look up table, but instead of using false() as  the last parameter, I would choose true().

I am not sure if this would be best created as a calculated column in the table that holds the incident date and time values, or have a separate table and create a relationship to use the shift in the rows/columns of my pivot table. 

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved