Getting Density Of Values Across Years And Months

Aug 29, 2007

Hi All,

I have the following table "Project"

-------------------------------------------------------------------------------------------
ID Name Start Date End Date
--------------------------------------------------------------------------------------------
001 Project 1 2-2-2003 2-3-2007
002 Project 2 1-24-2003 2-6-2007
003 Project 3 4-10-2005 2-10-2008
004 Project 4 5-20-2006 6-6-2008
...
015 Project 15 2-20-2006 3-3-2009
----------------------------------------------------------------------------------------------
What I want is the the following output.

Output 1:

------------------------------------------------------------------------------------------------------------------------------------------------
Year Projects No. Of Projects Starting Months
------------------------------------------------------------------------------------------------------------------------------------------------
2003 Project 1 , Project 2 2 February, January
2005 Project 3 1 April
2006 Project 4, Project 15 2 May , Feb
------------------------------------------------------------------------------------------------------------------------------------------------

(the order displayed in the months shoudl be in accordance with the order of the projecs in the projects column...)
and also the following

Output 2: (this is optional view...)
-----------------------------------------------------------------------------------------
Year Start Date Project Name
-----------------------------------------------------------------------------------------
2003


February 2 Project 1
January 24 Project 2
2005
April 4 Project 3
2006
May 5 Project 4
February 20 Project 15
-------------------------------------------------------------------------------------------

I am very much in need of Output 1. Could someone help me,




View 5 Replies


ADVERTISEMENT

SQL Server 2012 :: Sales Over Years - Retrieve Values In Single Query For Multiple Years And Grand Total?

Apr 24, 2015

I need to list customers in a table that represents sales over the years.

I have tables:

Customers -> id | name |...
Orders -> id | idCustomer | date | ...
Products -> id | idOrder | unitprice | quantity | ...

I am using this SQL but it only gets one year:

SELECT customers.name , SUM(unitprice*qt) AS total
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
WHERE year(date)=2014
GROUP BY customers.name
ORDER BY 2 DESC

I need something like this:

customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015)
--------
customer A | 1000$ | 2000$ | 3000$
customer B | 100$ | 100$ | 200$

Is it possible to retrieve these values in a single SQL query for multiple years and grand total?

View 6 Replies View Related

T-SQL Script To Convert An Integer Into Years And Months

Dec 15, 2007



Hi there,

for displaying purposes I'm trying to convert number of months into years and months. ie. 35 months = 2 years and 11 months.

how can I do this within a T-SQL script?

Thanks for your help.

Manny

View 1 Replies View Related

Display The Variance Between Two Years In Row With Months In Columns?

Mar 17, 2008

I already read a lot about the inscope-function and how it is used to display variances over time periods. But I don't know where to start, as there is no tutorial how to setup this functionality. What I want to display within the report is the following:





Code Snippet

Months
ProductGroup Article Year 1 2 3 4 5 ....
Bicycles 1020 2007 1500 2000 etc.
2008 3000 3000
Var. abs. 1500 1000
Var. % 100% 50%
1025 2007 0 1000
2008 500 1200
Var. abs. 500 200
Var. % 500% 20%
Motorcycles etc.

View 14 Replies View Related

SQL Server 2008 :: Find Patients With Age 6 Months To 5 Years

Mar 3, 2015

formula to return patients ages 6 months to 5 years?

create table dbo.TEST
(
MRN varchar(10),

[Code]....

View 6 Replies View Related

Transact SQL :: Get Counts Including Previous Years / Months?

May 28, 2015

I have simple query that works fine if I write it every time. I want to get this one time group by each year and week ending. here us the query:

Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/09'

Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/16'

Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/23'

How can I get the count for each week year and each week ending? Weekending 05/16 will include ID's from weekending 05/09 as well and week ending 05/23 will include all the ID's from  05/16 and 05/09 and so on...

View 13 Replies View Related

Counting The Years In Multiple Datetime Values

Jul 19, 2007

am making a CV program and i need a way to count the experience the user has:
i have his begin date and end Date as datetime in an sql server.
i can do it programicly but i prefer to do it at the sql side
the question:
how can i get how much exp he has aka :
SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate ))
but for all the datarow
(he has more than one BeginDate and EndDate (for each job he has one))
P.S i want to be able to use it in a where clause :
select * from jobap
where -- or HAVING
JobAp.ind = JobApExp.JobAp AND
SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) > CONVERT(int,@Exp)
thanks in advance

View 8 Replies View Related

Analysis :: Calculate Percentage Difference Of Two Values From Selected Years

Aug 31, 2015

Developing a measure which displays the difference of two values from the selected years.

An example : Show the difference of the sales amount from 2013 and 2015.

Since i am not really into mdx or calculated members.

View 6 Replies View Related

Listing All Months Regardless Of Values

Jun 15, 2005

I have written the following query which returns the number of orders received grouped by the year and month:
SELECT DATEPART(yyyy, order_placeddate) AS year, DATEPART(mm, order_placeddate) AS month, count(order_id) AS orders
FROM orders
GROUP BY DATEPART(yyyy, order_placeddate), DATEPART(mm, order_placeddate)
ORDER BY year, month
year month orders
---- ----- ------
2004 6 17
2004 7 37
2004 8 30
2004 9 42
2004 10 34
2004 11 46
2005 1 25
2005 2 7
2005 4 1
The obvious problem with the above is that it misses out the months that have no orders, i.e. December, March, May, etc.

Is there a way I can amend my query so that it shows all months regardless of whether any orders were placed?

I have thought about trying to LEFT OUTER JOIN the above to a table that has rows with values of 1 – 12, but I’m not convinced this is the answer... and I don’t really know how to do it!

Do let me know if any of the above is unclear – what I’m after is the following:
year month orders
---- ----- ------
2004 6 17
2004 7 37
2004 8 30
2004 9 42
2004 10 34
2004 11 46
2004 12 0
2005 1 25
2005 2 7
2005 3 0
2005 4 1
2005 5 0
Many thanks

View 1 Replies View Related

SQL Server 2008 :: Split Values In 12 Months

Oct 16, 2015

I need to split the amount equally into 12 months from Jan 2015 through Dec 2015.There is no date column in the table and the total amount has to be splitted equally.Guess I can't use Pivot here because the date column is not there ...How can I achieve this ?

CREATE TABLE #tbl_data (
Region Varchar(25),
Amount FLOAT,

[code]...

View 4 Replies View Related

SQL Server 2008 :: Display All Months Even If Values Are NULL

May 13, 2015

I am stuck on a query where I need to display all the month year values even if the corresponding count_booking values are NULL. The requirement is to display the 13 month year period from current date.

For e.g. if the date exists in the current month then starting from May 15 go all the way back to Apr 14.

My current query works in terms of displaying all the 13 months if the count_booking values exist for all the months. However, if some months are missing the values then those months don't show up.how to display all the months even if the values are NULL ? Query below:

SELECT COUNT(m.BOOKING_ID) AS count_booking, LEFT(DATENAME(MONTH, m.CREATE_DT), 3) + ' ' + RIGHT('00' + CAST(YEAR(m.CREATE_DT) AS VARCHAR), 2)
AS month_name
FROM MG_BOOKING AS m
WHERE (m.CREATE_DT >= DATEADD(m, - 13, GETDATE()))

[code]...

View 8 Replies View Related

SQL Server 2012 :: Generate Months Based On Previous Values

Jul 7, 2015

I have a data that with month values ranging from jan 2012 till july 2013 with some values associated with it.

I want to generate months automatically after july 2013 till december 2013 in sql something like the below one:

Is there a way in sql to do this?

View 2 Replies View Related

Selecting Numeric Or Time-scale Values On Chart Yields Duplicate Months

Feb 15, 2008



I have a line chart which works great except when the x axis displays duplicate months. I have tried format codes of MM-YYYY and MMM and have the "numeric or time-scale values" checkbox checked. The data displays fine but the X axis in the case of the MMM format code displays as:

Sep Oct Oct Nov Nov

leaving the user to wonder where exactly does October and November start...

I've googled around but don't see off-hand what I am doing wrong. I am display 3 simultaneous lines if that matters...

Thanks!
Craig

View 4 Replies View Related

SQL Server 2012 :: Select Query - Get Result As Month And Values For All Months Whether Or Not Data Exists

Jul 27, 2015

I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.

The Similar table would be-

create table testing(
DepDate datetime,
val int)
insert into testing values ('2014-01-10 00:00:00.000', 1)
insert into testing values ('2014-05-19 00:00:00.000', 10)
insert into testing values ('2014-08-15 00:00:00.000', 20)
insert into testing values ('2014-11-20 00:00:00.000', 30)

But in result i want the table as -

Month Value

Jan1
Febnull
Marnull
Aprnull
May10
Junnull
Julnull
Aug20
Sepnull
Octnull
Nov30
Decnull

View 9 Replies View Related

Selecting Data Within 1 Month, 6 Months, 12 Months

Mar 27, 2008



I have the following table



FeedBack Type Date

test2 positive 03/15/08

tes3 negative 03/01/08

.. ....



in my page i need to select the number of negative/positive comments within the last



1 month, 6 months, 12 months



How can I accomplish that?
thanks

View 5 Replies View Related

Scan Density < 50%

Aug 14, 2001

I am having problems w/ the indexes on a particular table. Currently the scan density if 25%. I have ran the dbcc dbreindex and the scan density does not improve. I have manually dropped and recreated all of the indexes on the table (there are only two) and still no help.

I have a clustered Primary Key index on my part_number and project_number fields and another index on the project_number field. If I drop the second index and only have my primary key index, it still doesn't help. The fill factor was originally set to 90% and I changed this to 30%, 20%, 10%, 0% and the best scan density I could acheive was 50%.

The data in this table is not updated that often, on average about 15 records a day are updated, and there are only 107 records. This is a small table, but there is a possibility for it to grow rapidly and I want to be prepared now.

Does anyone have any ideas or suggestions? I've tried everything I can think of with no luck.

TIA,

Chris

View 7 Replies View Related

How To Impove Scan Density

Aug 27, 2001

Case
I run dbcc showcontig
Result
DBCC SHOWCONTIG scanning 'rpRateCode' table...
Table: 'rpRateCode' (213627854); index ID: 1, database ID: 17
TABLE level scan performed.
- Pages Scanned................................: 2
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2] !!!!!!!
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 3088.5
- Avg. Page Density (full).....................: 61.84%

Bellow is table structure

CREATE TABLE [dbo].[rpRateCode] (
[ID_RateCode] [ID] NOT NULL ,
[ID_ScheduleOfPresence] [ID] NULL ,
[ID_ScheduleOfBilling] [ID] NULL ,
[IsExtensible] [bit] NOT NULL ,
[IsFixedRate] [Boolean] NOT NULL ,
[ID_RateCodeFollow] [ID] NULL ,
[ID_RateDef] [ID] NULL ,
[ID_MarketSegment] [ID] NOT NULL ,
[ID_HskLevelPattern] [ID] NULL ,
[ComplimentaryType] [AType] NULL ,
[BillingType] [AType] NULL ,
[Discriminator] [Discriminator] NOT NULL ,
[PrintRateOnConfirm] [Boolean] NOT NULL ,
[PrintRateOnArrivalList] [Boolean] NOT NULL ,
[PrintRateOnRegCard] [Boolean] NOT NULL ,
[MinNights] [NbOfNights] NULL ,
[MaxNights] [NbOfNights] NULL ,
[IsLimited] [Boolean] NULL ,
[presencetype] [tinyint] NULL ,
[RateNote] [varchar] (60) NULL ,
[IsAgeCatDiffRoom] [Boolean] NULL ,
[ID_Currency] [ID] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[rpRateCode] WITH NOCHECK ADD
CONSTRAINT [PK__rpRateCode__2EBAFAC0] PRIMARY KEY CLUSTERED
(
[ID_RateCode]
) ON [PRIMARY]
GO

My question how to increase Scan Density for this table if CLUSTERED index exist?

View 1 Replies View Related

Scan Density Question

Dec 16, 2005

Can someone explain why i can drop and recreate indexes on say 2 tables and they had a scan densitiy of say 15% and 10%.

After i recreate the indexes on both tables one comes back with a scan density of 100% and the other only 25%.

What would cause this table to only go to 25% and not 100% like i want it to be?

Thanks for your help :)

View 13 Replies View Related

Keyword Density/Count

Apr 16, 2007

Hi All,let me try to explain what I'm trying to accomplish - I really hope someonecan help.I have a table (tblArticles) which has the following:vcrKeywords varchar(2000)txtBody text(8000)vcrType varchar(128)and this is a sample of the datavcrKeyWords || txtBody || vcrTypekey1,key2,key4,key7,key9 || <snipped body|| Site5 Newskey1,key3,key6,key8,key9 || <snipped body|| Site5 Newskey1,key3,key4,key5,key9 || <snipped body|| Site5 Newskey1,key2,key5,key7,key8 || <snipped body|| Site5 NewsWhat I'm trying to accomplish is to return a keyword count based on thecontent of vcrKeywords (i.e. each comma seperated entry as a count.My SQL statement originally was :SELECT vcrKeyWords, COUNT(vcrKeyWords) AS keycount FROM tblArticleswhere vcrType LIKE 'site 5%' GROUP BY vcrKeyWords ORDER BY keycount DESCHowever, this simply matches the entire vcrKeyword Column and not each commaseperated value.Is there a way in SQL that I can achieve this or do I need to use some kindof scripting language to accomplish it...?Regards,Carl.

View 6 Replies View Related

Page Density/dbcc Showcontig

Dec 5, 2000

I ran a DBCC Showcontig (tableid) and it tell me that one of my tables is only putting 0.19% data on each page. How can I control this for more data on a page. It tells me that I have 8078.8 bytes free per page.

View 1 Replies View Related

Scan Density Of Nonclustered Indexes

Oct 23, 1999

A few days after droping and recreating noclustered indexes on a large table, scan density of most indexes falls below 20%. Only a few number of rows has deleted. Update operation didn't update these keys and there was a lot of inserts. Could anyone explain why are indexes fragmented so much?

View 2 Replies View Related

Scan Density - Small Tables

Aug 16, 2004

I have attached a DBCC ShowContig for a table that has a less than 50% scan density value.

I vendor in insistent I have run their scripts incorrectly three times. I have run a "dbcc reindex" and even dropped primary key and indexes for a table and the scan density does not change. I have read white papers that state that on small tables you may never get a scan density of 100%. I am using this forum as a yardstick for this question.

Thanks

Table: 'ACCESSPROFPERM' (859150106); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 4
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 2.0
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 709.5
- Avg. Page Density (full).....................: 91.23%

DBCC SHOWCONTIG scanning 'ACCESSPROFPERM' table...
Table: 'ACCESSPROFPERM' (859150106); index ID: 2, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 4
- Extents Scanned..............................: 4
- Extent Switches..............................: 3
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 25.00% [1:4]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 75.00%
- Avg. Bytes Free per Page.....................: 2013.0
- Avg. Page Density (full).....................: 75.13%
DBCC SHOWCONTIG scanning 'ACCESSPROFPERM' table...
Table: 'ACCESSPROFPERM' (859150106); index ID: 3, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 3
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 1723.3
- Avg. Page Density (full).....................: 78.71%

View 2 Replies View Related

Compensate For Keyword Density In Full Text Search

Apr 8, 2008

Hi,

When using FREETEXTTABLE the RANK returned seems to be partially based on keyword density. Has anyone come across a method of compensating for this keyword density so that good matches found in a lot of text and a small amount of text return the same RANK?

For an example look at the site I'm working on when someone searches for "whale watching" - http://www.yougodo.com/Search.aspx?ks=whale+watching - you can see that we are showing poor quality results at the top as our first sort order is based on RANK.

If we could remove the keyword density factor from RANK then this would allow second order sorting criteria to come in to play and move the more valuable results to the top.

Any help, pointers, advice would be greatly appreciated,
Gavin.

www.gavinharriss.com

View 1 Replies View Related

How Many Years

Sep 28, 2005

Of database experience do you have?

And what are they?

View 14 Replies View Related

Happy New Years

Dec 30, 2004

I just wanted to wish everyone a Happy New Years and I wanted to thank all submitters to this forum for breaking up my code monkey workday and cutting into my productivity and I have found many of these discussions helpful in my preperation for my long procrastinated MCDBA exams.

Get hammered and do not drive.

View 2 Replies View Related

Calculating Age In Years

Dec 19, 2001

--Use the following formula to calculate a person's age in years in a stored procedure,
--where @dob is the person's date of birth and @yyyymmdd is the date on which to determine the age:

DECLARE @age int
DECLARE @dob datetime
DECLARE @yyyymmdd varchar(11)

SELECT @dob = '12/06/1966'
SELECT @yyyymmdd = GETDATE()
SELECT @age = FLOOR(DATEDIFF(day, @dob, @yyyymmdd) / 365.25)
PRINT CONVERT(varchar, @age)

--Notes:
--Substitute "getdate()" for the @yyyymmdd variable if you want to determine the person's age right now.
--The function divides by 365.25 to allow for leap years and uses the FLOOR function to make sure the function returns an integer.
--The function DATEDIFF(year, @dob, @yyyymmdd) doesn't work because Microsoft chose to implement
--the "year" part of the function to calculate the number of year boundaries crossed.

View 20 Replies View Related

Add 28 Years Aagin

Jul 20, 2005

HI all,This has puzzled me all morning. I have a int field which reads19691124 (UK dates) this is actually a date 24 November 1969 I need toadd 28 years to this making it 19971124 but I’m stumped!Any ideas anyone?CheersSean*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 4 Replies View Related

Happy New Years Dbforums

Dec 30, 2005

remember if you drink too much, get a taxi and charge it to your company.

View 4 Replies View Related

Measuring Consecutive Years

Oct 24, 2006

Hi there.

I work for a charitable organization, am new to this form (and sql programming) and trying to create a flag for unique records indicating the number of consecutive years a donor has given.

I have create a sample db idenifying donor, giving year and total pledges with multiple donor records existing for multiple years having donated.

CREATE TABLE mygifts06 (Donor_id varchar (10), Gift_yr nvarchar (4), Tot_pledges numeric (16,2))

INSERT INTO mygifts06 (Id,Gift_yr,Pledges)
SELECT 155758,2005,15.00 UNION ALL
SELECT 155759,2004,25.00 UNION ALL
SELECT 155758,2004,40.00 UNION ALL
SELECT 155757,2005,100.00 UNION ALL
SELECT 155758,2002,30.00 UNION ALL
SELECT 155758,2001,120.00 UNION ALL
SELECT 155755,2003,15.00 UNION ALL
SELECT 155758,2006,80.00 UNION ALL
SELECT 155757,2003,65.00 UNION ALL
SELECT 155759,2005,400.00


For the above dataset, I am trying to create the following output

Donor_id 2_consec_gifts 3_consec_gifts 4 consec_gifts
--------- -------------- -------------- --------------
155755000
155757000
155758110
155759100


Do I need to use a cursor for this task? I lack experienced in using cursors is there an alternative method someone could suggest?

Thanks in advance.

View 9 Replies View Related

Need To Get Just Last 2 Years Worth Of Data

Jun 23, 2008

SELECT * FROM TEST NOLOCK
WHERE FY_CD = DATEPART(YEAR, GETDATE())

This gives me last years - but they want last years 2007 and 2008

Any suggestions

View 3 Replies View Related

How To Get 20 Years From Current Year

Sep 30, 2014

I want an sql query that wil give me listing of 20 years from current year.

View 2 Replies View Related

How To Get Date Of Birth And Age In Years?

Mar 27, 2007

I have a table name employee and datetime column named dateofbirth, how can i write a select statement to show their, date of birth and age in years?

This is how i did it, i couldn't finish it, Any feedback would be very thankful.

select name, datebirth, year(dateofbirth)
from employee

View 10 Replies View Related

Compare The Same Period Of Different Years

May 21, 2008

Hello to everybody, I'm quite new to the Analysis Services world and would like to create a BI Solution for one of my customers with the following problem: I have sales data from the ERP from different years (2005 and on). My customer wants a report that shows the sales of the current year up to the month he wants to specify (e.g. january - march 2008) compared with the sales of the same period of the previous year (e.g. january - march 2007), divided into product category.

Is there a way to build a cube and then a matrix report in Reporting Services that lets the user specify the period for the current year and shows the same period of the previous year. Do I achieve this behaviour with a calculated member in the cube (e.g. ParallelPeriod)

Any hint (or web like example) would be very appreciated.

Best regards

Alessandro

View 1 Replies View Related







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