Date.. - SQL Server Forums

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

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Date..

Author

Topic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-09-13 : 12:15:09

hi

I have table in which the data is stored yearly wise. i.e

Date Value
2004/02/28 A001
2004/06/30 A006
2005/03/31 A005
2005/11/13 A004
2005/05/23 A009

in the above table we have 2 record for year 2004 (Feb and June), and three record for the year 2005(March,May,Nov)

I would like to have a querry which gives me total 24 record including the above 5 record with Data in "Value" Column EQUAL to NULL for the months which are not present in the table for the year 2004 and 2005

Complicated things can be done by simple thinking

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-13 : 12:28:07

You need a table of all years/months that you wish to return. That is the starting point for your select. From there, you can LEFT OUTER JOIN to your data.

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-13 : 13:21:27

But you have varying Days? What Day would you like to use?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-13 : 13:35:28

It's funny how it'll do an implicit conversion in the predicate, but not in the SELECT


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE YearMonth([Year] char(4), [Month] char(2))
CREATE TABLE myTable99([Date] datetime, Value varchar(10))
GO

INSERT INTO YearMonth([Year],[Month])
SELECT '2004', '1'  UNION ALL
SELECT '2004', '2'  UNION ALL
SELECT '2004', '3'  UNION ALL
SELECT '2004', '4'  UNION ALL
SELECT '2004', '5'  UNION ALL
SELECT '2004', '6'  UNION ALL
SELECT '2004', '7'  UNION ALL
SELECT '2004', '8'  UNION ALL
SELECT '2004', '9'  UNION ALL
SELECT '2004', '10' UNION ALL
SELECT '2004', '11' UNION ALL
SELECT '2004', '12' UNION ALL
SELECT '2005', '1'  UNION ALL
SELECT '2005', '2'  UNION ALL
SELECT '2005', '3'  UNION ALL
SELECT '2005', '4'  UNION ALL
SELECT '2005', '5'  UNION ALL
SELECT '2005', '6'  UNION ALL
SELECT '2005', '7'  UNION ALL
SELECT '2005', '8'  UNION ALL
SELECT '2005', '9'  UNION ALL
SELECT '2005', '10' UNION ALL
SELECT '2005', '11' UNION ALL
SELECT '2005', '12'
GO

INSERT INTO myTable99([Date], Value)
SELECT '2004/02/28', 'A001' UNION ALL
SELECT '2004/06/30', 'A006' UNION ALL
SELECT '2005/03/31', 'A005' UNION ALL
SELECT '2005/11/13', 'A004' UNION ALL
SELECT '2005/05/23', 'A009'
GO

    SELECT COALESCE(b.[Date],CONVERT(datetime,[Month]+'/01/'+[Year])), b.Value
      FROM YearMonth a 
 LEFT JOIN myTable99 b 
	ON a.[Year] = YEAR([Date]) 
       AND a.[Month] = MONTH([Date]) 
GO

SET NOCOUNT OFF
DROP TABLE myTable99, YearMonth
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-09-13 : 13:40:14

The days dosent matters it month and year which matters ..

Complicated things can be done by simple thinking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources