Posted: 25 Jul 2013 09:30 AM PDT

Hi all. I need to sum the attendance hours by category and then group by 'Week of'. The 'Week of' start date is defined by the Monday in that week but Sunday is works too. If the Category Values are in 'Art' or 'PE', they need to be combined into Non Educational. I also need to be able to flag the day(s) a student reaches 120 hours.[code="sql"]CREATE TABLE Attendance ( ID int, Category varchar(20), Title varchar(20), Date datetime, Hours int, )INSERT INTO Attendance VALUES (4504498, 'GED Program', '7/1/2012', 7),(4504498, 'GED Program', '7/2/2012', 3),(4504498, 'GED Program', '7/3/2012', 3),(4504498, 'GED Program', '7/4/2012', 7),(4504498, 'GED Program', '7/5/2012', 3),(4504498, 'GED Program', '7/8/2012', 3),(4504498, 'GED Program', '7/9/2012', 7),(4504498, 'GED Program', '7/10/2012',7),(4504498, 'GED Program', '7/11/2012',3),(4504498, 'GED Program', '7/12/2012',3),(4504498, 'High School', '7/1/2012', 7),(4504498, 'High School', '7/2/2012', 3),(4504498, 'High School', '7/3/2012', 3),(4504498, 'High School', '7/4/2012', 3),(4504498, 'High School', '7/5/2012', 3),(4504498, 'High School', '7/8/2012', 7),(4504498, 'High School', '7/9/2012', 3),(4504498, 'High School', '7/10/2012',8),(4504498, 'High School', '7/11/2012',3),(4504498, 'High School', '7/12/2012',7),(9201052, 'Art', '7/15/2012', 6),(9201052, 'Art', '7/16/2012', 3),(9201052, 'Art', '7/17/2012', 7),(9201052, 'PE', '7/17/2012', 7),(9201052, 'PE', '7/18/2012', 7)[/code]I need an end result which looks like this:ID Category Week of Total Hours4504498 GED Program 7/1/2012 264504498 GED Program 7/8/2012 234504498 High School 7/1/2012 194504498 High School 7/8/2012 289201052 Non Educational 7/15/2012 30ID Day_120_Hours_Reached356485 6/30/2012356485 11/15/2012555666 10/12/2012555666 2/25/2013I have been looking for examples of a Week function that will pull out the 'week of' from a date using MS Sql Server and I can't find much info. Any feedback is appreciated.

Posted: 25 Jul 2013 05:53 AM PDT

Hello, I'm trying to pull information from server B into a temp table in a stored proc that does stuff and then inserts it into server A. The stored proc will be ran on server A. The only problem is that i need to try to find a work around so that I'm not using sp_addlinkedServer because of the security rights that follow it ( i prefer not to deal with that mess).I'm currently using OpenDataSource but having that long string at the end of every from statement looks sloppy. I've tried to use OpenRowSet but i cant seem to get it to work, not to mention it will look sloppy like the OpenDataSource anyways.Is their any other work arounds or any way to make more compact. Thanks

select distinct

Posted: 25 Jul 2013 05:30 AM PDT

Helloi´ve this querySELECT distinct BO.NMDOS,bo.ETOTALDEB, bo.tpdesc ,bo.dataobra,BO.NOME ,BO.OBRANO,ft.nmdoc,FT.FNO,CASE WHEN FT.NDOC<> 1 THEN 0 ELSE FT.ETTILIQ END as etiliquido FROM BO left JOIN BI ON bi.bostamp=bo.bostamp left JOIN FI ON fi.bistamp=bi.bistamp left JOIN FT ON FT.FTSTAMP=FI.FTSTAMP WHERE BO.ndos='18' and bo.fechada=0 GROUP BY BO.NMDOS,bo.DATAOBRA,BO.NOME,BO.OBRANO,BI.OBRANO,FT.FNO,FT.ETTILIQ,bo.tpdesc ,ft.nmdoc,bo.ETOTALDEB,ft.ndoc order by bo.DATAOBRA --- that returns this something like this--Dossier 1|1000,00|10|20130210|client|999|Invoice|1|150,00Dossier 1|1000,00|10|20130210|client|999|Invoice|4|250,00Dossier 1|1000,00|10|20130210|client|999|Invoice|6|250,00.... and goes on...my question ? can i remove the duplicate row value 1000,00, to return only the first, to something like thisDossier 1|1000,00|10|20130210|client|999|Invoice|1|150,00Dossier 1|Null|10|20130210|client|999|Invoice|4|250,00Dossier 1|Null|10|20130210|client|999|Invoice|6|250,00thanks in advance

Sql samples, sql tips

Thursday, July 25, 2013

[SQL Server] Sum the Attendance Hours by Category and then Group by 'Week of'

[SQL Server] Sum the Attendance Hours by Category and then Group by 'Week of'

No comments:

Post a Comment

Search This Blog