Query for Table structure

Hi,
I'm looking for some code that will pull back a list all tables in a database that meet a certain requirement - in this case all tables that have an identity column.
Can anyone point me in the right direction?
Cheers
Gregselect object_name(id),* from syscolumns
where autoval is not null

Query every entry not in each hour

Hello,

I have been having a tough time writing the follow requirement for a
query.

On a table that the primary key is a tagId and an hourly timestamp, I
would like to find out for every hour which tags did not get entered
into the database. Essentially I am looking for patterns of entries
that are not making it into tableB.

Examples of the tables:
TableA TableB
TagID and TagName TagId Timestamp
PK PK1 PK2

approx 6000 rows approx 6000 rows per hour

I am thinking that I will need to do something like:

Select tableB1.time, count(*) from tableB1 group by tableB1.time
having tableB1.time >= XXXX and tableB1.time <= XXXX and tableB1.tagId
not in (select tagId from tableA where not exists (select
tableA.tagId, distinct.tableB2.time from tableB2)

I have been trying to create an effecient query handle this but have
not had any luck. Any assistance would be more then appreciated.

Thanks,
AndyThis requires a third tables. It would have a row for every hourly
timestamp, regardless of whether there are rows for that timestamp for
the table you described. You MIGHT get away without that table by
deriving it from TableB, but I suspect that would result in gaps.

The query performs a cross join, then eliminates rows that match
TableB using EXISTS.

SELECT *
FROM TableA as A
CROSS JOIN
TimeStamps as C
WHERE NOT EXISTS
(select * from TableB as B
where A.TagiD = B.TagIC
and C.Timestamp = B.Timestamp)

Roy Harvey
Beacon Falls, CT

On Tue, 17 Jul 2007 17:22:09 -0000, mcdonaghandy@.gmail.com wrote:

Quote:

Originally Posted by

>Hello,
>
>I have been having a tough time writing the follow requirement for a
>query.
>
>On a table that the primary key is a tagId and an hourly timestamp, I
>would like to find out for every hour which tags did not get entered
>into the database. Essentially I am looking for patterns of entries
>that are not making it into tableB.
>
>Examples of the tables:
>TableA TableB
>TagID and TagName TagId Timestamp
>PK PK1 PK2
>
>approx 6000 rows approx 6000 rows per hour
>
>I am thinking that I will need to do something like:
>
>Select tableB1.time, count(*) from tableB1 group by tableB1.time
>having tableB1.time >= XXXX and tableB1.time <= XXXX and tableB1.tagId
>not in (select tagId from tableA where not exists (select
>tableA.tagId, distinct.tableB2.time from tableB2)
>
>I have been trying to create an effecient query handle this but have
>not had any luck. Any assistance would be more then appreciated.
>
>Thanks,
>Andy

Query Brain Teaser - Revenue Projections

I have a requirement (motivated by a SOX thing) that is just giving me
fits. I know it should be easy and I'm probably overthinking it, but I
just can seem to find the best way to get where I need to go.

I have some payment projection data derived from a huge procedure that
I'm dumping into a temp table that looks like looks this:

Key Pd Start End AnnualAmt MonthAmt DailyAmt
6789 1 2005-06-01 2010-05-31 49,500.00 4,125.00 135.616438
6789 2 2010-06-01 2015-05-31 54,450.00 4,537.50 149.178082
6789 3 2015-06-01 2020-05-31 59,895.00 4,991.25 164.095890
6789 4 2020-06-01 2024-05-31 65,884.50 5,490.38 180.505479

(there are actually 6 levels of keys, but you get the idea)

I need it to get into a reporting table looking like this:

Key Rev Year ProjectedAmt
6789 2005 29,021.92
6789 2006 49,500.00
6789 2007 49,500.00
6789 2008 49,500.00
6789 2009 49,500.00
6789 2010 20,478.08

6789 2010 31,924.11
6789 2011 54,450.00
6789 2012 54,450.00
6789 2013 54,450.00
6789 2014 54,450.00
6789 2015 22,525.88

6789 2015 35,117.40
6789 2016 59,895.00
6789 2017 59,895.00
6789 2018 59,895.00
6789 2019 59,895.00
6789 2020 24.779.10

etc...

I'm having a problem wrapping my head around how to get the rows in the
middle of each period.

The other, probably minor and statistically insignificant, issue is
proration on a leap year. If a proration occurs on a leap year and I
have to calculate the proration based on a DATEDIFF and an Annual or
Monthly Amount, I'm going to be a day over.

Anybody have any tricks or ideas?

Thanks so much for your help!

JodyFirst, are your interest rules based upon Annual rates, monthly rates,
ro daily rates. You need to know this in order to figure out what is
wrong with your current data. Currently, either your annual or your
daily data is wrong for leap year.

build a table that contains the start of each revenue year.

so, the entry would be
2005 1/1/2005
2006 1/1/2006
2007 1/1/2007
etc.

I will get lamblasted for this, but I would use a, gasp, cursor. Scroll
through your years table calculating the payments for each year, for a
given key/period..
To do this, Using Datediff, calculate the number of days between the
beginning of the year and the end of the year. For the beginning and
end, you will do calcs either on daily or percentages of the annual.

Someone could probably figure out a set based solution to this.
Personally, my brain isn't big enough. There is not that much data, and
a cursor is easier to think about on this one.

Outside of this inner cursor, have another cursor scrolling through
each of the key/periods.
So, two nested cursors. First one is Key/period. Inner is the years.
Make sense?|||Doug:

Thanks for the reply. There's nothing wrong with the data (at least
not up to this point in the process) and I'm not calculating interest,
just the prorated Annual Amount by year.

The Annual Amount is the base, is never adjusted up for a leap year and
could make the full years easy if it makes sense to use it.

MonthlyAmt is Annual/12 and isn't really worth much in this context.

DailyAmt is just Annual/365 (366 in a leap year) and is necessary for
partial month proration. I suppose I could create a date table for the
next 25 years with no 2/29's in it, and do my DATEDIFFs against that...

I was hoping to avoid the cursor thing...

Jody|||I got it! (and it doesn't run too bad...)

SELECTpt.Key,
pt.Period,
y.Year AS RevYear,
CASE WHEN Year = DATEPART(YEAR,Start) THEN

DailyAmt*DATEDIFF(DAY,Start,DATEADD(MS,-3,DATEADD(YY,DATEDIFF(YY,0,Start
)+1,0)+1))
WHEN Year = DATEPART(YEAR,End) THEN
DailyAmt*DATEDIFF(DAY,DATEADD(YY, DATEDIFF(YY,0,End),0)-1,End)
ELSE DailyAmt*365
END as ProratedAnnualRent
INTO Report
FROM#ProjectionTemp pt
JOIN (SELECTDISTINCT Year
FROMdim_fiscal_day) y
ON y.Year >= DATEPART(YEAR,pt.Start)
AND y.Year <= DATEPART(YEAR,pt.End)