nested query problem ! - 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 2005 Forums

Transact-SQL (2005)

nested query problem !

Author

Topic

ozibella
Starting Member

4 Posts

Posted - 2011-01-18 : 03:47:14

hi everyone,
i have a stored procedure which creates a table 'Date'. Date has 2 columns d1,
d2 .
e.g

d1:
01.12.2010
01.01.2011

d2:
31.21.2010
11.01.2011

so i have some date ranges 01.12.2010 - 31.21.2010, 01.01.2011 - 11.01.2011
then in my 'Account' table i have redemptionDate column.
e.g

redemptionDate:
14.12.2010
05.01.2011

what i want is,
using date ranges in 'Date', calculate day in each month depends on
redemptionDate.
ex:

for 14.12.2010 =>
in first row until 31.12.2010 there are 17 days
in second row until 11.01.2011 no days

for 05.01.2011 =>
in first row until 31.12.2010 there are 31 days
in second row until 11.01.2011 there are 4 days

so the result should be

column1:
14.12.2010
05.01.2011
05.01.2011

column2:
17
31
4

Can anyone help?

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2011-01-18 : 09:46:10

How are there 31 days from Dec 31, 2010 until Jan 05, 2011?
How are there 4 days from Jan 05, 2011 until Jan, 11 2011?

I think you need to explain what you are trying to accomplish better. Also, could you explain the date format you are using, just so I am sure? I am assuming DD.MM.YYYY because your 31.21.2010 looks like a typo. Actual table definitions would be nice as well. I'm not sure why "in second row until 11.01.2011 no days." I assume there is some sort of relationship between the tables?

===
http://www.ElementalSQL.com/

ozibella
Starting Member

4 Posts

Posted - 2011-01-18 : 10:45:44

i'm calculating day count for each date range in 'Date' table.
ranges:
01.12.2010 - 31.12.2010
01.01.2011 - 11.01.2011

1)
first row in 'Account' table is 14.12.2010
then,
-first date range, which redemptionDate in is 01.12.2010 - 31.12.2010
day count in that date range is datediff(dd,'14.12.2010','31.12.2010')=17
-second date range 01.01.2011 - 11.01.2011 does not include redemptionDate 14.12.2010

2)
second row in 'Account' table is 05.01.2011
then,
-first date range, which redemptionDate in is 01.12.2010 - 31.12.2010
05.01.2011 is not in 01.12.2010 - 31.12.2010
and it comes after this range
because of the financial rules i take the day count 31 for it. (days from 01.12.2010 to 31.12.2010)
-second date range, which redemptionDate in is 01.01.2011 - 11.01.2011
day count in that date range is datediff(dd,'01.01.2011','05.01.2011')=4

so the procedure should return something like that

column1 ______________column2
01.12.2010-31.12.2010 ____17
01.01.2011-11.01.2011 ____31
01.01.2011-11.01.2011 ____ 4

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-18 : 11:30:57

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

DATE is a reserved word and should never be a table name. The only format allowed in ANSI/ISO Standard SQL is ISO-8601. Let's try to correct what you posted so you will not make these mistakes again:

>> I have a stored procedure which creates a table 'Date'. Date has 2 columns d1, <<

I hope not! Creating tables in procedures means you have no idea what a table is, or how to doa data model. You also do not know what the schema looks likes from moment to moment.

CREATE TABLE SillyDates
(d1 DATE NOT NULL,
d2 DATE NOT NULL,
PRIMARY KEY (d1, d2), -- guessing
CHECK (d1 < d2)); --more guessing

INSERT INTO SillyDates
VALUES ('2010-12-01', '2010-21-31') -- impossible date!!
(2011-01-01', '2011-01-11');

>> So I have some date ranges '2010-01.12.2010 - 31.21.2010, 01.01.2011 - 11.01.2011<<

Another impossible date and we have no idea where these ranges are kept -- table? query?

SQL is based on tables; tables have no ordering; the concept of a first and second row do not exist. This is not a file system; we use keys and not physical locators to get data.

Will you please post again with DDL, valid data and clear specifications?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

ozibella
Starting Member

4 Posts

Posted - 2011-01-19 : 03:26:58

sorry for couldn't explain
actually, i m trying to create a financial report
Here is my code:

declare @DateStart DATETIME
declare @DateEnd   DATETIME
set    @DateStart='01.12.2010' set @DateEnd='11.01.2011'

DECLARE @Date TABLE (
d1  datetime,
d2 datetime )


;WITH cteTally AS
(--==== Returns a value of 1 to the number of months in date range
 SELECT TOP (DATEDIFF(mm,DATEADD(mm,DATEDIFF(mm,0,@DateStart),0),  --First of start month 
           DATEADD(mm,DATEDIFF(mm,0,@DateEnd)+1,0))) --First of month after end month
        N = ROW_NUMBER() OVER (ORDER BY t1.Object_ID)
   FROM Master.sys.All_Columns t1
)

insert into @Date (d1,d2)
SELECT   case when DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N-1,0) < @DateStart then @DateStart else DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N-1,0) end as 'Date1'
,case when  DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N-1,0))+1,0)) > @DateEnd then @DateEnd else DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N-1,0))+1,0)) end as 'Date2'
FROM cteTally t

my first aim is to create monthly date ranges according to parameters @DateStart and @DateEnd
you can see date ranges using

select * from @Date

_____d1______________ _____d2______________
2010-12-01 00:00:00.00 2010-12-31 23:59:59.000
2011-01-01 00:00:00.000 2011-01-11 00:00:00.000

my Account table in my database is something like that:

_id________redemptionDate___________amount__
1_______14.12.2010 00:00:00__________1000
2_______05.01.2011 00:00:00__________5000

now, i should calculate day count for each redemptionDate depends on for each date range in @Date table

ozibella
Starting Member

4 Posts

Posted - 2011-01-20 : 10:52:05

quote:
Originally posted by ozibella

i'm calculating day count for each date range in 'Date' table.
ranges:
01.12.2010 - 31.12.2010
01.01.2011 - 11.01.2011

1)
first row in 'Account' table is 14.12.2010
then,
-first date range, which redemptionDate in is 01.12.2010 - 31.12.2010
day count in that date range is datediff(dd,'14.12.2010','31.12.2010')=17
-second date range 01.01.2011 - 11.01.2011 does not include redemptionDate 14.12.2010

2)
second row in 'Account' table is 05.01.2011
then,
-first date range, which redemptionDate in is 01.12.2010 - 31.12.2010
05.01.2011 is not in 01.12.2010 - 31.12.2010
and it comes after this range
because of the financial rules i take the day count 31 for it. (days from 01.12.2010 to 31.12.2010)
-second date range, which redemptionDate in is 01.01.2011 - 11.01.2011
day count in that date range is datediff(dd,'01.01.2011','05.01.2011')=4

so the procedure should return something like that

column1 ______________column2
01.12.2010-31.12.2010 ____17
01.01.2011-11.01.2011 ____31
01.01.2011-11.01.2011 ____ 4

upps sorry
the result should be
column1 ______________column2
01.12.2010-31.12.2010 ____13 -->datediff(dd,'01.12.2010','14.12.2010')
01.01.2011-11.01.2011 ____31
01.01.2011-11.01.2011 ____ 4

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources