Count days between to dates group by month

Hi i have an database that looks like this:
http://www.cyren.no/diagram3.gif
And the output here http://www.cyren.no/ods
(Se picture)
I want to get an query where a user can search in a date range.
The output should be 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
The error is if a car has been made inactive and the make a new post
with the same regnr. Or the sp works fine...
CREATE PROCEDURE sp_aktive
(
@.datoFOM datetime,
@.datoTOM datetime,
@.avdeling int
)
AS
Declare @.period as datetime
Set @.period =3D @.datoFOM
if(object_id('tempdb.dbo.#temp')<>1)
drop table #temp
if(object_id('tempdb.dbo.#temp2')<>1)
drop table #temp2
--SET dateformat dmy
If(object_id('tempdb.dbo.#periods')<>1)
drop table #periods
Select @.period as dato
into #periods
while @.Period < @.datoTOM
begin
Select @.Period =3D dateadd(month,1,@.period)
Insert into #periods
Select @.period
End
SELECT fkbravdeling, a.SiOppForsikringFra,
case when year(b.dato) =3D year(gjelderFra) and month(b.dato) =3D
month(gjelderFra) then
gjelderfra
else
b.dato
end as gjelderfra, a.Regnr
into #temp
FROM
cyren.tblbiler a
join
(select distinct cast('01' + datename(month, gjelderFra) + cast(year(
gjelderFra) as varchar) as datetime) as dato from cyren.tblbiler) b
on b.dato between cast('01' + datename(month, gjelderFra) + cast(year(
gjelderFra) as varchar) as datetime) and
coalesce(SiOppForsikringFra,'01jan2050')
join #periods c
on b.dato =3D c.dato
select *
into #temp2
from
(
SELECT anavn,
sum(
case when month(SiOppForsikringFra) =3D month(gjelderfra) and
year(SiOppForsikringFra) =3D year(gjelderfra)then
DateDiff(d, gjelderfra, SiOppForsikringFra) +1
else
DateDiff(d, gjelderfra,
dateadd(day, -1, '01' + datename(month, dateadd(month,1,
gjelderfra)) + cast(year(dateadd(month,1, gjelderfra)) as varchar)) +1
)
end
) as AntDays,
'Antall aktive biler denne m=E5ned' as regnr, count(*) as AntCars,
year(gjelderfra) as [year], month(gjelderfra) as [month],
datename(month,gjelderfra) + ' ' + cast(year(gjelderfra) as varchar)
as dato,
2 as [order]
FROM
#temp a join
cyren.tblAvdeling
on FKBRAvdeling=3DAID
WHERE fkbravdeling=3D@.avdeling AND gjelderfra between @.datoFOM and
@.datoTOM
group by anavn, month(gjelderfra), year(gjelderfra),
datename(month,gjelderfra) + ' ' + cast(year(gjelderfra) as varchar)
) a
union
(
SELECT anavn,
case when month(SiOppForsikringFra) =3D month(gjelderfra) and
year(SiOppForsikringFra) =3D year(gjelderfra)then
DateDiff(d, gjelderfra, SiOppForsikringFra) +1
else
DateDiff(d, gjelderfra,
dateadd(day, -1, '01' + datename(month, dateadd(month,1, gjelderfra))
+ cast(year(dateadd(month,1, gjelderfra)) as varchar)) + 1
)
end AntDays,
Regnr, 1 as AntCars ,year(gjelderfra) as [year], month(gjelderfra) as
[month],
datename(month,gjelderfra) + ' ' + cast(year(gjelderfra) as varchar)
as dato,
1 as [order]
FROM
#temp a join
cyren.tblAvdeling b
on FKBRAvdeling=3DAID
WHERE fkbravdeling=3D@.avdeling AND gjelderfra between @.datoFOM and
@.datoTOM
)
SELECT a.*, BID, GjelderFra, SiOppForsikringFra, Aktiv
FROM #Temp2 a
left join cyren.tblbiler b
on a.regnr =3D b.regnr and b.Fkbravdeling=3D@.avdeling--Denne er kun hvis
den skal kj=F8res p=E5 avdeling
order by anavn, [year], [month], [order]
GOOn 16 Jan 2006 05:04:07 -0800, web@.cyren.no wrote:

>Hi i have an database that looks like this:
>http://www.cyren.no/diagram3.gif
>And the output here http://www.cyren.no/ods
>(Se picture)
>
>I want to get an query where a user can search in a date range.
>
>The output should be like this:
(snip)
>The error is if a car has been made inactive and the make a new post
>with the same regnr. Or the sp works fine...
>CREATE PROCEDURE sp_aktive
(snip)
Hi web,
First: never use sp_ as the first three characters for your stored
procedure names. This prefixed is resserved for system stored
procedures. SQL Server will look for them in the master database first,
then in the current database. Apart from the performance hit, you'll run
into much more severe problems if MS adds a system stored procedure with
the same name in the next release or service pack...
Second: I don't understand what exactly it is you're asking. I've seen
the picture of the table structure, and the ~20,000 row report you've
posted links to (and I even have a feeling that I understand what some
of the Norwewgian words mean <g> ). But I fail to see what you want. And
the highly complex code in the stored proc you posted doesn't help
either.
A better way to describe your problem would be to post:
* CREATE TABLE statements for the tables involved. You may simplify them
by omitting irrelevant columns (and translating remaining column names
to English would not be a bad idea either), but please do include all
constraints and properties.
* INSERT statements for a few rows of sample data. The amount of data
should be enough to illustrate the problem you're trying to solve, but
not more. Of course, they should run against the CREATE TABLE statements
you post.
* Expected output (based on the posted sample data, of course).
* And a description of why the sample data has to yield the results you
posted - in other words, a description of the actual busniess problem
that you're trying to solve.
Check out www.aspfaq.com/5006 for some useful hints on how to assemble
the info for this follow-up post.
Hugo Kornelis, SQL Server MVP

Couldn't connect to reports

Hi,

I'm not able to browse through http://localhost/reports.

When I'm trying to connect it gives me "Server Error in '/Reports' Application."At the same time I'm able to view Reportserver.At the same time I'm able to view Reportserver.(i.e)http://localhost/reportserver

please can anyone suggest me some solution to this problem..

Server Error in '/Reports' Application.

---

The trust relationship between the primary domain and the trusted domain failed.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.SystemException: The trust relationship between the primary domain and the trusted domain failed.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SystemException: The trust relationship between the primary domain and the trusted domain failed.

]

System.Security.Principal.NTAccount.TranslateToSids(IdentityReferenceCollection sourceAccounts, Boolean& someFailed) +1167

System.Security.Principal.NTAccount.Translate(IdentityReferenceCollection sourceAccounts, Type targetType, Boolean& someFailed) +42

System.Security.Principal.NTAccount.Translate(IdentityReferenceCollection sourceAccounts, Type targetType, Boolean forceSuccess) +46

System.Security.Principal.WindowsPrincipal.IsInRole(String role) +205

System.Web.Configuration.AuthorizationRule.IsTheUserInAnyRole(StringCollection roles, IPrincipal principal) +120

System.Web.Configuration.AuthorizationRule.IsUserAllowed(IPrincipal user, String verb) +300

System.Web.Configuration.AuthorizationRuleCollection.IsUserAllowed(IPrincipal user, String verb) +191

System.Web.Security.UrlAuthorizationModule.OnEnter(Object source, EventArgs eventArgs) +3403086

System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92

System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

Hi,

What are the security settings in IIS for both sites?

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi,

we had given full control to the administrators as well as system users.

And read and execute permission to reportserveruser as well as webservice user.

Thanks

Anandhi

|||

That is for NTFS security. What are the permissions set on the virtual directory?

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi,

Execute Scripts only permission for both

Thanks

Anandhi

|||

I mean in the Directory Security Tab.

There you can see whether the site is accessed via:

anonymous (+ which account)|||

Sorry for misunderstanding,

We have chosen for Integrated Windows Authentication

Anonymous is not enabled.

Thanks,

Anandhi

|||

Is this the same as for the report server?

By default this virtual directory is mapping to a folder in your C:\Program Files\... (for me it is C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting Services\ReportManager). This means that the users of the http://localhost/reports url, must have read permissions to it.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Permissions for both reports and reportserver are one and the same.Read permission is there.And it is mapped to the folder as you had specified.

Thanks,

Anandhi

|||

Did you migrate from one domain to another?

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

No we havn't migrated to any other domain.

Thanks,

Anandhi

Could not connect to http://localhost/Reports

Hi,
I have a problem when I try to connect to Report Server which is installed
locally. (http://localhost/Reports). When I enter this address in the
address bar, the progress bar starts to grow but the page will not be
opened. Eventually, my PC will be frozen and I will have to restart my
machine.
The installation went without any problems. SQL server 2000 is installed
locally and the OS is Windows XP SP1. What confuses me the most is that
everything was working ok, several months ago. Exactly the same behavior is
on the other PC. Report Server installed locally is not responding.
I suppose that maybe some security patch caused this strange behavior but
this is only guessing.
What might be the reason for this?
Any idea will be appreciated.
Thanks in advanced,
Mladen
--
Message posted via http://www.sqlmonster.comCheck out the Event Viewer.
Also check the log files under the LOG folder. c:\program files\SQL
Server...
Do a search for LOG...
"Mladen via SQLMonster.com" wrote:
> Hi,
> I have a problem when I try to connect to Report Server which is installed
> locally. (http://localhost/Reports). When I enter this address in the
> address bar, the progress bar starts to grow but the page will not be
> opened. Eventually, my PC will be frozen and I will have to restart my
> machine.
> The installation went without any problems. SQL server 2000 is installed
> locally and the OS is Windows XP SP1. What confuses me the most is that
> everything was working ok, several months ago. Exactly the same behavior is
> on the other PC. Report Server installed locally is not responding.
> I suppose that maybe some security patch caused this strange behavior but
> this is only guessing.
> What might be the reason for this?
> Any idea will be appreciated.
> Thanks in advanced,
> Mladen
> --
> Message posted via http://www.sqlmonster.com
>|||I have tried everything (that is why I did not answer earlier).
I have reinstalled Reporting Services using Domain Admin account and still
is not working.
I used built-in account (NTAUTHORITY\SYSTEM) for ReportServer Windows
service and xxx\ASPNET (xxx is the name of my machine where I try to
install it) for auto start service account. Since nothing was working
before, now I used domain admin account to connect to the Report server
database. Of course, it can connect to Report Server database.
There are no errors in the Reporting Services logs and the last thing that
can be seen in the log is "Polling started".
NT Event log does not contain errors as well.
This is all frustrating and I don't know what else can I do to solve this
(or where to look).
--
Message posted via http://www.sqlmonster.com|||What is more "interesting" is that my colleagues all have the same problem.
Everything was working ok some time ago but one day it stopped functioning.
--
Message posted via http://www.sqlmonster.com|||What is more "interesting" is that my colleagues all have the same problem.
Everything was working ok some time ago but one day it stopped functioning.
--
Message posted via http://www.sqlmonster.com