I have the following working TSQL query in ms SQL 2008 SELECT Date, COUNT(click) AS clicks, COUNT(sale) AS sales, count(lead) as leads FROM ( SELECT ClickDate as date ,ID AS click ,CAST(NULL AS int) AS sale , CAST(null as int) as lead FROM clicks UNION ALL SELECT Date,null, ID ,NULL FROM sales UNION ALL SELECT Date,...
Hello, I have a SQL Server 2008 database with 2 tables. These tables are Address and Store. These tables are defined as Address -------- ID (int) Line1 (nvarchar(255)) PostalCode (nvarchar(5)) City (nvarchar(255)) State (nvarchar(255)) Store ----- ID (int) LocationID (int) Name I am trying to find a list of stores that are located a...
Hi All, It has been quite a while since I used SQL Server and had been primarily working on Oracle DBs. I had grown accustomed to building materialized views off of my OLTP tables to speed up performance. I was excited to find information around what appeared to be the SQL Server equivalent of a Mat view...however, when I started read...
I have UserScores Table with data like this: Id userId Score 1 1 10 2 2 5 3 1 5 I would like to have a query or SQL block that can give me the following output Id userId Score 3 1 5 2 2 5 That is, I would like to pick rows that are unique by 'user id' that belong...
Hello, I wrote a pretty complex Stored Procedure that takes about 2 minutes to run (Is is a single Update statement). However, in this 2 minutes of time the stored procedure times out. I was wondering if there was a way to refresh the Stored Procedures timeout expiration so that I don't have to change the servers timeout from 30 seconds....
Using WITH OVER or by using other method, how to start new sequence number for another group of records? It is SQL Server 2005. E.g. how to get following ouput (I am talking about RowNum column in expected output)? Table: id name 100 A 200 B 300 C 200 B 200 B Expected ouput: RowNum id 1 100 1 200 2 ...
Hello everyone i have big query to calculation and counter by clinic ID SELECT nc.ID AS ClinicID, nc.Name AS ClinicName, SUM(cr.CountRecept * cs.Price) AS TotalPriceService, SUM(cr.TotalPaid) AS TotalPaid, SUM(cs.Price * cr.Company_Percentage / 100) AS TotalInsurance, SUM(cr.CountRecept) AS TotalCountRecept FROM ...
Comparing two codes below,both do the same,but with slighty differences: ALTER procedure [dbo].[SP_USUARIOS_UPDATE] @usu_ds varchar(100), @usu_dt_lst_log datetime, @usu_ds_senha varchar(255), @usu_ds_email varchar(100) as begin declare @usu_ID int; create table #TempUser ( UsuID int, S...
Hello all, I'm not an expert in SQL by any means, and am having a hard time getting the data I need from a query. I'm working with a single table, Journal_Entry, that has a number of columns. One column is Status_ID, which is a foreign key to a Status table with three values "Green", "Yellow", and "Red". Also, a journal entry is logged ...
I need some help with this procedure: What its supposed to do is try to insert a new user if there is no other with same NAME. If there is a already an user, it should rollback else commit. But it doesn't work, it commits anyway. Any suggestions? SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[SP_USUARIOS_INSERT...
Well interesting to me at least... Say I have two tables: myLookUpTable: lookupId | Name -------- ----- 1 Red 2 Green 3 Blue and InfoTable: infoId lookupId Amount ParentId ------ -------- ------ -------- 1 1 2 332 2 3 14 332 How would I write a query t...
Does anyone know the range comparison of the BETWEEN clause? if I have a datetime datatype, does the BETWEEN clause compare until hour/minute/second level? ...
This is the output I got from my query. You can see Monthly sales for 1997 is followed by monthly sales of 1998. (It might not show in proper format here but they are all in a row) Month Year Sales --------------------------- 1 1997 61258.07045 2 1997 38483.63504 3 1997 38547.21998 4 1997 53032.95254...
Hi All, I am using a CTE to recurse data I have stored in a recursive table. The trouble is I am trying to figure out how I can use "FOR XML" to build the desired xml output. I have a Table of Contents table I am recursing and I want to be able to use that data to generate the XML. Here is an example of what the data is simliar to: ...
I'd like a SELECT query to return specific value if the count of a nested subquery is 0 ... SELECT ( SELECT (CASE COUNT(*) = 0 THEN 'TRUE' ELSE 'FALSE' END) FROM List WHERE Status = 1 AND Deleted = 1 ) AS Status This does not work what is wrong with this syntax? ...
Hello, I am trying to find duplicate rows in my DB, like this: SELECT email, COUNT(emailid) AS NumOccurrences FROM users GROUP BY emailid HAVING ( COUNT(emailid) > 1 ) This is ok, it returns me the emailid and the found matches. Now what I wanna do is to compare the ID column to another table I have and set a column there with the cou...
I need to produce the column "required" in the following table using SQL without using loops and correlated sub queries. Is this possible in SQL 2008? Date Customer Value Required Rule 20100101 1 12 12 20100101 2 0 If no value assign 0 20100101 3 32 32 20100101 ...
Hi I would like to rotate a full table: LANGUAGE litAccept litDelete litErrorMsg .............. ------------------------------------------------- SPANISH Accept Delete Error has ocurred ENGLISH Aceptar Borrar Ha ocurrido un error ..... into something like this: LANGUAGE ENGLISH SPANISH ----------------------------...
Hi All, I have the following records depicting shifts in the Shifts table. ID, Description, Start Time, End Time 1, Morning, 06:00, 13:59 2, Afternoon, 14:00, 21:59 3, Night, 22:00, 05:59 I now need to be able to get the shift relevant to a passed time but get stuck with getting the record for the night shift where the time starts b...
I'm trying to update a date dimension table from the accounting years table of our ERP System. If I run the following Query: SELECT fcname FYName ,min(fdstart) YearStart ,max(fdend) YearEnd ,max(fnnumber) PeriodCount FROM M2MData01.dbo.glrule GLR GROUP BY fcname I get the following data: FYName YearStart...