Query

Question

Query

Viewing 10 posts - 31 through 40 (of 40 total)

You must be logged in to reply to this topic. Login to reply

sqlfriend SSC Guru Points: 52794 More actions · Answer 1

thank you. So far the last 30 days we consider it as calendar days similar like last month.

But suppose I have a school calendar table, it looks like for calculation of any rolling 30 day I will need to use a student table to cross join calendar table and use over () ....rows between 30 day preceding……, row here becomes very important. That seems the only way to deal with the Calculation. We have 50000 student , not sure about performance yet.

Also suppose the business requirement changed,we need school days instead of calendar days and we have a school calendar table that marked which are holiday and service days. And I still use the above over ()... Order by ...rows between..Windows function but just exclude non-service days. That is fine. Now I also need to calculate the last 30 day, this time I mean exactly the past most recent 30 school days, how can I do it?

Thanks

pietlinden SSC Guru Points: 63599 More actions · Answer 2

Okay, this is getting pretty old. If you don't want to post some sample consumable data... a CREATE TABLE script and some INSERTs to populate the table(s), then I guess you don't really want any help... So do everyone a favor and read this article and then post back.

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

drew.allen SSC Guru Points: 77014 More actions · Answer 3

sqlfriends (11/23/2016)
thank you. So far the last 30 days we consider it as calendar days similar like last month.
But suppose I have a school calendar table, it looks like for calculation of any rolling 30 day I will need to use a student table to cross join calendar table and use over () ....rows between 30 day preceding……, row here becomes very important. That seems the only way to deal with the Calculation. We have 50000 student , not sure about performance yet.
Also suppose the business requirement changed,we need school days instead of calendar days and we have a school calendar table that marked which are holiday and service days. And I still use the above over ()... Order by ...rows between..Windows function but just exclude non-service days. That is fine. Now I also need to calculate the last 30 day, this time I mean exactly the past most recent 30 school days, how can I do it?
Thanks

Your query defines a result set. The window functions specify a window on that result set. It will use whatever result set your query defines. If you only want school days, only include school days in your result set.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

sqlfriend SSC Guru Points: 52794 More actions · Answer 4

Thanks. This time I mean how to query the last 30 days not any rolling days using school calendar table?

Phil Parkin SSC Guru Points: 247180 More actions · Answer 5

pietlinden (11/23/2016)
Okay, this is getting pretty old. If you don't want to post some sample consumable data... a CREATE TABLE script and some INSERTs to populate the table(s), then I guess you don't really want any help... So do everyone a favor and read this article and then post back.
Forum Etiquette: How to post data/code on a forum to get the best help[/url]

^^Please do this. You are wasting people's time by not doing it.

drew.allen SSC Guru Points: 77014 More actions · Answer 6

sqlfriends (11/23/2016)
Thanks. This time I mean how to query the last 30 days not any rolling days using school calendar table?

That is basic SQL. You should be able to figure this out on your own.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

pietlinden SSC Guru Points: 63599 More actions · Answer 7

you can use plain old DATEADD if you're just counting days, without respect to holidays and weekends... but until you post actual consumable data, it's hard to tell.

sqlfriend SSC Guru Points: 52794 More actions · Answer 8

This time I use school calendar table that has a flag that marks which are school days . It looks to me to count absense for the last 30 days I will still have to use over() window's function. Dateadd does it work

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 9

please do as you have been asked on this thread and other you posted.....provide some scripts.

take a look here and follow the good advice, I am sure it will help others to provide what you are asking for.

https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

sqlfriend SSC Guru Points: 52794 More actions · Answer 10

sqlfriend

SSC Guru

Points: 52794

November 23, 2016 at 3:43 pm

#1914345

Ok thanks , will read.