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.
(I moved this thread from datagrid area) I have a sql database that has individual records consisting of name, date, hours worked among other fields.Date and name is part of a unique identifier, so there can NOT be two records for the same person for the same date. My users need a grid view that displays days worked in ONE LINE per user. I have gotten close, but can't quite get the last part. Ive tried group by, distinct, and with rollup and no luck.TABLE:dan 12/13/2012 12:00:00 AM9.123dan 12/14/2012 12:00:00 AM3.123123cara 12/12/2012 12:00:00 AM4.222cara 12/16/2012 12:00:00 AM3.3333cara 12/17/2012 12:00:00 AM2 CODE: Select distinct(name), (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Sunday')as Sunday, (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Monday')as Monday, (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Tuesday')as Tuesday, (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Wednesday')as Wednesday, (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Thursday')as Thursday, (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Friday')as Friday, (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Saturday')as Saturday,(select sum(hours)from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours) as Totalfrom dbo.testtime YTgroup by date, name, hours RESULTS: cara NULL NULL NULL 4.222 NULL NULL NULL 4.222cara NULL 2 NULL NULL NULL NULL NULL 2cara 3.3333 NULL NULL NULL NULL NULL NULL 3.3333dan NULL NULL NULL NULL NULL 3.123123 NULL 3.123123dan NULL NULL NULL NULL 9.123 NULL NULL 9.123 Like I said, I am SO close, I just need it to look like;NAME SUN MIN TU WED TH FR SA TOTAL cara 3.333 2 4.222 9.555 dan 9.125 3.125 12.5 TIAdan
Is it possible to write an expression for RS2005 in the details row in a table that will separate and add the rejection and amount field values for each calendar week and lay them out to look like below:
Week
Rejections
Amount
1
14
15
2
0
0
3
0
0
4
1
6
5
0
0
6
0
0
7
0
0
8
7
8
I€™m trying to make a report that shows the number of rejections and amounts on a week to week basis. So I need RS2005 to add the rejections and amounts for each week date range and display it line-by-line automatically on a per-week basis.
Week 1 is 1/1/07-1/6/07 according to DATEPART(WEEK,Date) and so on€¦
declare @table table ( ParentID INT, ChildID INT, Value float ) INSERT INTO @table SELECT 1,1,1.2
[code]....
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending
I need to import an ASCII tab delimited file that has roughly 5,000 recordsonce a week into a SQL Server table. I have researched BCP and it seemslike the way to go. Am I headed in the right direction?Thanks in advance,James
I have a production database with input from 3 locations outstation. I need to bring down this server once in a while for tuning and other administrative stuff. Right now I'm in no position to shut the server down. Is there any way I can do this.
Also how can I have 2 databases and toggle between the 2 seamlessly without effecting the users. Does it have to be a mirror on the devices ? What do the professionals usually do
I have heard about clusters. Is this a solution for my problem and what is a cluster anyway ? Vijay
I'm building an ASP.NET application that uses SQL DMO to automate database dumps. This will be used by our DBAs to simplify the transfer of databases between server environments. As part of the process, I need to create a backup of an online database and then restore with a different name to run cleanup scripts against it to remove unneeded audit data, etc. I've tried setting objSqlBackup.BackupSetName to accomplish this, but it appears the backup files retain the original database name internally. When attempting to restore the backup, this results in an error saying that the database already exists. Does anyone have a code sample or link that explains how an alternate name can be specified? In SQL Server Enterprise Manager, there is a field for specifying an alternate name when creating a backup. Given that SQL DMO is supposed to offer the same functionality, there must be a way... FYI, The full process is as follows: - Create backup of selected database - Restore database with a different name (this is where I need help) - Run cleanup scripts to clear out unnecessary audit data, etc. - Create backup of "cleaned" database - Create zip file of database backup Thanks in advance for any help you can provide!
We are trying to compare our current calendar week (based on Monday being the first day of the week) with the previous calendar week.Â
I'm trying to produce a line chart with 2 axis:
- x axis; the day of the week (Mon, Tues, Wed etc - it is fine for this to be a # rather than text e.g. 1 = Mon, 2 = Tues etc) - y axis; the cumulative number of ordersÂ
The chart needs two series:
Previous Week. The running count of orders placed that week. Current Week. The running count of orders placed this week.Â
Obviously in such a chart the 'Current Week' series is going not going to have values along the whole axis until the end of the week. This is expected and the aim of the chart is to see the current week compares against the previous week for the same day.Â
I have two tables:
Orders TableCalendar Table
The calendar table's main date column is [calDate] and there are columns for the usual [calWeekNum], [calMonth] etc.Â
My measure for counting orders is simply; # Orders: = countrows[orders].
How do I take this measure and then work out my two series. I have tried numerous things such as adapting TOTALMTD(), following articles such as these:
-Â [URL] ... -Â [URL] ...
But I have had no luck. The standard cumulative formulas do work e.g. if I wanted a MTD or YTD table I would be ok, it's just adjusting to a WTD that is causing me big issues.
I'd like to add a yesterday dimension member to a new dimension, like a "Time Utility" dimension, that references the second last day of non empty data in a cube.
At the moment, I'm doing this:
Code Snippet
create member [MIA DW].[DATE TIME].[Date].[Yesterday] as [DATE TIME].[Date].&[2007-01-01T00:00:00]
select [Measures].members on 0, non empty [DATE TIME].[Date].members on 1 from [MIA DW] But the [yesterday] member does not seem to belong to [DATE TIME].[Date].members?
So I guess there's two questions:
1) Can I have a new empty dimension which contains all these special members like "Yesterday" or "This Week" and "Last Week" (these last two obviously refer to a set of Dates)
2)How come the Yesterday member is not returned by the .members function?
A SQL Server 2005 stored procedure expects a parameter UserID depending upon which it retrieves the no. of records & OrderIDs corresponding to the UserID from a DB table (note that OrderID & UserID are two of the columns in the DB table). So for e.g. consider a user whose UserID=6 & the DB table has 3 records where UserID=6. In other words, there are 3 OrderID records of the user whose UserID=6, say, OrderID=8, OrderID=17 & OrderID=29. The stored procedure will finally return 2 columns - the OrderCount (which is 3 for UserID=6) & the OrderID (which will be 8, 17 & 29 for UserID=6). This is the stored procedure:ALTER PROCEDURE dbo.OrderCount @UserID intASDECLARE @OrderCount intSET @OrderCount = (SELECT COUNT(OrderID) FROM NETOrders WHERE UserID= @UserID)SELECT @OrderCount AS OrderCount, OrderIDFROMNETOrdersWHEREUserID = @UserIDIn a VB class file, I am invoking the stored procedure in a function named GetOrderCount which returns a SqlDataReader back to the calling ASPX page. I want the ASPX page to display the 3 OrderIDs of UserID=6 in a Label control. Unlike the DataBinding controls like DataList, DataGrid, Repeater controls, the Label control will not automatically loop through the recordset. So I tried to accomplish this using a For....Next loopDim i As IntegerDim sqlReader As SqlDataReaderiUserID = Request.Cookies("UserID").Value'ShopCart is the name of the class in the VB class fileboShopCart = New ShopCartsqlReader = boShopCart.GetOrderCount(iUserID)While (sqlReader.Read) If (sqlReader.GetValue(0) > 1) Then pnlLinks.Visible = True For i = 1 To sqlReader.GetValue(0) lblLinks.Text = sqlReader.GetValue(1)(i) Next i Else pnlLinks.Visible = False End IfEnd WhileBut this generates the following error:No default member found for type 'Integer'.pointing to the linelblLinks.Text = sqlReader.GetValue(1)(i)in the above shown ASPX code. Can someone please correct me & suggest how do I loop through the recordset so that I can display the records in a Label control?
I have the following sql table and would like to group the results by "StoryTitle" to display in a datalist. The Storytitle field in the datalist is a LabelID StoryTitle StoryAuthor Rating StoryID Comments 1 About Me goodyone 6 20 Great Story 2 About Me goodyone 5 20 Love your work 3 Hello World magicme 6 26 What a Story 4 Hello World magicme 7 26 This Reminds me of... I know i have to do something in the SQL Datasource statement. Not sure how to do it. here is my statement below <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:BrillConnectionString1 %>" SelectCommand="SELECT * FROM [iaw.comments]"> </asp:SqlDataSource>
TableName: Order_Archive Fields: orderid load_date filename order_date dollar
I load a file each week into a table, each file has unique orderid, load_date, filename, order_date and dollar. However the same orderid, order_date and dollar could appear in another file with different load_date and file_name.
m.material_subtotal, m.cec_job_id,m.location FROM material m left outer join Other_Project op on m.project_id=op.other_proj_id left outer join Daily_Time_Entry dl on dl.project_id =op.other_proj_id inner join Employee_Mgmt emp on emp.emp_mgmt_id = dl.emp_mgmt_id inner join CEC_Job cec ON dl.cec_job_id = cec.cec_job_id where (dl.dt_date='3/14/2008'and m.material_date='3/14/2008') and (dl.project_type='Other Project' and m.project_type = 'Other Project') and (dl.cec_job_id=m.cec_job_id) and (dl.daily_type='Electrical') and (dl.project_id='18')
Fields are Dt_id int Daily_type varchar Dt_date datetime Emp_id int Project_type varchar Project_id int Time_st int Time_ot int Time_dt int Records in Daily_Time_Entry are as follows
Where dt_date is in dd/mm/yyyy format Also in project_type field -- AB Corporate is taken for Ab_Corporate_Project , AB Plant for Ab_Plant_Project, Other Project for Other_Project This means if Project_type = AB Corporate then search into Ab_Corporate_Project Else if Project_type = AB Plant then search into Ab_Plant_Project Else if Project_type = Other Project then search into Other_Project
Now I want to display records as per datewise suppose I want to view records by date 13/03/2008
I am saving files in SQL Server 2005 with a datetime field called news_date_time and I want to display all today's records regardless of the record time.
I tried this code but didn't work..
[code] SqlCommand sql_command = new SqlCommand("SELECT * FROM files_news WHERE news_date_time = TODAY ORDER BY news_date_time DESC", sql_connection); [/code]
How would I list the users in the users table that have duplicate IDs or count of IDs > 1?The UserName field is unique. State UserName First Last ID City CountTX Kkeaton Kathryn Keaton 1001 Dallas 2TX KakiKeaton Kathryn Keaton 1001 Dallas 2I think I have to use a subselect? If I use group by then it won't show both records. It shows only one of them.Thanks Craig
I have two tables, Promotion and Promolocation. The Promotion table is used to set up promotions or sales, and consists of a PromoID, StartDate, and EndDate. Each PromoID is referenced in the Promolocation table, which is used to assign items to a promotion for various locations or stores. The Promolocation table consists of PromoID, LocID, SkuID, PromoPrice, and DiscLevel.
There are times where an item or SkuID will exist in more than one promotion, however, our application is currently not intelligent enough to determine which promotion to use, so it sets the active promotion based on the StartDate being before other promotions' StartDate and the EndDate being after other promotions' EndDate.
I want to find all promoid's where a sku exists in more than one promotion. I want to signify which promotion is active, using 1 as the first active promotion, 2 as the next active, 3 as the next, etc. To determine which promotion is the first active promotion, the StartDate must be before any of the other promotions' StartDate, and the EndDate must be after other promotions' EndDate. If the promotions' StartDate is after the other promotions' StartDate but not before the other promotions' EndDate, and the EndDate is before or on other promotions' EndDate, then that's the second active promotion. If the StartDate is the same as other promotions' StartDate, but the EndDate is before other promotions' EndDate, then that's the third active promotion.
For example:
PromoID StartDate EndDate ------- --------- ------- PROMO1 1/1/2004 1/1/2006 (1st Active Promotion) PROMO2 2/1/2004 1/1/2006 (2nd Active Promotion) PROMO3 1/1/2004 12/1/2005 (3rd Active Promotion)
Here's a query I am using to display all active promotions:
select pl.promoid, pr.startdate, pr.enddate, pl.locid, pl.skuid, pl.promoprice, pl.disclevel from promolocation pl inner join promotion pr on pl.promoid = pr.promoid where pr.enddate >= getdate()
I would like to build a query that will return all the records in Table1 that will not match with records in table 2. All colums in table 1 have NULL values. Only one column is populated with state abreviations.
SELECT nvl(field1, 0), field2, field3, nvl(field4, 0), nvl(field5, 0), nvl(field6, 0) FROM TBL1 ------------------------------------------- Result: Record count 3000.
Only field3 is populated everything else is null.
select field3 from tbl1 group by field2 - record count = 48
I would like to run a SELECT statement using the SQLCMD, however, I do not like the count of records be displayed after the execution. How do I do that?
BrokerDimId  BrokerNm 1  Broker1 2  Broker2 3  Broker3
And I have a fact table that has 2 rows that join back to the first 2 dimension rows like this:
BrokerDimId  Amt 1  $100 2  $200
I want to create the mdx so that I get all of the dimension rows, even if there is no record in the fact table for it and just default the amount to 0, like this
I have a table that have student names and their birth dates just like below
a)Â tblStudentInfo
which shows records like below (Birth Dates are shown in Year-Month-Day format)
ID  StudentName     BirthDate   1   ABC            2002-12-25 2   DEF             2002-09-10  3   GHI             2002-09-19 4   JKL             2002-06-10Â
[code]...
I want to perform a query that should display the upcoming birthday of all students according to today.Lets say, today is 2015-10-02, so the query should display the result according to today's date just like below
ID  StudentName     BirthDate   1   ABC            2002-12-25 2   VWX            2002-01-01 3   STU             2002-02-03 4   PQR            2002-03-05Â
while(select MAX(wrh) from @tem1 where wrh = 0) < 1 begin update @tem1 set wrh = (select toaccount from @tem1 where reportdate = (select min(reportdate) from @tem1 where wrh = 0))+(select max(wrh) from @tem1) where wrh = (select max(wrh) from @tem1 where wrh = 0 ) and reportdate = (select min(reportdate) from @tem1 where wrh = 0) end
this is the result while executing loop statement .
employeeidreportdatereportatleftatdehdrhwehwrh 129029 Jan 201409:3019:15008:0009:20024:00065:54 129028 Jan 201409:0018:45008:0009:18016:00056:34 129027 Jan 201409:0018:45008:0009:18008:0009:18 129025 Jan 201408:0010:00005:0002:00045:00047:16 -- week end 129024 Jan 201409:1718:45008:0009:01040:00045:16 129023 Jan 201409:1918:46008:0009:06032:00036:15 129022 Jan 201409:1718:47008:0009:05024:00027:09 129021 Jan 201409:1618:35008:0008:46016:00018:04 129020 Jan 201409:1818:55008:0009:03008:0009:03
How to update only that week hrs , don't continue next week...