I have a table that records when a value changes. For example item = x, date = 2014-06-15, value = 0; item = y, date = 2014-06-16, value = 1; item = x, date = 2014-07-10, value = 1; item = y, date = 2014-07-12, value = 2 .... etc etc.
How can I find out what the value is for each item on any given date in any month?
I want to list Dates for particular day between two dates.
for eg. if user enters 08/01/2014 and 08/31/2014 with value 0 then all the Sundays and related dates should be display.
if above contamination with 1 then all Mondays,
I get a red squiggle below the first IF statement. Can't figure out the correct syntax.
UNION ALL
IF ((SELECT Flexpool FROM Huizen WHERE IDHuis = @LOCATIECODE ) = 'WAAR')
SELECTM.LOCATIECODE, M.AfdelingZPT, DATEPART(ISO_WEEK, M.Begindatum) wk, M.DienstGroep, NULL Leeg, NULL Leeg, SUM(M.Hrs) AS Uren
FROMDRPHrsManual M
[Code] ....
So I would like to split rows in my query based on if they match criteria and I really havent dealt with splitting in SQL only combining(which is usually what everybody does lol)
So for instance my query results is this
SOPNUMBER TAX TOTAL
14-123456 $500.00
I’m trying to write a query that will select closest value to Amount field in #TestValue without go over #SM.GMAdj value.Outcome of the query should be something like
PercRate Amount Code GMAdjPayment
-------------- ----------- ---------------------------------
6.00 ----172715 -----13-------171314.36------900
7.00 ----200200 -----18-------200299.00------500
Tables are:
SELECT PercRate, Amount, Code
INTO #TestValues
FROM (
SELECT 6.00, 172715, 13 UNION ALL
SELECT 6.50, 172716, 13 UNION ALL
SELECT 7.00, 181351, 13 UNION ALL
SELECT 7.50, 190419, 13 UNION ALL
[code]...
I thought this would be somewhat easy but I'm having trouble with this one. I have a statement that if 'ACTLABCOST' or 'ACTMATCOST' has a value of 0.00 then I need to make it .00.
Here's the statement:
Select
CONVERT(VARCHAR(10), xn_approveddate, 101) + ' ' + convert(VARCHAR(8), xn_approveddate, 108)as "Approved Date",
WORKTYPE,
[Code]....
I have a more than 50 departments in my table and trying to get top 10, something I need to show in a report like top9 departments and then rest of them has to show as Others in 10th row. is it possible ?
when I use top 10 from table I am getting top 10 rows but I need the top 9 list and rest as others in 10th row.
I have table like below
Custid, department, location
1 Fin NY
2 Acc NY
3 HR MI
4
5
6
My client wants IN_PUNCH to be the first in punch of the day and the OUT_PUNCH to be the last out punch of the day based on the TransactionDate as it is showing in the desired results table for 07/29 and 07/30 (Changes are highlighted in Bold).
I tried using MIN and MAX at different areas but to no avail.Also, is there a way to add a counter at the end of each row so in case if there were two shifts in a day then it be a '1' for row 1 and a '2' for row 2?
WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS,PAYCODE,SHIFT_LABEL,DOW) AS
(
SELECT 1234,'07/27/2015','07/27/2015','07/27/2015', '12:00','12:00','8', 'Hol', 'NULL', 'Monday' UNION ALL
SELECT 1234,'07/28/2015','07/28/2015','07/28/2015', '08:00','','4.00', 'Absent', 'Batax 1st','Tuesday' UNION ALL
SELECT 1234,'07/28/2015','07/28/2015','07/28/2015', '12:15','14:00','3.75', 'Regular', 'Batax 1st','Tuesday' UNION ALL
[code]...
I have a field in a table which was VERY poorly designed, but that is a matter for another day. Long story short, this field contains, in most instances, where the sale was obtained, the sales man name, and a comment about the sale. A few records have garbage data in the field as the salesman name was not obtained so we want to attribute the info to 'unknown'Is it possible in SQL Server 2008 to write a query that will display the saleinfo for each salesman then a total row under the salesman? Something similar to this
Internet Mark .... statistics here
Phone Mark - applied for credit .... statistics here
Phone Mark - customer referral ..... statistics here
Marks Sales Totals .... statistics here
Next salesman data would go here
but break that down by each salesman and attribute the garbage data like 85623, albaca, racava to salesman 'Unknown'..This is garbage data that should suffice to achieve my desired end result. I know this is unable to occur with a simple select. I even tried a few CTE queries but couldn't get the syntax accurate due to saleinfo basically being a catch all column I was unsure of how to only extrapolate the data I needed
Create Table sales
(
saleid int
,saleinfo varchar(200)
,salestatus varchar(200)
)
Insert Into sales VALUES
(1, 'Phone Mark customer referral', 'Done'), (2, 'Phone Mark customer referral ', 'Done'), (3, 'Phone Mark - applied for credit', 'Holding Pattern')
,(4, 'Internet Mark', 'Done'), (5, 'Internet Mark', 'Holding Pattern'), (6, 'Internet Mark', 'Holding Pattern')
,(7, 'Phone Stan', 'Holding Pattern'), (8, 'Phone Stan', 'Done'), (9, 'Phone Stan', 'Holding Pattern')
,(10, 'Internet Stan - lives to far to drive', 'Done'), (11, 'Internet Stan', 'Done'), (12, 'Phone Vic', 'Done')
,(13, 'Phone Vic', 'Holding Pattern'), (14, '85623', 'Done'), (15, 'albaca', 'Done'), (16, 'racava', 'Done')
I have a query which is working fine. Is it possible that if the table3's column(Child) is only related to table 1 to show it under table 1 and not under table 2, but at the same time another (Child) has a parent in table 2 (which usually is the case) it will show under table 2 as its currently doing.
In other words Child column is directly under Table2's row column name (Father), but occasionally it comes under Table1 with no relation to Table 2.
How can I out put that in a query for a treeview? I am assuming that I will have to program the out come in c# also with 3 for loops and in the second loop I can check if the column is grandchild or Child and make that as a second row or 2nd node of treeview, but I am having a problem building a query in sql.
The query below shows all Parent, then child then grand child(all well and working), but what is desired is at times child takes place of a father.
declare @x as xml
set @x =
(
SELECT distinct
Table1.AssetSysID, Table1.Asset_ID , Table1.FromLR, Table1.Asset_ID + ', ' + Table1.[Desc2] as GarndFather,
Table2.ACISysID ,Table2.PAssetSysID, Table2.FeatureName + ', ' + Table2.[DESC] AS Father,
Table3.ITMSysID ,Table3.Item_ID + ',' + Table3.[DESC] as Child
[Code] .....
Sample Data
MemberID Codes
00000123 012|222|123|333
00000233 012|222|332
00000244 012|211
00000332 012
I am trying to get it so as following:
MemberID Code1, Code2, Code3, Code4
I tried using the XML method but in working with an example, I actually got stuck on one of the declare fields and cant seem to work around it.
Failed Attempt:
DECLARE @x xml;
DECLARE @line VARCHAR(MAX);
SET @x = Cast(
'<field>'
+ replace(@line, '|', '</field><field>')
+ '</field>' AS XML);
[code]...
Here is example table and data
CREATE TABLE [dbo].[sales](
[date_value] [datetime]NOT NULL,
[monthly_total] [int] NOT NULL
)
insert into sales ( date_value, monthly_total)
[code]...
How can I create a query to get below result?
YearMonthMonthlySaleAccumulated Total
2012Jan110 110
2012Feb130 240
2012Mar30 270
2012Apr60 330
2012May10 340
[code]...
create table cust_details
(
id int ,
city varchar(20),
cust_name varchar(20),
sales int
)
insert into cust_details
values('1','Tamilnadu','101','500'),
values('2','Tamilnadu','102','300'),
values('3','Pondi','103','200'),
values('4','Pondi','104','100')
My expecting o/p:
-----------------
city customer sales
tamilnadu 101 500
102 300
------
total 800
-------
or
customer sales
101 500
102 300
total 800
Like I wanna display separate location?
How do you take a string and search for the first "-" and then drop everything from the start to, and including, the "-"?
View 5 Replies View RelatedIn our concern, we have a table with lots of redundant rows, to avoid the redundant, we create some structure and denormalize the table now we need to migrate the old data into the new structure.
Here is my following table structure
DECLARE @TestTable AS TABLE(id INT, DAta1 VARCHAR(500), Data2 VARCHAR(500), Data3 VARCHAR(3))
INSERT INTO @TestTable
VALUES
(1, 'Name', 8, 1),
(1, 'possible Shifts', 30, 7),
(1, 'First shift', 22, 8),
(1, 'Second Shift', 24, 9),
[Code] ....
This is just a sample data we have a lot amount of data like this, any changes in Data1 or Data2 might came under a new version, any new insert or delete in a ID column based Set might consider as a new version. So I need to migrate this into the following structure of output
DECLARE @tbl AS TABLE (ID INT, DAta1 VARCHAR(200), DAta2 VARCHAR(200), Dversion INT)
I need to import the data as set with out redundant please do not consider the column data3, and the output might be like this
SELECT * FROM
(
VALUES
(1,'Name','8',1),
(1,'possible Shifts','30',1),
(1,'First shift','22',1),
[Code] ....
This is for single time import only...
I am trying to code a rule at the moment which sets a value based on the value of the previous row. This is as far as I have got so far. I am trying to run it as a set based command and I believe I may need to make use of a numbers table.
Anyway the code is....
declare @data table
(ID int identity(1,1),
DeviceID int,
EventTypeID int,
EventID int)
[Code] ....
So the output is currently incorrect. Each DeviceID needs to have a ID assigned to the MIN(ID) which isn't yet in the code above. This ID has to be sequential across the full table and not dependent on ID.
Next the rule is coded in the case statement above.
So for each DeviceID, when the EventType goes from 1, 2 or 4, to 0 or 3, the following record after the 0 or 3 will have a new EventID. And conversely when the EventType goes from 0 or 3 to 1,2, or 4, the record that is the 1, 2 or 4 will have a new EventID.
I am having SP where I am pulling data from linked server. Previously its working fine but suddenly started to give below error.
Msg 15281, Level 16, State 1, Procedure Procedure_Name Line 184
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
I have a report that looks like the following
NameCity Client NoAccount No Balance
SmithSydney 1234561258792 3.95
JonesMelbourne 2589641000657 9.54
BrownPerth 9876541000879 5.46
BrownPerth 9876541000880 7.51
WhiteSydney 6548521007562 10.65
HOWEVER I need it to look like the following:
NameCity Client NoAccount No BalanceAccount No Balance
SmithSydney 1234561258792 3.95
JonesMelbourne 2589641000657 9.54
BrownPerth 9876541000879 5.461000880 7.51
WhiteSydney 6548521007562 10.65
The requirement is that if the Client No is the same that the Account No and Balance appear on the same row but just additional columns. There is no restriction on how many extra columns there would be.
Once the query is working it needs to go into Visual Studio so that the report can be set up as a subscription.
I've One table that contains all current appointments booked as below.
RNappt_idclinic_idderived_location_idprovider_idappt_date_timeappt_date_endtime
1102186011435/27/14 8:305/27/14 9:00 AM
2102196011435/27/14 9:305/27/14 10:00 AM
3102206011435/27/14 10:305/27/14 11:00 AM
4102216011435/27/14 14:005/27/14 2:30 PM
5102226011435/27/14 16:005/27/14 4:30 PM
6102236011435/27/14 16:305/27/14 5:00 PM
for Same Clinic,location and Provider there's a schedule data for same day 24 hours
having every five mintues as below:
clinic_idderived_location_idprovider_idstart_timeendtimeunblock
601143 8:01 8:040
6011438:05 8:100
601143
60114313:0113:041
601143
60114313:5514:001
601143 1
60114317:5518:000
60114323:550:001
for rows having no start and endtime assume it as regular intervals.
So i need to show available appointment with duration one hour with the available schedule which is for every five minutes
Like My first appointment for today starts at 8:30 but 8- 8:30 is unblock so there could be an appointment but as this chunk is less than 60 so need to create it
For Schedule table below is what i've to create for temporary basis as this will be available in nightly load in a table.
DECLARE @num int=5
,@LASTtime TIME =CAST('23:55' as TIME)
,@Time TIME =CAST('00:00' as TIME)
,@Timeprev TIME =CAST('00:00' as TIME)
WHILE ( @Time<>@LASTtime)
BEGIN
[code]....
I want to calculate total sum of column.
I have a table with many values of column:
declare @time table
(times nvarchar(255))
INSERT INTO @time (times)
Select ('05:01')
UNION ALL
Select ('00:01')
UNION ALL
Select ('170:01')
UNION ALL
Select ('2543:01')
Select * from @time
How can I get seconds value for every row?
05:01 - 301
00:01 - 1
170:01 - 10201
P.s. Finally I use - Select convert(char,dateadd(second,152581,60),108) as sumtime
its possible to check what the first node is before loading an XML file. The file name is just a combination of random numbers e.g. TMP_18028423.XML there's no way of knowing what it contains I need to check before loading into a table. Then I'll process the data from the temp table. i need to check if the first node is <Departments> then load the file that contains departments.
Here's the script I'm using to load the File:
CREATE PROCEDURE JobDepartmentXMLImport
@FileName varchar(256)
AS
BEGIN
--Create temp table
CREATE TABLE #TempDepartment(
[DepartmentName] nvarchar(64) NULL,
[code]....
How to get task counts in a sales report.
The data is located in three tables: Projects, ProjectTasks, Sales.
I need to group the data by month and project. It needs to include sales per month as well as the number of each project task completed that month.
Projects:
ProjectIDName
1Project1
ProjectTasks:
ProTaskIDProjectIDTaskCodeBeginDateEndDate
11 Task11/1/20141/15/2014
21Task21/15/20141/20/2014
31Task3 1/21/20141/29/2014
Sales:
SalesIDProjectIDClosingAmount
11 1/31/2014$5000
Query Output:
ClosingDateProject TotalSalesTask1sTask2sTask3s
1/2014Project1$500011 1
My query so far is:
SELECT right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)) as ClosingDate,
p.name as Project, SUM(s.amount) as TotalSales
FROM Sales s
JOIN Project p ON p.projectID = s.projectID
WHEREs.closing >= DATEADD(mm, -12, GETDATE())
GROUP BY right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)), p.name
This will give me the grouping by month/year and project.
I have been using dynamic sql in an inner cursor and I get an error about the fetched variable of the outer cursor. How can I fix it?
DECLARE A1 CURSOR GLOBAL FOR
SELECT Iid, Server, Dbname
FROM@OuterTable;
[code]...
My query pulls data from two separate databases, and it runs extremely slow. The queries run well when they are pulling from their own database. When I join them to pull from both databases.
View 9 Replies View RelatedA user is wanting to run a query against the current 3 active databases. These change every month on the 1st e.g. Test-06-14. Therefore I want to set up a dynamic query which will always use the current database. I believe I am almost there but I cannot set the USE @DatabaseName dynamically yet.
DECLARE @DB_Name varchar(100)
DECLARE @DatabaseName varchar(100)
DECLARE @Command nvarchar(200)
DECLARE @Command2 nvarchar(200)
DECLARE database_cursor CURSOR FOR
[Code] ....
I have two tables that I need to combine, they are as follows:
AccountCheckInDT
3289442/14/2014 15:11
3289442/14/2014 15:21
3289442/15/2014 15:24
3289442/15/2014 17:45
3289442/15/2014 23:58
3289442/16/2014 20:09
3289442/17/2014 11:27
3289442/19/2014 9:35
3289442/19/2014 9:41
AccountCheckOutDT
3289442/14/2014 16:18
3289442/16/2014 0:27
3289442/19/2014 9:28
3289442/19/2014 10:00
This is how I need the end result to look:
AccountCheckInDT CheckOutDT
3289442/14/2014 15:11 2/14/2014 16:18
3289442/14/2014 15:21
3289442/15/2014 15:24
3289442/15/2014 17:45
3289442/15/2014 23:58 2/16/2014 0:27
3289442/16/2014 20:09
3289442/17/2014 11:27
3289442/19/2014 9:35 2/19/2014 9:28
3289442/19/2014 9:41 2/19/2014 10:0
in a StoredProc I want depending on whether a parameter is passed formulate the Where clause accordingly.
If parameter IS NOT NULL
THEN take all Rows
Else take the row with the ID (INTEGER)
How do I formulate the where clause?
We have a medium sized database with the next tables:
- PA: 525000 records
- PR: 780000 records
- R: 1000 records
- B: 45 records
PA: PK = PAARDCODE
PR: PK = PAARDREGISTERCODE
PR: FK = PAARDCODE
PR: FK = REGISTERCODE
R: PK = REGISTERCODE
R: FK = BOEKCODE
B: PK = BOEKCODE
When I group by B.BOEKCODE the query lasts: 10 (or more when 'where' option is added) seconds
When I group by R.BOEKCODE the query lasts less than 2 seconds.
SELECT B.BOEKOMSCHRIJVING, B.BOEKCODE -- or R.BOEKCODE
FROM PA
INNER JOIN PR ON PA.PAARDCODE = PR.PAARDCODE
INNER JOIN R ON R.REGISTERCODE = PR.REGISTERCODE
INNER JOIN B ON R.BOEKCODE = B.BOEKCODE
GROUP BY BOEKOMSCHRIJVING, B.BOEKCODE -- or R.BOEKCODE
ORDER BY BOEKOMSCHRIJVING Why is the option of B.BOEKCODE a lot slower compared to R.BOEKCODE?
Any example on encrypting a SSN Field using TDE?
[URL].....
Writing this code using a CTE or any other method which do not use a table variable or a temp table as the users do not permissions to run this code from the application because of their roles which are required for creating and dropping the temp tables. I replaced table variable in place of Temp tables however the query never completes, probably because of the number of rows.
SELECT A.[Entry No_], A.[G_L Account No_],A.[Gen_ Prod_ Posting Group],A.[Document No_],A.[Posting Date]
INTO temp1
FROM [G_L Entry] as A ,[G_L Account] as B
where A.[G_L Account No_]= B.[No_]
SELECT VE.[Entry No_],VE.[Document No_],VE.[Posting Date],VE.[Gen_ Prod_ Posting Group] , GLILER.[G_L Entry No_]
INTO temp2
[code]....
I need to create a TSQL to return MAX(Value) removing the first part and last part.
Example
I have these Varchar Reference code:
1.00001-Q1
2.00100-Q2
3.00005-Q4
I need to cut the string to find the max number excluding (Part1):
1.
2.
3.
And also excluding (Part3):
-Q1
-Q2
-Q4
In this case converting varchar to INT, the correct value that i want is: 00101
The middle part excluding (Part1 and Part3)
Then my final reference could be:
1.00101-Q1
or
2.00101-Q2
or
3.00101-Q4
I need to write SP which will call from front End to populate dropdown, But in dropdown uppermost value should be frequently used.
For example, My SP Select CountryID,Country Columns where i am displaying Country in dropdown. Now first time it will sort by ascending, next time if user selects US then US should be top and rest things after that.
Now If user selects Canada 3 times then result should sort as Canada,US, and rest countries.
if again US Selects 7 times then result should display as US,Canada,........
In short need to sort depending used previously. i.e. most popular first and so on......
DECLARE @DocHandle int
DECLARE @XmlDocument nvarchar(1000)
SET @XmlDocument = N'<Attributes><Attribute name="ABCD">50405</Attribute></Attributes>'
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument
SELECT * FROM OPENXML (@DocHandle, '/Attributes/Attribute')
WITH (name varchar(100),
Value varchar(100) '@name/text()[1]' )
EXEC sp_xml_removedocument @DocHandle
THe result of the above query is
nameValue
ABCDABCD
How do we get the value 50405 from the above xml