I have a table census data containing the number of people having ages ranging from 0 to 120 years. Year 0 is in a column called F_0 and year 120 is in a column called F_120 with everything in between.
For example, when I create a script for the table the column for year 0 appears as: [F_0] [numeric](38, 8) NULL
I've already created 2 summary columns (people 0 to 18 years old and people 19 to 59 years old).
I follow the same methodology to create a summary table for people from 60 to 100 years old, but all the calculated values are NULL.
So the following is successful:
[Under_19] AS (((((((((((((((((([F_0]+[F_1])+[F_2])+[F_3])+[F_4])+[F_5])+[F_6])+[F_7])+[F_8])+[F_9])+[F_10])+[F_11])+[F_12])+[F_13])+[F_14])+[F_15])+[F_16])+[F_17])+[F_18]) PERSISTED,
But this is not:
[Over_60] AS (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((([F_60]+[F_61])+[F_62])+[F_63])+[F_64])+[F_65])+[F_66])+[F_67])+[F_68])+[F_69])+[F_70])+[F_71])+[F_72])+[F_73])+[F_74])+[F_75])+[F_76])+[F_77])+[F_78])+[F_79])+[F_80])+[F_81])+[F_82])+[F_83])+[F_84])+[F_85])+[F_86])+[F_87])+[F_88])+[F_89])+[F_90])+[F_91])+[F_92])+[F_93])+[F_94])+[F_95])+[F_96])+[F_97])+[F_98])+[F_99])+[F_100])+[F_101])+[F_102])+[F_103])+[F_104])+[F_105])+[F_106])+[F_107])+[F_108])+[F_109])+[F_110])+[F_111])+[F_112])+[F_113])+[F_114])+[F_115])+[F_116])+[F_117])+[F_118])+[F_119])+[F_120]) PERSISTED,
Columns are obviously fixed, but not rows. I want to show this data using lables and SqlDataReader for report purpose like; Label1.text=dr("value16").toString( ) Label2.text=dr("value28").toString( ) Label3.text=dr("value31").toString( ) etc
Do you have any idea how i can do it or am I approaching it in the wrong way????
Hi. I have a Metrix include CheckNbr, InvoiceNbr, InvoiceAmount, ItemNo, ItemQty and Group by CheckNbr & InvoiceNbr. InvoiceNbr parent group is CheckNbr. Does anybody knows how to make a summary of InvoiceAmount when CheckNbr changed? I tried to use sum(InvoiceAmount, Group) but I got wrong amount, because the amount will duplicate count according to ItemNo record count.
I have column which stores People count based on department, Now I want to keep them in the batch of 1000, If the running summary of (No of people) from departments reached 1000 then it should start sum(no of people) from 0 to 1000
Is there any running summary kind of function which can start sum record with in range of 0-1000
For Ex. My Data stored like this Dept People Count CSE 200 IT 250 EEE 312 ECE 214 MEC 337
Batch Grouping Dept People Count BatchSum CSE 200 200 IT 250 450 EEE 312 762 ECE 214 976 MEC 337 337 (Note here since its crossing 1000, its resetting and starting summary)
I implemented this with While Loop & if condition, But its very slow, is there any other way to achieve it in better way.
Trying to create a summary table of current product table, so that they is only 1 line for each product (even if the product is in both warehouses - in this case take table for warehouse 1) Therefore standard case statement becomes:
CASE WHEN exists (select product from stock where warehouse = '02' and product = stock.product and product in (select product from stock where warehouse = '01')) ProductDescription = (select distinct long_description from stock where warehouse = '01' and product = stock.product) ELSE ProductDescription = (select distinct long_description from stock where product = stock.product and (warehouse = '01' or warehouse = '02')) END
Is there another way of writting this, instead of repeating the above code for each column in the table?
I have a query as below - Select TeamName , SUM(CASE WHEN Team_Date = '01/07/2007' THEN Team_Total ELSE 0 END) AS [Jul 07] , SUM(CASE WHEN Team_Date = '01/08/2007' THEN Team_Total ELSE 0 END) AS [Aug 07] , SUM(CASE WHEN Team_Date = '01/09/2007' THEN Team_Total ELSE 0 END) AS [Sep 07] , SUM(CASE WHEN Team_Date = '01/10/2007' THEN Team_Total ELSE 0 END) AS [Oct 07] , SUM(CASE WHEN Team_Date = '01/11/2007' THEN Team_Total ELSE 0 END) AS [Nov 07] , SUM(CASE WHEN Team_Date = '01/12/2007' THEN Team_Total ELSE 0 END) AS [Dec 07] , SUM(CASE WHEN Team_Date = '01/01/2008' THEN Team_Total ELSE 0 END) AS [Jan 08] , SUM(CASE WHEN Team_Date = '01/02/2008' THEN Team_Total ELSE 0 END) AS [Feb 08] , SUM(CASE WHEN Team_Date = '01/03/2008' THEN Team_Total ELSE 0 END) AS [Mar 08] , SUM(CASE WHEN Team_Date = '01/04/2008' THEN Team_Total ELSE 0 END) AS [Apr 08] , SUM(CASE WHEN Team_Date = '01/05/2008' THEN Team_Total ELSE 0 END) AS [May 08] , SUM(CASE WHEN Team_Date = '01/06/2008' THEN Team_Total ELSE 0 END) AS [Jun 08] FROM dbo.uView_DimHC_Team_Details_View1 where TeamParentID < '3' GROUP BY TeamName ORDER BYTeamName
This basically creates a table where I have team names as rows, month names as columns and team strength as each value. Now I want to add one row at the bottom which gives a summary which basically calculates all the values in that column. I am displaying this on web where I can do this using gridview but the problem is I am using the same gridview for 2-3 different queries which have different columns so the rowdatabound method can not be used. How can I get aggregate row at the end of table from this table?
...but apparently not me, I'm very new with this T-SQL stuff and am seeking the advice of the seasoned pros at this forum.
Description on my SQL-5 Environment:
Table I Sales: Prod_ID, Prod_DT, Sales_DT, Buyer_Name, Buyer_State
Table II Repairs: Prod_ID, Prod_DT, Sales_DT, Repair_DT
These 2 tables are joined by the common key Prod_ID & also and share the product's production & sales dates. What I would like to do is produce a rate summary similar to description below.
Important to remember that not all products experience repairs, so the basis for Sales_CNT needs to be the Sales Table, even thou Prod_DT also appears in Repairs Table.
It's simple enough for novice like me to produce 2 tables independently and then merge back those resulting tables into the single table output described above. But my question is how do I write a single SQL "SELECT" request that will produce the results into just a single table.
I am doing some work in microstrategy reports and using SQL data cube. I am creating a summary report (Counting on Unique ID) in MS where when I put in a particular attribute/column from cube it splits the data count down into 2 separate values that I have defined in the select case statement of the SQL query.
I want to work out a ratio of the count unique ID between these two values but can't do this in MS report as it is not a physical column but summary split of data.Is there a way I can do this in SQL? My summary looks something like this:
N E ====================== ========================= **Y** N **Y** N ========= ========== ========= ========= 570 140 89 56
Where the Y/N field is the one I have split down against the N/E column. I want to work out ratio between the 2 "Y" fields but in SQL. Here is a SQL example snapshot of my data:
Unique_ID New/Existing Application Attend_Interview ========= ======================== ================ 12554445 E Y 65766879 N N 53375654 N Y 44323224 E N 93656786 E Y
wondering if I might be able to do a procedure or similar or something more dynamic in SQL query?
I know similar questions have been asked but I wanted to try my luck that my issue is somehow different.
I am querying a database which has detail information (sales transactions) and is grouped by customer. I also have a table with one record for each customer with some historical sales information (summary information). The requirements for the report are to have the sums of the sales for each customer along with the historical data for that customer in the same row in the table. I haven't found a way to do this using one dataset and from what I've read, the current version doesn't support joining multiple datasets over a grouping field (customer).. or at all.
Hi;I am trying to write a rountine ( below ) that will go into a colum oftext data type ( fae.pmcommnt ) locate the word "to" and replace it.I have the routine below. I get no error messages, but it also seemsto do nothing :).Any clues would be greatly appreciated.ThanksSteve================================================== =============declare @ptrP intSELECT @ptrP = PATINDEX('%to%', pmcommnt)from fae where projid ='00013'declare @ptrPC binary(16)select @ptrPC = TEXTPTR(pmcommnt)from faeif( TEXTVALID ('fae.pmcommnt', @ptrPC ) > 0 )print 'works'print @ptrPUPDATETEXT fae.pmcommnt @ptrPC @ptrP 2 'JJ'select projid, pmcommnt from fae
I've been struggling with this for about 2 weeks now and can't seem to get any further.I have two tables: orders and orders_extended. They can be joined by the common orderid field (example SELECT * FROM orders JOIN orders_extended ON orders.order = orders_extended.orderid WHERE 1=1)I need to create a report that sums the following fields:
SELECT
CONVERT(VARCHAR(12), orderdate, 101) As orderdate , COALESCE ( CASE WHEN orders_extended.productprice < 0 THEN 'DISCOUNT' ELSE orders_extended.productnumber END , CASE WHEN orders_extended.productnumber LIKE '%AB%' THEN 'PRODUCTGROUPAB' ELSE orders_extended.productnumber END , CASE WHEN orders_extended.productnumber LIKE '%CD%' THEN 'PRODUCTGROUPCD' ELSE NULL END
[code]...
What I'm trying to accomplish is to get the total dolloar amount of sales for each day for each payment type on one line per productgroup.
I want to produce a summary table of amounts per status per user.
I have 2 tables:
Invoices:
Code: user_id, amount, status 1, £10, S 2, £20, P 3, £30, P 3, £40, E
Users:
Code: user_id, name 1, user A 2, user B 3, user C
And I want to produce a summary table like this:
Code: S P E Total user A £10 £10 user B £20 £20 user C £30 £40 £70
What I have is:
Code: SELECT Users.name, (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'S')), (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'P')), (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'E')), (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices .user_id AND (t1.status IN ('S','P','E'))) FROM Invoices LEFT JOIN Users ON Users.user_id = Invoices .user_id GROUP BY Invoices.user_id, Users.name ORDER BY Users.name
This does give me what I want, however the real situation has lots of status codes, many more fields in the Invoices table, hundreds of users and hundred of thousands of records in the Invoice table and I have run out of system memory.
I have a source table with App_Id, App_Type , Tier_Type, Date_Id as columns. I have a destination table with Date_Id, Total_App_Count, Booked_App_Type_Count, Approved_App_Type_Count columns.
I need to count the Total number of records , count of records with App_Type = "Booked" and count of records with App_Type = "Approved" and populate in my destination table.
What all the steps i need to create in SSIS to get this results ?
Working within SQL Server 2014 the old standby of shape2sql doesn't seem to be working. I even tried the trick of copying the DLL and renaming it. Still no joy.i need the alternatives to be free or near free, simple and easy to recommend to others. I'm checking out ogr2ogr currently.
I have some old code consisting of over 30 Union statements, unioning 7/1/15-8/1/15 data according to the below code. Each block is the same except for the date. How could I rewrite these 30 union statements into a more concise query?
Each client (people_id) has a distinct agency_id_number. So the code below totals the number of distinct clients/day and groups them by programs and facilities.
Sample data that you can use to test this report:
IF OBJECT_ID(N'tempdb..#sample_census') IS NOT NULL drop table #sample_census
Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.
I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.
Easy Peasy for you guys, any help would be appreciated.
I have below tables in my power pivot.Fact 1 & Fact 2 - connected directly to Mainframes - Data is from the same table broken up due to size of the data.Date Table - Relation ship between both the fact tables.How do i create a summary pivot to get the number of tasks that have been completed in each month.
i just clicked on Advanced mode in Column Group, and then in Row Group Side i set Fixed Data=true for first top static. I'm using local report not server report and i'm displaying that local report in Reportviewer. Now also its not working....
If on the source I have a new column, the script generated by SqlPackage.exe recreates the table on the background with moving the data into a temp storage. If the table is big, such approach can cause issues.
Example of the script is below: in the source project I added columns [MyColumn_LINE_1] and [MyColumn_LINE_5].
Is there any way I can make it generating an alter statement instead?
BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET XACT_ABORT ON; CREATE TABLE [dbo].[tmp_ms_xx_MyTable] ( [MyColumn_TYPE_CODE] CHAR (3) NOT NULL,
[Code] ....
The same script is generated regardless the table having data or not, having a clustered or nonclustered PK.
I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster?
I have think three ways to do it. 1. leave as it is. 2. 7 years partition on one server 3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A: (name phone house cost of house,zipcodecountystatecountry) -a view will later split this large varchar string based column b: is the source filename of the data load (varchar 256) ....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
I am trying to create a table that holds info about a user; with the usual columns for firstName, lastName, etc.... no problem creating the table or it's columns, but how can I "restrict" the values of my State column in the 'users' table so that it only accepts values from the 'states' table?
Hi, I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time. thanks. varun
Hi, All, I have agentID in product table. Now I add agentID column in transaction table. Now I want to copy all agentID from product table to transaction table based on the order_id in both table. Can you show me an example? Thanks Betty
I created a PDF that contains information from a few different tables using VB.Net(ie name, date, etc). Once the PDF is created I stored it in a new SQL table (Form_001) in the varbinary max column. My issue now is to populate the other colums in Form_001 that conatins the data from the other tables. Is it possible to populate the columns even though the names are different with data from the other tables?
Okay, after I got everything imported, I found that a few thousand columns had "Shifted" on me. So now I am trying to "Shift them over" to where they need to be.
I did this: INSERT INTO dbo.TABLENAME (COLUMN_NAME_TO_BE POPULATED) SELECT COLUM_NAME_OF_INFO_TO_BE_MOVED FROM dbo.TABLENAME WHERE MFG = 'MANUFACTURER_NAME' AND PN LIKE '8888888888' GO
I populated the PN column with 8888888888 to use as a reference point, and the MFG column already was populated with the correct name, so I was using those as my unique reference points.
Other columns that have the same MFG name are correct, so I have to use two unique identifiers to specify the actual data that needs to be moved.
It inserted 'NULL' in the whole table after I ran it in the Query Analyzer. It appeared to disregard the WHERE statement all together Any ideas on what I am doing wrong here?
Is there a way to move the data from one column to the next one over by specifying other WHERE criterias?
I need to find a table and column name from some given data. I know what data i want to edit, I just need to know where it is located and the database is too big to manually go through. It is Microsoft sql server 2000. Any help is appreciated.
