Alternate week database records display

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

General SQL Server Forums

New to SQL Server Programming

Alternate week database records display

Author

Topic

dia1234
Starting Member

12 Posts

Posted - 2013-10-04 : 09:13:50

In our organization we have fixed two weeks menu. On our intranet i have database entries with two weeeks menu without dates. I want first six entries to appear in one week and next six entries to appear in another week. How can i achieve this with SQL query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-04 : 10:16:55

show some data and explain what you want

your current explanation just asks for something like this (which I'm almost is not your exact requirement!)


select *,dateadd(wk,datediff(wk,0,getdate())-((rn-1)/6),0)
from
(
select *,row_number() over (order by idcol) as rn
from yourtable
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

dia1234
Starting Member

12 Posts

Posted - 2013-10-07 : 02:15:57

I have 12 entries of a menu. First six entries are for week1 and another six are for week2. Same menue will then appear for a week3 which was for week1 and then again week2's menue will appear for week4 and so on and so forth.

Please suggest. Any changes in tables can also be done. any ideas >?>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 05:44:32

quote:
Originally posted by dia1234

I have 12 entries of a menu. First six entries are for week1 and another six are for week2. Same menue will then appear for a week3 which was for week1 and then again week2's menue will appear for week4 and so on and so forth.

Please suggest. Any changes in tables can also be done. any ideas >?>

so do you mean just replicating the data for next 2 weeks? how is week info stored? is it a datetime field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

dia1234
Starting Member

12 Posts

Posted - 2013-10-07 : 05:47:57

i dont stored the dates as these are constant menu for week so no use of storing dates...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 06:06:32

quote:
Originally posted by dia1234

i dont stored the dates as these are constant menu for week so no use of storing dates...

so hwo would you determine next two weeks?

illustrate with an example how your data will be for current 2 weeks and how you want next set to be generated

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

dia1234
Starting Member

12 Posts

Posted - 2013-10-07 : 07:30:02

this is what the problem is . How do i determine to display the next set.I am open for options here.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 07:47:08

hmm..at least show us how data is currently present in the table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

dia1234
Starting Member

12 Posts

Posted - 2013-10-07 : 08:08:59

Id| menu
1 | Chicken with Rice
2 | Potato Salad
3 | Baked Beans
8 | Burger with fries
9 | macroni cheese chicken
.....

so on and so forth . 7 more entries are there in the database like this to make it 12 entries for two six days menue.
Hope that helps in explaining the situation.
Thanks for yout time and concern

dia1234
Starting Member

12 Posts

Posted - 2013-10-07 : 08:09:00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 23:46:47

quote:
Originally posted by dia1234

Id| menu
1 | Chicken with Rice
2 | Potato Salad
3 | Baked Beans
8 | Burger with fries
9 | macroni cheese chicken
.....

so on and so forth . 7 more entries are there in the database like this to make it 12 entries for two six days menue.
Hope that helps in explaining the situation.
Thanks for yout time and concern

ah
this has no relation with what you explained so far. I cant even see a field here which indicates the week information. So didnt understand relevance of what you mean by replicating this for next 2 weeks.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

dia1234
Starting Member

12 Posts

Posted - 2013-10-08 : 00:35:25

As i explained in my first post that as the menu is constant i have not stored dates which seems logical. I just needed an advice to implement this to display one set of entries one week and another on the second week,again first set on third and the second set of entries on fourth week. I also stated that i am open to any workable idea/query XML file option any thing.

Apologies if there are still any confusions . Hope i have make my self clear.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-08 : 00:45:03

quote:
Originally posted by dia1234

As i explained in my first post that as the menu is constant i have not stored dates which seems logical. I just needed an advice to implement this to display one set of entries one week and another on the second week,again first set on third and the second set of entries on fourth week. I also stated that i am open to any workable idea/query XML file option any thing.

Apologies if there are still any confusions . Hope i have make my self clear.

What do you mean by a set here? how you determine what all records belong to a set? I dont see any field which can be used for grouping the values to a set either!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

dia1234
Starting Member

12 Posts

Posted - 2013-10-08 : 01:06:15

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-08 : 01:18:22

quote:
Originally posted by dia1234

i can add there a weeknumber field here to make it a set of entries for a perticular week.

Id| menu | weeknumber
1 | Chicken with Rice |1
2 | Potato Salad |1
3 | Baked Beans|1
8 | Burger with fries |2
9 | macroni cheese chicken |2
.....

Ok..Now that adds some sense to what you were explaining.

In that case do like this


INSERT Table
SELECT t.id,t.menu,t.weeknumber + (Cnt *2)
FROM Table t
CROSS JOIN (SELECT 1 AS Cnt
           )t1

depending to how many weeks you want values to be repeated you need to add that many values inside t1.
Current code just replicates the values for one more week following current week

ie week 3 for week 1 items
and week 4 for week 2 items etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

dia1234
Starting Member

12 Posts

Posted - 2013-10-08 : 01:32:23

Thanks a lot for your time.I will apply it and post back.

dia1234
Starting Member

12 Posts

Posted - 2013-10-11 : 08:37:31

i want to select some entries. cant understand why the insert statement? am i missing some thing as the sql server is not parsing the query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-11 : 08:54:43

quote:
Originally posted by dia1234

i want to select some entries. cant understand why the insert statement? am i missing some thing as the sql server is not parsing the query

in that case just ignore the INSERT part

SELECT t.id,t.menu,t.weeknumber + (Cnt *2)
FROM Table t
CROSS JOIN (SELECT 1 AS Cnt
)t1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources