Hi,
Does anyone know if it is possible to set up stored procedures which will create, modify, and disable SQL Server Agent Jobs?
I have a web based application which I need to enable an administrator to change the frequency which a job runs and encapsulating the modifications in a stored procedure would be the easiest way for me to integrate this, if it's possbible.
Regards, MattHi,
You may use sp_update_jobschedule, sp_update_job etc or if you create your own sp and use tables such as sysjobschedules, sysjobs etc.
Tables and procs are located in MSDB.
Ex for updating scheduled time:
UPDATE msdb.dbo.sysjobschedules
SET active_start_time = 164000
WHERE (job_id = '8A0F1080-D22A-4F82-AE13-68F789989D1D')
AND (name = 'Once')
Try this and let us know how it work
Regards|||Thanks Tommy,
Going to give this a shot this afternoon. I'll let you know how it turns out.
Showing posts with label based. Show all posts
Showing posts with label based. Show all posts
Thursday, March 29, 2012
Create a view to get latest status for each application
I would like some help creating a view that will display the latest status for each application. The lastest status should be based on CreateDt.
For example:
Table Structure:
============
Application: ApplicationID, Name, Address, City, State, Zip, etc..
ApplicationAction: ApplicationActionID, ApplicationID, Status (Ex:new, reviewed, approved, closed), CreateDt
View should display:
==============
ApplicantID, ApplicantActionID, Status, CreateDt
Example:
==========
ApplicantID=4, Name=Bob Smith, etc...
ApplicantActionID=1, ApplicantID=4, Status=New, CreatDt=1/3/20071:00
ApplicantActionID=2, ApplicantID=4, Status=Reviewed, CreatDt=1/3/2007 2:00
ApplicantActionID=3, ApplicantID=4, Status=Approved, CreatDt=1/4/2007 1:00
... etc...
View should return:
Applicant=4, ApplicantActionID=3, Status=Approved, CreatDt=1/4/2007 1:00
etc...
Hint: Use MAX(CreatDt) to get the information you need.|||
well I got that far, I need help getting beyond that:( Any ideas?
|||Can you post what you have so far?|||Oh well..
Declare @.ApTable (ApplicationIDint ,Name varchar(50), Addressvarchar(50), Cityvarchar(50), Statevarchar(50), Zipvarchar(50))Declare @.AATable (ApplicationActionIDint, ApplicationIDint, Statusvarchar(50), CreateDtdatetime)Insert into @.ApSELECt 4,'Bob Smith','123 street','SomeCity','SS','12345'UNIONALLSELECT 5,'New Smith','sss','ss','a','4455'Insert into @.AASELECT 1,4,'New','1/3/2007 1:00'UNIONALLSELECT 2,4,'Reviewed','1/3/2007 2:00'UNIONALLSELECT 3,4,'Approved','1/4/2007 1:00'UNIONALLSELECT 1,5,'New','5/24/2006 1:00'Select *from @.ApSelect *from @.AASELECT aa.*FROM (select applicationid,max(Createdt)as MxCreateDtfrom @.AAgroup by applicationid) Xjoin @.AA AAon aa.createdt = X.MXcreatedt
Tuesday, March 27, 2012
Create a view based on variables?
Hi All,
I would like to create a view based on a variable (a date).
CREATE VIEW testView (@.myDate)
AS
select * from testTable
WHERE testVar = @.myDate
--
This is far from what the final view would look like, but if anyone knows if something similiar to this can be done, I would greatly appreciate it.
Thanks.I would say "No, it can't." Why can't you just create the view without the where clause and select from it where the date = @.myDate when you need it? You could create a function that returns a table and takes the date as an argument.
create a view
How can I create a view which based on a store procedure and then sp will
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itself), and a SELECT
statement read data from tables or views, it doesn't execute stored procedures. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...
> How can I create a view which based on a store procedure and then sp will return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: "00KobeBrian" <a@.b.com>
>>Subject: create a view
>>Date: Tue, 11 Apr 2006 15:54:51 +0800
>>Lines: 4
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>>X-RFC2646: Format=Flowed; Original
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>>Message-ID: <OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 210.177.248.66
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:427533
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>How can I create a view which based on a store procedure and then sp will
>>return a select statement only? Thanks.
>>|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itself), and a SELECT
statement read data from tables or views, it doesn't execute stored procedures. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...
> How can I create a view which based on a store procedure and then sp will return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: "00KobeBrian" <a@.b.com>
>>Subject: create a view
>>Date: Tue, 11 Apr 2006 15:54:51 +0800
>>Lines: 4
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>>X-RFC2646: Format=Flowed; Original
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>>Message-ID: <OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 210.177.248.66
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:427533
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>How can I create a view which based on a store procedure and then sp will
>>return a select statement only? Thanks.
>>|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.
create a view
How can I create a view which based on a store procedure and then sp will
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itsel
f), and a SELECT
statement read data from tables or views, it doesn't execute stored procedur
es. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I
wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...seagreen">
> How can I create a view which based on a store procedure and then sp will
return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/d...n-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itsel
f), and a SELECT
statement read data from tables or views, it doesn't execute stored procedur
es. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I
wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...seagreen">
> How can I create a view which based on a store procedure and then sp will
return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/d...n-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.
Create a string from report parameters, return checksum
Hi,
We want to add an extra checksum parameter to our RS report, and make the
report work only if the correct checksum is entered/passed based on all the
other parameter fields, as a simple security check when rendering reports
from a system with its own security system (users not in AD/domains).
1. How do I construct a stored procedure that creates a string consisting of
all but the last parameter and returns a checksum ? (checksums we know how to
create from strings)
2. How do I filter my report based on that ?
"where ... and @.checksum = checksum_proc.checksum" ?
Report and parameters ex.
report1: product_string, checksum
report2: customer_string, department_string, date_string, checksum
Or is there a better way ?
/JeromeOn Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
wrote:
> Hi,
> We want to add an extra checksum parameter to our RS report, and make the
> report work only if the correct checksum is entered/passed based on all the
> other parameter fields, as a simple security check when rendering reports
> from a system with its own security system (users not in AD/domains).
> 1. How do I construct a stored procedure that creates a string consisting of
> all but the last parameter and returns a checksum ? (checksums we know how to
> create from strings)
> 2. How do I filter my report based on that ?
> "where ... and @.checksum = checksum_proc.checksum" ?
> Report and parameters ex.
> report1: product_string, checksum
> report2: customer_string, department_string, date_string, checksum
> Or is there a better way ?
> /Jerome
Here is another option you might want to consider. You might want to
have a report parameter that has a string datatype and is used as a
password. You might pass the password entered by the user back to the
stored procedure and if the password matches one in a list somewhere
(table, etc) you send the complete dataset back to the report;
otherwise, you send back no data or a single line of all nulls or a
text message of "you do not have correct permissions to access this
report" to the report. Also, to enforce security, you might create the
stored procedure using "with encryption" that way noone can look at
the logic -or- you could add a certain number of characters to the
true password in a table and just remove them when you do the table
lookup for the passwords. Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thanks,
The report must be started with a URL (cant render reports from our system)
and should only be allowed for a certain combinations of parameters set by
our program, ex product 100 with department A. The user must not seconds
later go to the reportserver and manually enter product 100 with department B
with the same password.
If using a "one-time" password parameter, will this mean we should store all
parameters in a table as well ? If we delete the stored password in the
procedure, is it possible for the user to re-render the report to Excel etc
?
A last checksum question: Is there a function to be used in a stored
procedure that gets current report's parameter 1, 2 ... ?
/Jerome k
"EMartinez" wrote:
> On Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
> wrote:
> > Hi,
> >
> > We want to add an extra checksum parameter to our RS report, and make the
> > report work only if the correct checksum is entered/passed based on all the
> > other parameter fields, as a simple security check when rendering reports
> > from a system with its own security system (users not in AD/domains).
> >
> > 1. How do I construct a stored procedure that creates a string consisting of
> > all but the last parameter and returns a checksum ? (checksums we know how to
> > create from strings)
> >
> > 2. How do I filter my report based on that ?
> > "where ... and @.checksum = checksum_proc.checksum" ?
> >
> > Report and parameters ex.
> > report1: product_string, checksum
> > report2: customer_string, department_string, date_string, checksum
> >
> > Or is there a better way ?
> >
> > /Jerome
>
> Here is another option you might want to consider. You might want to
> have a report parameter that has a string datatype and is used as a
> password. You might pass the password entered by the user back to the
> stored procedure and if the password matches one in a list somewhere
> (table, etc) you send the complete dataset back to the report;
> otherwise, you send back no data or a single line of all nulls or a
> text message of "you do not have correct permissions to access this
> report" to the report. Also, to enforce security, you might create the
> stored procedure using "with encryption" that way noone can look at
> the logic -or- you could add a certain number of characters to the
> true password in a table and just remove them when you do the table
> lookup for the passwords. Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||What do you propose to do to reject the user's request if it is invalid by
the rules (see thread: "Am I crazy or is there no form validation" in this
forum). I'm not arguing with you, just wondering what you think is the best
strategy here.
IAC, if I were faced with your requirement, I would probably have the users
submit their reporting URL to a small proxy web application that did the
validation you require against the user's credentials, etc. If the tests
passed (whatever they are) then the proxy would submit the request to the
report server and return the server's response.
By "small" I mean that you could probably get away with a simple APX page
for this, and in addition you would have the ability to return whatever type
of user feedback you wanted if the tests failed.
>L<
"/jerome k" <jeromek@.discussions.microsoft.com> wrote in message
news:8A257C82-B978-446C-A2B7-0FFAC3773B32@.microsoft.com...
> Thanks,
> The report must be started with a URL (cant render reports from our
> system)
> and should only be allowed for a certain combinations of parameters set by
> our program, ex product 100 with department A. The user must not seconds
> later go to the reportserver and manually enter product 100 with
> department B
> with the same password.
> If using a "one-time" password parameter, will this mean we should store
> all
> parameters in a table as well ? If we delete the stored password in the
> procedure, is it possible for the user to re-render the report to Excel
> etc
> ?
> A last checksum question: Is there a function to be used in a stored
> procedure that gets current report's parameter 1, 2 ... ?
> /Jerome k
> "EMartinez" wrote:
>> On Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
>> wrote:
>> > Hi,
>> >
>> > We want to add an extra checksum parameter to our RS report, and make
>> > the
>> > report work only if the correct checksum is entered/passed based on all
>> > the
>> > other parameter fields, as a simple security check when rendering
>> > reports
>> > from a system with its own security system (users not in AD/domains).
>> >
>> > 1. How do I construct a stored procedure that creates a string
>> > consisting of
>> > all but the last parameter and returns a checksum ? (checksums we know
>> > how to
>> > create from strings)
>> >
>> > 2. How do I filter my report based on that ?
>> > "where ... and @.checksum = checksum_proc.checksum" ?
>> >
>> > Report and parameters ex.
>> > report1: product_string, checksum
>> > report2: customer_string, department_string, date_string, checksum
>> >
>> > Or is there a better way ?
>> >
>> > /Jerome
>>
>> Here is another option you might want to consider. You might want to
>> have a report parameter that has a string datatype and is used as a
>> password. You might pass the password entered by the user back to the
>> stored procedure and if the password matches one in a list somewhere
>> (table, etc) you send the complete dataset back to the report;
>> otherwise, you send back no data or a single line of all nulls or a
>> text message of "you do not have correct permissions to access this
>> report" to the report. Also, to enforce security, you might create the
>> stored procedure using "with encryption" that way noone can look at
>> the logic -or- you could add a certain number of characters to the
>> true password in a table and just remove them when you do the table
>> lookup for the passwords. Hope this is helpful.
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
>>
We want to add an extra checksum parameter to our RS report, and make the
report work only if the correct checksum is entered/passed based on all the
other parameter fields, as a simple security check when rendering reports
from a system with its own security system (users not in AD/domains).
1. How do I construct a stored procedure that creates a string consisting of
all but the last parameter and returns a checksum ? (checksums we know how to
create from strings)
2. How do I filter my report based on that ?
"where ... and @.checksum = checksum_proc.checksum" ?
Report and parameters ex.
report1: product_string, checksum
report2: customer_string, department_string, date_string, checksum
Or is there a better way ?
/JeromeOn Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
wrote:
> Hi,
> We want to add an extra checksum parameter to our RS report, and make the
> report work only if the correct checksum is entered/passed based on all the
> other parameter fields, as a simple security check when rendering reports
> from a system with its own security system (users not in AD/domains).
> 1. How do I construct a stored procedure that creates a string consisting of
> all but the last parameter and returns a checksum ? (checksums we know how to
> create from strings)
> 2. How do I filter my report based on that ?
> "where ... and @.checksum = checksum_proc.checksum" ?
> Report and parameters ex.
> report1: product_string, checksum
> report2: customer_string, department_string, date_string, checksum
> Or is there a better way ?
> /Jerome
Here is another option you might want to consider. You might want to
have a report parameter that has a string datatype and is used as a
password. You might pass the password entered by the user back to the
stored procedure and if the password matches one in a list somewhere
(table, etc) you send the complete dataset back to the report;
otherwise, you send back no data or a single line of all nulls or a
text message of "you do not have correct permissions to access this
report" to the report. Also, to enforce security, you might create the
stored procedure using "with encryption" that way noone can look at
the logic -or- you could add a certain number of characters to the
true password in a table and just remove them when you do the table
lookup for the passwords. Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thanks,
The report must be started with a URL (cant render reports from our system)
and should only be allowed for a certain combinations of parameters set by
our program, ex product 100 with department A. The user must not seconds
later go to the reportserver and manually enter product 100 with department B
with the same password.
If using a "one-time" password parameter, will this mean we should store all
parameters in a table as well ? If we delete the stored password in the
procedure, is it possible for the user to re-render the report to Excel etc
?
A last checksum question: Is there a function to be used in a stored
procedure that gets current report's parameter 1, 2 ... ?
/Jerome k
"EMartinez" wrote:
> On Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
> wrote:
> > Hi,
> >
> > We want to add an extra checksum parameter to our RS report, and make the
> > report work only if the correct checksum is entered/passed based on all the
> > other parameter fields, as a simple security check when rendering reports
> > from a system with its own security system (users not in AD/domains).
> >
> > 1. How do I construct a stored procedure that creates a string consisting of
> > all but the last parameter and returns a checksum ? (checksums we know how to
> > create from strings)
> >
> > 2. How do I filter my report based on that ?
> > "where ... and @.checksum = checksum_proc.checksum" ?
> >
> > Report and parameters ex.
> > report1: product_string, checksum
> > report2: customer_string, department_string, date_string, checksum
> >
> > Or is there a better way ?
> >
> > /Jerome
>
> Here is another option you might want to consider. You might want to
> have a report parameter that has a string datatype and is used as a
> password. You might pass the password entered by the user back to the
> stored procedure and if the password matches one in a list somewhere
> (table, etc) you send the complete dataset back to the report;
> otherwise, you send back no data or a single line of all nulls or a
> text message of "you do not have correct permissions to access this
> report" to the report. Also, to enforce security, you might create the
> stored procedure using "with encryption" that way noone can look at
> the logic -or- you could add a certain number of characters to the
> true password in a table and just remove them when you do the table
> lookup for the passwords. Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||What do you propose to do to reject the user's request if it is invalid by
the rules (see thread: "Am I crazy or is there no form validation" in this
forum). I'm not arguing with you, just wondering what you think is the best
strategy here.
IAC, if I were faced with your requirement, I would probably have the users
submit their reporting URL to a small proxy web application that did the
validation you require against the user's credentials, etc. If the tests
passed (whatever they are) then the proxy would submit the request to the
report server and return the server's response.
By "small" I mean that you could probably get away with a simple APX page
for this, and in addition you would have the ability to return whatever type
of user feedback you wanted if the tests failed.
>L<
"/jerome k" <jeromek@.discussions.microsoft.com> wrote in message
news:8A257C82-B978-446C-A2B7-0FFAC3773B32@.microsoft.com...
> Thanks,
> The report must be started with a URL (cant render reports from our
> system)
> and should only be allowed for a certain combinations of parameters set by
> our program, ex product 100 with department A. The user must not seconds
> later go to the reportserver and manually enter product 100 with
> department B
> with the same password.
> If using a "one-time" password parameter, will this mean we should store
> all
> parameters in a table as well ? If we delete the stored password in the
> procedure, is it possible for the user to re-render the report to Excel
> etc
> ?
> A last checksum question: Is there a function to be used in a stored
> procedure that gets current report's parameter 1, 2 ... ?
> /Jerome k
> "EMartinez" wrote:
>> On Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
>> wrote:
>> > Hi,
>> >
>> > We want to add an extra checksum parameter to our RS report, and make
>> > the
>> > report work only if the correct checksum is entered/passed based on all
>> > the
>> > other parameter fields, as a simple security check when rendering
>> > reports
>> > from a system with its own security system (users not in AD/domains).
>> >
>> > 1. How do I construct a stored procedure that creates a string
>> > consisting of
>> > all but the last parameter and returns a checksum ? (checksums we know
>> > how to
>> > create from strings)
>> >
>> > 2. How do I filter my report based on that ?
>> > "where ... and @.checksum = checksum_proc.checksum" ?
>> >
>> > Report and parameters ex.
>> > report1: product_string, checksum
>> > report2: customer_string, department_string, date_string, checksum
>> >
>> > Or is there a better way ?
>> >
>> > /Jerome
>>
>> Here is another option you might want to consider. You might want to
>> have a report parameter that has a string datatype and is used as a
>> password. You might pass the password entered by the user back to the
>> stored procedure and if the password matches one in a list somewhere
>> (table, etc) you send the complete dataset back to the report;
>> otherwise, you send back no data or a single line of all nulls or a
>> text message of "you do not have correct permissions to access this
>> report" to the report. Also, to enforce security, you might create the
>> stored procedure using "with encryption" that way noone can look at
>> the logic -or- you could add a certain number of characters to the
>> true password in a table and just remove them when you do the table
>> lookup for the passwords. Hope this is helpful.
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
>>
Thursday, March 22, 2012
Create a database based on an existing one?
I want to create one databse with exactly the same tables as another, is that possible?Yes, you can use the Transfer object to create a script that recreates all objects in a target database. See the ScriptTransfer() method.
Friday, February 24, 2012
Couple of newbie questions
My company is moving to SQL Server soon, and I'm certainly excited about it. Currently we are using a FoxPro based solution which is slow and unreliable.
I'm working on the configuration and have a few questions. We are planning on going with a SQL Server 2000 Standard Edition running on Windows 2000 Server. Some of what Microsoft has said on the web site is very vague concerning the licensing and other maximums.
What is the max memory that you can use with SQL Standard on a Windows 2000 Server (not Advanced)?
If you have more memory in the server than SQL Standard will support, is that OK? For example, can you say, if SQL Standard maxes out at 2 GB, put in 3 GB, and let the OS have the rest? Our database will probably be around 2 GB to start.
Do you need seperate CALs on Windows 2000, or does it work like other apps like Exchange, where you only need the CAL's for the application (or in this case, the Processor licences).
For a 3 processor server, does anyone think that for about 60-70 users that I should go with a CAL or Processor license model?
Should I spring for the 2MB cache on the processor? Planning on a 3 way 700 Mhz Xeon.
Thanks in advance.According to Books Online:
1. What is the max memory that you can use with SQL Standard on a Windows 2000 Server (not Advanced)?
2GB is the maximum that SQL Std can use.
2. If you have more memory in the server than SQL Standard will support, is that OK? For example, can you say, if SQL Standard maxes out at 2 GB, put in 3 GB, and let the OS have the rest? Our database will probably be around 2 GB to start.
If the OS can see the additional RAM, this should be ok. I cannot think how much memory that Win2k std can use.
3. Do you need seperate CALs on Windows 2000, or does it work like other apps like Exchange, where you only need the CAL's for the application (or in this case, the Processor licences).
If you go with per processor licensing of SQL Server, that is all you need.
4. For a 3 processor server, does anyone think that for about 60-70 users that I should go with a CAL or Processor license model?
I found SQL2k Std per processor licensing at ~4500 multiplied by 3 you would be at ~$13,500
I found SQL2k Std 10 client for $2000. If you purchased 7 of these (70 user), you would be at ~$14,000
Right now, the price is about the same. You need to weigh the options, and look toward the future.
What happens if the 3 processor server needs 5 more processors? What happens if you add 40 more users? How will this impact your licenses (and cost)?
5. Should I spring for the 2MB cache on the processor? Planning on a 3 way 700 Mhz Xeon.
I don't know. What is the price difference between 2MB, 1MB, and 'normal' Xeons? We have an 8 way 700 with 2MB cache and things perform quite well. I am sure that we paid a premium for the 2MB chips...I do not know what performance would be like if we had lesser Xeons. Maybe someone else can help out here with some suggestions.
________________
Keith|||2. If you have more memory in the server than SQL Standard will support, is that OK? For example, can you say, if SQL Standard maxes out at 2GB, put in 3 GB, and let the OS have the rest? Our database will probably be around 2 GB to start.
Win2k and NT Standard see 4GIG max which is the memory space directly addressable by a a 32bit operating system.
The 2 gig limit for SQL Server comes from the fact that the NT architecture has a 2gig per process memory limit.
5. Should I spring for the 2MB cache on the processor? Planning on a 3 way 700 Mhz Xeon.
It's hard to say. All of our SQL Servers now have 2MB processor caches. We had one Dell 4-way server with 512K (standard) caches that severely under-performed and was replaced by a Compaq server. But the Compaq server was much more carefully specified so direct comparisons are tough. I know this. I've read of people with performance problems when CPU utilization is at 100% on all 4 processors. On our Compaq servers, when all four processors are at 100%, people can still work. It's a little slower, but almost goes unnoticed except by those that cause the performance spike (period end processes, some custom reports). On the Dell, if all 4 where at 100% no one moved. It would stop everyone in their tracks. Compaq's processor multi-processor architecture probably has something to do with it, but I bet the 2MB cache in each processor has something to do with it also.
If the cost is not unreasonable go for the 2mb cache. We choose slower processors with 2MB cache over a faster processor with 512k or 1mb and haven't looked back.|||Thanks for the repsonse. It will be a few thousand more to upgrade from the 1 MB to 2 MB cache, so it may be difficult to get that passed through our budget.
Out of curiosity, how many users are you guys dealing with? I'll be around 50 concurrent to start, while not expecting that number to go up to more than 75 any time soon (a few years off).|||We have about 60 users hitting the system full time.
Keep in mind, it is not so much the user count, but the way the application and database were created that really affects performance.
________________
Keith|||I don't even know exactly right now. Probably right around 35-60 depending on the time of day. But really it's not the number of users, but the work you expect the server to perform for those users.
Our primary server runs an accounting and distribution package that is heavily optimized for SQL Server and WAN environments relying on Triggers, and stored procedures, and temp tables for reports. (And not measly select * from thistable stored procedures, these are real ones that do real work.) This application works the server. 1MB may be just fine. At the time we specified the hardware we had the choice of 1mb cache and 650Mhz processors or 2mb Cache and 450mhz processors to fit in our price range. We went with the 450mhz processors and 2mb cache. 20
Make sure your application isn't just a fancy data store. We way equipped some hardware we have right now for a mission critical clustered solution. Quad processor Active/Active Compaq machines with 4gig each running SQL Enterprise and NT Enterprise, and we get the app installed on a test machine and I find it was ported from Oracle and AS/400 and is mostly flat files stored in the database. No stored procedures, no referential integrity, hardly even any indexes. The groupresponsible for getting the hardware obviously didn't do their researchfirst. Such a database will hardly be taxing the CPU's and is more likely to have blocking issues than performance issues. I can't wait until we go live...
I'm working on the configuration and have a few questions. We are planning on going with a SQL Server 2000 Standard Edition running on Windows 2000 Server. Some of what Microsoft has said on the web site is very vague concerning the licensing and other maximums.
What is the max memory that you can use with SQL Standard on a Windows 2000 Server (not Advanced)?
If you have more memory in the server than SQL Standard will support, is that OK? For example, can you say, if SQL Standard maxes out at 2 GB, put in 3 GB, and let the OS have the rest? Our database will probably be around 2 GB to start.
Do you need seperate CALs on Windows 2000, or does it work like other apps like Exchange, where you only need the CAL's for the application (or in this case, the Processor licences).
For a 3 processor server, does anyone think that for about 60-70 users that I should go with a CAL or Processor license model?
Should I spring for the 2MB cache on the processor? Planning on a 3 way 700 Mhz Xeon.
Thanks in advance.According to Books Online:
1. What is the max memory that you can use with SQL Standard on a Windows 2000 Server (not Advanced)?
2GB is the maximum that SQL Std can use.
2. If you have more memory in the server than SQL Standard will support, is that OK? For example, can you say, if SQL Standard maxes out at 2 GB, put in 3 GB, and let the OS have the rest? Our database will probably be around 2 GB to start.
If the OS can see the additional RAM, this should be ok. I cannot think how much memory that Win2k std can use.
3. Do you need seperate CALs on Windows 2000, or does it work like other apps like Exchange, where you only need the CAL's for the application (or in this case, the Processor licences).
If you go with per processor licensing of SQL Server, that is all you need.
4. For a 3 processor server, does anyone think that for about 60-70 users that I should go with a CAL or Processor license model?
I found SQL2k Std per processor licensing at ~4500 multiplied by 3 you would be at ~$13,500
I found SQL2k Std 10 client for $2000. If you purchased 7 of these (70 user), you would be at ~$14,000
Right now, the price is about the same. You need to weigh the options, and look toward the future.
What happens if the 3 processor server needs 5 more processors? What happens if you add 40 more users? How will this impact your licenses (and cost)?
5. Should I spring for the 2MB cache on the processor? Planning on a 3 way 700 Mhz Xeon.
I don't know. What is the price difference between 2MB, 1MB, and 'normal' Xeons? We have an 8 way 700 with 2MB cache and things perform quite well. I am sure that we paid a premium for the 2MB chips...I do not know what performance would be like if we had lesser Xeons. Maybe someone else can help out here with some suggestions.
________________
Keith|||2. If you have more memory in the server than SQL Standard will support, is that OK? For example, can you say, if SQL Standard maxes out at 2GB, put in 3 GB, and let the OS have the rest? Our database will probably be around 2 GB to start.
Win2k and NT Standard see 4GIG max which is the memory space directly addressable by a a 32bit operating system.
The 2 gig limit for SQL Server comes from the fact that the NT architecture has a 2gig per process memory limit.
5. Should I spring for the 2MB cache on the processor? Planning on a 3 way 700 Mhz Xeon.
It's hard to say. All of our SQL Servers now have 2MB processor caches. We had one Dell 4-way server with 512K (standard) caches that severely under-performed and was replaced by a Compaq server. But the Compaq server was much more carefully specified so direct comparisons are tough. I know this. I've read of people with performance problems when CPU utilization is at 100% on all 4 processors. On our Compaq servers, when all four processors are at 100%, people can still work. It's a little slower, but almost goes unnoticed except by those that cause the performance spike (period end processes, some custom reports). On the Dell, if all 4 where at 100% no one moved. It would stop everyone in their tracks. Compaq's processor multi-processor architecture probably has something to do with it, but I bet the 2MB cache in each processor has something to do with it also.
If the cost is not unreasonable go for the 2mb cache. We choose slower processors with 2MB cache over a faster processor with 512k or 1mb and haven't looked back.|||Thanks for the repsonse. It will be a few thousand more to upgrade from the 1 MB to 2 MB cache, so it may be difficult to get that passed through our budget.
Out of curiosity, how many users are you guys dealing with? I'll be around 50 concurrent to start, while not expecting that number to go up to more than 75 any time soon (a few years off).|||We have about 60 users hitting the system full time.
Keep in mind, it is not so much the user count, but the way the application and database were created that really affects performance.
________________
Keith|||I don't even know exactly right now. Probably right around 35-60 depending on the time of day. But really it's not the number of users, but the work you expect the server to perform for those users.
Our primary server runs an accounting and distribution package that is heavily optimized for SQL Server and WAN environments relying on Triggers, and stored procedures, and temp tables for reports. (And not measly select * from thistable stored procedures, these are real ones that do real work.) This application works the server. 1MB may be just fine. At the time we specified the hardware we had the choice of 1mb cache and 650Mhz processors or 2mb Cache and 450mhz processors to fit in our price range. We went with the 450mhz processors and 2mb cache. 20
Make sure your application isn't just a fancy data store. We way equipped some hardware we have right now for a mission critical clustered solution. Quad processor Active/Active Compaq machines with 4gig each running SQL Enterprise and NT Enterprise, and we get the app installed on a test machine and I find it was ported from Oracle and AS/400 and is mostly flat files stored in the database. No stored procedures, no referential integrity, hardly even any indexes. The groupresponsible for getting the hardware obviously didn't do their researchfirst. Such a database will hardly be taxing the CPU's and is more likely to have blocking issues than performance issues. I can't wait until we go live...
Sunday, February 19, 2012
Counting unresolved support calls based on two dates
I'm sure this question shouldn't be too difficult but I just keep having a mental block about how to do it - any help would be very gratefully received!
I have a single table which records support calls logged to a helpdesk.
The three relevant fields for this question are:
TICKET_NUMBER - unique integer assigned to each call (not null)
DATE_LOGGED - date when the call was registered (not null)
DATE_RESOLVED - date when the call was resolved, null if still unresolved.
Now, I'm trying to produce a report which states the number of unresolved calls carried over at the end of each month. I can work this out for any given month on a parameterised basis as follows:
SELECT
COUNT(TICKET_NUMBER) AS UNRESOLVED_CALLS
FROM
CALL_TABLE
WHERE
DATE_LOGGED <= @.LastDayOfMonth
AND (DATE_RESOLVED > @.LastDayOfMonth OR DATE_RESOLVED IS NULL)
However, what I just can't seem to get my head round is how to run this for all of the data at once to get a report like this:
Month End Unresolved Calls
JAN 102
FEB 97
MAR 113
etc.
One thought I had was to have a DUMMY_DATE_TABLE which just contained the last date of each month as a field called DUMMY_DATE, and join to that table as follows:
SELECT
COUNT(TICKET_NUMBER) AS UNRESOLVED_CALLS
MONTH(DUMMY_DATE) AS MONTH,
FROM
CALL_TABLE, DUMMY_DATE_TABLE
WHERE
DATE_LOGGED <= DUMMY_DATE
AND (DATE_RESOLVED > DUMMY_DATE OR DATE_RESOLVED IS NULL)
But that seems a bit silly to have to maintain a table that only has the last date of each month in it, and I'm pretty sure there must be a cleaner way.
Can anybody help point me in the right direction?!
thanks,
Alastairyou will still need some way of "generating" the month end dates
my advice: use an integers table --create table integers (i integer not null primary key);
insert into integers (i) values
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);now you can use this to generate as many monthend dates as you want, by generating the correct number of integers
for example, here we can generate 48 integers (will give you 4 years) --select tens.i*10 + units.i as dd
from integers as units
cross
join integers as tens
where tens.i*10 + units.i between 0 and 47 with me so far?|||The biggest problem is that SQL doesn't have a uniform way of manipulating date values... Every SQL database engine has to "roll your own" functions for manipulating dates, so the only way to simply get portable date manipulation is to do it on the client.
If it wasn't for that limitation, this would be easy to code in pure SQL... Just group by year and month, count incidents created in that month as one column and incidents not resolved in that month in a second column. This is easy to do on a single database engine, but I don't know of any portable way to do it.
-PatP|||pat, what about if a call is entered in march 2006, and goes unresolved through to may 2006, with your method it will not get counted as unresolved in april 2006
what if there were no calls created or resolved in april 2006, with your method that month will then be totally missing
i think you have to somehow generate the months, and then use a LEFT OUTER JOIN|||portable date manipulation ==> do it on the client?
no way!!!!
you're going to return 300,000 rows to the client just so that you can avoid having to decide between using EXTRACT() or MONTH() or DATEPART() on the server?
FEH!!|||Thankyou both for your feedback.
Pat - sorry I didn't mention but I'll be doing this in DB2 SQL, so I'm happy with the syntax of functions dealing with dates/months etc. - my concerns were more with the problems that r937 mentioned about how to ensure the logic for grouping is done appropriately at the end of every calendar month (even, for instance, if there were no calls received in any dates of that month)...
Thanks for the suggestion about the integer table - I guess I was hoping there was a way that was a bit... cleaner, but I guess not!
alastair|||so do you want me to continue outlining how to generate the monthend dates from the integers?
i'm afraid i don't have DB2 to play with, but the strategy is to start with the earliest month (which can be obtained by a SELECT MIN() subquery), and then add a number of months to it, where this number is an integer in the range 0 through 47 (or whatever)
if you want me to help you with this, i will need a moment to go search google to see if i can find the DB2 SQL Reference manual again, they keep moving it
got a link?|||No -thankyou for your help, I can implement it from here (although, for reference, the DB2 SQL reference lib can be found here: http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/db2/rbafzmst02.htm)
I am grateful for your answer, it's just that I come from a C background, where constructing a routine to loop through month end-dates is very easy, so the thought of needing to create a user table just to hold integers seems very odd!|||yes, but to write efficient queries, you may want to unlearn the programmer's record-at-a-time-in-a-loop mentality, and start dealing with single sets of records
thanks for the link
WTF, there's no "date add" function to add 1 month to a date? does this mean we would have to add 1 to MONTH() and watch for year-end boundaries? eeewww...|||No, in DB2 release 5 you have to use date arithmetic expressions instead of date manipulation functions... This is exactly the problem I was referring to earlier... It isn't hard to code a database engine specific solution, but those solutions are engine specific... You have to code one for every blasted database engine!
Alastair: If a call originates in Novemember and is resolved in February, do you want it counted in just November, or November, December, and January? Based on your original descripton, I would think it would only be counted in November, but based on Rudy's interpretation you might want it counted in all of them. Please clarify.
-PatP|||portable date manipulation ==> do it on the client?
no way!!!!
you're going to return 300,000 rows to the client just so that you can avoid having to decide between using EXTRACT() or MONTH() or DATEPART() on the server?
FEH!!Show me a better way to get portable date manipulation in SQL, and I'll be eternally grateful. So far, if I want do to an operation on three different engines, I have to code it three different ways (or resort to bringing the data to a client, which as you pointed out is usually ugly).
-PatP|||there's no "date add" function to add 1 month to a date? does this mean we would have to add 1 to MONTH() and watch for year-end boundaries?
In DB2, you can writeCURRENT DATE + 1 monthto specify the date in the next month with the same day number as today.
B.t.w.: to obtain the last day of (say) February 2006, you may useCAST('2006-03-01' AS date) - 1 day|||Alastair: If a call originates in Novemember and is resolved in February, do you want it counted in just November, or November, December, and January? Based on your original descripton, I would think it would only be counted in November, but based on Rudy's interpretation you might want it counted in all of them. Please clarify.
-PatP
No, I want it to be counted in the total for every month until that call is resolved (i.e. November, December, and January).
I agree with you that data formats are always a bugger to work with - I work across DB2 {d'2005-03-01'}, SQL Server (01/03/2005), and Oracle (01-Mar-05) in both UK and US date formats and it gives me no end of headaches. However, the problem I am concerned with here is not so much how to work out the months - more the logic to work out the outstanding calls (for instance, consider that I had asked for help with a report that listed the outstanding calls at the end of every day, rather than every month and I would still have the same problems)
In the DB2 SQL reference I have noticed procedural loop statements such as WHILE, DO etc. which I thought would let me calculate and loop through each date, but it seems that these are only available in stored procs rather than in inline SQL which is what I need to do here.... darn.|||A classical "group by" is not possible in this case, since an item from January which is resolved in May has to be counted as "unresolved" in all of January, February, March and April, i.e., not just in one "group by month" group.
Recursive SQL could be used to accomplish this, but let's keep out of that :-)
So the only viable approach left is one that generates the report for a single month.
(This query could be put in a (parametrised) stored procedure, to be called for all months of interest.)
So let's assume there is a column called MONTH_LOGGED, and a column called MONTH_RESOLVED, both of the form 12*YEAR(date_..)+MONTH(date_..), with a NULL in MONTH_RESOLVED if not yet resolved.
Now for the report of the month @.Month_Reported (which is the mentioned stored proc parameter):
SELECT count(*)
FROM cal_table
WHERE MONTH_LOGGED <= @.Month_Reported
AND ( MONTH_RESOLVED > @.Month_Reported OR
MONTH_RESOLVED IS NULL )|||peter, if you look at post #1, that's pretty much how alastair is doing it now
what he wants is to do it for multiple months at a time
the solution i suggested involves generating the months based on "DATEADD" functionality to add an integer number of months to a start date, and i believe he said he could build up that solution on his own|||Sure, but does that take into account the fact that a call from January, resolved in March, must be counted in both January and February?
I have a single table which records support calls logged to a helpdesk.
The three relevant fields for this question are:
TICKET_NUMBER - unique integer assigned to each call (not null)
DATE_LOGGED - date when the call was registered (not null)
DATE_RESOLVED - date when the call was resolved, null if still unresolved.
Now, I'm trying to produce a report which states the number of unresolved calls carried over at the end of each month. I can work this out for any given month on a parameterised basis as follows:
SELECT
COUNT(TICKET_NUMBER) AS UNRESOLVED_CALLS
FROM
CALL_TABLE
WHERE
DATE_LOGGED <= @.LastDayOfMonth
AND (DATE_RESOLVED > @.LastDayOfMonth OR DATE_RESOLVED IS NULL)
However, what I just can't seem to get my head round is how to run this for all of the data at once to get a report like this:
Month End Unresolved Calls
JAN 102
FEB 97
MAR 113
etc.
One thought I had was to have a DUMMY_DATE_TABLE which just contained the last date of each month as a field called DUMMY_DATE, and join to that table as follows:
SELECT
COUNT(TICKET_NUMBER) AS UNRESOLVED_CALLS
MONTH(DUMMY_DATE) AS MONTH,
FROM
CALL_TABLE, DUMMY_DATE_TABLE
WHERE
DATE_LOGGED <= DUMMY_DATE
AND (DATE_RESOLVED > DUMMY_DATE OR DATE_RESOLVED IS NULL)
But that seems a bit silly to have to maintain a table that only has the last date of each month in it, and I'm pretty sure there must be a cleaner way.
Can anybody help point me in the right direction?!
thanks,
Alastairyou will still need some way of "generating" the month end dates
my advice: use an integers table --create table integers (i integer not null primary key);
insert into integers (i) values
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);now you can use this to generate as many monthend dates as you want, by generating the correct number of integers
for example, here we can generate 48 integers (will give you 4 years) --select tens.i*10 + units.i as dd
from integers as units
cross
join integers as tens
where tens.i*10 + units.i between 0 and 47 with me so far?|||The biggest problem is that SQL doesn't have a uniform way of manipulating date values... Every SQL database engine has to "roll your own" functions for manipulating dates, so the only way to simply get portable date manipulation is to do it on the client.
If it wasn't for that limitation, this would be easy to code in pure SQL... Just group by year and month, count incidents created in that month as one column and incidents not resolved in that month in a second column. This is easy to do on a single database engine, but I don't know of any portable way to do it.
-PatP|||pat, what about if a call is entered in march 2006, and goes unresolved through to may 2006, with your method it will not get counted as unresolved in april 2006
what if there were no calls created or resolved in april 2006, with your method that month will then be totally missing
i think you have to somehow generate the months, and then use a LEFT OUTER JOIN|||portable date manipulation ==> do it on the client?
no way!!!!
you're going to return 300,000 rows to the client just so that you can avoid having to decide between using EXTRACT() or MONTH() or DATEPART() on the server?
FEH!!|||Thankyou both for your feedback.
Pat - sorry I didn't mention but I'll be doing this in DB2 SQL, so I'm happy with the syntax of functions dealing with dates/months etc. - my concerns were more with the problems that r937 mentioned about how to ensure the logic for grouping is done appropriately at the end of every calendar month (even, for instance, if there were no calls received in any dates of that month)...
Thanks for the suggestion about the integer table - I guess I was hoping there was a way that was a bit... cleaner, but I guess not!
alastair|||so do you want me to continue outlining how to generate the monthend dates from the integers?
i'm afraid i don't have DB2 to play with, but the strategy is to start with the earliest month (which can be obtained by a SELECT MIN() subquery), and then add a number of months to it, where this number is an integer in the range 0 through 47 (or whatever)
if you want me to help you with this, i will need a moment to go search google to see if i can find the DB2 SQL Reference manual again, they keep moving it
got a link?|||No -thankyou for your help, I can implement it from here (although, for reference, the DB2 SQL reference lib can be found here: http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/db2/rbafzmst02.htm)
I am grateful for your answer, it's just that I come from a C background, where constructing a routine to loop through month end-dates is very easy, so the thought of needing to create a user table just to hold integers seems very odd!|||yes, but to write efficient queries, you may want to unlearn the programmer's record-at-a-time-in-a-loop mentality, and start dealing with single sets of records
thanks for the link
WTF, there's no "date add" function to add 1 month to a date? does this mean we would have to add 1 to MONTH() and watch for year-end boundaries? eeewww...|||No, in DB2 release 5 you have to use date arithmetic expressions instead of date manipulation functions... This is exactly the problem I was referring to earlier... It isn't hard to code a database engine specific solution, but those solutions are engine specific... You have to code one for every blasted database engine!
Alastair: If a call originates in Novemember and is resolved in February, do you want it counted in just November, or November, December, and January? Based on your original descripton, I would think it would only be counted in November, but based on Rudy's interpretation you might want it counted in all of them. Please clarify.
-PatP|||portable date manipulation ==> do it on the client?
no way!!!!
you're going to return 300,000 rows to the client just so that you can avoid having to decide between using EXTRACT() or MONTH() or DATEPART() on the server?
FEH!!Show me a better way to get portable date manipulation in SQL, and I'll be eternally grateful. So far, if I want do to an operation on three different engines, I have to code it three different ways (or resort to bringing the data to a client, which as you pointed out is usually ugly).
-PatP|||there's no "date add" function to add 1 month to a date? does this mean we would have to add 1 to MONTH() and watch for year-end boundaries?
In DB2, you can writeCURRENT DATE + 1 monthto specify the date in the next month with the same day number as today.
B.t.w.: to obtain the last day of (say) February 2006, you may useCAST('2006-03-01' AS date) - 1 day|||Alastair: If a call originates in Novemember and is resolved in February, do you want it counted in just November, or November, December, and January? Based on your original descripton, I would think it would only be counted in November, but based on Rudy's interpretation you might want it counted in all of them. Please clarify.
-PatP
No, I want it to be counted in the total for every month until that call is resolved (i.e. November, December, and January).
I agree with you that data formats are always a bugger to work with - I work across DB2 {d'2005-03-01'}, SQL Server (01/03/2005), and Oracle (01-Mar-05) in both UK and US date formats and it gives me no end of headaches. However, the problem I am concerned with here is not so much how to work out the months - more the logic to work out the outstanding calls (for instance, consider that I had asked for help with a report that listed the outstanding calls at the end of every day, rather than every month and I would still have the same problems)
In the DB2 SQL reference I have noticed procedural loop statements such as WHILE, DO etc. which I thought would let me calculate and loop through each date, but it seems that these are only available in stored procs rather than in inline SQL which is what I need to do here.... darn.|||A classical "group by" is not possible in this case, since an item from January which is resolved in May has to be counted as "unresolved" in all of January, February, March and April, i.e., not just in one "group by month" group.
Recursive SQL could be used to accomplish this, but let's keep out of that :-)
So the only viable approach left is one that generates the report for a single month.
(This query could be put in a (parametrised) stored procedure, to be called for all months of interest.)
So let's assume there is a column called MONTH_LOGGED, and a column called MONTH_RESOLVED, both of the form 12*YEAR(date_..)+MONTH(date_..), with a NULL in MONTH_RESOLVED if not yet resolved.
Now for the report of the month @.Month_Reported (which is the mentioned stored proc parameter):
SELECT count(*)
FROM cal_table
WHERE MONTH_LOGGED <= @.Month_Reported
AND ( MONTH_RESOLVED > @.Month_Reported OR
MONTH_RESOLVED IS NULL )|||peter, if you look at post #1, that's pretty much how alastair is doing it now
what he wants is to do it for multiple months at a time
the solution i suggested involves generating the months based on "DATEADD" functionality to add an integer number of months to a start date, and i believe he said he could build up that solution on his own|||Sure, but does that take into account the fact that a call from January, resolved in March, must be counted in both January and February?
Friday, February 17, 2012
Counting no. of records
Hi Ref No. First Name Surname 18 test test 18 test test 18 test test 19 test test 19 test test
I need to calulate the records returned on the ref no. I have managed to set page breaks based on a new ref no. with grouping and therefore the count will be displayed at the end of each of the records returned. As you can see there are three records returned for ref no. 18 and 2 for 19. How can I achieve this.
I have created the table with the same information you have given.
I opened my Sql Server Reporting Services 2005, creating the new report.
First I placed the table control, placing the ref no, firstname, surname then i have created new group in the table called - "refno" ....in the group footer i have used the function ===> =CountRows("refno")
I need the ability to calculate the no. of records based on the no. of times a value in the sql report is given. For example based on a table shown below:
I need to calulate the records returned on the ref no. I have managed to set page breaks based on a new ref no. with grouping and therefore the count will be displayed at the end of each of the records returned. As you can see there are three records returned for ref no. 18 and 2 for 19. How can I achieve this.
Many thanks in advance
If an aggregate of that column doesn't already exist then create one using something like
SUM(Ref No) AS 'Number of Records'
You could do this in the SQL or you can create it as a function which would look something like
=SUM(Ref No)
This field should be added to the footer column of the group.
Hope this helps
|||Hi,I have created the table with the same information you have given.
I opened my Sql Server Reporting Services 2005, creating the new report.
First I placed the table control, placing the ref no, firstname, surname then i have created new group in the table called - "refno" ....in the group footer i have used the function ===> =CountRows("refno")
I think you know, how to set page break after group ends....
Do this....You will get the answer?
If you have any queries..let me know...
M Sivakumar|||Thanks for your reply Harley.
I have tried what you have suggested but =SUM is calculating the total for the Ref No. So if the ref no. 18 occurs twice, I get the value 36.. and so forth. Is this the right function? I thought this function would be to calculate the numbers mathematically. I know there is a count function but do not know the syntax of the expression. I need to understand how to use this function to calculate the no. of records returned for the ref no. e.g. if ref.no 18 occurs 10 times in the report, then below it should say total records = 10 and so forth for other ref no.s. I hope this makes more sense.
Cheers|||Hi Siva
First of all thanks for your help in trying to help, greatly appreciated.
Right, I do know how to create groups and set page breaks which I have implemented in my report and have the group footer below my rows. I tried using the countrows function but I think I am doing this wrong, the way I have added this is by using:
=CountRows(Fields!RefNo.Value)
the function with the fieldname in the brackets (is this the correct way?)
Do I need the speech marks also, I have tried all possibilites but it gives me an error:
The value expression for the textbox ‘textbox70’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
Thank you again for your help
|||Sorry,I meant use COUNT(RefNo) that should work, I always make that mistake then wonder why I get ridiculous answers :)
thanks|||
Hi,
No It is not the correct way. After creating the table, you just the create the new group and by default the group name will be "table1_Group1", you just modify the name into "refno" if you want to make it meaningful or you just leave it..
Now in the group footer...any of your cell you just add
"table1_Group1"
=CountRows("table1_Group1")
if u renamed into "refno"
=CountRows("refno")
I think, it will help your need.
|||I think I have found the solution to this. Siva first of all thank you for pointing me to the right direction.The countrows function is indeed the right function for counting the rows returned. The way I have got this to work is by saying the following:
=CountRows("groupname") where groupname is the name of your group you have defined. It is imperative to include the speech marks.
I had a group set up as my criteria and have used this and seems to be working 
.
By the way, a good reference for this function and others supported by Microsoft use this website, its really good:
http://msdn2.microsoft.com/en-us/library/ms226986(en-US,SQL.90).aspx|||The above may be a better way of doing it but the way explained does work!|||Hi Harley
I'm sure it does work, not tried using it but I have the countrows function working so will stick with this one. In fact the reason why I feel the countrows function is better is due to the records returning actually fulfill my criteria which I specified in the group.
Cheers for your help too.
Tuesday, February 14, 2012
Counting by Calendar and Fiscal periods in same query
I have a query that returns results based on a count of tests done by period
and year. The period can either be a calendar month or Fiscal Calendar mont
h.
As always thanks in advance!
Select
count( modelDesc)as CompCnt,
TestYear as CalYear,
TestMonth as CalMonth,
FiscYear as FiscYear,
FiscMonth as FiscMonth
From CompFails
group by ModelDesc,FiscYear, FiscMonth, testYear, testMonth
I also attempted to set the count as
select COUNT(ModelDesc)as ModelCnt FROM (SELECT
DISTINCT f.modelDesc from compfails f
group by FiscYear, FiscMonth) as FiscCompCnt
And got the "subquery returns more than one row error"
Currently the query returns the results as so:
Current Results
COMPcnt CalYear CalMonth FiscYear FiscMonth
-- -- -- -- --
26 2005 10 2006 1
1 2005 10 2006 2
17 2005 11 2006 2
1 2005 11 2006 3
10 2005 12 2006 3
19 2006 1 2006 4
1 2006 1 2006 5
16 2006 2 2006 5
I would like the results as follows but I am having a heck of a time
figuring the syntax.
CalCompCnt CalYear CalMonth FiscCompCnt FiscYear FiscMonth
-- -- -- -- --
20 2006 1 26 2006 1
16 2006 2 18 2006 2
27 2005 10 11 2006 3
18 2005 11 19 2006 4
10 2005 12 17 2006 5
DDL Follows:
CREATE TABLE CompFails (
ModelDesc Varchar(40) NULL,
CalorimeterTestDate DATETIME NULL,
TestYear INT NULL,
TestMonth INT NULL,
FiscYear INT NULL,
FiscMonth INT NULL
)
INSERT INTO CompFails
(ModelDesc,
CalorimeterTestDate,
TestYear,
TestMonth,
FiscYear,
FiscMonth)
VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-03 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-03 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-10 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-11 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-11 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-14 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-14 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-12 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-12 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-18 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-19 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-20 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-28 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-18 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-20 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-27 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-11-02 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-10-24 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-11-08 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-10-25 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-25 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-11-04 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-07 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-08 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-04 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-10-31 00:00:00.000,2005,10,2006,2)
VALUES('tstModelDesc',2005-12-05 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-11-15 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-10 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-10 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-12-02 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-11-15 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-29 00:00:00.000,2005,11,2006,3)
VALUES('tstModelDesc',2005-11-16 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-12-07 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-12-09 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-12-16 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-12-17 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2006-01-04 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-02 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2005-12-19 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2006-01-09 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-13 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-02-07 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2006-01-13 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-16 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-18 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-19 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-28 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-22 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-02-01 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-25 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-20 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-21 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-02-01 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-01-30 00:00:00.000,2006,1, 2006,5)
VALUES('tstModelDesc',2006-01-27 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-28 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-02-02 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-08 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-22 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-16 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-16 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-17 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-15 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-14 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-15 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-21 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-18 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-19 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-21 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-01-02 00:00:00.000,2006,1, 2006,4)At the moment I'm not too sure about doing it one query - but you can
easily do it in two queries.
Barry|||I've continued working on this problem and have a partial solution if anyone
could help me refine it It still returns two rows with the same values The
results follow this select statement.
Select DISTINCT
y.CalCompCnt,
compfails.TestYear as CalYear,
compfails.TestMonth as CalMonth,
x.FiscCompCnt,
compfails.FiscYear as FiscYear,
compfails.FiscMonth as FiscMonth
From CompFails
INNER JOIN (Select DISTINCT Count(f.modelDesc) as FiscCompCnt,
f.fiscYear, f.fiscMonth from CompFails f
group by FiscYear, FiscMonth)x ON x.FiscYear = compfails.Fiscyear and
x.FiscMonth = compfails.FiscMonth
INNER JOIN (Select DISTINCT Count(b.modelDesc) as CalCompCnt,
b.TestYear, b.TestMonth from CompFails b
group by b.TestYear, b.TestMonth)y ON y.testYear = compfails.testyear and
y.testMonth = compfails.testMonth
group by compfails.FiscYear, compfails.FiscMonth,
compfails.testYear, compfails.testMonth, x.fisccompcnt, y.calcompcnt
CalCompCnt CalYear CalMonth FiscCompCnt FiscYear FiscMonth
-- -- -- -- -- --
27 2005 10 26 2006 1
27 2005 10 18 2006 2
18 2005 11 18 2006 2
18 2005 11 11 2006 3
10 2005 12 11 2006 3
20 2006 1 19 2006 4
20 2006 1 17 2006 5
16 2006 2 17 2006 5
"StvJston" wrote:
> I have a query that returns results based on a count of tests done by peri
od
> and year. The period can either be a calendar month or Fiscal Calendar mo
nth.
> As always thanks in advance!
> Select
> count( modelDesc)as CompCnt,
> TestYear as CalYear,
> TestMonth as CalMonth,
> FiscYear as FiscYear,
> FiscMonth as FiscMonth
> From CompFails
> group by ModelDesc,FiscYear, FiscMonth, testYear, testMonth
> I also attempted to set the count as
> select COUNT(ModelDesc)as ModelCnt FROM (SELECT
> DISTINCT f.modelDesc from compfails f
> group by FiscYear, FiscMonth) as FiscCompCnt
> And got the "subquery returns more than one row error"
>
> Currently the query returns the results as so:
> Current Results
> COMPcnt CalYear CalMonth FiscYear FiscMonth
> -- -- -- -- --
> 26 2005 10 2006 1
> 1 2005 10 2006 2
> 17 2005 11 2006 2
> 1 2005 11 2006 3
> 10 2005 12 2006 3
> 19 2006 1 2006 4
> 1 2006 1 2006 5
> 16 2006 2 2006 5
> I would like the results as follows but I am having a heck of a time
> figuring the syntax.
> CalCompCnt CalYear CalMonth FiscCompCnt FiscYear FiscMonth
> -- -- -- -- --
> 20 2006 1 26 2006 1
> 16 2006 2 18 2006 2
> 27 2005 10 11 2006 3
> 18 2005 11 19 2006 4
> 10 2005 12 17 2006 5
>
> DDL Follows:
> CREATE TABLE CompFails (
> ModelDesc Varchar(40) NULL,
> CalorimeterTestDate DATETIME NULL,
> TestYear INT NULL,
> TestMonth INT NULL,
> FiscYear INT NULL,
> FiscMonth INT NULL
> )
> INSERT INTO CompFails
> (ModelDesc,
> CalorimeterTestDate,
> TestYear,
> TestMonth,
> FiscYear,
> FiscMonth)
> VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-03 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-03 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-10 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-11 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-11 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-14 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-14 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-12 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-12 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-18 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-19 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-20 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-28 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-18 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-20 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-27 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-11-02 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-10-24 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-11-08 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-10-25 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-25 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-11-04 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-07 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-08 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-04 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-10-31 00:00:00.000,2005,10,2006,2)
> VALUES('tstModelDesc',2005-12-05 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2005-11-15 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-10 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-10 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-12-02 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2005-11-15 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-29 00:00:00.000,2005,11,2006,3)
> VALUES('tstModelDesc',2005-11-16 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2005-12-07 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2005-12-09 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2005-12-16 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2005-12-17 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2006-01-04 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-02 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2005-12-19 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2006-01-09 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-13 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-02-07 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2006-01-13 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-16 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-18 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-19 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-28 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-22 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-02-01 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-25 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-20 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-21 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-02-01 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-01-30 00:00:00.000,2006,1, 2006,5)
> VALUES('tstModelDesc',2006-01-27 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-28 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-02-02 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-08 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-22 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-16 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-16 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-17 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-15 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-14 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-15 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-21 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-18 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-19 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-21 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-01-02 00:00:00.000,2006,1, 2006,4)
>
>|||If I understand the problem, the fiscal calendar is different from the
regular calendar in two ways. First, it is offset by three months.
Second, months do not cut off at the calendar month end.
You need to construct a reference table to relate the two calendars.
The table should have one row per month. The table looks a lot like
certain columns your desired results, though it should contain the
full calendar of months:
CalYear CalMonth FiscYear FiscMonth
-- -- -- --
2005 10 2006 1
2005 11 2006 2
2005 12 2006 3
2006 1 2006 4
2006 2 2006 5
This table needs two unique constraints, (CalYear,CalMonth) and
(FiscYear,FiscMonth). One should be the primary key, the other a
unique index.
CREATE TABLE Months (
TestYear INT NULL,
TestMonth INT NULL,
FiscYear INT NULL,
FiscMonth INT NULL
)
GO
INSERT Months VALUES (2005,10,2006,1)
INSERT Months VALUES (2005,11,2006,2)
INSERT Months VALUES (2005,12,2006,3)
INSERT Months VALUES (2006, 1,2006,4)
INSERT Months VALUES (2006, 2,2006,5)
GO
We then will calculate each set of numbers, calendar and fiscal, in
two derived tables, and use the Months table to bring it all together.
SELECT M.TestYear, M.TestMonth, C.TestCount,
M.FiscYear, M.FiscMonth, F.FiscCount
FROM Months as M
LEFT OUTER
JOIN (select TestYear, TestMonth, count(*) as TestCount
from CompFails
group by TestYear, TestMonth) as C
ON M.TestYear = C.TestYear
AND M.TestMonth = C.TestMonth
LEFT OUTER
JOIN (select FiscYear, FiscMonth, count(*) as FiscCount
from CompFails
group by FiscYear, FiscMonth) as F
ON M.FiscYear = F.FiscYear
AND M.FiscMonth = F.FiscMonth
TestYear TestMonth TestCount FiscYear FiscMonth FiscCount
-- -- -- -- -- --
2005 10 27 2006 1 26
2005 11 18 2006 2 18
2005 12 10 2006 3 11
2006 1 20 2006 4 19
2006 2 16 2006 5 17
Roy Harvey
Beacon Falls, CT
On Mon, 24 Apr 2006 11:31:02 -0700, StvJston
<StvJston@.discussions.microsoft.com> wrote:
>I have a query that returns results based on a count of tests done by perio
d
>and year. The period can either be a calendar month or Fiscal Calendar mon
th.
>As always thanks in advance!
>Select
> count( modelDesc)as CompCnt,
> TestYear as CalYear,
> TestMonth as CalMonth,
> FiscYear as FiscYear,
> FiscMonth as FiscMonth
>From CompFails
>group by ModelDesc,FiscYear, FiscMonth, testYear, testMonth
>I also attempted to set the count as
>select COUNT(ModelDesc)as ModelCnt FROM (SELECT
> DISTINCT f.modelDesc from compfails f
> group by FiscYear, FiscMonth) as FiscCompCnt
>And got the "subquery returns more than one row error"
>
>Currently the query returns the results as so:
>Current Results
> COMPcnt CalYear CalMonth FiscYear FiscMonth
> -- -- -- -- --
> 26 2005 10 2006 1
> 1 2005 10 2006 2
> 17 2005 11 2006 2
> 1 2005 11 2006 3
> 10 2005 12 2006 3
> 19 2006 1 2006 4
> 1 2006 1 2006 5
> 16 2006 2 2006 5
>I would like the results as follows but I am having a heck of a time
>figuring the syntax.
> CalCompCnt CalYear CalMonth FiscCompCnt FiscYear FiscMonth
>-- -- -- -- --
> 20 2006 1 26 2006 1
> 16 2006 2 18 2006 2
> 27 2005 10 11 2006 3
> 18 2005 11 19 2006 4
> 10 2005 12 17 2006 5
>
>DDL Follows:
>CREATE TABLE CompFails (
>ModelDesc Varchar(40) NULL,
>CalorimeterTestDate DATETIME NULL,
>TestYear INT NULL,
>TestMonth INT NULL,
>FiscYear INT NULL,
>FiscMonth INT NULL
> )
>INSERT INTO CompFails
> (ModelDesc,
> CalorimeterTestDate,
> TestYear,
> TestMonth,
> FiscYear,
> FiscMonth)
>VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-03 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-03 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-10 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-11 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-11 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-14 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-14 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-12 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-12 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-18 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-19 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-20 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-28 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-18 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-20 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-27 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-11-02 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-10-24 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-11-08 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-10-25 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-25 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-11-04 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-07 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-08 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-04 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-10-31 00:00:00.000,2005,10,2006,2)
>VALUES('tstModelDesc',2005-12-05 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2005-11-15 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-10 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-10 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-12-02 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2005-11-15 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-29 00:00:00.000,2005,11,2006,3)
>VALUES('tstModelDesc',2005-11-16 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2005-12-07 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2005-12-09 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2005-12-16 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2005-12-17 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2006-01-04 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-02 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2005-12-19 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2006-01-09 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-13 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-02-07 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2006-01-13 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-16 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-18 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-19 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-28 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-22 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-02-01 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-25 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-20 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-21 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-02-01 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-01-30 00:00:00.000,2006,1, 2006,5)
>VALUES('tstModelDesc',2006-01-27 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-28 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-02-02 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-08 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-22 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-16 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-16 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-17 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-15 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-14 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-15 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-21 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-18 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-19 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-21 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-01-02 00:00:00.000,2006,1, 2006,4)
>|||First construct a general enterprise calendar table; you should talk to
the trolls in accounting so you get the fiscal part right.
CREATE TABLE Calendar
(cal_date DATETIME NOT NULL PRIMARY KEY,
cal_year INTEGER NOT NULL,
cal_month INTEGER NOT NULL,
fiscal_year INTEGER NOT NULL,
fiscal_month INTEGER NOT NULL,
etc.);
Now get your tests table right. For example, why are things you must
know set to NULLs? Why did you have no key?
CREATE TABLE CompFails
(model_desc VARCHAR(40) NOT NULL,
test_date DATETIME NOT NULL,
PRIMARY KEY (model_desc, test_date)
);
Then your view or query will look like this skeleton:
SELECT ..
FROM CompFails AS T, Calendar AS C
WHERE T.fail_date = C.cal_date
AND ..;
He had computable columns in your original non-table, in addition to
the other design flaws.|||Hi,
Here is the query that gives you the result that you wanted.
select a.CalCompCnt,a.calyear ,a.calmonth,b.FiscCompCnt,b.FiscYear
,b.FiscMonth from
(select count(*) as id ,x.CalCompCnt,x.calyear ,x.calmonth
from
(select count(modeldesc) as CalCompCnt, TestYear as calyear, TestMonth as
calmonth from compfails group by TestYear,TestMonth) as x,
(select count(modeldesc) as CalCompCnt, TestYear as calyear, TestMonth as
calmonth from compfails group by TestYear,TestMonth) as y
where x.calmonth >= y.calmonth
group by x.CalCompCnt,x.calyear ,x.calmonth ) as a,
(select count(*) as id ,x.FiscCompCnt,x.FiscYear ,x.FiscMonth
from
(select count(modeldesc) as FiscCompCnt, FiscYear, FiscMonth from compfails
group by FiscYear, FiscMonth) as x,
(select count(modeldesc) as FiscCompCnt, FiscYear, FiscMonth from compfails
group by FiscYear, FiscMonth) as y
where x.FiscMonth >= y.FiscMonth
group by x.FiscCompCnt,x.FiscYear ,x.FiscMonth ) as b
where a.id = b.id
-- But a note of caution. I will never use this query in production. and the
result that you want doesn't seem functionally valid. I doubt you really hav
e
a requirement like this. Anyways, I had fun in writing this query :)
Hope I helped.|||Here is a better solution. I feel its got more meaning. relating the fiscal
and the calendar month. and its simple.
Let me know your thoughts. Hope this helps
select * from
(select count(modeldesc) as CalCompCnt, TestYear as calyear, TestMonth as
calmonth from compfails group by TestYear,TestMonth) as a,
(select count(modeldesc) as FiscCompCnt, FiscYear, FiscMonth from compfails
group by FiscYear, FiscMonth) as b
where fiscmonth + (12 *(FiscYear -calyear)) - calmonth = 3
and fiscyear = calyear + floor(calmonth/10)|||I thought you would have a comment, always appreciate your input.
I have a Fiscal Calendar table that is correct for the corp. The data SET I
am selecting from "CompFails" is a temp table that contains the records that
meet the selection criteria.
One of the report requirements (I know let the report do the work) is the
ability to select between fiscal and calendar data points. By returning bot
h
sets at the same time I can keep the fiscal/calendar date selection out of m
y
sql.
CompFails will be a temporary table in a stored proc once development is
done. Nulls and indexes will get handled as I continue testing. (There will
never be null fields all the fields are required from the selection or
computed results for the specific model/serial no in each row.
Primary key is done through the serialNo field which I did not include in my
example since it wasn't required for the select I am doing.
I solved this problem myself lastnight using another tack.
I created a Union query with the desired fields and added a dummy flag for
fiscal or calendar.
What seems to have been causing the problem was when a record was outside of
the Fiscal Month range but still in the calendar month range I would get a
duplicate return.
It was a small roadblock but as always learn or die...(grin)
Thanks all for the help and comments...
"--CELKO--" wrote:
> First construct a general enterprise calendar table; you should talk to
> the trolls in accounting so you get the fiscal part right.
> CREATE TABLE Calendar
> (cal_date DATETIME NOT NULL PRIMARY KEY,
> cal_year INTEGER NOT NULL,
> cal_month INTEGER NOT NULL,
> fiscal_year INTEGER NOT NULL,
> fiscal_month INTEGER NOT NULL,
> etc.);
> Now get your tests table right. For example, why are things you must
> know set to NULLs? Why did you have no key?
> CREATE TABLE CompFails
> (model_desc VARCHAR(40) NOT NULL,
> test_date DATETIME NOT NULL,
> PRIMARY KEY (model_desc, test_date)
> );
> Then your view or query will look like this skeleton:
> SELECT ..
> FROM CompFails AS T, Calendar AS C
> WHERE T.fail_date = C.cal_date
> AND ..;
> He had computable columns in your original non-table, in addition to
> the other design flaws.
>|||I should have made myself more clear but when formulating a question its har
d
to figure out what information people need.
The CompFails table is a result set that is returned from sql that performs
calculations and summations on each record and loads CompFails.
the testMonth, Testyear, and FiscYear and FiscMonth are returned based on
the actual test date in the original data. They are correct values based on
the actual test date data using the year(CalorimetertestDate) and
month(CalorimetertestDate). The fiscal Year and month are returned to
CompFails using the following sql
INNER JOIN (select f.Monthno, f.Fiscalyear, MIN(f.BeginDate) as FBDate,
Max(f.Enddate)as FEndDate from fiscalcalendar f
where f.BeginDate>=CONVERT(VarCHAR, @.StartDate ,112)
and f.EndDate <= CONVERT(Varchar, @.EndDate,112)
group by f.monthno, f.Fiscalyear) fTest ON calorimetertestdate
between
ftest.fbdate and ftest.fenddate
I didn't expect folks to get caught with figuring dates. I just wanted to
return the correct monthly totals based on fiscal months or Calendar months.
By using a Union query with a dummy flag (calendar or Fiscal) I get both set
s
correctly and only have to make one call to the table.
Thanks for the help.
"Roy Harvey" wrote:
> If I understand the problem, the fiscal calendar is different from the
> regular calendar in two ways. First, it is offset by three months.
> Second, months do not cut off at the calendar month end.
> You need to construct a reference table to relate the two calendars.
> The table should have one row per month. The table looks a lot like
> certain columns your desired results, though it should contain the
> full calendar of months:
> CalYear CalMonth FiscYear FiscMonth
> -- -- -- --
> 2005 10 2006 1
> 2005 11 2006 2
> 2005 12 2006 3
> 2006 1 2006 4
> 2006 2 2006 5
> This table needs two unique constraints, (CalYear,CalMonth) and
> (FiscYear,FiscMonth). One should be the primary key, the other a
> unique index.
> CREATE TABLE Months (
> TestYear INT NULL,
> TestMonth INT NULL,
> FiscYear INT NULL,
> FiscMonth INT NULL
> )
> GO
> INSERT Months VALUES (2005,10,2006,1)
> INSERT Months VALUES (2005,11,2006,2)
> INSERT Months VALUES (2005,12,2006,3)
> INSERT Months VALUES (2006, 1,2006,4)
> INSERT Months VALUES (2006, 2,2006,5)
> GO
> We then will calculate each set of numbers, calendar and fiscal, in
> two derived tables, and use the Months table to bring it all together.
> SELECT M.TestYear, M.TestMonth, C.TestCount,
> M.FiscYear, M.FiscMonth, F.FiscCount
> FROM Months as M
> LEFT OUTER
> JOIN (select TestYear, TestMonth, count(*) as TestCount
> from CompFails
> group by TestYear, TestMonth) as C
> ON M.TestYear = C.TestYear
> AND M.TestMonth = C.TestMonth
> LEFT OUTER
> JOIN (select FiscYear, FiscMonth, count(*) as FiscCount
> from CompFails
> group by FiscYear, FiscMonth) as F
> ON M.FiscYear = F.FiscYear
> AND M.FiscMonth = F.FiscMonth
> TestYear TestMonth TestCount FiscYear FiscMonth FiscCount
> -- -- -- -- -- --
> 2005 10 27 2006 1 26
> 2005 11 18 2006 2 18
> 2005 12 10 2006 3 11
> 2006 1 20 2006 4 19
> 2006 2 16 2006 5 17
> Roy Harvey
> Beacon Falls, CT
>
> On Mon, 24 Apr 2006 11:31:02 -0700, StvJston
> <StvJston@.discussions.microsoft.com> wrote:
>
>
and year. The period can either be a calendar month or Fiscal Calendar mont
h.
As always thanks in advance!
Select
count( modelDesc)as CompCnt,
TestYear as CalYear,
TestMonth as CalMonth,
FiscYear as FiscYear,
FiscMonth as FiscMonth
From CompFails
group by ModelDesc,FiscYear, FiscMonth, testYear, testMonth
I also attempted to set the count as
select COUNT(ModelDesc)as ModelCnt FROM (SELECT
DISTINCT f.modelDesc from compfails f
group by FiscYear, FiscMonth) as FiscCompCnt
And got the "subquery returns more than one row error"
Currently the query returns the results as so:
Current Results
COMPcnt CalYear CalMonth FiscYear FiscMonth
-- -- -- -- --
26 2005 10 2006 1
1 2005 10 2006 2
17 2005 11 2006 2
1 2005 11 2006 3
10 2005 12 2006 3
19 2006 1 2006 4
1 2006 1 2006 5
16 2006 2 2006 5
I would like the results as follows but I am having a heck of a time
figuring the syntax.
CalCompCnt CalYear CalMonth FiscCompCnt FiscYear FiscMonth
-- -- -- -- --
20 2006 1 26 2006 1
16 2006 2 18 2006 2
27 2005 10 11 2006 3
18 2005 11 19 2006 4
10 2005 12 17 2006 5
DDL Follows:
CREATE TABLE CompFails (
ModelDesc Varchar(40) NULL,
CalorimeterTestDate DATETIME NULL,
TestYear INT NULL,
TestMonth INT NULL,
FiscYear INT NULL,
FiscMonth INT NULL
)
INSERT INTO CompFails
(ModelDesc,
CalorimeterTestDate,
TestYear,
TestMonth,
FiscYear,
FiscMonth)
VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-03 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-03 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-10 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-11 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-11 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-14 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-14 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-12 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-12 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-18 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-19 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-20 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-28 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-18 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-20 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-27 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-11-02 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-10-24 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-11-08 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-10-25 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-25 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-11-04 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-07 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-08 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-04 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-10-31 00:00:00.000,2005,10,2006,2)
VALUES('tstModelDesc',2005-12-05 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-11-15 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-10 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-10 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-12-02 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-11-15 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-29 00:00:00.000,2005,11,2006,3)
VALUES('tstModelDesc',2005-11-16 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-12-07 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-12-09 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-12-16 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-12-17 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2006-01-04 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-02 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2005-12-19 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2006-01-09 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-13 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-02-07 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2006-01-13 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-16 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-18 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-19 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-28 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-22 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-02-01 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-25 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-20 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-21 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-02-01 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-01-30 00:00:00.000,2006,1, 2006,5)
VALUES('tstModelDesc',2006-01-27 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-28 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-02-02 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-08 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-22 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-16 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-16 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-17 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-15 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-14 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-15 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-21 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-18 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-19 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-21 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-01-02 00:00:00.000,2006,1, 2006,4)At the moment I'm not too sure about doing it one query - but you can
easily do it in two queries.
Barry|||I've continued working on this problem and have a partial solution if anyone
could help me refine it It still returns two rows with the same values The
results follow this select statement.
Select DISTINCT
y.CalCompCnt,
compfails.TestYear as CalYear,
compfails.TestMonth as CalMonth,
x.FiscCompCnt,
compfails.FiscYear as FiscYear,
compfails.FiscMonth as FiscMonth
From CompFails
INNER JOIN (Select DISTINCT Count(f.modelDesc) as FiscCompCnt,
f.fiscYear, f.fiscMonth from CompFails f
group by FiscYear, FiscMonth)x ON x.FiscYear = compfails.Fiscyear and
x.FiscMonth = compfails.FiscMonth
INNER JOIN (Select DISTINCT Count(b.modelDesc) as CalCompCnt,
b.TestYear, b.TestMonth from CompFails b
group by b.TestYear, b.TestMonth)y ON y.testYear = compfails.testyear and
y.testMonth = compfails.testMonth
group by compfails.FiscYear, compfails.FiscMonth,
compfails.testYear, compfails.testMonth, x.fisccompcnt, y.calcompcnt
CalCompCnt CalYear CalMonth FiscCompCnt FiscYear FiscMonth
-- -- -- -- -- --
27 2005 10 26 2006 1
27 2005 10 18 2006 2
18 2005 11 18 2006 2
18 2005 11 11 2006 3
10 2005 12 11 2006 3
20 2006 1 19 2006 4
20 2006 1 17 2006 5
16 2006 2 17 2006 5
"StvJston" wrote:
> I have a query that returns results based on a count of tests done by peri
od
> and year. The period can either be a calendar month or Fiscal Calendar mo
nth.
> As always thanks in advance!
> Select
> count( modelDesc)as CompCnt,
> TestYear as CalYear,
> TestMonth as CalMonth,
> FiscYear as FiscYear,
> FiscMonth as FiscMonth
> From CompFails
> group by ModelDesc,FiscYear, FiscMonth, testYear, testMonth
> I also attempted to set the count as
> select COUNT(ModelDesc)as ModelCnt FROM (SELECT
> DISTINCT f.modelDesc from compfails f
> group by FiscYear, FiscMonth) as FiscCompCnt
> And got the "subquery returns more than one row error"
>
> Currently the query returns the results as so:
> Current Results
> COMPcnt CalYear CalMonth FiscYear FiscMonth
> -- -- -- -- --
> 26 2005 10 2006 1
> 1 2005 10 2006 2
> 17 2005 11 2006 2
> 1 2005 11 2006 3
> 10 2005 12 2006 3
> 19 2006 1 2006 4
> 1 2006 1 2006 5
> 16 2006 2 2006 5
> I would like the results as follows but I am having a heck of a time
> figuring the syntax.
> CalCompCnt CalYear CalMonth FiscCompCnt FiscYear FiscMonth
> -- -- -- -- --
> 20 2006 1 26 2006 1
> 16 2006 2 18 2006 2
> 27 2005 10 11 2006 3
> 18 2005 11 19 2006 4
> 10 2005 12 17 2006 5
>
> DDL Follows:
> CREATE TABLE CompFails (
> ModelDesc Varchar(40) NULL,
> CalorimeterTestDate DATETIME NULL,
> TestYear INT NULL,
> TestMonth INT NULL,
> FiscYear INT NULL,
> FiscMonth INT NULL
> )
> INSERT INTO CompFails
> (ModelDesc,
> CalorimeterTestDate,
> TestYear,
> TestMonth,
> FiscYear,
> FiscMonth)
> VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-03 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-03 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-10 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-11 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-11 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-14 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-14 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-12 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-12 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-18 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-19 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-20 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-28 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-18 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-20 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-27 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-11-02 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-10-24 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-11-08 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-10-25 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-10-25 00:00:00.000,2005,10,2006,1)
> VALUES('tstModelDesc',2005-11-04 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-07 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-08 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-04 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-10-31 00:00:00.000,2005,10,2006,2)
> VALUES('tstModelDesc',2005-12-05 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2005-11-15 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-10 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-10 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-12-02 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2005-11-15 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-29 00:00:00.000,2005,11,2006,3)
> VALUES('tstModelDesc',2005-11-16 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
> VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2005-12-07 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2005-12-09 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2005-12-16 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2005-12-17 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2006-01-04 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-02 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2005-12-19 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2006-01-09 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-13 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-02-07 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
> VALUES('tstModelDesc',2006-01-13 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-16 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-18 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-19 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-28 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-22 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-02-01 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-25 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-20 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-21 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-02-01 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-01-30 00:00:00.000,2006,1, 2006,5)
> VALUES('tstModelDesc',2006-01-27 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-01-28 00:00:00.000,2006,1, 2006,4)
> VALUES('tstModelDesc',2006-02-02 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-08 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-22 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-16 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-16 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-17 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-15 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-14 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-15 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-21 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-18 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-19 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-02-21 00:00:00.000,2006,2, 2006,5)
> VALUES('tstModelDesc',2006-01-02 00:00:00.000,2006,1, 2006,4)
>
>|||If I understand the problem, the fiscal calendar is different from the
regular calendar in two ways. First, it is offset by three months.
Second, months do not cut off at the calendar month end.
You need to construct a reference table to relate the two calendars.
The table should have one row per month. The table looks a lot like
certain columns your desired results, though it should contain the
full calendar of months:
CalYear CalMonth FiscYear FiscMonth
-- -- -- --
2005 10 2006 1
2005 11 2006 2
2005 12 2006 3
2006 1 2006 4
2006 2 2006 5
This table needs two unique constraints, (CalYear,CalMonth) and
(FiscYear,FiscMonth). One should be the primary key, the other a
unique index.
CREATE TABLE Months (
TestYear INT NULL,
TestMonth INT NULL,
FiscYear INT NULL,
FiscMonth INT NULL
)
GO
INSERT Months VALUES (2005,10,2006,1)
INSERT Months VALUES (2005,11,2006,2)
INSERT Months VALUES (2005,12,2006,3)
INSERT Months VALUES (2006, 1,2006,4)
INSERT Months VALUES (2006, 2,2006,5)
GO
We then will calculate each set of numbers, calendar and fiscal, in
two derived tables, and use the Months table to bring it all together.
SELECT M.TestYear, M.TestMonth, C.TestCount,
M.FiscYear, M.FiscMonth, F.FiscCount
FROM Months as M
LEFT OUTER
JOIN (select TestYear, TestMonth, count(*) as TestCount
from CompFails
group by TestYear, TestMonth) as C
ON M.TestYear = C.TestYear
AND M.TestMonth = C.TestMonth
LEFT OUTER
JOIN (select FiscYear, FiscMonth, count(*) as FiscCount
from CompFails
group by FiscYear, FiscMonth) as F
ON M.FiscYear = F.FiscYear
AND M.FiscMonth = F.FiscMonth
TestYear TestMonth TestCount FiscYear FiscMonth FiscCount
-- -- -- -- -- --
2005 10 27 2006 1 26
2005 11 18 2006 2 18
2005 12 10 2006 3 11
2006 1 20 2006 4 19
2006 2 16 2006 5 17
Roy Harvey
Beacon Falls, CT
On Mon, 24 Apr 2006 11:31:02 -0700, StvJston
<StvJston@.discussions.microsoft.com> wrote:
>I have a query that returns results based on a count of tests done by perio
d
>and year. The period can either be a calendar month or Fiscal Calendar mon
th.
>As always thanks in advance!
>Select
> count( modelDesc)as CompCnt,
> TestYear as CalYear,
> TestMonth as CalMonth,
> FiscYear as FiscYear,
> FiscMonth as FiscMonth
>From CompFails
>group by ModelDesc,FiscYear, FiscMonth, testYear, testMonth
>I also attempted to set the count as
>select COUNT(ModelDesc)as ModelCnt FROM (SELECT
> DISTINCT f.modelDesc from compfails f
> group by FiscYear, FiscMonth) as FiscCompCnt
>And got the "subquery returns more than one row error"
>
>Currently the query returns the results as so:
>Current Results
> COMPcnt CalYear CalMonth FiscYear FiscMonth
> -- -- -- -- --
> 26 2005 10 2006 1
> 1 2005 10 2006 2
> 17 2005 11 2006 2
> 1 2005 11 2006 3
> 10 2005 12 2006 3
> 19 2006 1 2006 4
> 1 2006 1 2006 5
> 16 2006 2 2006 5
>I would like the results as follows but I am having a heck of a time
>figuring the syntax.
> CalCompCnt CalYear CalMonth FiscCompCnt FiscYear FiscMonth
>-- -- -- -- --
> 20 2006 1 26 2006 1
> 16 2006 2 18 2006 2
> 27 2005 10 11 2006 3
> 18 2005 11 19 2006 4
> 10 2005 12 17 2006 5
>
>DDL Follows:
>CREATE TABLE CompFails (
>ModelDesc Varchar(40) NULL,
>CalorimeterTestDate DATETIME NULL,
>TestYear INT NULL,
>TestMonth INT NULL,
>FiscYear INT NULL,
>FiscMonth INT NULL
> )
>INSERT INTO CompFails
> (ModelDesc,
> CalorimeterTestDate,
> TestYear,
> TestMonth,
> FiscYear,
> FiscMonth)
>VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-03 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-03 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-10 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-11 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-11 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-14 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-14 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-12 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-12 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-18 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-19 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-20 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-28 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-18 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-20 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-27 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-11-02 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-10-24 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-11-08 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-10-25 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-10-25 00:00:00.000,2005,10,2006,1)
>VALUES('tstModelDesc',2005-11-04 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-07 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-08 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-04 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-10-31 00:00:00.000,2005,10,2006,2)
>VALUES('tstModelDesc',2005-12-05 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2005-11-15 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-10 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-10 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-12-02 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2005-11-15 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-29 00:00:00.000,2005,11,2006,3)
>VALUES('tstModelDesc',2005-11-16 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
>VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2005-12-07 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2005-12-09 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2005-12-16 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2005-12-17 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2006-01-04 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-02 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2005-12-19 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2006-01-09 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-13 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-02-07 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
>VALUES('tstModelDesc',2006-01-13 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-16 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-18 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-19 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-28 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-22 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-02-01 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-25 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-20 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-21 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-02-01 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-01-30 00:00:00.000,2006,1, 2006,5)
>VALUES('tstModelDesc',2006-01-27 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-01-28 00:00:00.000,2006,1, 2006,4)
>VALUES('tstModelDesc',2006-02-02 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-08 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-22 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-16 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-16 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-17 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-15 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-14 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-15 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-21 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-18 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-19 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-02-21 00:00:00.000,2006,2, 2006,5)
>VALUES('tstModelDesc',2006-01-02 00:00:00.000,2006,1, 2006,4)
>|||First construct a general enterprise calendar table; you should talk to
the trolls in accounting so you get the fiscal part right.
CREATE TABLE Calendar
(cal_date DATETIME NOT NULL PRIMARY KEY,
cal_year INTEGER NOT NULL,
cal_month INTEGER NOT NULL,
fiscal_year INTEGER NOT NULL,
fiscal_month INTEGER NOT NULL,
etc.);
Now get your tests table right. For example, why are things you must
know set to NULLs? Why did you have no key?
CREATE TABLE CompFails
(model_desc VARCHAR(40) NOT NULL,
test_date DATETIME NOT NULL,
PRIMARY KEY (model_desc, test_date)
);
Then your view or query will look like this skeleton:
SELECT ..
FROM CompFails AS T, Calendar AS C
WHERE T.fail_date = C.cal_date
AND ..;
He had computable columns in your original non-table, in addition to
the other design flaws.|||Hi,
Here is the query that gives you the result that you wanted.
select a.CalCompCnt,a.calyear ,a.calmonth,b.FiscCompCnt,b.FiscYear
,b.FiscMonth from
(select count(*) as id ,x.CalCompCnt,x.calyear ,x.calmonth
from
(select count(modeldesc) as CalCompCnt, TestYear as calyear, TestMonth as
calmonth from compfails group by TestYear,TestMonth) as x,
(select count(modeldesc) as CalCompCnt, TestYear as calyear, TestMonth as
calmonth from compfails group by TestYear,TestMonth) as y
where x.calmonth >= y.calmonth
group by x.CalCompCnt,x.calyear ,x.calmonth ) as a,
(select count(*) as id ,x.FiscCompCnt,x.FiscYear ,x.FiscMonth
from
(select count(modeldesc) as FiscCompCnt, FiscYear, FiscMonth from compfails
group by FiscYear, FiscMonth) as x,
(select count(modeldesc) as FiscCompCnt, FiscYear, FiscMonth from compfails
group by FiscYear, FiscMonth) as y
where x.FiscMonth >= y.FiscMonth
group by x.FiscCompCnt,x.FiscYear ,x.FiscMonth ) as b
where a.id = b.id
-- But a note of caution. I will never use this query in production. and the
result that you want doesn't seem functionally valid. I doubt you really hav
e
a requirement like this. Anyways, I had fun in writing this query :)
Hope I helped.|||Here is a better solution. I feel its got more meaning. relating the fiscal
and the calendar month. and its simple.
Let me know your thoughts. Hope this helps
select * from
(select count(modeldesc) as CalCompCnt, TestYear as calyear, TestMonth as
calmonth from compfails group by TestYear,TestMonth) as a,
(select count(modeldesc) as FiscCompCnt, FiscYear, FiscMonth from compfails
group by FiscYear, FiscMonth) as b
where fiscmonth + (12 *(FiscYear -calyear)) - calmonth = 3
and fiscyear = calyear + floor(calmonth/10)|||I thought you would have a comment, always appreciate your input.
I have a Fiscal Calendar table that is correct for the corp. The data SET I
am selecting from "CompFails" is a temp table that contains the records that
meet the selection criteria.
One of the report requirements (I know let the report do the work) is the
ability to select between fiscal and calendar data points. By returning bot
h
sets at the same time I can keep the fiscal/calendar date selection out of m
y
sql.
CompFails will be a temporary table in a stored proc once development is
done. Nulls and indexes will get handled as I continue testing. (There will
never be null fields all the fields are required from the selection or
computed results for the specific model/serial no in each row.
Primary key is done through the serialNo field which I did not include in my
example since it wasn't required for the select I am doing.
I solved this problem myself lastnight using another tack.
I created a Union query with the desired fields and added a dummy flag for
fiscal or calendar.
What seems to have been causing the problem was when a record was outside of
the Fiscal Month range but still in the calendar month range I would get a
duplicate return.
It was a small roadblock but as always learn or die...(grin)
Thanks all for the help and comments...
"--CELKO--" wrote:
> First construct a general enterprise calendar table; you should talk to
> the trolls in accounting so you get the fiscal part right.
> CREATE TABLE Calendar
> (cal_date DATETIME NOT NULL PRIMARY KEY,
> cal_year INTEGER NOT NULL,
> cal_month INTEGER NOT NULL,
> fiscal_year INTEGER NOT NULL,
> fiscal_month INTEGER NOT NULL,
> etc.);
> Now get your tests table right. For example, why are things you must
> know set to NULLs? Why did you have no key?
> CREATE TABLE CompFails
> (model_desc VARCHAR(40) NOT NULL,
> test_date DATETIME NOT NULL,
> PRIMARY KEY (model_desc, test_date)
> );
> Then your view or query will look like this skeleton:
> SELECT ..
> FROM CompFails AS T, Calendar AS C
> WHERE T.fail_date = C.cal_date
> AND ..;
> He had computable columns in your original non-table, in addition to
> the other design flaws.
>|||I should have made myself more clear but when formulating a question its har
d
to figure out what information people need.
The CompFails table is a result set that is returned from sql that performs
calculations and summations on each record and loads CompFails.
the testMonth, Testyear, and FiscYear and FiscMonth are returned based on
the actual test date in the original data. They are correct values based on
the actual test date data using the year(CalorimetertestDate) and
month(CalorimetertestDate). The fiscal Year and month are returned to
CompFails using the following sql
INNER JOIN (select f.Monthno, f.Fiscalyear, MIN(f.BeginDate) as FBDate,
Max(f.Enddate)as FEndDate from fiscalcalendar f
where f.BeginDate>=CONVERT(VarCHAR, @.StartDate ,112)
and f.EndDate <= CONVERT(Varchar, @.EndDate,112)
group by f.monthno, f.Fiscalyear) fTest ON calorimetertestdate
between
ftest.fbdate and ftest.fenddate
I didn't expect folks to get caught with figuring dates. I just wanted to
return the correct monthly totals based on fiscal months or Calendar months.
By using a Union query with a dummy flag (calendar or Fiscal) I get both set
s
correctly and only have to make one call to the table.
Thanks for the help.
"Roy Harvey" wrote:
> If I understand the problem, the fiscal calendar is different from the
> regular calendar in two ways. First, it is offset by three months.
> Second, months do not cut off at the calendar month end.
> You need to construct a reference table to relate the two calendars.
> The table should have one row per month. The table looks a lot like
> certain columns your desired results, though it should contain the
> full calendar of months:
> CalYear CalMonth FiscYear FiscMonth
> -- -- -- --
> 2005 10 2006 1
> 2005 11 2006 2
> 2005 12 2006 3
> 2006 1 2006 4
> 2006 2 2006 5
> This table needs two unique constraints, (CalYear,CalMonth) and
> (FiscYear,FiscMonth). One should be the primary key, the other a
> unique index.
> CREATE TABLE Months (
> TestYear INT NULL,
> TestMonth INT NULL,
> FiscYear INT NULL,
> FiscMonth INT NULL
> )
> GO
> INSERT Months VALUES (2005,10,2006,1)
> INSERT Months VALUES (2005,11,2006,2)
> INSERT Months VALUES (2005,12,2006,3)
> INSERT Months VALUES (2006, 1,2006,4)
> INSERT Months VALUES (2006, 2,2006,5)
> GO
> We then will calculate each set of numbers, calendar and fiscal, in
> two derived tables, and use the Months table to bring it all together.
> SELECT M.TestYear, M.TestMonth, C.TestCount,
> M.FiscYear, M.FiscMonth, F.FiscCount
> FROM Months as M
> LEFT OUTER
> JOIN (select TestYear, TestMonth, count(*) as TestCount
> from CompFails
> group by TestYear, TestMonth) as C
> ON M.TestYear = C.TestYear
> AND M.TestMonth = C.TestMonth
> LEFT OUTER
> JOIN (select FiscYear, FiscMonth, count(*) as FiscCount
> from CompFails
> group by FiscYear, FiscMonth) as F
> ON M.FiscYear = F.FiscYear
> AND M.FiscMonth = F.FiscMonth
> TestYear TestMonth TestCount FiscYear FiscMonth FiscCount
> -- -- -- -- -- --
> 2005 10 27 2006 1 26
> 2005 11 18 2006 2 18
> 2005 12 10 2006 3 11
> 2006 1 20 2006 4 19
> 2006 2 16 2006 5 17
> Roy Harvey
> Beacon Falls, CT
>
> On Mon, 24 Apr 2006 11:31:02 -0700, StvJston
> <StvJston@.discussions.microsoft.com> wrote:
>
>
counting based on bit flag
I have a table with an id field (int) and a bit flag. example below
id flag
1 true
1 true
1 false
1 true
2 true
2 false
I am looking for a query that will provide me the following results if possible
id true false
1 3 1
2 1 1
Any and all help is appreciated.
My efforts so far aren't worth sharing. I am looking for completely new approaches.
Thanks a ton
Use something like this:
Code Snippet
DECLARE @.MyTable table
( ID int,
Flag smallint
)
INSERT INTO @.MyTable VALUES ( 1, 1 )
INSERT INTO @.MyTable VALUES ( 1, 1 )
INSERT INTO @.MyTable VALUES ( 1, 0 )
INSERT INTO @.MyTable VALUES ( 1, 1 )
INSERT INTO @.MyTable VALUES ( 2, 1 )
INSERT INTO @.MyTable VALUES ( 2, 0 )
SELECT
[ID],
True = sum( CASE Flag WHEN 1 THEN 1 ELSE 0 END ),
False = sum ( CASE Flag WHEN 0 THEN 1 ELSE 0 END )
FROM @.MyTable
GROUP BY [ID]
ID True False
-- -- --
1 3 1
2 1 1
Counting based on a field combination
Having a brainfart...
I need a query that returns a record count, based on two distinct fields.
For example:
Order Revision Customer
001 1 Bob
001 2 Bob
002 1 John
003 1 John
004 1 John
005 1 Bob
006 1 Bob
006 2 Bob
The query on the above data should return a count of orders, regardless of
the revision numbers (each order number should only be counted once).
So WHERE Customer = 'Bob', it should return OrderCount = 3
TIA!
Calan> The query on the above data should return a count of orders,
regardless of
> the revision numbers (each order number should only be counted once).
> So WHERE Customer = 'Bob', it should return OrderCount = 3
> TIA!
> Calan
--something like this?
create table #foo (
Ord int,
Revision int,
Customer varchar(10)
primary key (ord,revision)
)
GO
insert into #foo( Ord, Revision, Customer )Values(001, 1, 'Bob' )
insert into #foo( Ord, Revision, Customer )Values(001, 2, 'Bob' )
insert into #foo( Ord, Revision, Customer )Values(002, 1, 'John' )
insert into #foo( Ord, Revision, Customer )Values(003, 1, 'John' )
insert into #foo( Ord, Revision, Customer )Values(004, 1, 'John' )
insert into #foo( Ord, Revision, Customer )Values(005, 1, 'Bob' )
insert into #foo( Ord, Revision, Customer )Values(006, 1, 'Bob' )
insert into #foo( Ord, Revision, Customer )Values(006, 2, 'Bob' )
select customer,count( distinct ord) as 'orders'
from #foo
group by customer
drop table #foo
go
--Strider|||Please give proper DDL with your posts, including the definition of
keys and constraints. They can make a big difference to the answer.
Post sample data as INSERT statements so that we can test out solutions
with your data.
Assuming the key in this case is (order,revision):
SELECT customer, count(*)
FROM Orders
GROUP BY customer
If I'm wrong about the key, try:
SELECT customer, count(DISTINCT order)
FROM Orders
GROUP BY customer
(both untested)
The COUNT(DISTINCT...) version typically runs significantly slower.
--
David Portas
SQL Server MVP
--
Subscribe to:
Posts (Atom)