January 8, 2010 at 9:25 am
Hi
In following sql statement
select * from tableA
where year = '2009' and month = 'December'
above statment show all records when all the above statments are TRUE. (year = 2009 and Month = December)
if I use OR instead of and it gives me results when either of the statements are TRUE or BOTH. (year = 2009 or month = December)
But when all above conditions are TRUE , it gives me all records for year 2009 , not just for December 2009 (it show meall results for Jan,Feb,Mar....December for the year 2009)
I need to see all records for year 2009 , if found filter by month December and only show me all results for year 2009 and month December,
If December does not exist then show me rest of the months details.
Sql query works fine when I use OR operator but it fails to serve my purpose when both conditons are matched . I still get all the records not just
specified to both conditons.
I hope you could understand the problem , if any question , kindly do not hesitate to ask.
January 8, 2010 at 9:32 am
Please read the link below in my sig, it will show you how to create tables and load sample data for people to test their solutions to your problem.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2010 at 9:50 am
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test]
GO
CREATE TABLE [dbo].[test] (
[order] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[year] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[month] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into test values ('123','2009','March')
insert into test values ('123','2009','June')
insert into test values ('123','2009','July')
insert into test values ('123','2009','December')
My answer/solution use the Case function.
select case Month
WHEN 'December' THEN 'December'
ELSE Month
END AS Month, [order], [year] from test
where year = '2009'
January 8, 2010 at 10:11 am
Hi
Thanks for your help.
select case Month
WHEN 'December' THEN 'December'
ELSE Month
END AS Month, [order], [year] from test
where year = '2009'
I see you have used case , but when I run your query it return all the results for year 2009,
Month Order Year
March1232009
June 1232009
July 1232009
December1232009
If i change the WHERE statement to year = '2009' and month = 'December'
It shows me following result
Month Order Year
December1232009
if i change WHERE statement to year = '2009' and month = 'November'
it does not show any results.
Month Order Year
Which is what my question is , incase when it does not find the valid month it should give me all results based on valied year and vice versa.
But if both year and month are found , it should only show me results matching for year and month ( it should show same results which you might
have got while running the following query
SELECT * FROM TEST WHERE YEAR = '2009' AND MONTH = 'DECEMBER'
I appreciate your help.
Thanks
January 8, 2010 at 10:23 am
drop table #test
GO
CREATE TABLE #test (
[order] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[year] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[month] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into #test values ('123','2009','March')
insert into #test values ('123','2009','June')
insert into #test values ('123','2009','July')
insert into #test values ('123','2009','December')
SELECT *
FROM #test t
WHERE [year] = '2009'
AND [month] = CASE WHEN (SELECT 1
FROM #test t
WHERE [year] = '2009'
AND [month] = 'December') > 0 THEN 'December' ELSE [month] END
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2010 at 11:22 am
Great! Its working.
1) When I selected year 2009 and month December , it only showed me one result (which is what I wanted)
2) When I selected year 2009 and month Feburary (Where Feburary does not exists) it gives all the results for year 2009 (Again what I wanted )
But
3) When I selected year 2008 (which does not exist) it returned no records (preferably i was expecting to get results based on month December)
4) When I selected year 2008 and month Feburary which both do not exist , I got no result (Which is again what I was expecting)
I appreciate your help . Let me know if point# 3 can be fixed .
Thanks
January 11, 2010 at 2:28 am
Of course...
drop table #test
GO
CREATE TABLE #test (
[order] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[year] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[month] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into #test values ('123','2009','March')
insert into #test values ('123','2009','June')
insert into #test values ('123','2009','July')
insert into #test values ('123','2009','December')
insert into #test values ('123','2008','March')
insert into #test values ('123','2008','June')
insert into #test values ('123','2008','July')
insert into #test values ('123','2008','December')
DECLARE @YEAR CHAR(4), @Month VARCHAR(10)
SELECT @YEAR = '2008', @Month = 'February'
SELECT *
FROM #test t
WHERE [year] = @YEAR
AND [month] = CASE WHEN (SELECT 1 FROM #test t
WHERE [year] = @YEAR AND [month] = @Month) > 0
THEN @Month
ELSE [month] END
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 11, 2010 at 4:22 am
Hi
Thanks for your help.
Is your last query different from your second last one .
Because while I select incorrect year but correct month , I get no records.
Thanks
January 11, 2010 at 4:40 am
Please post the exact code which you ran, and describe any result set (or error messages). Thanks.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 11, 2010 at 5:15 am
drop table test
GO
CREATE TABLE test (
[order] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[year] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[month] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into test values ('123','2009','March')
insert into test values ('123','2009','June')
insert into test values ('123','2009','July')
insert into test values ('123','2009','December')
insert into test values ('123','2008','March')
insert into test values ('123','2008','June')
insert into test values ('123','2008','July')
insert into test values ('123','2008','December')
DECLARE @YEAR CHAR(4), @Month VARCHAR(10)
SELECT @YEAR = '2008', @Month = 'FFebruary'
SELECT *
FROM test t
WHERE [year] = @YEAR
AND [month] = CASE WHEN (SELECT 1 FROM test t
WHERE [year] = @YEAR AND [month] = @Month) > 0
THEN @Month
ELSE [month] END
When I selected @YEAR = '2008', @Month = 'December'
I got the following result
Order;Year; Month
1232008December
When I selected @YEAR = '2008', @Month = 'May'
I got the following result
Order;Year; Month
1232008March
1232008June
1232008July
1232008December
But when I selected @Year = '2010' , @Month = 'December'
I got no results
I was expecting to see the following output for the above
Order;Year; Month
1232008December
Thanks
January 11, 2010 at 5:30 am
If there is no matching data for the supplied year/month, then data should be returned for which years / months instead?
I think we've tied this down for months - return rows for all months - but I'm unsure how you want to treat years. If year 2010 is supplied, then you are expecting year 2008. Can you explain the logic please?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 11, 2010 at 5:57 am
Hi
Yes you are right , i see you have tied down with months , so if the year is correct and month is wrong , i see all records for that year,
but if year is incorrect then i see no result.
Basically I am creating Reports in SQL Server Reporting Services. Which has 3-4 parameters , Although I could allow to have NULL or Blank values
in parameter field in reports but SQL does not accept it and return no answer for a query where parameter is Blank or incorrect.
That is why I was trying to do a work around , so in my report parameter , some one enters incorrect year , or month , i would still get result based
on the matched parameter , which applies for NULL or BLANK field on reports.
I guess I sound too complicated . Simply Is it possible to have a stored procedure in SQL with 3 parameter .
let say @param1 ; @param2 ;@param3
Now query would only run when all these three parameters are supplied and when all three are valid values existing in database i would get ANSWER
What if I only supply @param1 value and leave @param2 and @param3 as BLANK . I expect query to get result based on @param1 , may be i will see lot of records , so to filter it down i will provide @param2 , results would be filtered and to further filter report i would supply all three parameters.
Kindly let me know if i was able to address my problem.
Thanks
January 11, 2010 at 6:24 am
Ahmad
I can see where you're coming from with this and I'm pretty sure that in some circumstances, your suggestion might make sense: you're indicating to the user "No, there isn't any data matching these parameters - this is what we've got".
Are you sure that this is what you want to do? It's quite unusual - typically the user would get a report header displaying the filter i.e. year and month, but with no data displayed.
If you are sure that you want to display data in a report which doesn't match the parameters requested by the user, then it's a straightforward job, but you will have to decide what to return with different combinations of year and month for which there is no matching data.
1. Year/month in the future
2. Year/month which is "current", i.e. within the last fiscal/accounting/calendar year
3. Year/month which is in the past.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 11, 2010 at 8:18 am
This Report is for only Internal use only. Specially for Technical team.
Secondly, the data itself stored in sql table is for present day only , after 24 hours time data moved to archieve table.
so even if i see all the results by doing
select * from table A , data is not too much .
But I want to create filters to narrow down my results. by creating Where clauses , since @parameter values are dynamic
I created a stored procedure which display records once user enters all the parameters. But There are many rows
and its difficult to enter correct values for all parameters , therefore I was thinking to create blank parameter ,
and it would be upto user to either use parameters or just see all available records
Thanks
January 11, 2010 at 8:22 am
ahmadjk (1/11/2010)
This Report is for only Internal use only. Specially for Technical team.Secondly, the data itself stored in sql table is for present day only , after 24 hours time data moved to archieve table.
so even if i see all the results by doing
select * from table A , data is not too much .
But I want to create filters to narrow down my results. by creating Where clauses , since @parameter values are dynamic
I created a stored procedure which display records once user enters all the parameters. But There are many rows
and its difficult to enter correct values for all parameters , therefore I was thinking to create blank parameter ,
and it would be upto user to either use parameters or just see all available records
Thanks
So, you could restrict the data using any one of the three parameters? Or indeed, any combination of the three?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply