All,
I have two functions where one calls the other:
-- simplified example
create function plus(@.a as integer, @.b as integer)
returns integer
begin
return @.a + @.b
end
go
create function t(@.a as integer)
returns integer
begin
return cr_user.plus(10, @.a)
end
go
Is there any way to create those without explicitely mentioning the
database user ("cr_user" in this case)? Ideally I would like to put them
into a file and execute it with osql and those functions are created for
the current user. Until now I could not find a working placeholder to use
in "t" that will call the current user's "plus". (SUSER_SNAME does not
work.)
The only alternative seems to be ATM to always create functions for dbo
which is always defined.
Any ideas? TIA
Kind regards
robertWhy would you need functions that are owned by specific users? Does the
purpose of these functions depend on individual users?
Make it a practice to always qualify object names with the owner.
ML|||ML wrote:
> Why would you need functions that are owned by specific users?
You can only use functions with the owner as prefix and other parts of the
software insert the user as prefix when generating queries.
> Does
> the purpose of these functions depend on individual users?
No, other than that it is only used by a single user.
> Make it a practice to always qualify object names with the owner.
Why? To avoid name clashes? I prefer the unqualified version. It avoids
a lot of hassle and we don't have to take care of name clashes.
Regards
robert|||"Name clashes"?
There are no name clashes if common objects have the default owner (dbo). If
you created these obviously common functions with a qualified name (using db
o
as the owner), you could prevent any problems.
The pros of using qualified names are simply too numerous to mention here.
In SQL 2005 the owner is replaced by a more powerful quaifier - the schema -
maybe you should look into it to better understand its purpose.
I'm not criticizing your standards, but maybe they aren't as perferct as
you'd like to believe.
Please explain with an example why you need to create objects for every
user, when all of the objects are equal in purpose? And what do you mean by
"only used by a single user"? Is this a security issue?
ML|||ML wrote:
> "Name clashes"?
> There are no name clashes if common objects have the default owner
> (dbo).
But there would be if multiple objects with the same name but different
owners existed. I assumed you were referring to this situation when
promoting qualified names.
> If you created these obviously common functions with a
> qualified name (using dbo as the owner), you could prevent any
> problems.
> The pros of using qualified names are simply too numerous to mention
> here.
Impressive. Care to reveal some detail here? All I found so far was
faster access to execution plans and better reuse of execution plans of
stored procedures when called with qualified names.
> In SQL 2005 the owner is replaced by a more powerful quaifier - the
> schema - maybe you should look into it to better understand its
> purpose.
I might if I have the time. But ATM we're on 2k so I wouldn't want to
complicate things by mixing in concepts we can't use anyway. :-)
> I'm not criticizing your standards, but maybe they aren't as perferct
> as you'd like to believe.
I'm far from believing they are perfect. They just exist. And there is
some effort attached to changing them. Hence my conservatism. :-)
> Please explain with an example why you need to create objects for
> every user, when all of the objects are equal in purpose? And what do
> you mean by "only used by a single user"? Is this a security issue?
I don't create the same objects for every user. There is just one (1)
database user (apart from dbo). All tables, views, procedures and
functions are created unqualified and thus belong to this single user.
I'd like to stick with that simple policy. Unfortunately you can create
functions unqualified but you have to invoke them qualified. I don't see
why this is or has to be but apparently it's the way things are. Do you
have an idea why they did it this way?
I'll check whether the privileges our app requires are sufficient to
create the function for user dbo. That seems the second best alternative.
Hm, it seems creating is ok but invoking is prohibited. Darn. Seems like
I have to go down the preprocessing road.
Cheers
robert|||Ok, simply put most benefits of using qualified names are related to
performance. As you've mentioned. Another might be context of use, yet in
your case I see no need for that, since you in fact deal with a single user
(owner). In a multi role environment it could provide more clarity if method
s
that basically serve the same purpose for a variety of roles need to provide
special context-related features, which can be simplified using different
owners while keeping one name. E.g. a procedure to get product details might
return a different result for owner Engineering and a different one for owne
r
Marketing.
If you are using a single owner, why not use dbo?
But on the other hand, your only issue is with invoking functions, so either
way using a qualified name is mandatory. Simply because MS SQL Server says
so. :)
ML|||ML wrote:
> Ok, simply put most benefits of using qualified names are related to
> performance. As you've mentioned. Another might be context of use,
> yet in your case I see no need for that, since you in fact deal with
> a single user (owner). In a multi role environment it could provide
> more clarity if methods that basically serve the same purpose for a
> variety of roles need to provide special context-related features,
> which can be simplified using different owners while keeping one
> name. E.g. a procedure to get product details might return a
> different result for owner Engineering and a different one for owner
> Marketing.
Ok, thanks for clarifying.
> If you are using a single owner, why not use dbo?
I would but it seems the permissions we require for the DB user are
sufficient to create a function with owner dbo but not to invoke it later.
For the time being I'll stick with the preprocessing (i.e. the actual user
name is injected before SQL is executed).
> But on the other hand, your only issue is with invoking functions, so
> either way using a qualified name is mandatory. Simply because MS SQL
> Server says so. :)
Yep. Still I don't have an idea why this is so - I suspect the words
"historic" and "legacy" would be used in an explanation... :-)
Again, thanks for helping out!
Kind regards
robert
Showing posts with label returns. Show all posts
Showing posts with label returns. Show all posts
Tuesday, March 27, 2012
Create a string of records from a table in a stored procedure,
I have a table tblCustomers in a one-to-many relationship with table
tblProducts.
What I want to do is to create a stored procudure that returns a list
of each customer in tblCustomers but also creates a field showing a
string (separated by commas)of each matching record in tblProducts.
tblProducts.
What I want to do is to create a stored procudure that returns a list
of each customer in tblCustomers but also creates a field showing a
string (separated by commas)of each matching record in tblProducts.
So the return would look like:
CustID Customer ProductList
1 Smith Apples, Oranges, Pears
2 Jones Pencils, Pens, Paper
etc...
Instead of:
CustID Customer Product
1 Smith Apples
1 Smith Oranges
1 Smith Pears
2 Jones Pencils
2 Jones Pens
2 Jones Paper
Which is what you get with this:
SELECT tblCusomers.CustID, tblCusomers.Customer,
tblProducts.Product
FROM
tblCusomers INNER JOIN
tblProducts ON
tblCustomers.CustID = tblProducts.CustID
I'd appreciate any help!
lq"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0401190748.491c6219@.posting.google.c om...
> I have a table tblCustomers in a one-to-many relationship with table
> tblProducts.
> What I want to do is to create a stored procudure that returns a list
> of each customer in tblCustomers but also creates a field showing a
> string (separated by commas)of each matching record in tblProducts.
> So the return would look like:
> CustID Customer ProductList
> 1 Smith Apples, Oranges, Pears
> 2 Jones Pencils, Pens, Paper
> etc...
> Instead of:
> CustID Customer Product
> 1 Smith Apples
> 1 Smith Oranges
> 1 Smith Pears
> 2 Jones Pencils
> 2 Jones Pens
> 2 Jones Paper
> Which is what you get with this:
> SELECT tblCusomers.CustID, tblCusomers.Customer,
> tblProducts.Product
> FROM
> tblCusomers INNER JOIN
> tblProducts ON
> tblCustomers.CustID = tblProducts.CustID
> I'd appreciate any help!
> lq
Generally the best way to do this would be in a front end application, where
it's easier to handle string manipulation. But this thread may be useful if
you have no other choice than to do it in MSSQL:
http://tinyurl.com/bib2
Simon|||laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0401190748.491c6219@.posting.google.com>...
> I have a table tblCustomers in a one-to-many relationship with table
> tblProducts.
> What I want to do is to create a stored procudure that returns a list
> of each customer in tblCustomers but also creates a field showing a
> string (separated by commas)of each matching record in tblProducts.
> So the return would look like:
> CustID Customer ProductList
> 1 Smith Apples, Oranges, Pears
> 2 Jones Pencils, Pens, Paper
> etc...
> Instead of:
> CustID Customer Product
> 1 Smith Apples
> 1 Smith Oranges
> 1 Smith Pears
> 2 Jones Pencils
> 2 Jones Pens
> 2 Jones Paper
> Which is what you get with this:
> SELECT tblCusomers.CustID, tblCusomers.Customer,
> tblProducts.Product
> FROM
> tblCusomers INNER JOIN
> tblProducts ON
> tblCustomers.CustID = tblProducts.CustID
> I'd appreciate any help!
> lq
You can try the following code in same sequence to get the string of
concatinated records
/*Temp table */
drop table tb_view
Create table dbo.tb_View
(
CustID int,
Customer varchar(20),
Product varchar(20)
)
INSERT INTO tb_View values (1,'Smith','Apples')
INSERT INTO tb_View values (1,'Smith','Oranges')
INSERT INTO tb_View values (1,'Smith','Pears')
INSERT INTO tb_View values (2,'Jones','Pencils')
INSERT INTO tb_View values (2,'Jones','Pens')
INSERT INTO tb_View values (2,'Jones','Paper')
/*Create a function to do the job*/
Create function dbo.fn_concatinate(@.CustId as int) returns
varchar(100)
as
begin
declare @.ret_value varchar(100)
SET @.ret_value=''
Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
CustID=@.CustId
RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
end
/*Use function in query */
select CustID,Customer,dbo.fn_concatinate(CustID) from tb_View group
by CustID,Customer|||Amit Gupta (amiiit@.hotmail.com) writes:
> /*Create a function to do the job*/
> Create function dbo.fn_concatinate(@.CustId as int) returns
> varchar(100)
> as
> begin
> declare @.ret_value varchar(100)
> SET @.ret_value=''
> Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
> CustID=@.CustId
> RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
> end
Not that this function relies on undefined behaviour. It may return
the expected result, or it may return something else. See
http://support.microsoft.com/default.aspx?scid=287515.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Amit,
Thank you for your examples. I realize there is still a lot to learn
for with the SQL. I have never used "Create function" and don't know
where it goes. Sorry for the ignorance...
lq
amiiit@.hotmail.com (Amit Gupta) wrote in message news:<6e4179ce.0401200003.67a1735e@.posting.google.com>...
> laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0401190748.491c6219@.posting.google.com>...
> > I have a table tblCustomers in a one-to-many relationship with table
> > tblProducts.
> > What I want to do is to create a stored procudure that returns a list
> > of each customer in tblCustomers but also creates a field showing a
> > string (separated by commas)of each matching record in tblProducts.
> > So the return would look like:
> > CustID Customer ProductList
> > 1 Smith Apples, Oranges, Pears
> > 2 Jones Pencils, Pens, Paper
> > etc...
> > Instead of:
> > CustID Customer Product
> > 1 Smith Apples
> > 1 Smith Oranges
> > 1 Smith Pears
> > 2 Jones Pencils
> > 2 Jones Pens
> > 2 Jones Paper
> > Which is what you get with this:
> > SELECT tblCusomers.CustID, tblCusomers.Customer,
> > tblProducts.Product
> > FROM
> > tblCusomers INNER JOIN
> > tblProducts ON
> > tblCustomers.CustID = tblProducts.CustID
> > I'd appreciate any help!
> > lq
>
> You can try the following code in same sequence to get the string of
> concatinated records
> /*Temp table */
> drop table tb_view
> Create table dbo.tb_View
> (
> CustID int,
> Customer varchar(20),
> Product varchar(20)
> )
> INSERT INTO tb_View values (1,'Smith','Apples')
> INSERT INTO tb_View values (1,'Smith','Oranges')
> INSERT INTO tb_View values (1,'Smith','Pears')
> INSERT INTO tb_View values (2,'Jones','Pencils')
> INSERT INTO tb_View values (2,'Jones','Pens')
> INSERT INTO tb_View values (2,'Jones','Paper')
> /*Create a function to do the job*/
> Create function dbo.fn_concatinate(@.CustId as int) returns
> varchar(100)
> as
> begin
> declare @.ret_value varchar(100)
> SET @.ret_value=''
> Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
> CustID=@.CustId
> RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
> end
> /*Use function in query */
> select CustID,Customer,dbo.fn_concatinate(CustID) from tb_View group
> by CustID,Customer|||Specifically...
When I try to cretae a stored procedure containing this Create
Function, I get the error:
"You cannot chnage the object type in a script."
lq
amiiit@.hotmail.com (Amit Gupta) wrote in message news:<6e4179ce.0401200003.67a1735e@.posting.google.com>...
> laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0401190748.491c6219@.posting.google.com>...
> > I have a table tblCustomers in a one-to-many relationship with table
> > tblProducts.
> > What I want to do is to create a stored procudure that returns a list
> > of each customer in tblCustomers but also creates a field showing a
> > string (separated by commas)of each matching record in tblProducts.
> > So the return would look like:
> > CustID Customer ProductList
> > 1 Smith Apples, Oranges, Pears
> > 2 Jones Pencils, Pens, Paper
> > etc...
> > Instead of:
> > CustID Customer Product
> > 1 Smith Apples
> > 1 Smith Oranges
> > 1 Smith Pears
> > 2 Jones Pencils
> > 2 Jones Pens
> > 2 Jones Paper
> > Which is what you get with this:
> > SELECT tblCusomers.CustID, tblCusomers.Customer,
> > tblProducts.Product
> > FROM
> > tblCusomers INNER JOIN
> > tblProducts ON
> > tblCustomers.CustID = tblProducts.CustID
> > I'd appreciate any help!
> > lq
>
> You can try the following code in same sequence to get the string of
> concatinated records
> /*Temp table */
> drop table tb_view
> Create table dbo.tb_View
> (
> CustID int,
> Customer varchar(20),
> Product varchar(20)
> )
> INSERT INTO tb_View values (1,'Smith','Apples')
> INSERT INTO tb_View values (1,'Smith','Oranges')
> INSERT INTO tb_View values (1,'Smith','Pears')
> INSERT INTO tb_View values (2,'Jones','Pencils')
> INSERT INTO tb_View values (2,'Jones','Pens')
> INSERT INTO tb_View values (2,'Jones','Paper')
> /*Create a function to do the job*/
> Create function dbo.fn_concatinate(@.CustId as int) returns
> varchar(100)
> as
> begin
> declare @.ret_value varchar(100)
> SET @.ret_value=''
> Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
> CustID=@.CustId
> RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
> end
> /*Use function in query */
> select CustID,Customer,dbo.fn_concatinate(CustID) from tb_View group
> by CustID,Customer|||In the DAH! Department...
I realize I have to do the Create Function in Enterprise Manager. I've
been using Microsoft Access MSDE as the front end development tool...
lq
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns947725BA51CYazorman@.127.0.0.1>...
> Amit Gupta (amiiit@.hotmail.com) writes:
> > /*Create a function to do the job*/
> > Create function dbo.fn_concatinate(@.CustId as int) returns
> > varchar(100)
> > as
> > begin
> > declare @.ret_value varchar(100)
> > SET @.ret_value=''
> > Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
> > CustID=@.CustId
> > RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
> > end
> Not that this function relies on undefined behaviour. It may return
> the expected result, or it may return something else. See
> http://support.microsoft.com/default.aspx?scid=287515.
Sunday, March 25, 2012
Create a new date
Hi,
I want to write a query that returns me the first date of the month...
I wrote this query
SELECT DateAdd(day,- Day(GetDate()) + 1,GetDate())
THis works fine for me, is there any function that build a new date, without using the DateAdd function?
here is another way
select dateadd(mm, datediff(mm, 0, getdate())+0, 0)
and another
select convert(datetime,(convert(varchar(6),getdate(),112) + '01'))
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Thanks 
I used the second query :
select convert(datetime,(convert(varchar(6),getdate(),112) + '01'))
Friday, February 17, 2012
counting number of specific rows in SQL
hi,
I would like to create a user defined SQL function which returns the number of rows which meets certain condition, and the average value of one of the culomns. I cannot find a code example for it. Please help.
Thanks,
Dror.
I don't think a SQL Server function is the best option for you. If you are returning this to SSRS, you'll probably want to leverage a stored procedure. Still, here is a generic representation of what you're asking for:
create function dbo.MyFunction( @.MyParam varchar(10) )
returns @.ret table (MyCount int, MyAvg float)
begin
insert into @.ret (MyCount, MyAvg)
select count(*), avg(MyField) from MyTable where MyOtherField=@.MyParam
return
end
This function returns a table. To retrieve data, you will call it as follows:
select *
from dbo.MyFunction( @.MyParam)
Again, I don't think this is the best approach to this problem. There are more easily read and consumed approaches to this problem.
Thanks,
Bryan
A VB function is not a good choice either. Are you using this data in SSRS?
Thanks,
Bryan
Hi Bryan,
To tell the truth - I do not even know what SSRS is...I am new to this field, still learning. I tried to minimize my application for security (I'm affraid of paople will restore my code and see my logic creating the code), so some of the important stuff will perform as stored procedures on the SQL (which I'm also learning now...) Now I managed to calculate the average, and count the rows. But my function needs to return the result in a variable: the first variable - numbers of rows, the second - the average of a column, and the third - I need to find the fifth biggest value in a specific column. The prolem is assigning the results into variables.
Thanks again for your help,
Dror.
|||Try something like this with appropriate substitutions. Please note, the data types I'm using for the parameters does not imply that these would be the approapriate data types for your application.
Code Snippet
create proc MyProc @.MyParam varchar(10), @.MyCount int OUTPUT, @.MyAvg float OUTPUT
as
select @.MyCount = count(*), @.MyAvg = avg(MyField)
from MyTable
where MyOtherField = @.MyParam
return 0
end
You can look up more info on using output parameters by reading the Books Online entry for the CREATE PROCEDURE statement.
Thanks,
Bryan Smith
Counting NULL columns?
If I had a basic table with 3 VARCHAR fields; let's say A, B, C
How could I write a query that returns the count of the number of NULL columns for every record in a table?
Ideally, it would be something like:
SELECT
CAST (A IS NULL) AS INTEGER
+ CAST (B IS NULL) AS INTEGER
+ CAST (C IS NULL) AS INTEGER
FROM MyTable
That doesn't work at all. I can't seem to do "IS NULL" in the SELECT area. Should I write a T-SQL user-defined function that takes all three columns as parameters? Would that be performance friendly for large data sets?select 'a is null', count(*) from mytable where a is null
union all
select 'b is null', count(*) from mytable where b is null
union all
select 'c is null', count(*) from mytable where c is null
union all
select 'all are null', count(*) where (a is null or b is null or c is null)|||select sum(case when A is null then 1 else 0 end) as Anulls
, sum(case when B is null then 1 else 0 end) as Bnulls
, sum(case when C is null then 1 else 0 end) as Cnulls
from yourtable|||Rudy, I think he wants to count the nulls for each row:
select case when A is null then 1 else 0 end
+ case when B is null then 1 else 0 end
+ case when C is null then 1 else 0 end as NullValues
from yourtable|||blindman, you are too right
the results of your query, however, are totally useless, as there is nothing to tell you which rows have which numbers of nulls
3
2
0
1
0
0
0
2
0
3
0
2
1
at least my query actually produces something useful
:cool:|||Hey, he only said he wanted the count! Ain't my problem if the project specs are no good... :D
And Roger, yes if you have a lot of columns it might be worthwhile to write a function that returns 1 if a value is Null, and 0 if it is not. Call it "NullBit" or something.
... and make Rudy happy by at least including a primary key in your result set!|||many ways to skin the null|||Rudy, I think he wants to count the nulls for each row:
select case when A is null then 1 else 0 end
+ case when B is null then 1 else 0 end
+ case when C is null then 1 else 0 end as NullValues
from yourtable
Yes, you are right; that is exactly what I wanted. It works perfectly!! Thank you so much!! I'm surprised; no one else in my office could come up with this. I was planning on writing a user-defined function with if statements but this is much more elegant.
Thanks Rudy and blindman!
FYI, I'm using this in a WHERE clause for a a large UPDATE command that merges import data into a production table. I only want to overwrite the existing data if the new data has more non-NULL fields.|||I only want to overwrite the existing data if the new data has more non-NULL fields.
surely the number of non-nulls is of secondary concern
suppose i had a Three Stooges table with this row:
'curly','larry',null
you're saying it's okay to overwrite this with
'tom','dick','harry'
but i've done many merges myself, and i'm sure there's more to your example than just three fields...|||Consider using this instead:
Update A
set A.Stooge1 = coalesce(A.Stooge1, B.Stooge1),
A.Stooge2 = coalesce(A.Stooge2, B.Stooge2),
A.Stooge3 = coalesce(A.Stooge3, B.Stooge3),
..etc..
from A inner join B on A.PKey = B.Pkey
This merges the two datasets together, giving priority to data in table A.|||Follow the blindman, follow the blindman!!!
-PatP|||Only when you are in the dark...|||nice
might also want to add a WHERE clause so you don't unnecessarily update every row, just the ones which actually have changed|||Good point. It's a tough call on what proportion of the rows need to be updated to justify the overhead of the where clause. Maybe for a one-time shot a SELECT INTO followed by renaming the resulting table would be fastest?
How could I write a query that returns the count of the number of NULL columns for every record in a table?
Ideally, it would be something like:
SELECT
CAST (A IS NULL) AS INTEGER
+ CAST (B IS NULL) AS INTEGER
+ CAST (C IS NULL) AS INTEGER
FROM MyTable
That doesn't work at all. I can't seem to do "IS NULL" in the SELECT area. Should I write a T-SQL user-defined function that takes all three columns as parameters? Would that be performance friendly for large data sets?select 'a is null', count(*) from mytable where a is null
union all
select 'b is null', count(*) from mytable where b is null
union all
select 'c is null', count(*) from mytable where c is null
union all
select 'all are null', count(*) where (a is null or b is null or c is null)|||select sum(case when A is null then 1 else 0 end) as Anulls
, sum(case when B is null then 1 else 0 end) as Bnulls
, sum(case when C is null then 1 else 0 end) as Cnulls
from yourtable|||Rudy, I think he wants to count the nulls for each row:
select case when A is null then 1 else 0 end
+ case when B is null then 1 else 0 end
+ case when C is null then 1 else 0 end as NullValues
from yourtable|||blindman, you are too right
the results of your query, however, are totally useless, as there is nothing to tell you which rows have which numbers of nulls
3
2
0
1
0
0
0
2
0
3
0
2
1
at least my query actually produces something useful
:cool:|||Hey, he only said he wanted the count! Ain't my problem if the project specs are no good... :D
And Roger, yes if you have a lot of columns it might be worthwhile to write a function that returns 1 if a value is Null, and 0 if it is not. Call it "NullBit" or something.
... and make Rudy happy by at least including a primary key in your result set!|||many ways to skin the null|||Rudy, I think he wants to count the nulls for each row:
select case when A is null then 1 else 0 end
+ case when B is null then 1 else 0 end
+ case when C is null then 1 else 0 end as NullValues
from yourtable
Yes, you are right; that is exactly what I wanted. It works perfectly!! Thank you so much!! I'm surprised; no one else in my office could come up with this. I was planning on writing a user-defined function with if statements but this is much more elegant.
Thanks Rudy and blindman!
FYI, I'm using this in a WHERE clause for a a large UPDATE command that merges import data into a production table. I only want to overwrite the existing data if the new data has more non-NULL fields.|||I only want to overwrite the existing data if the new data has more non-NULL fields.
surely the number of non-nulls is of secondary concern
suppose i had a Three Stooges table with this row:
'curly','larry',null
you're saying it's okay to overwrite this with
'tom','dick','harry'
but i've done many merges myself, and i'm sure there's more to your example than just three fields...|||Consider using this instead:
Update A
set A.Stooge1 = coalesce(A.Stooge1, B.Stooge1),
A.Stooge2 = coalesce(A.Stooge2, B.Stooge2),
A.Stooge3 = coalesce(A.Stooge3, B.Stooge3),
..etc..
from A inner join B on A.PKey = B.Pkey
This merges the two datasets together, giving priority to data in table A.|||Follow the blindman, follow the blindman!!!
-PatP|||Only when you are in the dark...|||nice
might also want to add a WHERE clause so you don't unnecessarily update every row, just the ones which actually have changed|||Good point. It's a tough call on what proportion of the rows need to be updated to justify the overhead of the where clause. Maybe for a one-time shot a SELECT INTO followed by renaming the resulting table would be fastest?
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 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)