Problem with query that list pr month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with query that list pr month

Author

Topic

mortency
Starting Member

4 Posts

Posted - 2004-10-04 : 09:56:52

I have problems with a query that i cant se to solve. Maybe someone here can help a norwegian guy????

The database have about 10000 posts with cars.

i have made this query:

SELECT DISTINCT
BID,Regnr,ANavn,GjelderFra,SiOppForsikringFra,Aktiv,
CASE WHEN SiOppForsikringFra is null THEN
DateDiff(d, GjelderFra, getdate())
ELSE
DateDiff(d, GjelderFra, SioppForsikringFra)
END AS AntDays

FROM
marsh_HKL.tblbiler, (biler is cars)
marsh_HKL.tblAvdeling (avdeling is department)

WHERE

FKBRAvdeling=AID
ORDER BY ANAVN ASC

The data is
Regnr RegNo of the car
ANavn is department name
GjelderFra is the date the car will be active from
SiOppForsikringFra is the date the cars shouldent be active more from
Aktiv is if the car is active YES/NO

You may see the tabels her --> http://www.cyren.no/diagram3.gif

I want the output like this:

Department(ANavn)
JANUAR 2003
VF54423(RegNr) 21.01.2003(GjelderFra) 23.01.2003(SiOppforsikringFra) 3(AntDays)
VF54422(RegNr) 21.01.2003(GjelderFra) 23.03.2003(SiOppforsikringFra) 9(AntDays)
SUM JANUAR 12 days 2 cars

FEBRUARY 2003
VF54422(RegNr) 21.01.2003(GjelderFra) 23.03.2003(SiOppforsikringFra) 30(AntDays)
SUM JANUAR 30 days 1 car

MARS 2003
VF54422(RegNr) 21.01.2003(GjelderFra) 23.03.2003(SiOppforsikringFra) 23(AntDays)
SUM JANUAR 23 days 1 car

Is this possible?

Or must the query be somthing like this? But together?

SELECT
Month(gjelderFra) as Month1,
Year(gjelderFra) as Year1,
Count(Regnr) as total

FROM
marsh_HKL.tblbiler (biler is cars)
WHERE FKBRAvdeling=184
Group by Month(GjelderFra), Year(gjelderFra)

SELECT
Month(SioppForsikringFra) as Month1,
Year(SioppForsikringFra) as year1,
Count(Regnr) as total

FROM
marsh_HKL.tblbiler (biler is cars)
WHERE FKBRAvdeling=184
Group by Month(SioppForsikringFra), Year(SioppForsikringFra)

//Newbie Morten

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-04 : 11:57:00

It would be easier to help if you write table structure as create statements and add some data with insert. (use the button with the '#' sign to insert code and keep its format)

Check if these selects helps you (I did not test them)

--Detail
Select ANavn, datepart(month, isnull(SiOppForsikringFra,getDate())) as detMonth, 
    Regnr, GjelderFra, SiOppforsikringFra
    CASE WHEN SiOppForsikringFra is null THEN 
        DateDiff(d, GjelderFra, getdate()) 
    ELSE 
        DateDiff(d, GjelderFra, SioppForsikringFra) 
    END AS AntDays
FROM marsh_HKL.tblbiler, marsh_HKL.tblAvdeling 
WHERE FKBRAvdeling=AID
ORDER BY SiOppForsikringFra, ANAVN ASC

--Summary
Select datepart(month, isnull(SiOppForsikringFra,getDate())) as sumMonth, AIDsum, 
    (CASE WHEN SiOppForsikringFra is null THEN 
        DateDiff(d, GjelderFra, getdate()) 
    ELSE 
        DateDiff(d, GjelderFra, SioppForsikringFra) 
    END) AS SumAntDays
FROM marsh_HKL.tblbiler, marsh_HKL.tblAvdeling 
WHERE FKBRAvdeling=AID
Group BY datepart(month, isnull(SiOppForsikringFra,getDate())),AID

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-10-04 : 11:59:57

1st

Change

CASE WHEN SiOppForsikringFra is null THEN
DateDiff(d, GjelderFra, getdate())
ELSE
DateDiff(d, GjelderFra, SioppForsikringFra)
END AS AntDays

To

AntDays = CASE WHEN SiOppForsikringFra is null THEN
DateDiff(d, GjelderFra, getdate())
ELSE
DateDiff(d, GjelderFra, SioppForsikringFra)
END

Second I would handle the report gouping on the front end.

Jim
Users <> Logic

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-08 : 10:17:34

[code]Select ANavn, datepart(month, isnull(SiOppForsikringFra,getDate())) as detMonth,
Regnr, GjelderFra, SiOppforsikringFra
CASE WHEN SiOppForsikringFra is null THEN
DateDiff(d, GjelderFra, getdate())
ELSE
DateDiff(d, GjelderFra, SioppForsikringFra)
END AS AntDays
FROM marsh_HKL.tblbiler inner join marsh_HKL.tblAvdeling on
marsh_HKL.tblbiler.xyz = marsh_HKL.tblAvdeling.xyz --LINK THE TWO TABLES
INNER JOIN
(Select datepart(month, isnull(SiOppForsikringFra,getDate())) as sumMonth, AIDsum,
(CASE WHEN SiOppForsikringFra is null THEN
DateDiff(d, GjelderFra, getdate())
ELSE
DateDiff(d, GjelderFra, SioppForsikringFra)
END) AS SumAntDays
FROM marsh_HKL.tblbiler, marsh_HKL.tblAvdeling
Group BY datepart(month, isnull(SiOppForsikringFra,getDate())),AID) summary ON
marsh_HKL.tblbiler.xyz = SUMMARY.xyz --LINK SUMMARY WITH THE OTHER TABLES
ORDER BY SiOppForsikringFra, ANAVN ASC[/code]

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-08 : 10:22:42

quote:
Originally posted by JimL

1st

Change

CASE WHEN SiOppForsikringFra is null THEN
DateDiff(d, GjelderFra, getdate())
ELSE
DateDiff(d, GjelderFra, SioppForsikringFra)
END AS AntDays

To

AntDays = CASE WHEN SiOppForsikringFra is null THEN
DateDiff(d, GjelderFra, getdate())
ELSE
DateDiff(d, GjelderFra, SioppForsikringFra)
END

Second I would handle the report gouping on the front end.

Jim
Users <> Logic

?? Why do that, Jim ? Both are legal and are equivalent.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources