Showing posts with label missing. Show all posts
Showing posts with label missing. Show all posts

Thursday, March 29, 2012

Filter does not work on a group?

I must be missing something. I have a table with two groups. One groups on
the user who created the record the other on the date the record was created.
I sort on the username group by name and the createdate group with a
category type.
My dataset pulls information for the current year however in this table I
want to only display data for the last week. If I apply the filter to the
table I receive expected results however if I apply the filter to the group
nothing comes back.
For either group if I put "= #2/22/2005#" for the expression "<=" as the
operator and "= Fields!CreateDate" as the Value I return 0 records despite
having some. I know my syntax is correct becaus this works at the table
level.
Can anyone tell me what I am missing?And you have date values in there more recent than 2/22/2005?
2/22/2005 <= Fields!CreateDate
I would try "= Fields!CreateDate" in the expression and "= #2/22/2005#" in
the value.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ben Holcombe" <BenHolcombe@.discussions.microsoft.com> wrote in message
news:41E911DB-58AE-4F74-9A6B-B6144C454DCA@.microsoft.com...
>I must be missing something. I have a table with two groups. One groups
>on
> the user who created the record the other on the date the record was
> created.
> I sort on the username group by name and the createdate group with a
> category type.
> My dataset pulls information for the current year however in this table I
> want to only display data for the last week. If I apply the filter to the
> table I receive expected results however if I apply the filter to the
> group
> nothing comes back.
> For either group if I put "= #2/22/2005#" for the expression "<=" as the
> operator and "= Fields!CreateDate" as the Value I return 0 records despite
> having some. I know my syntax is correct becaus this works at the table
> level.
> Can anyone tell me what I am missing?|||Same result. I know there are values past 2/22/2005 because I see them
without the filter applied. The filter works at the table level just not the
group level. This is strange.
"Jeff A. Stucker" wrote:
> And you have date values in there more recent than 2/22/2005?
> 2/22/2005 <= Fields!CreateDate
> I would try "= Fields!CreateDate" in the expression and "= #2/22/2005#" in
> the value.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Ben Holcombe" <BenHolcombe@.discussions.microsoft.com> wrote in message
> news:41E911DB-58AE-4F74-9A6B-B6144C454DCA@.microsoft.com...
> >I must be missing something. I have a table with two groups. One groups
> >on
> > the user who created the record the other on the date the record was
> > created.
> > I sort on the username group by name and the createdate group with a
> > category type.
> >
> > My dataset pulls information for the current year however in this table I
> > want to only display data for the last week. If I apply the filter to the
> > table I receive expected results however if I apply the filter to the
> > group
> > nothing comes back.
> >
> > For either group if I put "= #2/22/2005#" for the expression "<=" as the
> > operator and "= Fields!CreateDate" as the Value I return 0 records despite
> > having some. I know my syntax is correct becaus this works at the table
> > level.
> >
> > Can anyone tell me what I am missing?
>
>

Tuesday, March 27, 2012

Filter & Parameter pane missing in MDX Query Designer in SQL Reporting Services

I've posted this in the SQL Reporting Services forum as well, but thought I'd try here as some of you may be using Analysis server with SRS also. I'm developing my first Analysis Server report in SRS. Everything was going well when I discovered the filters & parameters pane was missing in the MDX Query Designer in data mode. I have my columns and rows but need to drag a dimension into the filters & parameters pane in MDX Query Designer and set a filter and parameter on it. Any idea how to make it viewable?

Additional information on this thread. The filter and parameter pane does appear in the MDX Query Designer in SQL Server Business Intelligence Development Studio on a report created from a SQL Server 2005 database, but not on a SQL Server 2000 database. Am I on to something and is there a work around for SQL Server 2000 databases?

Filling out missing data in subsequent records?

Hi

I receive several datafiles from another system that are more or less in a Excel pivot table like format.
That is the first row representing the current data is fully filled, while subsequent rows, representing historic data are left partly empty.
Current rows and historic rows have different identifiers, e.g. rectype=0 or 1
Filling out the missing data on the historic record should be simple, if only all current rows would be filled.
Some current rows aren't filled, so the stuff like the following doesn't work:
update t1
set t1.colA =
(
select top 1 t2.colA
from mytable AS t2
where t2.rowid <= t1.rowid
and t2.cola <> 0
order by t2.rowid desc
)
from mytable AS t1

Somehow I need to check for the rectype, so I don't fill out rows with data from a previous entity

Any suggestions before I revert to using a cursor?

And while we are at it: I am in for an easy way to do this for all (about 60) colums in one move?

Before you ask:
After filling everything out we process the file to arrive at a few handy fromto tables, so we can use the correct data about the entity's status at a particular point in time elsewhere

I am using MS SQL Server 2005, and solutions are allowd to use any specific trickery that MSSQL allows.

Many thanks for any constructive thoughts

Cheers

DrioWould this do the job? My changes are highlighted

UPDATE t1
SET t1.colA =
(
SELECT TOP 1 t2.colA
FROM MyTable AS t2
WHERE t2.rowid < t1.rowid
AND t2.colA <> 0
ORDER BY t2.rowid DESC
)
FROM mytable AS t1
WHERE rectype = 0

I havn't tested this code - it's only in my head (/on the screen) so don't use it on your live data ;)|||Thnak you georgev:
for the tagline (I won't do it again; a real eye-opener)
for the small correction in my code and for the direction.

It goes wrong where there are history record after the current reccord with no data.Obvioulsy they get filled from the previous current record that hadd data.

My interim solution
1. update all current records wh data with a dummy value
2. use the fill out query
We then have to check the marked records and see if we can find a pattern that allows us the handle them in code (otherwise someone has to go through them manually; only .25% of total)

Thanks for you swift response

Cheers

Drio

filling in missing values

I have a table that keeps track of click statistics for each one of my dealers.. I am creating graphs based on number of clicks that they received in a month, but if they didn't receive any in a certain month then it is left out..I know i have to do some outer join, but having trouble figuring exactly how..here is what i have:

select d.name, right(convert(varchar(25),s.stamp,105),7), isnull(count(1),0)
from tblstats s(nolock)
join tblDealer d(nolock)
on s.dealerid=d.id
where d.id=31
group by right(convert(varchar(25),s.stamp,105),7),d.name
order by 2 desc,3,1

this dealer had no clicks in april so this is what shows up:
joe blow 10-2004 567
joe blow 09-2004 269
joe blow 08-2004 66
joe blow 07-2004 30
joe blow 06-2004 8
joe blow 05-2004 5
joe blow 03-2004 9To have all the dealers needed:

select d.name, right(convert(varchar(25),s.stamp,105),7), isnull(count(1),0)
from tblstats s(nolock)
RIGHT OUTER join tblDealer d(nolock)
on s.dealerid=d.id
where d.id=31
group by right(convert(varchar(25),s.stamp,105),7),d.name
order by 2 desc,3,1

To have all the dates, that is something else ...
SQL Server can only base itself on what it finds in the database ...
If there is notking in tblstats (s) for that moment and you get your months from that table it will not work ...
Create an extra table (or an temp table) that will store all months and create an outer join on that table

Filling in missing data for a continous range, e.g. x-axis ..

Hi,

I want to display some stats using captured information. the x-axis is the date on which something occured. the y-axis is the number of occurance on that day.

The problem is that the x-axis need to be of continous range, so say from 1st Mar 2006 to 8th Mar 2006, I need to display every date within that range.

I've gathered the neccessary stats with a group statement but I was hoping I can fill in the gaps without having to loop through the returned data to identify missing dates, is there any built int SQL Server function which will allow me to do this within my SELECT statement?

Cheers
james
`I dont think there is such a inbuilt function available in sql server. But if you could post the ddl ,some sample data and the expected result, i m sure someone here would definately be able to help you.|||One way to do this is to enumerate the values that you need, something like:SELECT d
FROM (SELECT Convert(DATETIME, '2006-03-01') AS d
UNION SELECT '2006-03-02'
UNION SELECT '2006-03-03'
UNION SELECT '2006-03-04'
UNION SELECT '2006-03-05'
UNION SELECT '2006-03-06'
UNION SELECT '2006-03-07'
UNION SELECT '2006-03-08') AS z-PatP|||Or more generically, create a table of sequential numbers in your database starting with zero, from which you can dynamically create any sequence of dates. Like this:select XValues.XValue,
YourData.YValue
from --XValues
(select dateadd(day, SeqValue, '2006-01-01') as XValue
from SequentialNumbers) XValues
left outer join YourData on XValues.XValue = YourData.XValue|||Most times that I have need to do something like this, I have generated a calendar master table with dates and perhaps some bit flags to indicate other generic data about that date (eg, weekends, public holidays, non-working day).

Then I can join (or outer join) my recordset to the calendar master table for reporting purposes.

Regards,

hmscott|||Most times that I have need to do something like this, I have generated a calendar master table with dates and perhaps some bit flags to indicate other generic data about that date (eg, weekends, public holidays, non-working day).

Then I can join (or outer join) my recordset to the calendar master table for reporting purposes.

Regards,

hmscott

Thank you all
I think blindman and htmscott have described pretty much the same method.
I will give these a try and see how it goes.

Cheers
James|||Thanks again guys, the method suggested by you guys works great.

Cheers
Jamessql

Monday, March 26, 2012

Fill up missing months.

Hi,
I've got this resultset:
year month group value
2005 5 a 10
2005 6 a 20
2005 1 b 15
2005 3 b 16
2005 9 c 15
and now I need to fill it up with "missing" months -- like this:
year month group value
2005 1 a 0
2005 2 a 0
2005 3 a 0
2005 4 a 0
2005 5 a 10
2005 6 a 20
2005 7 a 0
2005 8 a 0
2005 9 a 0
2005 10 a 0
2005 11 a 0
2005 12 a 0
2005 1 b 15
2005 2 b 0
2005 3 b 16
2005 4 b 0
2005 5 b 0
2005 6 b 0
2005 7 b 0
2005 8 b 0
2005 9 b 0
2005 10 b 0
2005 11 b 0
2005 12 b 0
2005 1 c 0
2005 2 c 0
2005 3 c 0
2005 4 c 0
2005 5 c 0
2005 6 c 0
2005 7 c 0
2005 8 c 0
2005 9 c 15
2005 10 c 0
2005 11 c 0
2005 12 c 0
Anything I can think of is some cursor or loop-oriented approach with a
little help of "Dates" table. Maybe someone can come up with more
ellegant, set oriented solution.
Thanks in advance
MaciekHi Maciek !
Use a calendar table:
http://www.aspfaq.com/show.asp?id=2519
HTH, Jens Suessmeyer.|||Create a Calendar table and populate it with all the dates you will
ever need (a calendar I'm using right now has 100 years worth). Once
you've done that you can just outer join it in your query.
You'll may still use a loop to populate the calendar but you only need
to do that once and do it at install time rather than runtime.
David Portas
SQL Server MVP
--|||DECLARE @.StartYear SMALLINT , @.EndYear SMALLINT
SELECT @.StartYear = 2004 , @.EndYear = 2005
SELECT
Years.N AS [Year]
, Months.N AS [Month]
FROM
( SELECT * FROM tblNumbers WHERE N BETWEEN @.StartYear AND @.EndYear ) Years
CROSS JOIN
( SELECT * FROM tblNumbers WHERE N BETWEEN 1 AND 12 ) Months
ORDER BY
[Year]
, [Month]
Then right outer join the table to your resultset.
HTH
"maciek" <maciek@.kolobrzeg.com.pl> wrote in message
news:1129805888.703877.78890@.g47g2000cwa.googlegroups.com...
> Hi,
> I've got this resultset:
> year month group value
> 2005 5 a 10
> 2005 6 a 20
> 2005 1 b 15
> 2005 3 b 16
> 2005 9 c 15
> and now I need to fill it up with "missing" months -- like this:
> year month group value
> 2005 1 a 0
> 2005 2 a 0
> 2005 3 a 0
> 2005 4 a 0
> 2005 5 a 10
> 2005 6 a 20
> 2005 7 a 0
> 2005 8 a 0
> 2005 9 a 0
> 2005 10 a 0
> 2005 11 a 0
> 2005 12 a 0
> 2005 1 b 15
> 2005 2 b 0
> 2005 3 b 16
> 2005 4 b 0
> 2005 5 b 0
> 2005 6 b 0
> 2005 7 b 0
> 2005 8 b 0
> 2005 9 b 0
> 2005 10 b 0
> 2005 11 b 0
> 2005 12 b 0
> 2005 1 c 0
> 2005 2 c 0
> 2005 3 c 0
> 2005 4 c 0
> 2005 5 c 0
> 2005 6 c 0
> 2005 7 c 0
> 2005 8 c 0
> 2005 9 c 15
> 2005 10 c 0
> 2005 11 c 0
> 2005 12 c 0
> Anything I can think of is some cursor or loop-oriented approach with a
> little help of "Dates" table. Maybe someone can come up with more
> ellegant, set oriented solution.
> Thanks in advance
> Maciek
>|||Additionally,
You'd want to cross join the Year/Month results with a distinct list of the
(a,b,c) column - so you'd get a full list of Years/Months/ColumnX :)
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:435774d3$0$140$7b0f0fd3@.mistral.news.newnet.co.uk...
> DECLARE @.StartYear SMALLINT , @.EndYear SMALLINT
> SELECT @.StartYear = 2004 , @.EndYear = 2005
> SELECT
> Years.N AS [Year]
> , Months.N AS [Month]
> FROM
> ( SELECT * FROM tblNumbers WHERE N BETWEEN @.StartYear AND @.EndYear )
Years
> CROSS JOIN
> ( SELECT * FROM tblNumbers WHERE N BETWEEN 1 AND 12 ) Months
> ORDER BY
> [Year]
> , [Month]
> Then right outer join the table to your resultset.
> HTH
> "maciek" <maciek@.kolobrzeg.com.pl> wrote in message
> news:1129805888.703877.78890@.g47g2000cwa.googlegroups.com...
>|||Thanks Jens and David,
Sorry, I put it into wrong words when I wrote "(...) with a little help
of 'Dates' table." I thougt of the Calendar table you mentioned. I have
one in my db but the problem is I do not know how to write this outer
join :-( I mean if it was just one group ("a" for example) i would just
do a simple outer join on 'year' and 'month' columns, but since there
are a few groups I do not know how to make it work. How to repeat this
'twelve months' for each group. I think I'm not set oriented kind of
mind...|||Now I've got the clue!
Thanks!

Fill in missing date ranges

I have a table AssetValues that contains several rows of data for a
particular date. The table has missing dates and I need to populate these
missing dates one-time to create historical data and then daily to populate
missing data on an ongoing basis. The missing dates are not related to the
particular day of the w.
The logic for inserting new rows is as follows:
If there is a date without data, insert data using the previous date that
has data as long as the previous day is in the same month. So if I was
missing data for 20060430 and there was data for 20060429 then copy the data
from 20060429 changing the date to 20060430. If I was missing 20060430 and
20060429 then use the data from 20060428.
If I was missing 20060501 do not use the data from 20060430. The data needs
to be in the same month. In this case I would need to use the data from
20060502 if this was available or the next day in May when the data is
available. If the data is not available then take no action until it is.
I have a calendar table if needed.
Thanks to anyone who could help.
CREATE TABLE AssetValues
(
AssetValueDate datetime,
Category char(1),
Subcategory int
)
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','A', 1 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','B', 2 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','C', 3 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','A', 4 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','B', 5 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','C', 6 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','A', 7 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','B', 8 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','C', 9 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','A', 10 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','B', 11 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','C', 12 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','A', 13 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','B', 14 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','C', 15 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','A', 16 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','B', 17 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','C', 18 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','A', 19 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','B', 20 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','C', 21 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','A', 22 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','B', 23 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','C', 24 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','A', 25 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','B', 26 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','C', 27 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','A', 28 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','B', 29 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','C', 30 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','A', 31 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','B', 32 )
DROP TABLE AssetValuesTry,
create view v1
as
select a.dt
from dbo.calendar as a
where not exists (
select * from dbo.AssetValues as b
where b.AssetValueDate = a.dt
)
and a.dt between convert(char(6), (select min(AssetValueDate) from
dbo.AssetValues), 112) + '01' and dateadd(day, -1, dateadd(month, 1,
convert(char(6), (select max(AssetValueDate) from dbo.AssetValues), 112) +
'01'))
go
select *
from v1
go
-- previous available day
insert into dbo.AssetValues
select
v1.dt, t1.Category, t1.Subcategory
from
v1 inner join dbo.AssetValues as t1
on t1.AssetValueDate = (select max(a.AssetValueDate) from dbo.AssetValues
as a where a.AssetValueDate < v1.dt and datediff(month, a.AssetValueDate,
v1.dt) = 0)
go
-- next available day
insert into dbo.AssetValues
select
v1.dt, t1.Category, t1.Subcategory
from
v1 inner join dbo.AssetValues as t1
on t1.AssetValueDate = (select min(a.AssetValueDate) from dbo.AssetValues
as a where a.AssetValueDate > v1.dt and datediff(month, v1.dt,
a.AssetValueDate) = 0)
go
AMB
"Terri" wrote:

> I have a table AssetValues that contains several rows of data for a
> particular date. The table has missing dates and I need to populate these
> missing dates one-time to create historical data and then daily to populat
e
> missing data on an ongoing basis. The missing dates are not related to the
> particular day of the w.
> The logic for inserting new rows is as follows:
> If there is a date without data, insert data using the previous date that
> has data as long as the previous day is in the same month. So if I was
> missing data for 20060430 and there was data for 20060429 then copy the da
ta
> from 20060429 changing the date to 20060430. If I was missing 20060430 and
> 20060429 then use the data from 20060428.
> If I was missing 20060501 do not use the data from 20060430. The data need
s
> to be in the same month. In this case I would need to use the data from
> 20060502 if this was available or the next day in May when the data is
> available. If the data is not available then take no action until it is.
> I have a calendar table if needed.
> Thanks to anyone who could help.
> CREATE TABLE AssetValues
> (
> AssetValueDate datetime,
> Category char(1),
> Subcategory int
> )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 1 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','B', 2 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','C', 3 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 4 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 5 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','C', 6 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','A', 7 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 8 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 9 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','A', 10 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','B', 11 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 12 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 13 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','B', 14 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','C', 15 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 16 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 17 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','C', 18 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','A', 19 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 20 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 21 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','A', 22 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','B', 23 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 24 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 25 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','B', 26 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','C', 27 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 28 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 29 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','C', 30 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','A', 31 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 32 )
>
> DROP TABLE AssetValues
>
>|||This duplicates all rows from the previous day. Is that what you want?
CREATE TABLE Dates (DateValue DATETIME PRIMARY KEY)
DECLARE @.Date DATETIME
, @.MaxDate DATETIME
SELECT @.Date = MIN(AssetValueDate)
, @.MaxDate = MAX(AssetValueDate)
FROM AssetValues
WHILE @.Date <= @.MaxDate BEGIN
INSERT Dates
SELECT @.Date
SET @.Date = @.Date + 1
END
GO
SELECT DateValue
, AssetValueDate
, Category
, Subcategory
FROM AssetValues av
, Dates
WHERE YEAR(DateValue) = YEAR(AssetValueDate)
AND
MONTH(DateValue) = MONTH(AssetValueDate)
AND
EXISTS
(
SELECT *
FROM AssetValues av1
WHERE AssetValueDate BETWEEN av.AssetValueDate AND DateValue
HAVING COUNT(DISTINCT AssetValueDate) = 1
)
ORDER BY DateValue, AssetValueDate
--Alan
Terri wrote:
> I have a table AssetValues that contains several rows of data for a
> particular date. The table has missing dates and I need to populate these
> missing dates one-time to create historical data and then daily to populat
e
> missing data on an ongoing basis. The missing dates are not related to the
> particular day of the w.
> The logic for inserting new rows is as follows:
> If there is a date without data, insert data using the previous date that
> has data as long as the previous day is in the same month. So if I was
> missing data for 20060430 and there was data for 20060429 then copy the da
ta
> from 20060429 changing the date to 20060430. If I was missing 20060430 and
> 20060429 then use the data from 20060428.
> If I was missing 20060501 do not use the data from 20060430. The data need
s
> to be in the same month. In this case I would need to use the data from
> 20060502 if this was available or the next day in May when the data is
> available. If the data is not available then take no action until it is.
> I have a calendar table if needed.
> Thanks to anyone who could help.
> CREATE TABLE AssetValues
> (
> AssetValueDate datetime,
> Category char(1),
> Subcategory int
> )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 1 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','B', 2 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','C', 3 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 4 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 5 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','C', 6 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','A', 7 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 8 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 9 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','A', 10 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','B', 11 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 12 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 13 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','B', 14 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','C', 15 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 16 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 17 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','C', 18 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','A', 19 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 20 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 21 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','A', 22 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','B', 23 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 24 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 25 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','B', 26 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','C', 27 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 28 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 29 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','C', 30 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','A', 31 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 32 )
>
> DROP TABLE AssetValues|||Thanks that worked. The only thing I still need to change is that the script
populated all the days in May from May 2nd data which was the most recent
data I had. I think I can add a date filter that will prevent population of
future dates.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:87565D10-F136-4D26-988B-5208CBEED90A@.microsoft.com...
> Try,
> create view v1
> as
> select a.dt
> from dbo.calendar as a
> where not exists (
> select * from dbo.AssetValues as b
> where b.AssetValueDate = a.dt
> )
> and a.dt between convert(char(6), (select min(AssetValueDate) from
> dbo.AssetValues), 112) + '01' and dateadd(day, -1, dateadd(month, 1,
> convert(char(6), (select max(AssetValueDate) from dbo.AssetValues), 112) +
> '01'))
> go
> select *
> from v1
> go
> -- previous available day
> insert into dbo.AssetValues
> select
> v1.dt, t1.Category, t1.Subcategory
> from
> v1 inner join dbo.AssetValues as t1
> on t1.AssetValueDate = (select max(a.AssetValueDate) from dbo.AssetValues
> as a where a.AssetValueDate < v1.dt and datediff(month, a.AssetValueDate,
> v1.dt) = 0)
> go
> -- next available day
> insert into dbo.AssetValues
> select
> v1.dt, t1.Category, t1.Subcategory
> from
> v1 inner join dbo.AssetValues as t1
> on t1.AssetValueDate = (select min(a.AssetValueDate) from dbo.AssetValues
> as a where a.AssetValueDate > v1.dt and datediff(month, v1.dt,
> a.AssetValueDate) = 0)
> go
>
> AMB
>|||No, but thanks. Sometimes I need to populate a date with data from a more
recent date when that data becomes available.
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1146684129.666739.113960@.j73g2000cwa.googlegroups.com...
> This duplicates all rows from the previous day. Is that what you want?
> CREATE TABLE Dates (DateValue DATETIME PRIMARY KEY)
> DECLARE @.Date DATETIME
> , @.MaxDate DATETIME
> SELECT @.Date = MIN(AssetValueDate)
> , @.MaxDate = MAX(AssetValueDate)
> FROM AssetValues
> WHILE @.Date <= @.MaxDate BEGIN
> INSERT Dates
> SELECT @.Date
> SET @.Date = @.Date + 1
> END
> GO
> SELECT DateValue
> , AssetValueDate
> , Category
> , Subcategory
> FROM AssetValues av
> , Dates
> WHERE YEAR(DateValue) = YEAR(AssetValueDate)
> AND
> MONTH(DateValue) = MONTH(AssetValueDate)
> AND
> EXISTS
> (
> SELECT *
> FROM AssetValues av1
> WHERE AssetValueDate BETWEEN av.AssetValueDate AND DateValue
> HAVING COUNT(DISTINCT AssetValueDate) = 1
> )
> ORDER BY DateValue, AssetValueDate
> --Alan
> Terri wrote:
these
populate
the
that
data
and
needs
>

Wednesday, March 21, 2012

Files are missing or not registered...files not registered

Hello,
I am having a problem connecting to SQL server 2000 through enterprise manager. Whenever i try to look at a table, view or DTS package I get the message...

"The query cannot be executed because some files are either missing or not registered. Run setup again to make sure the required files are registered."

I have tried to re-register, unregister and re-register.

I have tried to create a new instance of SQL server and attach the Data files to this.

I have tried to Re-install client tools.

The only option I am left with is totally removing SQL server and re-installing then restoring the Databases from a backup tape...
I just wanted to know if anyone has anyother ideas before i go ahead and do this.
Thanks in advance.

JF

One more thing...
I can access all of the tables through access and excel and other data analyzers as I normally would.hi
have you tried checking the error on microsoft tech net, they dont always mean what they say. have had lot of success in the past

all the best

Originally posted by josef fukano
Hello,
I am having a problem connecting to SQL server 2000 through enterprise manager. Whenever i try to look at a table, view or DTS package I get the message...

"The query cannot be executed because some files are either missing or not registered. Run setup again to make sure the required files are registered."

I have tried to re-register, unregister and re-register.

I have tried to create a new instance of SQL server and attach the Data files to this.

I have tried to Re-install client tools.

The only option I am left with is totally removing SQL server and re-installing then restoring the Databases from a backup tape...
I just wanted to know if anyone has anyother ideas before i go ahead and do this.
Thanks in advance.

JF

One more thing...
I can access all of the tables through access and excel and other data analyzers as I normally would.|||Originally posted by kpaynter
hi
have you tried checking the error on microsoft tech net, they dont always mean what they say. have had lot of success in the past

all the best

kpaynter

I found this article on microsoft tech net...

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b315868

On it has commands to be ran in command promt.
It did the trick, thank you very much for your reply...
You saved my 3 day weekend!!!!
Thanks again,
JF

Sunday, February 26, 2012

File Size - Probably missing something basic...

We have one main data file, and I would like to track how
fast it grows.
In the Task Pad of SQL Server it gives the file size as X
By running "select * from sysfiles" I get Y,
But by looking at the actual file size I get Z.
Can anyone tell me whats happening ?
JI'm not sure what you mean by the taskpad of SQL Server.. but here goes.
The sysfiles table shows the size of your database is 8k data pages. So you
need to multiply that number by 8.
For example: select * from sysfiles (while in my master database) reveals
the following two files.
size: 1464
size: 96
If I multiply those out as: SELECT ((1464 * 8) + (96 * 8)) you get about 12
MBs
Now if I go out to my: \Program Files\Microsoft SQL Server\MSSQL\Data
My master.mdb file is 11.4 MB
and the ldf file is .7 MB
That makes just over 12 MB... So these are the same.
If I right-click the database in Enterprise Manager and choose properties, I
get just over 12 MB's...
So, these are all the same size as far as I can see...
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:4cb201c490f4$f09a23b0$a501280a@.phx.gbl...
> We have one main data file, and I would like to track how
> fast it grows.
> In the Task Pad of SQL Server it gives the file size as X
> By running "select * from sysfiles" I get Y,
> But by looking at the actual file size I get Z.
> Can anyone tell me whats happening ?
> J|||Thanks for that.
What I mean by taskpad is if you go to SQL Server, select
the database, then View -> Taskpad it shows the size of
the file.
J
>--Original Message--
>I'm not sure what you mean by the taskpad of SQL
Server.. but here goes.
>The sysfiles table shows the size of your database is 8k
data pages. So you
>need to multiply that number by 8.
>For example: select * from sysfiles (while in my master
database) reveals
>the following two files.
>size: 1464
>size: 96
>If I multiply those out as: SELECT ((1464 * 8) + (96 *
8)) you get about 12
>MBs
>
>Now if I go out to my: \Program Files\Microsoft SQL
Server\MSSQL\Data
>My master.mdb file is 11.4 MB
>and the ldf file is .7 MB
>That makes just over 12 MB... So these are the same.
>If I right-click the database in Enterprise Manager and
choose properties, I
>get just over 12 MB's...
>
>So, these are all the same size as far as I can see...
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>
>
>"Jimbo" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4cb201c490f4$f09a23b0$a501280a@.phx.gbl...
>> We have one main data file, and I would like to track
how
>> fast it grows.
>> In the Task Pad of SQL Server it gives the file size as
X
>> By running "select * from sysfiles" I get Y,
>> But by looking at the actual file size I get Z.
>> Can anyone tell me whats happening ?
>> J
>
>.
>

File Size - Probably missing something basic...

We have one main data file, and I would like to track how
fast it grows.
In the Task Pad of SQL Server it gives the file size as X
By running "select * from sysfiles" I get Y,
But by looking at the actual file size I get Z.
Can anyone tell me whats happening ?
JI'm not sure what you mean by the taskpad of SQL Server.. but here goes.
The sysfiles table shows the size of your database is 8k data pages. So you
need to multiply that number by 8.
For example: select * from sysfiles (while in my master database) reveals
the following two files.
size: 1464
size: 96
If I multiply those out as: SELECT ((1464 * 8) + (96 * 8)) you get about 12
MBs
Now if I go out to my: \Program Files\Microsoft SQL Server\MSSQL\Data
My master.mdb file is 11.4 MB
and the ldf file is .7 MB
That makes just over 12 MB... So these are the same.
If I right-click the database in Enterprise Manager and choose properties, I
get just over 12 MB's...
So, these are all the same size as far as I can see...
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:4cb201c490f4$f09a23b0$a501280a@.phx.gbl...
> We have one main data file, and I would like to track how
> fast it grows.
> In the Task Pad of SQL Server it gives the file size as X
> By running "select * from sysfiles" I get Y,
> But by looking at the actual file size I get Z.
> Can anyone tell me whats happening ?
> J|||Thanks for that.
What I mean by taskpad is if you go to SQL Server, select
the database, then View -> Taskpad it shows the size of
the file.
J

>--Original Message--
>I'm not sure what you mean by the taskpad of SQL
Server.. but here goes.
>The sysfiles table shows the size of your database is 8k
data pages. So you
>need to multiply that number by 8.
>For example: select * from sysfiles (while in my master
database) reveals
>the following two files.
>size: 1464
>size: 96
>If I multiply those out as: SELECT ((1464 * 8) + (96 *
8)) you get about 12
>MBs
>
>Now if I go out to my: \Program Files\Microsoft SQL
Server\MSSQL\Data
>My master.mdb file is 11.4 MB
>and the ldf file is .7 MB
>That makes just over 12 MB... So these are the same.
>If I right-click the database in Enterprise Manager and
choose properties, I
>get just over 12 MB's...
>
>So, these are all the same size as far as I can see...
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>
>
>"Jimbo" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4cb201c490f4$f09a23b0$a501280a@.phx.gbl...
how[vbcol=seagreen]
X[vbcol=seagreen]
>
>.
>

File Size - Probably missing something basic...

We have one main data file, and I would like to track how
fast it grows.
In the Task Pad of SQL Server it gives the file size as X
By running "select * from sysfiles" I get Y,
But by looking at the actual file size I get Z.
Can anyone tell me whats happening ?
J
I'm not sure what you mean by the taskpad of SQL Server.. but here goes.
The sysfiles table shows the size of your database is 8k data pages. So you
need to multiply that number by 8.
For example: select * from sysfiles (while in my master database) reveals
the following two files.
size: 1464
size: 96
If I multiply those out as: SELECT ((1464 * 8) + (96 * 8)) you get about 12
MBs
Now if I go out to my: \Program Files\Microsoft SQL Server\MSSQL\Data
My master.mdb file is 11.4 MB
and the ldf file is .7 MB
That makes just over 12 MB... So these are the same.
If I right-click the database in Enterprise Manager and choose properties, I
get just over 12 MB's...
So, these are all the same size as far as I can see...
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:4cb201c490f4$f09a23b0$a501280a@.phx.gbl...
> We have one main data file, and I would like to track how
> fast it grows.
> In the Task Pad of SQL Server it gives the file size as X
> By running "select * from sysfiles" I get Y,
> But by looking at the actual file size I get Z.
> Can anyone tell me whats happening ?
> J
|||Thanks for that.
What I mean by taskpad is if you go to SQL Server, select
the database, then View -> Taskpad it shows the size of
the file.
J

>--Original Message--
>I'm not sure what you mean by the taskpad of SQL
Server.. but here goes.
>The sysfiles table shows the size of your database is 8k
data pages. So you
>need to multiply that number by 8.
>For example: select * from sysfiles (while in my master
database) reveals
>the following two files.
>size: 1464
>size: 96
>If I multiply those out as: SELECT ((1464 * 8) + (96 *
8)) you get about 12
>MBs
>
>Now if I go out to my: \Program Files\Microsoft SQL
Server\MSSQL\Data
>My master.mdb file is 11.4 MB
>and the ldf file is .7 MB
>That makes just over 12 MB... So these are the same.
>If I right-click the database in Enterprise Manager and
choose properties, I
>get just over 12 MB's...
>
>So, these are all the same size as far as I can see...
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>
>
>"Jimbo" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:4cb201c490f4$f09a23b0$a501280a@.phx.gbl...
how[vbcol=seagreen]
X
>
>.
>