First of all I wants to "Thank You" all of you has been trying to helps me solve some of the problems.
Now, I come across trying to get data records from 3 columns but two columns "SeasonalStartsuppressiondate" & "SeasonalEndSuppressionDate" both contain no year last four digits. How would I select a query have some records that customer is not fall in between vacation time given a particular months Mar/02/ and Nov/04/? I only want record with “Y? fields not in those months. Notice don't have "YEAR" at the end and so, I can't determine it year of 2005 or 2006.
Here's my table and query statement:
Table - Column fields
SeasonalFlag, SeasonalStartSuppDate, SeasonalEndSuppDate
Y ,04/02,11/04
N ,....,....
FROM advodb ----<<<<my sample query>>>>>-----
WHERE (seasonalflag='y'
AND
convert(datetime,seasonalstartsuppdate + '/2007')< getdate()
AND
convert(datetime,seasonalendsuppdate + '/2007') > getdate())
The goal is to group all this transactions by Month and Year.
Something like this....
MS030_A MS030_F MS036_A MS036_F MS040_A MS040_F Month Year
3 2 2 2 2 2 1 2006
4 4 7 9 8 9 2 2006
10 10 6 8 8
3 2006
4 4 5 5 3 2 4 2006
5 6 8 3 7 1 5 2006 For just one date column it is pretty straight forward i.e., just do a select count and group by DATEPART ( Mm, DateField) but for multiple columns i am in a total fix....
I have a sum on a reportitems cell in my header: =Sum(ReportItems!textbox1.Value)
When I run the report, it looks excellent. My issue is when I export it. When I export to Excel, it looks just like it did. When I export to PDF, it gives me a total per page, not for the report.
Does ReportItem behave differently when rendered between excel and PDF? Or is it because I am putting a SUM on a ReportItems cell?
Does anyone know the logic that Enterprise Manager/SQL Server uses to distinguish system objects from user objects. I've looked through all the sysobjects in the database and there doesn't seem to be any unique logic for seperating all objects. User tables are easily distinguished from system tables (U/S) but thats not the case with stored procedures and views. I'm looking for suggestions... Thanks, Adam
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1]( [TestTable1_pk] [int] IDENTITY(1,1) NOT NULL, [Source_ID] [int] NOT NULL, [Edition_fk] [int] NOT NULL, [Key1_fk] [int] NOT NULL, [Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
I am planning to use transacational replication (instead of merge replication) on my SQL server 2000. My application is already live and is being used by real users.
How can I ensure that replicated data on different server would have exact same values of identity columns and date columns (where every I set default date to getdate())?
It is very important for me to have a mirror image of data (without using clustering servers).
I am working on a Statistical Reporting system where:
Data Repository: SQL Server 2005 Business Logic Tier: Views, User Defined Functions, Stored Procedures Data Access Tier: Stored Procedures Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by
different organizational hierarchies different number of layers within a hierarchy select a organization or select All of the organizations with the organizational hierarchy combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies: Hierarchy 1
Country -> Work Group -> Project Team (Project Team within Work Group within Country) Hierarchy 2
Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:
Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams Country = "USA", Work Group = all work groups
How to implement the data interface (Stored Procs) to the Reports Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all") (WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)
Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.
I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select Jan 1- 23rd feb 1-23rd march 1-23rd april 1-23rd ,value from table
I have a scenario that reminds me of a pivot table and I am wondering if there is a way to handle this in SQL.
I have four tables. Product Line, Item, Property, and Value.
A Product Line has many items and an item can have many property's and a property can have many values.
I want to select a product line and show all the items with the Property's as column headers and the Values as the data. The thing I am having trouble with is the property's for an item are variable from a few to a whole bunch.
So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.
Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?
have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003.
i have tried with the following query, i need help putting my queries together select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004')
when i run this query i get this message
Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15
I have a table with a field 'service_date' held as datetime. I would like to select from the table where the service date is equal to today. The input is dd/mm/yy.
I use smalldatetime for a datetime and i just display the date part i'd like to compare the date part of the smalldatetime and the date i have how can i do that ? I know we can select the day,month or year ... If you know a link where i could find different kinds of example about sql command it would be great to
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
The table B stores the item process time. The structure is as below
Item ProcessStartDate ProcessEndDate V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000 Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000 W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000 A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000 B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000 C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000
I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000 B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000 C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000
Scenario 2 expected result
Table A
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
I have three INT columns in a table that record the users birth year, month, and day.
BDAY_DAY (INT) BDAY_YEAR (INT) BDAY_MONTH (INT)
I'd like to include a function in my query that will return their Age in years based on these three columns.
I found this function on the internets, but I'm not sure how to build a DATETIME object using the three int date columns to pass to the function. If you could help me there it'd be most appriciated.
Create FUNCTION dbo.GetAge (@DOB datetime, @Today Datetime) RETURNS Int AS Begin Declare @Age As Int Set @Age = Year(@Today) - Year(@DOB) If Month(@Today) < Month(@DOB) Set @Age = @Age -1 If Month(@Today) = Month(@DOB) and Day(@Today) < Day(@DOB) Set @Age = @Age - 1 Return @AGE End
Usage (how do i pass the three columns into this function??)
SELECT Last_Name, First_Name, ssn, dob FROM Employee_Data e (nolock) WHERE Cust_Id = 'Customer1' and dbo.GetAge(e.Date_Of_Birth, getdate()) >= 21
SQL Server 2000 I have four columns of date in a table say Date1, Date2, Date3, Date4 in table Tab_A. I want to get average date of four columns for each row. Please advise how?
I have 6 columns, all with dates within them, i.e.Proposed Start Date 1Proposed Start Date 2Proposed Start Date 3Proposed Finish Date 1Proposed Finish Date 2Proposed Finish Date 3What I need to do is narrow this down into two fields:Start DateFinish DateSo I need to find the newest value from the columns. i.e.If PS1 filled PS2 and PS3 empty, then Start Date = PS1If PS3 empty and PS2 filled, then Start Date = PS2If PS3 filled then PS3and similarly for Proposed Finish Dates.Anyone knew how I can do this.(Maybe the following will help for the programmers out there:If PS3 <> nullThen StartDate = PS3Else if PS2 <> nullThen StartDate = PS2ElseStartDate = PS1End IFJagdip
I know you can restrict/grant select rights to certain columns in SQL Server table. My question is if in a select query, where you list out the columns (select colum1, column2...) that you have select rights on do you also have to have select rights on columns that apear in where clause or the columns in a join? For example say I have select rights on column1 and column2 of test table, but not on column3. If I run a select statement like this: select column1, column2 from test where column3 = 'abc' Will I get a select persmission denied because the query is using column3 in the where clause. David
Hi, I've written a SELECT statement that returns columns dependant on a bitwise parameter @Populate.SELECTCASE WHEN (1 & @Populate) = 1 THEN [Column1] ELSE Null END AS [Column1],CASE WHEN (2 & @Populate) = 2 THEN [Column2] ELSE Null END AS [Column2],CASE WHEN (4 & @Populate) = 4 THEN [Column3] ELSE Null END AS [Column3],CASE WHEN (8 & @Populate) = 8 THEN [Column4] ELSE Null END AS [Column4],etcIs this the most efficient way to acheive this since I am only seeing a small performance gain. It still returns all columns but depending on @Populate will leave some columns with Null values.Any help much appreciated, thanks.
how can i retrieve two columns from sqltable with - seperating the results and a common column name someth like this select id ,name from user id name 1 a 2 b 3 c i need the result set to be my_reports 1-a 2-b 3-c is that possible, if so, could anyone tell me how to do?
OK, i'm sure this is an easy one, but can't seem to figure it out. Basically need to search against two database columns (fields) to see if either contain the work "test". Here is an example of the statement but of course dosn't work correctly: SELECT * FROM tbl_db WHERE skills LIKE 'test' AND title LIKE 'test' Please note the AND is incorrect. What i need to accomplish: if one record contains 'test' in its skills field then SELECT it. if one record contains 'test' in the title field the SELECT it. I need to make sure that is 'test' is in both skills and title fields then only display once. Thanks, Jake
I am working with several similar tables that for the most part contain the same columns. I need to export these tables to different csv files, and the csv file needs to have all possible column names and blank fields if the column does not exist in the table. Basically a set and subset thing.
for example TABLE AA has three columns A, B, C and TABLE AB has 2 columns B, D
I am working with over 200 tables, and am planning on automating this with c#. I could use the information_schema to create the SQL statement for each tabel but it sems like I should also be able to do this using one select statement.
I want to write a select statement that combines this. SELECT [A], [B], [C], '' AS [D] FROM [AA]
