Thread: Query table to compare last mo to current mo

This would be easy with the SQL EXCEPT command, but I don't think that exists in SQL 2000. In this case, you would need to do a self join using LEFT JOIN and only return the records where the joined-to values are NULL.

That's as clear as mud, I know -- so let me work on a solution.

[edit]
I didn't try this out, but it should work or be close:

Code:

Select A.*, isNull(B.OldFlag, '(new)')
 From myTable A
 Left Join (
    Select EQ, 'Old' as OldFlag
     from myTable   
     Where MonthYr <= '2009-11-01'
    ) B
 On (A.EQ = B.EQ)
 Where A.MonthYr = '2009-12-01'
   And B.OldFlag is null

Note that I just hard coded the dates in, not optimal. If you can, use a parameter for the starting date.

Code:

Select A.*, isNull(B.OldFlag, '(new)')
 From myTable A
 Left Join (
    Select EQ, 'Old' as OldFlag
     from myTable   
     Where MonthYr <= dateadd(m, -1, @rptMonth)
    ) B
 On (A.EQ = B.EQ)
 Where A.MonthYr = @rptMonth
   And B.OldFlag is null

and set @rptMonth to something like '2009-12-01' for December.
[/edit]

Goodness -- no bloody clue. That should work!

For now, change it to just B.OldFlag to see if the rest of the query works -- perhaps there is a syntax error elsewhere that is confounding the parser.

Ach! Sorry, I thought you were using SQL Server 2000 -- never looked at what subforum I was in. ISNULL don't work there the same way. Change it to iif(isNull(B.OldFlag), '(new)', B.OldFlag)

OldFlag is a column created by the subquery. It can be any value you want. In fact, it really isn't necessary since you could do the same thing by using B.EQ from the subquery.

lol, im guessing you don't actually have (new) in your data.

you were just pointing out the records in question,.

As long as you have an auto number, we can get your result.

Yeah, as usual there are often several ways to do the same thing. After review, I realized that the Old/New flag is silly -- the only records returned are the new records anyway.

Code:

Select A.*
 From myTable A
 Left Join (
    Select EQ
     from myTable   
     Where MonthYr <= '2009-11-01'
    ) B
 On (A.EQ = B.EQ)
 Where A.MonthYr = '2009-12-01'
   And B.EQ is null

Thanks AOG and W0lffy...yup, no (new) in data, just in example on here.

Ok, I am getting about 900 records now with the below, but I now need to tie in another table to linking by cost ctr to get it loc name. Ex: my tblLoc looks like (LocName - LocCostCtr), tblMain below has a field named CostCtr, but I tried this with the query below it gave me grief about having the * on A.*. Next I tried to just create a new query with the query below and tblLoc but when I pull in newquery below it was blank

1)Should I create a make table from the results I get and then link to tblLoc?
2) grrr...ok, sorry on the below I realized I need to only see if its new to it cost center/loc, example it could be new in the tblMain in 12/1/2009 but also see what cc it was/if it was new to its 12/1/2009 loc...let me think about this one some more...its confusing.

Code:

Select A.*
 From tblMain A
 Left Join (
    Select EQ
     from tblMain   
     Where MonthYr <=#11/1/2009#
    ) B
 On (A.EQ = B.EQ)
 Where A.MonthYr =#12/1/2009#
   And B.EQ is null

Yeah, not sure that Access likes the A.* syntax or not. If it continues to complain, just specify all the columns you need explicitly.

You should be able to add the JOIN to tabloc to the query right before the Where clause. Just remember to include a table alias (C?).