date - query - 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)

date - query

Author

Topic

palak
Yak Posting Veteran

55 Posts

Posted - 2008-03-03 : 14:31:49

Hello folks,

i have one table salestable in which tehre is field -salesdate
in this column sometimes date does not exist..but still i want output of that datecolumn.

id no num salesdate salesquantity
-------------------------------------
1 101 s101 1/1/2008 50.00
2 501 s501 1/5/2008 100.00
3 611 s611 2/3/2008 150.00

so i want results like:

id no num salesdate salesquantity
-------------------------------------
1 101 s101 1/1/2008 50.00
- - - 1/2/2008 -
- - - 1/3/2008 -
- - - 1/4/2008 -
2 501 s501 1/5/2008 100.00

----------------- and so on...

can you help me to wrie this query?
thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-03 : 15:52:58

There are several ways to do this, one way is to make use of the F_TABLE_DATE function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

-- Setup @StartDate and @EndDate (left up to the user)

SELECT
	Sales.ID,
	Sales.[No],
	Sales.Num,
	Date.Date,
	Sales.SalesQuantity
FROM
	dbo.F_TABLE_DATE(@StartDate, @EndDate) AS Date
LEFT OUTER JOIN
	Sales
	ON Date.Date = Sales.SalesDate

palak
Yak Posting Veteran

55 Posts

Posted - 2008-03-04 : 08:57:41

Thanks Lamprey for ur reply..

--i m getting 456 rows using this funciton
SELECT distinct date FROM
dbo.F_TABLE_DATE('1/1/2007', '3/31/2008') AS Date

-- i m getting 327 rows using my salestable
select distinct salesdate from salestable
where salesdate between '1/1/2007' and '3/31/2008'
order by salesdate

now i want 456-327 = 129 rows whcih will be the missing dates from salesdate...
so i want 129 rows..

if i m joining both fucntion and table like:
SELECT Date.Date
FROM dbo.F_TABLE_DATE('1/1/2007' , '3/31/2008' ) AS Date
LEFT OUTER JOIN
Sales
ON Date.Date = Sales.SalesDate

i m getting 456 rows...if i m using inner join then i m getting 327 rows..if i m using right outer join then i m getting same 327rows..

can anyone help me to get missing dates - 129rows..

thanks a lot!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 09:15:03

SELECT Date.Date
FROM dbo.F_TABLE_DATE('1/1/2007' , '3/31/2008' ) AS Date
LEFT OUTER JOIN
Sales
ON Date.Date = Sales.SalesDate
[red]where Sales.SalesDate is null[red]

E 12°55'05.25"
N 56°04'39.16"

palak
Yak Posting Veteran

55 Posts

Posted - 2008-03-04 : 09:19:27

Thanks Peso..

now i got it 129 rows..

thanks a lot!!!

palak
Yak Posting Veteran

55 Posts

Posted - 2008-03-04 : 11:19:37

i want all dates results in output using missing dates:
ouput:
id no num salesquantity salesdate
-------------------------------------
1 101 s101 50.00 1/1/2007
- - - --- - 1/2/2007
- - - -- -- 1/3/2007
- - - - -- 1/4/2007
2 501 s501 100.00 1/5/2007
................... and so on..

-- i have dates - which r in table - using my salestable
select distinct salesdate from salestable
where salesdate between '1/1/2007' and '3/31/2008'
order by salesdate

-- i have missing dates - have created view for that with using function

create view vsdate
as
SELECT Date.Date
FROM dbo.F_TABLE_DATE('1/1/2007' , '3/31/2008' ) AS Date
LEFT OUTER JOIN
Sales
ON Date.Date = Sales.SalesDate
where Sales.SalesDate is null
go

but i want whole ouput using both so i use case condition for date column

SELECT
Sales.ID,
Sales.[No],
Sales.Num,
Sales.SalesQuantity,
case when date.Date is not null then date.date
when date.date is null then vd.date
end as dates
FROM
dbo.F_TABLE_DATE(@StartDate, @EndDate) AS Date
LEFT OUTER JOIN
Sales
ON Date.Date = Sales.SalesDate

left outer join vsdate vd
on date.date <> vd.date

order by dates

but still not getting correct output..

m getting

id no num salesquantity salesdate
-------------------------------------
1 101 s101 50.00 1/1/2008
2 501 s501 100.00 1/5/2008
3 611 s611 150.00 2/3/2008

correct output should be:
id no num salesquantity salesdate
-------------------------------------
1 101 s101 50.00 1/1/2007
- - - --- - 1/2/2007
- - - -- -- 1/3/2007
- - - - -- 1/4/2007
2 501 s501 100.00 1/5/2007
................... and so on..

can anyone help me to figure it out.. thanks

i think i m worng in case condition.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-04 : 11:25:46

try like this

SELECT
Sales.ID,
Sales.[No],
Sales.Num,
Sales.SalesQuantity,
date.date
FROM
(
select distinct salesdate as date from salestable 
where salesdate between '1/1/2007' and '3/31/2008'
UNION ALL
SELECT distinct Date FROM  vsdate
)Date
LEFT OUTER JOIN Sales
ON Date.Date = Sales.SalesDate

palak
Yak Posting Veteran

55 Posts

Posted - 2008-03-04 : 11:37:30

Thanks visakh16..

Got the correct results..

you all r genius people. Gr8!!!

thanks a ton!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources