Creating SELECT Query Which Involves Getting FIRST Occurrence
Apr 25, 2014
I have a problem creating a SELECT query which involves getting the FIRST occurrence, the SUMS and the LAST occurrence of a selected row. I have two tables A and B which has the following data:
Table A
----------------------------------------------------------------
|Item ID | Item Name | Item Description | Current Quantity |
----------------------------------------------------------------
----1------------Bolts-------------35"----------------20
----2-----------Paint----------GOLD Paint------------30
----3-----------Screws------------30"----------------40
----------------------------------------------------------------
and Table B
--------------------------------------------------------------
|Log ID | Item ID | Beginning | Add | Less | Ending | Date |
--------------------------------------------------------------
---1---------1----------30-------0-----10------20------04-04
---2---------2----------40-------10----10------40------04-04
---3---------3----------50-------5-----10------45------04-04
---4---------1----------20-------0-----0-------20------04-05
---5---------2----------40-------0-----10------30------04-04
---6---------3----------45-------0-----5-------40------04-04
-------------------------------------------------------------
and I have two datetime pickers, one FROM date picker and a TO date picker If a user selects FROM 04-04 TO 04-05...I want a select statement which will select the Item Name and Desc, the BEGINNING QUANTITY (in reference to the FROM DATE), the SUM of Adds and Less and the ENDING QUANTITY (in reference to the To Date) and then group by their Item ID
In this case, it will generate a table like this
----------------------------------------------------------
Item Name | Item Desc | Beginning | Add | Less | Ending |
-----------------------------------------------------------
--Bolts---------35"---------30---------0-----10-----20
--Paint-----Gold Paint------40---------10----20-----30
--Screws-------30"---------50---------5-----15-----40
"SELECT A.Item Name, A,Item Desc, B.Beginning=(select B.Beginning from Table B where A.Item ID = B.Item ID AND Date = From Date), SUM(B.ADD), SUM(B.LESS), B.Ending=(select B.Ending from Table B where A.Item ID = B.Item ID and Date = To Date) WHERE A.Item ID = B.Item ID and Date(B.Date)>From Date AND Date(B.Date)< To Date group by B.Item ID order by A.Item Name, Item Desc"
My query returns the right Item Name, Desc, the Sum of Add and Less.My problem is that I can't get the value of my beginning and ending.I think I have a problem in A.Item ID = B.Item ID. As I tried a fixed value condition, (B.Item ID = 2), it will get the right result), I'm guessing the program is not getting A.Item ID.
View 1 Replies
ADVERTISEMENT
Jan 25, 2008
hey everyone,I'm having trouble cranking out an appropriate SQL query for what I'm trying to do. I'm trying to add a ratings system into a website, I have 2 relevant tables.Files = table listing all uploaded filesRatings = table listing all ratings for filesThe two DB's are theoretically linked by the fact that the ratings table has a field for "fileID" which matches up to "fileID" in the files table. The way the page works, is that there is a querystring in the URL "filename" where "filename" is a field within the files table. So basically I'm looking for the AVG rating.ratings, where rating.fileID = files.fileID, and the appropriate files.fileID is gained from files.fileID WHERE files.filename = request.querystring["filename"]holy crap, i hope that made sense to someone here. i'm not great at explaining this. My problem is I want the AVG ratings.rating from the table ratings.... where ratings.fileID = files.fildIDWhat I have so far, that works except for the AVG part is below. This code will pull only the ratings for the appropriate file, the problem is whenever I try to add "AVG(something)" to it, the query fails.SELECT files.FID AS Expr1, files.filename, ratings.FID, ratings.ratingFROM files INNER JOIN ratings ON files.FID = ratings.FIDWHERE (files.filename = @filename) @filename value is gained from the URL querystringany ideas?
View 1 Replies
View Related
Aug 31, 2015
I am trying to run an update statement against a vendor's database that houses HR information. If I run a regular select statement against the database with the following query, it returns without error:
SELECT "QUDDAT_DATA"."QUDDAT-INT", "NAME"."INTERNET-ADDRESS", "QUDDAT_DATA"."QUDFLD-FIELD-ID", "QUDDAT_DATA"."QUDTBL-TABLE-ID"
FROM "SKYWARD"."PUB"."NAME" "NAME", "SKYWARD"."PUB"."QUDDAT-DATA" "QUDDAT_DATA"
WHERE ("NAME"."NAME-ID"="QUDDAT_DATA"."QUDDAT-SRC-ID") AND "QUDDAT_DATA"."QUDTBL-TABLE-ID"=0 AND "QUDDAT_DATA"."QUDFLD-FIELD-ID"=16 AND "QUDDAT_DATA"."QUDDAT-INT"=11237When I try to convert it into an
[Code] ....
I am assuming I am receiving this error because it doesn't know where to find QUDDAT-INT? How can I fix that?
The "QUDDAT-INT" column houses the employee number. So in the case of the SELECT query above, I am testing against a specific employee number.
View 9 Replies
View Related
Nov 6, 2013
Have a need to scan a large table to see if a set of criteria have ever been met.
If/when the scan hits its first record meeting the criteria, the scan can be abandoned.
Is there some syntax/option that accomplishes this?
Right now, I am doing a SELECT with criteria against the table and the @@ROWCOUNT gives me a zero, or non-zero value.
But that methodology means that the SELECT has to execute against the entirety of the table.
I'd like to abandon the SELECT as soon as it detects a first record meeting the criteria.
View 3 Replies
View Related
Sep 12, 2013
I need to query the count of an occurrence in a given period of time, so I created this query. But it does not give me any results.
SELECT TOP 1000
o.[ID]
,o.[TimeOfOrder]
,x.[StreeLine1]
FROM [SC].[dbo].[bvc_Order] o
FULL JOIN SC.dbo.xmlAddressRead x
ON o.ID= x.id
WHERE DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour, -48, GETDATE()))) < 48
GROUP BY x.[StreeLine1], o.ID, o.TimeOfOrder
HAVING COUNT(x.[StreeLine1])>1
Then I change the query slightly and I ask it to show me the ones that are going to '599 Ships Landing Way' and it gives me 356 results! The Query doesn't crash, but it doesn't give me the results I need. What did I do incorrectly?
SELECT TOP 1000
o.[ID]
,o.[TimeOfOrder]
,x.[StreeLine1]
FROM [SC].[dbo].[bvc_Order] o
FULL JOIN SC.dbo.xmlAddressRead x
ON o.ID= x.id
WHERE DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour, -48, GETDATE()))) < 48
AND x.[StreeLine1]='599 Ships Landing Way'
I use Microsoft SQL 2008
View 12 Replies
View Related
Jun 18, 2015
I am using MS SQL 2012 and have a pretty simple table dbo. Migration Breakdown with sample data as follows.
DepartDateTime ZoneMovement
2015-06-26 14:00:00.000 6 to 4
2015-06-26 14:00:00.000 11 to 7
2015-06-26 15:30:00.000 9 to 6
2015-06-26 21:00:00.000 7 to 3
2015-06-27 08:01:00.000 7 to 4
[code]....
What I am trying to do is parse the data set to find out when we have more than three like movements ex. 3 to 10 within ANY rolling 72 hour period. I have looked at the SQL Window Functions OVER with a ROW | RANGE subclause, but I can't find out how to tackle this rolling 72 hour business.
View 9 Replies
View Related
Feb 22, 2005
Hi there,
I'm writing a stored procedure for a searching sql , below is the codes
CREATE PROCEDURE stp_SearchShop
@ShopName varchar(50)
AS
select shop_id,shop_name from shop where shop_name like %@ShopName%
GO
Sql Server returns error : Incorrect Syntax Code near '@ShopName' .
May i know how to implement LIKE SQLs in stored procedure? thanks a lot
View 2 Replies
View Related
Feb 8, 2008
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?
View 9 Replies
View Related
May 7, 2008
Here's my table:
-------------------------------
Orders
-------------------------------
UserID | Cost | Item
-------------------------------
Users can make several different orders. I want to only select the row with the first occurrence of a userID. For example:
-------------------------------
Orders
-------------------------------
UserID | Cost | Item
-------------------------------
1 | 3.00 | Box
1 | 6.00 | Candy
2 | 7.00 | Towel
3 | 5.00 | Flower
3 | 6.00 | Candy
Should only select:
1 | 3.00 | Box
2 | 7.00 | Towel
3 | 5.00 | Flower
View 8 Replies
View Related
May 12, 2006
This one shouldn't be too hard to do, just can't seem to figure it out.
I have two tables
tNames which contains 'ID' and 'Name'
tLocations which contains 'ID' and 'Location'
each ID may be associated with many Locations
I want to run a query to display these results:
[ID, Name, Location]
but only to return the first Location associated with that ID (or the max, min, I don't really care as long as it's only one result)
right now I have
Code:
SELECT tNames.ID, tNames.Name, tLocation.Location
FROM tNames INNER JOIN tLocations
ON tNames.ID = tLocations.ID
but this returns a new row for each different Location of the same ID
Thanks.
View 3 Replies
View Related
Mar 4, 2014
I have a sample table as follows:
IDITEM_EFFECTIVE_DTITEM_TERMAMOUNT
106738118-Jan-142141-200
106738102-Feb-142141-274.82
108766810-Jan-1421411238
108766810-Jan-142141-1238
108766810-Jan-142141309.5
108766810-Jan-142141-309.5
109754020-Jan-142141-3814.16
109754025-Feb-142141-100
110337727-Feb-142141-200.6
110337731-Jan-142141-19.24
I need to find rows from the above table which has the first occurence of the corresponding ID. This the output will contain the rows that will have first occurence of the following ids.
1067381, 1087668, 109754, 1103377.
How does one handle this. I have not been able to come up with the sql yet.
View 4 Replies
View Related
Mar 6, 2007
I have a field that contains data like "mary;john;dog;cat;frog" I want to query the field for the values between each of the semi colins. I was going to use Substring with a patIndex but I can only patIndex the first semi colin and not the 2nd 3rd.... I was wondering if someone could tell me the right was of doing this or point me in the right direction.
Thank you,
View 1 Replies
View Related
Nov 27, 2006
Dear folks,
create table temptable(eno, ename) as select eno, ename from emp.
here the problem is it is asking for the datatype for the temporary table.
is it not possible to create the temp table without providing the datatypes?
thank you very much.
Vinod
View 8 Replies
View Related
Jan 25, 2006
Hello,I am attempting to build a MS SQL query that will return data from"today"; today being current day 8:00AM-10:00PM today. My goal is toreturn the data from a table that is written to throughout the day, thequery will provide the current grade of service in our call center.I am having difficulty defining my where clause:- I can accomplish my goal my statically defining my 'date between' asthe actual date and time (not ideal)- I can accomplish the second part of my date using CURRENT_TIMESTAMP;but I am unable to define the starting pointHere is where I am thus far:/* We are going to count the total calls into each queue from start ofbusiness today (8:00AM) to now */select COUNT(Result) as "Total Sales Calls Offered" fromdbo.QueueEncounterwhere Direction='0'andQueueID='1631'and/* This is where I get lost */Time between DATEPART(day, GETDATE()) and DATEPART(day, GETDATE())Clearly the last line returns zero as there are no calls between thesame date range. How can I add to that line, or write this to work?Any thoughts?Thanks for the help.-Chris
View 4 Replies
View Related
Mar 20, 2007
Hi,I have a need to create a table detailing the ID of all contacts and thelast time they were contacted. This information is stored in 2 tables,'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'in the 'activity' table).I guess I need some sort if iteration to go through each contact and findfind the last activity that took place against each of them (there many bemore than 1 activity against each contact) and then place the output valuesinto the new table.Can anyone show me how to go about this?Thanks!
View 2 Replies
View Related
Jul 20, 2005
HiDose any body know why a temporary table gets deleted after querying it thefirst time (using SELECT INTO)?When I run the code bellow I'm getting an error message when open the temptable for the second time.Error Type:Microsoft OLE DB Provider for SQL Server (0x80040E37)Invalid object name '#testtable'.-------------------------------------------------------------------------------------------cnn.Execute("SELECT category, product INTO #testtable FROM properties")'---creating temporary TestTable and populate it with values from anothertableSET rst_testt = cnn.Execute("SELECT * from #testtable") '----- openingthe temporary TestTableSET rst_testt2 = cnn.Execute("SELECT * from #testtable") '----- ERRORopening the temporary TestTable for the second time (that where the erroroccurred)rst_testt2.Close '---- closing table connectionSET rst_testt2 = nothingrst_testt.Close '----- closing table connectionSET rst_testt = nothingcnn.Execute("DROP TABLE #testtable") '------ dropping the temporaryTestTable'-----------------------------------------------------------------------------------------But when I create the temp table first and then INSERT INTO that table somevalues then it is working fine.'-----------------------------------------------------------------------------------------cnn.Execute("CREATE TABLE #testtable (category VARCHAR(3), productVARCHAR(3))")cnn.Execute("INSERT INTO #testtable VALUES('5','4')")SET rst_testt = cnn.Execute("SELECT * from #testtable") '----- openingthe temporary TestTableSET rst_testt2 = cnn.Execute("SELECT * from #testtable") '----- openingthe temporary TestTable for the second timerst_testt2.Close '----- closing table connectionSET rst_testt2 = nothingrst_testt.Close '----- closing table connectionSET rst_testt = nothingcnn.Execute("DROP TABLE #testtable") '------ dropping the temporaryTestTable'-----------------------------------------------------------------------------------------Does any body know why the first code (SELECT INTO) is not working where thesecond code it working?regards,goznal
View 4 Replies
View Related
May 15, 2012
In the table you can create below, each set of order number records should contain one record with the OrderNoIndicator field set to 1. i.e. OrderNo 7032357. Is there a way of finding(without using the count function) sets of records that don't contain an OrderNoIndicator set to 1 i.e. OrderNo 7088650 all the OrderNoIndicator fields are set to 2...
use LEIS
If OBJECT_ID ('dbo.temp') IS NOT NULL
DROP TABLE dbo.temp
GO
CREATE TABLE dbo.temp
( OrderNo bigint not null,
OrderNoindicator tinyint not null
[Code] .....
View 14 Replies
View Related
Jul 20, 2005
Hi,In the datawarehouse DB (under MS commerce server 2002) a table storesthe referer domain name. Table structure is likerefererdomainid <binary>,domainInternalFlag<0/1>,refererDomainName<varchar>e.g.<binary>|0|unknown<binary>|1|google.com<binary>|1|yahoo.com<binary>|1|google.com<binary>|1|google.com<binary>|1|google.com<binary>|1|altavista.commy problem is to build a query (using this table only) whichrefererDomainName has the max occurrence and how many times. As in thetable above it is google.com and 4 times.Can anyone help me.Thanks in advance.
View 1 Replies
View Related
May 21, 2007
I have the following query:
SELECT Notifications.[TimeStamp])
FROM dbo.vwGrantsMaster LEFT OUTER JOIN
dbo.CoFundNotifications ON dbo.vwGrantsMaster.GrantFMBId = dbo.CoFundNotifications.GrantsFMBId
It is returning multiple records from Notifications table since it allows multiple entries under a single GrantFMBId. For example for a single GrantFMBId there can be multiple TimeStamp. When retrieved all are appearing even distinct key word is used.
What I am looking is that to get only the first occurrence of a record for a GrantFMBId from Notifications table.
Any help?
Thank you in advance.
View 3 Replies
View Related
Sep 6, 2006
Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return.
This is implemented as a stored procedure
declare @OwnerIdent varchar(7)
set @OwnerIdent='A12345B'
SELECT table1.val1 FROM table1 INNER JOIN table2
ON table1. Ident = table2.Ident
WHERE table2.Ident = @OwnerIdent
'Now for each result of the above I need to run the below query
SELECT Clients.Name , Clients.Address1 ,
Clients.BPhone, Clients.email
FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey
WHERE Growers.PIN = @newpin)
'@newpin being the result from first query
Any help appreciated
View 4 Replies
View Related
Jul 23, 2005
I have four total tables.Table One (Documents)- List of Documents. Each record has two fieldsrelated to this issue. First field (Document_ID) is the ID of thedocument, second field is the ID of the record (Task_ID) it isassociated to in Table Two.Table Two (Activities)- List of activities. Each record has two fieldsrelated to this issue. First field (Activity_ID) is the ID of theactivity, the second field (Group_ID) is the ID of the record it isassociated to in Table Three.Table Three (Groups) - List of groups. Each record has two fieldsrelated to this issue. First field (Group_ID) is the ID of the group,the second field (Stage_ID) is the ID of the record it is associated toin Table four.Table Four (Stages)- List of Event Stages. Each record has two fieldsthat is related to this issue. The first field (Stage_ID) is the ID ofthe stage of an event, the second record is the ID number associated tothe event. This last ID is a known value.20000024 = the Event IDI'm trying to come up with a list of Documents from the first tablethat is associated to an Event in the Fourth table.Query Analyzer shows no errors within the script. It just doesn'treturn any data. I know that it should, if it does what I'm wanting itto do.SELECT Document_ID FROM Documents as A where ((SELECT Event_ID FROMStages as D WHERE (D.Stage_ID = (SELECT Stage_ID FROM Groups as C WHERE(C.Group_ID = (SELECT Group_ID FROM Activity as B WHERE (B.Activity_ID= A.Activity_ID))))))= '20000024')
View 2 Replies
View Related
Jul 20, 2005
Hi All,I am new to MS SQL Server.I am using MS SQL 2000.I have a problem increating a table by using Select command.I have table called "test"and i want to create another table with the same structure and rows.Itried with the following commandcreate Table test1 as select * from test;But it give an syntax error.I have tried the same command in Oraclebut i was working.Does MS SQL 2000 Server supports this kind of Query.Please help me to solve the problem or any other methods to performthis operation.Thanks in AdvanceKevin
View 2 Replies
View Related
Sep 18, 2001
How to locate last occurrence of character in string?
View 1 Replies
View Related
Jun 25, 2002
I have a problem finding the last occurrence of a character in a string.
The charindex seems just able to search from left to right..
View 4 Replies
View Related
Sep 4, 2015
I have text column .I want to find out first occurance of string based on logic.I defiend Text with examples and also mentioned expected result.I coloured the text in word document,due to some reasons not displaying same here.Attached as image below texts to understand more clear.
TEXT 1: 'ABNAGENDRACSURENDRADJITHENDRAXNARENDRABVEERNDARAXDRMNDRAXRVINDRABNAGENDRACSURENDRADJITHEN'
From the above text1, I want to get “AXNARENDR”.
Based on logic defined below:
First I have to search for string “A” Then next to ‘A’ it should not be “B” or “C” or “D”.It can be anything other thing these three.Combination of “A” otherthan “B” or “C” or “D”
In the example text I defined “A”,”X” defined three times .I want to capture few characters from the first occurrence of the string
i.e AXNARENDR (TEXT1 I defined “A” with 4th occur
TEXT 2:
'ABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDRADJITHENABN
AGENDRACSURENDRADJITHENAYENDGHRABVEERNDARAXDRMNDRABNAGENDRACSURENDRADJITHENAYRVINDR'
From the above text2, I want to get “AYENDGHR”.
TEXT 3:
'ABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDR
ADJITHENABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDR
ADJITHENABNAGENDRACSURENDRADJITHABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHEN
ABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDRADJITHAZENIVKHRABVEERNDARAXDRMNDRAYRVINDR AZNHKLMN'
From the above text3, I want to get “AZENIVKHR”.
View 9 Replies
View Related
Oct 16, 2015
generate output as specified below. i need to get 1st value when record is duplicated in hierarchyval column
IF OBJECT_ID('tempdb..#test') IS NOT NULL
drop table #test
create table #test
(
hierarchyid int
,hierarchyval int
[code]...
View 3 Replies
View Related
Oct 24, 2013
I am having following data:
c1c2c3
122401
1221072
122833
122793
122813
122314
[code]....
I want to count number of occurrence in column c3. i.e., count 1 occurred value 9 times, count 2 occurred value 3 times, count 3 occurred value 3 times.
Output
c1p1p2p3
122933
View 6 Replies
View Related
Jun 5, 2007
What function(s) can be used to find the mode of data? I have a column that is populated with codes and I'd like to summarize the data by the code that occurs the most frequently. Any help is appreciated!!
View 5 Replies
View Related
Mar 8, 2006
Hi,I am trying to join two tables, one has multiple records per ID, theother is unique record per ID.The two tables look like belowAID date var1 var 2001 1/1 10 20001 2/1 12 15001 3/1 17 18002 2/1 13 10002 3/1 12 14............BID001002003004....The join conditions are1. table A's ID = table B's ID2. take the variables associated with the 1st occrrence of the datevariable in table A'sI have the following SQL code but the it didn't work. It says thecolumns are ambiguously defined. Anyone can help me? Greatly appreciateit!PROC SQL;CONNECT TO ORACLE (USER="&user" PASS="&pass" PATH="@POWH17"BUFFSIZE=25000);CREATE TABLE actvy1_1st AS SELECT * FROM CONNECTION TO ORACLE(SELECTactvy1.ID,actvy1.var1,actvy1.var2,actvy1.datefromA actvy1,(select a.ID,min(a.date) mindatefrom A a,B cwhere a.ID =c.IDgroup by ID) bwhere actvy1.ID =b.IDand actvy1.date =b.mindateorder by ID);disconnect from oracle;quit;
View 3 Replies
View Related
Sep 2, 2015
I have a weekly report with multiple products due for manufacture on the same day. viz. -
02/09/15 Product A
02/09/15 Product B
03/09/15 Product A
04/09/15 Product C
04/09/15 Product X
I need to highlight any product that appears more than once in the report. e.g. Product A.
View 7 Replies
View Related
May 27, 2015
I have [income]- (REVENUE) table of accounts, each account can have multiple instances for one month. how can I find the last occurrence of all the accounts in a month (and then summarize all of these latest occurrences)?
account revenue revenue_date
1 100 2010-01-01
1 200 2010-02-03
1 300 2010-02-04
1 1000 2010-06-04
2 100 2010-02-01
2 200 2010-03-03
2 1300 2010-07-04
2 11000 2010-08-04
3 2100 2010-01-01
3 2200 2011-02-03
3 300 2011-02-04
3 221000 2011-06-04
4 222 2010-02-01
4 2222 2012-03-03
View 10 Replies
View Related
Jul 23, 2005
I've got a view that creates a parent child relationship, this view isused in Analysis Services to create a dimension in a datastore. Thisquery tends to deadlock after about 10 days of running smoothly. Onlyway to fix it is to reboot the box, I can recycle the services for aquick fix but that usually only works for the next 1-2 times I call theview.This view is used to create a breakdown of the bill-to locations fromContinent-Global Region-Country-Sub Region-State/Province- City-ZipCodeYes, I know that sounds crazy, but it was a requirement.So why would I get a deadlock on a SELECT Query? Is there a way to setthe Isolation level to Repeatable Read for a view?Here is the view code:CREATE View dbo.vwBillToas-- US ZipCodeSelect 'Parent'=z.City+' ('+ ISNULL(RTRIM(z.State_shrt), '0') +cast(IsNull(z.US_Region_wk,0) as varchar) + ')',z.Zipcode_WK as 'Child',z.ZipCode_WK as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_US zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToWherez.US_Region_wk IS NOT NULLGroupby z.City,z.ZipCode_WK,US_Region_wk, z.State_shrtUnion--CitySelect 'Parent'=z.State_Long+' ('+cast(IsNull(z.US_Region_wk,0) asvarchar)+')',z.City as 'Child',z.City + ' ('+ ISNULL(RTRIM(z.State_shrt), '0') +cast(IsNull(z.US_Region_wk,0) as varchar) + ')' as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_US zWherez.US_Region_wk IS NOT NULLGroupby z.State_Long,z.City,z.State_shrt,z.US_Region_wkUnion-- Canada ZipCodeSelect 'Parent'=z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') +')',z.Zipcode_WK as 'Child',z.Zipcode_WK as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_CAN zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToGroupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrtUnion--CitySelect 'Parent'=z.Province_Long,z.City as 'Child',z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') + ')' as'Child_ID'Fromdbo.DIM_POSTAL_CODES_CAN zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToGroupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrtUnion-- Canada ProvinceSelect 'CANADA',Province_Long,Province_LongFromdbo.DIM_POSTAL_CODES_CANGroupby Province_LongUnion-- CountrySelect t.Region_NK,c.Country_Name,c.Country_NameFromdbo.DIM_COUNTRY cInnerJoin dbo.DIM_WORLD_REGION tOnc.Region_WK=t.Region_WKWherec.Country_Name Is Not NullGroup by t.Region_NK, c.Country_NameUnion-- SubRegionSelect c.Country_Name,sr.US_Region_Name,sr.US_Region_NameFromdbo.DIM_US_REGION srInnerJoin dbo.DIM_COUNTRY cOnsr.Country_wk=c.Country_WKGroupby c.Country_Name, sr.US_Region_NameUnion--RegionSelect sr.US_Region_Name,c.State_Long,c.State_Long+' ('+cast(c.US_Region_wk as varchar)+')'Fromdbo.DIM_US_REGION srInnerJoin dbo.DIM_POSTAL_CODES_US cOnsr.US_Region_WK=c.US_Region_WKGroupby sr.US_Region_Name, c.State_Long,c.US_Region_wkUnion-- ContinentSelect Null,Region_NK,Region_NK[color=blue]>From dbo.DIM_WORLD_REGION[/color]WhereRegion_NK Is Not Null
View 1 Replies
View Related
Jan 17, 2014
I'm trying to find the most succinct way to get the last occurrence of April 1st given a date.
At the moment I'm using this:
DECLARE @Date DATE = '20131217'
SELECT CONVERT(DATE, CAST(DATEPART(YEAR,
IIF(
--If we're at the start of a year
--we'll need to go back a year
DATEPART(MONTH, @Date) IN (1,2,3),
DATEADD(YEAR, - 1, @Date),
@Date
)) AS VARCHAR(4)) + '0401')
View 8 Replies
View Related
