Transact SQL :: Converting Union Statements Into Census Row Totals
Aug 20, 2015
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
Code Snippet SELECT Account.New_DistributorAccountNumber, Account.New_NameonBill, ActivityPointer.Subject, ActivityPointer.CreatedOn AS ExprX FROM Account INNER JOIN ActivityPointer ON Account.AccountId = ActivityPointer.RegardingObjectId UNION SELECT Account_1.New_DistributorAccountNumber AS Expr1, Account_1.New_NameonBill AS Expr2, ActivityPointer_1.CreatedOn AS Expr19, ActivityPointer_1.Subject AS Expr20 FROM Account AS Account_1 INNER JOIN Contact AS Contact_1 ON Account_1.AccountId = Contact_1.AccountId INNER JOIN ActivityPointer AS ActivityPointer_1 ON Contact_1.ContactId = ActivityPointer_1.RegardingObjectId
gives the error mentioned above. Each select statement, when run separately, works fine. Any clues?
I following two queries First query is SELECT HomeTeam, 1 AS Pld, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Won, CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Lost, HomeScore AS Scored, AwayScore AS Against, HomeScore - AwayScore AS Agg, CASE WHEN HomeScore > AwayScore THEN 3 ELSE 0 END AS Pts FROM tblFixtures WHERE (CompID = 1) AND (HomeScore IS NOT NULL)
and second query is
SELECT AwayTeam, 1 AS Pld, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Won, CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Lost, AwayScore AS Scored, HomeScore AS Against, AwayScore - HomeScore AS Agg, CASE WHEN HomeScore < AwayScore THEN 3 WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Pts FROM tblFixtures WHERE (CompID = 1) AND (HomeScore IS NOT NULL) what i need is I want to create another joint result of two queries which adds up all the totals from each of the other two queries.
I have two union statements that I need to join to each other. Then I need to join that result to another table. So-
[(TBL-A UNION TBL-B) JOINED TO (TBL-C UNION TBL-D)] JOINED TO TBL-E
Is this a subquery thing, or something else? I have not been able to figure out how to make it work! The unions I have made work fine though, on their own.
Wondering if there is a physical or realistic limitation to the numberof UNION statements I can create in a query? I have a client withapprox 250 tables - the data needs to be kept in seperate tables, but Ineed to be filtering them to create single results sets. Each tableholds between 35,000 - 150,000 rows. Should I shoot myself now?lq
This is a query that produces a table with garbage data, but (I think) will get the point across of what I need. SQL Server 2008
Create Table SanitationGarbage ( saleid int ,projectname varchar(200) ,typeofsale varchar(200)
[code]....
Now my select query below does not group the data like I need it to, nor does it show a total row like I need it to. How does this need to be written so the data is displayed in the proper formatting?
Select projectname ,Count(saleID) As [Total Sales] ,Count(case when typeofsale = 'Final' then saleID else null end) As [Final Sales] ,Count(case when typeofsale = 'Pending' then saleID else null end) As [Pending Sales] FROM SanitationGarbage GROUP BY projectname order by projectname asc
I have a ms-sql 2012 for this task. I read that running totals / sum over is better supported here.
There are customers with an account where they can insert or withdraw cash. When they insert, the inserted amount will get a date where it expires. After the expiration date, the cash becomes unavailable.
I'm not sure how to design the tables to meet my needs, but this is my best guess. And below is the first problem in the view.
I'm trying to generate a cumulative total with a rolling window of 13 time periods..Previously I was able to do left outer join to the same table 13 times to add the quantity field but it appears with the migration to SQL Server 2014, that many left outer joins is not possible (query that would run in 3 mins is taking well over 15 hours now)..
I've got some SQL that works as far as returning a recordset from a series ofUNION statements.viz:SELECT whateverUNION thisUNION thatUNION otherNow I want to group and sum on it's results.Started out tying:SELECT * FROM(union stuff)....but couldn't even get past the syntax check.Where I'm headed is a sort of pivot table presentation of some hours dataassociated with various projects with a column for each of six date ranges.Bottom line: can somebody give me a pointer to the syntax needed to wrap thoseUNION statements and then select/group/sum their results?--PeteCresswell
I have some data grouped in a table by a certain criteria, and for each group it is computed a subtotal for the group. Of the values from each of the group, I want to create a grand total on the report by adding every subtotal from each group.
Example: ... .... Group1 Value 10 20 Sub Total 1: 30
Group2 Value 15 25 Sub Total 2: 40
Now, I would like to be able to add subtotal 1 (30) to subtotal 2 (40) and my grand total would be 70. Can I accomplish this task in SSRS?
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 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,
I need to breakdown some information with just initials (sometimes 2, sometimes 3 if the initials are already used) SQL Server throws the error of:
Msg 125, Level 15, State 4, Line 1
Case expressions may only be nested to level 10.
What should I alter, or how should I write this query so I can use all of the needed case statements? (their are actually about 24 when statements, but this is just to get a working example to display)
Select case when employee_name Like 'Jorge Jones' Then 'JJ' when employee_name Like 'Mike Mikes' Then 'MM' when employee_name Like 'Albert Alvarez' Then 'AA' when employee_name Like 'Hernandez-Sotata' Then 'HS'
So I'm thinking if I can have multiple statements within the CASE-THEN..or do I have to CASE out each individually? Kind of like this....
CASE WHEN [AddressType] = 'M' THEN [MailingAddress].[Address1] [MailingAddress].[Address2] [MailingAddress].[City] [MailingAddress].[State] [MailingAddress].[Zipcode] WHEN [AddressType] = 'D' THEN [DefaultAddress].[Address1] [DefaultAddress].[Address2] [DefaultAddress].[City] [DefaultAddress].[State] [DefaultAddress].[Zipcode]
I have been providing sales data for a few months now from a table that is set up like this:
Date WorkDay GasSales EquipmentSales
9/1/2006 1 100.00 200.00
9/4/2006 2 50.00 45.00
etc.
As can be seen, the data is daily, i.e., on the first workday of September we sold one hundred dollars in gas and two hundred dollars in equipment. On the second workday of September we sold fifty dollars in gas and forty-five dollars in equipment.
Now, however, the data I have to pull from is cumulative. So, using the last table as an example it would look like this:
Date_WorkDay_GasSales_EquipmentSales
9/1/2006 1 100.00 200.00
9/4/2006 2 150.00 245.00
etc.
To make things more complicated, the powers that be wanted this data presented in this fashion:
Total Sales:
1_2_etc.
300.00 95.00 etc.
So, I have been doing a pivot on a CRT to get the data to look like I want. The code is like this:
with SalesCTE (Month, WorkDay, [Total Sales])
as
(
SELECT
datename(month, cag.date),
cag.WorkDay AS [Work Day],
sum(cag.sales_gas + cag.sales_hgs) AS [Total Sales]
FROM CAG INNER JOIN
Branch ON CAG.[Oracle Branch] = Branch.OracleBranch
group by cag.date, cag.WorkDay
)
select * from SalesCTE
pivot
(
sum([Total Sales])
for WorkDay
in ([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])
) as p
So, my question is:
How do I get the data to give back daily totals instead of the cumulative amounts for each workday? If the query was a simple one, I'd do something like
select [1] as [Day 1], [2]-[1] as [Day 2], [3]-[2] as [Day 3], etc.
but the query is far from normal, with the CRT and the pivot. I can't seem to get it to work how I'd like.
Any advice/answers? Thanks in advance!!!
P.S. I don't know how to get it to quit with the freakin' smileys.... I suppose you can figure out what my code is really supposed to look like above. Needless to say, it doesn't include a devil face and a damn music note...
I want to create a raw SQL resultset for outputting to Excel with some artificial headers transposed over the top of the 2nd part of the Union's column names. The first part of the Union will be the Headers. Like this, the space to the left of the topmost columns is preferably empty ....
I just want the text above as a 3 line header and there won't be any values obviously. Then the 2nd query will be joined to the above with a Union all. The 2nd query has all the same column names as what will be given in the first set above. What is the SQL Syntax for doing so? Do I have to use a from clause?
I am trying to generate a script to drop 15 tables which have dependencies across the database. Is there a script that could generate the drop stataments based on the child table first , parent table last strategy?
I am not sure if i am looking correctly at the deadlocks but i see deadlocks between two select statements.These statements are being run through an application. Below is the table schema from where the select is being performed
CREATE TABLE [dbo].[CMS_LOCKS7]( [PARENTID] [int] NOT NU, --we have a non clustered index on this column [CHILDID] [int] NOT NULL, --we have a non clustered index on this column [ISMEMBER] [int] NOT NULL, -- we have a non clustered index on this column [ORDINAL] [int] NULL,-- we have a non clustered index on this column
STEP1: CREATE TABLE Trace(Statement VARCHAR(MAX)) INSERT INTO Trace VALUES('select * from Account'),('select * from Account') ,('Select LastUpdated,Lastdeleted,LastInserted from History'), ('Insert into Account Select lastUpdated from History'),('Delete from OldAccount where LastUpdatedId=3'),('Delete from OldAccount where LastDeletedId=3'),('Delete from OldAccount where LastInserted=3'),('DROP TABLE BMP')
[code]....
now,when i run step3 ; i wanted to see if there is actually a delete or insert or select or update happens but as i used like %% (matching characters) i am getting all names matching with the % % , example row 7 in above is there a way i can use any wildcards and only find if there is actual delete, actual insert, actual select, actual update statement happening.
I have a table that keeps track of all changes that were performed in an application. There is a column called "old value" and column called "new value". There are some values in the application that don't require data therefore the "old value" or "new value" values can be empty. These columns are an nvarchar data type because the value can be text or numbers or dates. An example is "ReceivedDate". There is a report that is generated based on this table.
What is happening is the query in the report dataset is adding dates when it should be displaying empty. They query is using "CASE/WHEN/THEN". What I need is "When the column is "RecievedDate" and it is not null then convert it to a date". This is for formatting purposes.
This is an example of the table:
UpdateColumn Old Value New Value ReceivedDate 7/8/2015 5:00:00 AM ReceivedDate 7/8/2015 12:00:00 AM 7/9/2015 5:00:00 AM ReceivedDate 7/9/2015 12:00:00 AM
So, the first time it was updated there was no value but it was replaced with July 8, 2015 and so on.This is what the report is displaying
This is the query:
CASE UpdateColumn ... WHEN 'ReceivedDate' THEN (replace(convert(varchar(11),CONVERT ( date, oldvalue ), 106), ' ', '-') ) ... I tried adding CASE UpdateColumn ... WHEN 'ReceivedDate' IS NOT NULL THEN (replace(convert(varchar(11),CONVERT ( date, oldvalue ), 106), ' ', '-') ) ...
I am working on an app that getting quite a few deadlocks due to delete statements. I have turned on the sql trace flags and pulled the offending delete statements out of the ERRORLOG and trying to mesh those up with the indexes defined on the table, etc. looking to see if there is anything that can be done strictly from the db side (no app code change) to reduce/eliminate these deadlocks. I have ran some tests/played around with RCSI and even disabling lock escalation but neither have improved my results.
What I have done is to search the errorlog file for DELETE FROM Tablename, output those matching lines, then sort of normalize the literal values to # or XYZ, open in Notepad++, removed trailing whitespace + dups and sort to come up with these results for the unique list of offending T-SQL statements (a LOT easier to read in text editor so sending screen cap.
I have a database which will be generated via script. However there are some tables with default data that need to exist on the database.I was wondering if there could be a program that could generate inserts statments for the data in a table.I know about the import / export program that comes with SQL but it doesnt have an interface that lets you copy the insert script and values into an SQL file (or does it?)
I need to list out all the procedures which have select queries and does not use NOLOCK hint. Please note that there are more than 2000 sps and some sps have single select some have multiple, some does not have select queries at all. I need to find out only the ones which does not use NOLOCK hint in a select query.
I had to enable identity_insert on a bunch of tables and I have already done that. Now I need to modify my insert into select * from statements to include column list names along with identity columns for select as well as insert statements. The DDL is same but they are both different databases.There are almost 100 tables that it needs to be modified. Is there a way we can generate scripts for insert and select for each individual table along with their column lists including the identity column?
