creating a dropdown list of Saturdays

Hello Everyone,
This is my first time posting here. I am sorry if this is a beginner
question, but... I am a beginner. I am trying to write this as a stored
procedure. I need to create a dropdown list of Saturdays starting with the
first date in the database.
Here are my questions:
(1)What I am unsure about is taking the date given to me, changing it to a
Saturday, and then creating a temp table filled with a list of Saturdays up
to last Saturday.
(2)Would it be better to use the t_date(varchar) to create the temp table
with the Sat. and then convert to datetime datatype?
Table - - transactions
field1 - - t_date
field2 - - t_a_date
Any help is greatly appreciated.
Thanks in advance!!
Butch
--
Message posted via http://www.sqlmonster.comWhy don't you try something like this:
select t_date
from transactions
where datepart(dw, t_date) = 7
and t_date < getdate()
This will give you all dates that are on Saturday up through last Saturday
This will not count TODAY if today IS saturday - if you want to count today
if it is a saturday, then change the t_date < getdate() to t_date <= getdate()
--
~lb
"cearnhart via SQLMonster.com" wrote:
> Hello Everyone,
> This is my first time posting here. I am sorry if this is a beginner
> question, but... I am a beginner. I am trying to write this as a stored
> procedure. I need to create a dropdown list of Saturdays starting with the
> first date in the database.
> Here are my questions:
> (1)What I am unsure about is taking the date given to me, changing it to a
> Saturday, and then creating a temp table filled with a list of Saturdays up
> to last Saturday.
> (2)Would it be better to use the t_date(varchar) to create the temp table
> with the Sat. and then convert to datetime datatype?
> Table - - transactions
> field1 - - t_date
> field2 - - t_a_date
> Any help is greatly appreciated.
> Thanks in advance!!
> Butch
> --
> Message posted via http://www.sqlmonster.com
>|||Thank you for your quick response.
I tried this and it did not produce any results. :-(
here is my code:
select trans_id, t_a_date
from transactions
where trans_id = 6
AND datepart(dw, t_a_date) = 7
AND t_s_date < getdate()
trans_id gives me the first occurance of a date in the table. The date I get
is not a Saturday. I had to use t_a_date because of the datetime datatype.
Again, thank you for your time and help!
lonnye wrote:
>Why don't you try something like this:
>select t_date
>from transactions
>where datepart(dw, t_date) = 7
>and t_date < getdate()
>
--
Message posted via http://www.sqlmonster.com|||Is trans_id unique and/or the primary key on the table?
If you run the following, what do you get?
select datepart(dw, getdate())
Today is Thur, March 13... You should get 5 as your result.
Please let me know. (this is fun for me)
--
~lb
"cearnhart via SQLMonster.com" wrote:
> Thank you for your quick response.
> I tried this and it did not produce any results. :-(
> here is my code:
> select trans_id, t_a_date
> from transactions
> where trans_id = 6
> AND datepart(dw, t_a_date) = 7
> AND t_s_date < getdate()
> trans_id gives me the first occurance of a date in the table. The date I get
> is not a Saturday. I had to use t_a_date because of the datetime datatype.
> Again, thank you for your time and help!
>
> lonnye wrote:
> >Why don't you try something like this:
> >
> >select t_date
> >from transactions
> >where datepart(dw, t_date) = 7
> >and t_date < getdate()
> >
> --
> Message posted via http://www.sqlmonster.com
>|||trans_id is unique and is the primary key. And I do get 5 as my result
Thanks,
lonnye wrote:
>Is trans_id unique and/or the primary key on the table?
>If you run the following, what do you get?
>select datepart(dw, getdate())
>Today is Thur, March 13... You should get 5 as your result.
>Please let me know. (this is fun for me)
>> Thank you for your quick response.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1|||Then you wouldnt want to include the "where trans_id = 6" since that will
only return that one row - and wont return it if that row does not fall on a
saturday.
Let me know if you still have an issue.
--
~lb
"cearnhart via SQLMonster.com" wrote:
> trans_id is unique and is the primary key. And I do get 5 as my result
> Thanks,
> lonnye wrote:
> >Is trans_id unique and/or the primary key on the table?
> >If you run the following, what do you get?
> >
> >select datepart(dw, getdate())
> >Today is Thur, March 13... You should get 5 as your result.
> >Please let me know. (this is fun for me)
> >> Thank you for your quick response.
> >>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1
>|||It is still not producing results. The first date in the database is
09/17/2006(Sun), I want the dropdown to start with 09/23/2006(Sat), which
that date is not in the database.
Do I need to create a datetime variable and set that to the first date, and
then go from there? Does that make sense?
lonnye wrote:
>Then you wouldnt want to include the "where trans_id = 6" since that will
>only return that one row - and wont return it if that row does not fall on a
>saturday.
>Let me know if you still have an issue.
>> trans_id is unique and is the primary key. And I do get 5 as my result
>[quoted text clipped - 7 lines]
>> >Please let me know. (this is fun for me)
>> >> Thank you for your quick response.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1|||If that date does not exist in the database and you want it shown,then you
will need to pull from a variable that increments.
Personally I would rather it not show if I already know from the query that
there is no data for that date. This way the users would not be selecting a
date only to have nothing show up (but this is just personal preference).
--
~lb
"cearnhart via SQLMonster.com" wrote:
> It is still not producing results. The first date in the database is
> 09/17/2006(Sun), I want the dropdown to start with 09/23/2006(Sat), which
> that date is not in the database.
> Do I need to create a datetime variable and set that to the first date, and
> then go from there? Does that make sense?
>
> lonnye wrote:
> >Then you wouldnt want to include the "where trans_id = 6" since that will
> >only return that one row - and wont return it if that row does not fall on a
> >saturday.
> >Let me know if you still have an issue.
> >> trans_id is unique and is the primary key. And I do get 5 as my result
> >>
> >[quoted text clipped - 7 lines]
> >> >Please let me know. (this is fun for me)
> >> >> Thank you for your quick response.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1
>|||On Mar 13, 10:26=A0pm, lonnye <lon...@.discussions.microsoft.com> wrote:
> If that date does not exist in the database and you want it shown,then you=
> will need to pull from a variable that increments.
> Personally I would rather it not show if I already know from the query tha=t
> there is no data for that date. This way the users would not be selecting =a
> date only to have nothing show up (but this is just personal preference).
> --
> ~lb
>
> "cearnhart via SQLMonster.com" wrote:
> > It is still not producing results. =A0The first date in the database is
> > 09/17/2006(Sun), I want the dropdown to start with 09/23/2006(Sat), whic=h
> > that date is not in the database.
> > Do I need to create a datetime variable and set that to the first date, =and
> > then go from there? =A0Does that make sense? =A0
> > lonnye wrote:
> > >Then you wouldnt want to include the "where trans_id =3D 6" since that =will
> > >only return that one row - and wont return it if that row does not fall= on a
> > >saturday.
> > >Let me know if you still have an issue.
> > >> trans_id is unique and is the primary key. =A0And I do get 5 as my re=sult
> > >[quoted text clipped - 7 lines]
> > >> >Please let me know. (this is fun for me)
> > >> >> Thank you for your quick response.
> > --
> > Message posted via SQLMonster.com
> >http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1- =Hide quoted text -
> - Show quoted text -
Solution is creating a function ( with single date input ) which will
give the complete date table from imput date till recent date and from
that reslut set , u can eaisly run below query to extact what u
need .. We worked out here & its running fine .
select DateID, datename(dw, DateID) as Dayname from [dbo].[fnname]
('2006-01-01 00:00:00')
where datepart(dw, DateID) =3D 7 and DateID <=3D getdate()
even with this result set, you can map with your own fact table to get
the date for the missing dates in your database.
--Ayyappa